'Invalid column name [ColumnName]' on a nested linq query

Last update

After alot of testing, I realised that if i ran the same query over the same dataset (in this case a Northwind) table on SQL 2000 and SQL 2005, I get two different results. On SQL 2000, i get the error that's in the question. On SQL 2005, it succeeds.

So I've concluded that the query generated by linqpad doesn't work on sql 2000. To reproduce this, run:

OrderDetails
    .GroupBy(x=>x.ProductID)
    .Select(x=>new {product_id = x.Key, max_quantity = x.OrderByDescending(y=>y.UnitPrice).FirstOrDefault().Quantity}).Dump();

on a Northwind DB in sql 2000. The sql translation is:

SELECT [t1].[ProductID] AS [product_id], (
    SELECT [t3].[Quantity]
    FROM (
        SELECT TOP 1 [t2].[Quantity]
        FROM [OrderDetails] AS [t2]
        WHERE [t1].[ProductID] = [t2].[ProductID]
        ORDER BY [t2].[UnitPrice] DESC
        ) AS [t3]
    ) AS [max_quantity]
FROM (
    SELECT [t0].[ProductID]
    FROM [OrderDetails] AS [t0]
    GROUP BY [t0].[ProductID]
    ) AS [t1]

Original Question

I've got the following query:

ATable
.GroupBy(x=> new {FieldA = x.FieldAID, FieldB = x.FieldBID, FieldC = x.FieldCID})
.Select(x=>new {FieldA = x.Key.FieldA, ..., last_seen = x.OrderByDescending(y=>y.Timestamp).FirstOrDefault().Timestamp})

results in:

SqlException: Invalid column name 'FieldAID' x 5
SqlException: Invalid column name 'FieldBID' x 5
SqlException: Invalid column name 'FieldCID' x 1

I've worked out it has to do with the last query to Timestamp because this works:

ATable
.GroupBy(x=> new {FieldA = x.FieldAID, FieldB = x.FieldBID, FieldC =开发者_开发问答 x.FieldCID})
.Select(x=>new {FieldA = x.Key.FieldA, ...,  last_seen = x.OrderByDescending(y=>y.Timestamp).FirstOrDefault()})

The query has been simplified. The purpose is to group by a set of variables and then show the last time this grouping occured in the db.

I'm using Linqpad 4 to generate these results so the Timestamp gives me a string whereas FirstOrDefault gives me the whole object which isn't ideal.

Update

On further testing I've noticed that the number and type of SQLException is related to the class created in the groupby clause. So,

ATable
.GroupBy(x=> new {FieldA = x.FieldAID})
.Select(x=>new {FieldA = x.Key.FieldA, last_seen = x.OrderByDescending(y=>y.Timestamp).FirstOrDefault()})

results in

SqlException: Invalid column name 'FieldAID' x 5

---

You should use the SQL profiler to check if the SQL generated against the 2 databases is different.

We have only had two problems where something ran on SQL Server 2005 but not on SQL Server 2000. In both cases it was due to the lack of support for Multiple Active Result Sets (MARS) in SQL Server 2000. In one case it led to locking in the database, in the other case it led to a reduction of performance.