'Invalid column name [ColumnName]' in linq subquery

Question

Last update

After many tests, I realized that if I ran the same query in the same data table (in this case in Northwind) on SQL 2000 and SQL 2005, I get two different results. On SQL 2000, I get the error in the question. In SQL 2005, it succeeds.

So, I came to the conclusion that the query created by linqpad does not 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(); 

based on Northwind DB in sql 2000. Translating sql:

 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 have 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}) 

leads to:

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

I decided that this was due to the last Timestamp request, because it 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 request is simplified . The goal is to group by a set of variables and then show the last time this grouping occurred in db.

I use Linqpad 4 to generate these results, so Timestamp gives me a string, while FirstOrDefault gives me an entire object that is not perfect.

Update
During further testing, I noticed that the number and type of SQLException are related to the class created in the groupby clause. In this way,

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

leads to

 SqlException: Invalid column name 'FieldAID' x 5 

Answer 1

You should use the SQL profiler to check if the SQL created against the two databases is different.

We had only two problems when something worked on SQL Server 2005, but not on SQL Server 2000. In both cases, this was due to the lack of support for multiple sets of active results (MARS) in SQL Server 2000. In one case, this led to a lock in the database; in another case, it led to a decrease in performance.