4

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
Shiraz Bhaiji
  • 64,065
  • 34
  • 143
  • 252
Joe
  • 11,147
  • 7
  • 49
  • 60
  • 1
    Why are you using an anonymous type to store one field? (`last_seen`) – recursive Mar 08 '11 at 23:43
  • ah yeah, as i said in my question the query has been simplified (quite significantly) to cut away to crap that doesn't relate. running the simple query still gives me the error. that select statement actually contains alot more than one field which then is processed by orderby, thenby, select and finally a dump. – Joe Mar 08 '11 at 23:45
  • 3
    Maybe you should post a simple schema to recreate. I've tried creating a table with a timestamp column as described and can't reproduce. – Joe Albahari Mar 09 '11 at 01:56
  • 1
    i've tried this on the northwind table `OrderDetails .GroupBy(x=>x.ProductID) .Select(x=>x.OrderByDescending(y=>y.UnitPrice).FirstOrDefault().UnitPrice).Dump();` vs `OrderDetails .GroupBy(x=>x.ProductID) .Select(x=>x.Max(y=>y.UnitPrice)).Dump();` and both work. Trying to reproduce it on that table and present the results. – Joe Mar 09 '11 at 03:06
  • i noted that on the northwind table, if i do `OrderDetails .GroupBy(x=>x.ProductID).Select(x=>new {product = x.Key, data = x}).Dump();` then `data` is of type `IGrouping` where as on my table, it's of type `IGrouping`. Somehow, I think if i was to make mine `Int32` instead of `Int32?`, something will work out. – Joe Mar 09 '11 at 03:54
  • @Joe Albahari, i got to work on a similar dataset. I noticed that the one that it works on and mine are sql 2005 and 2010 respectively. But the one that it doesn't work on is sql 2000. So could that be the issue there? – Joe Mar 09 '11 at 04:37
  • Have you examined the SQL translation? What does it say? – Joe Albahari Mar 10 '11 at 03:19
  • I tried to make sense of the sql but my sql querying isn't that strong so i can't really spot the issue. but this seems to be reproduceable on your sample northwind given the query i've outlined in the updated question. it should be easy to get the sql query from there. to make life a bit easier, i've also upated the question with the sql translation. – Joe Mar 10 '11 at 04:12
  • 1
    I get the same error on a Sql2000 database error, but Max works so, you could try changing your original query to be ATable .GroupBy(x=> new {FieldA = x.FieldAID, FieldB = x.FieldBID, FieldC = x.FieldCID}) .Select(x=>new {FieldA = x.Key.FieldA, ..., last_seen = x.Max(y=>y.Timestamp)}) – sgmoore Sep 02 '11 at 17:05

1 Answers1

0

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.

Shiraz Bhaiji
  • 64,065
  • 34
  • 143
  • 252