在嵌套LINQ查询“无效的列名[的ColumnName]' [英] 'Invalid column name [ColumnName]' on a nested linq query

问题描述

最近更新

测试了很多之后，我意识到，如果我跑过去相同的数据集相同的查询（在这种情况下，罗斯文对SQL 2000和SQL 2005）的表，我得到两个不同的结果。
在SQL 2000年，我获得这就是问题中的错误。
在SQL 2005，它成功。

所以，我的结论是，linqpad生成的查询不会对SQL 2000的工作要重现此操作运行

 订单明细
 .GroupBy（X => x.ProductID）
。选择（X = >新建{= PRODUCT_ID x.Key，max_quantity = x.OrderByDescending（Y => y.UnitPrice）。.FirstOrDefault（）数量}）转储（）; 
  

在罗斯文数据库在SQL 2000的SQL转换为：

  SELECT [T1]。[产品id] AS [PRODUCT_ID]，（
选择[T3]。[数量] 
 FROM（
选择TOP 1 [T2]。[数量] 
从[订单明细] AS [T2] 
 WHERE [T1]。[产品ID] = [T 2]。[产品id] 
 ORDER BY [T2] [单价] DESC 
）AS [T3] 
）AS [max_quantity] 
 FROM（
选择[T0]。[产品id] 
 FROM [订单明细] AS [T0] 
 GROUP BY [T0]。[产品id] 
）AS [T1] 
  

原始的问题

我有以下查询：

  ATable 
 .GroupBy（X =>新建{FIELDA = x.FieldAID，FieldB = x.FieldBID，FieldC = x.FieldCID}）
 。选择（X =>新建{FIELDA = x.Key.FieldA，...，last_seen = x.OrderByDescending（Y =方式> y.Timestamp）.FirstOrDefault（）时间戳}）
  

结果：

 的SQLException ：无效的列名称FieldAID'×5 
的SQLException：无效的列名称FieldBID'×5 
的SQLException：无效的列名称FieldCIDX 1 
  

我已经计算出它与上次查询到时间戳这样做，因为这个作品：

  ATable 
 .GroupBy（X =>新{FIELDA = x.FieldAID，FieldB = x.FieldBID，FieldC = x.FieldCID}）
。选择（X =>新建{FIELDA = x.Key.FieldA，...，last_seen = X。 OrderByDescending（Y => y.Timestamp）.FirstOrDefault（）}）
  

查询已被简化。目的是通过组一组变量，然后显示上一次出现这种分组的分贝发生。

我用Linqpad 4产生这些结果使时间戳给了我一个字符串，而FirstOrDefault给我的整个对象是不理想的。

更新结果
在进一步测试我注意到的SQLException的数量和类型有关，GROUPBY子句中创建的类。
所以，

  ATable 
 .GroupBy（X =>新建{FIELDA = x.FieldAID} ）
。选择（X =>新建{FIELDA = x.Key.FieldA，last_seen = x.OrderByDescending（Y => y.Timestamp）.FirstOrDefault（）}）
  

结果

 的SQLException：无效列名'FieldAID'×5 
  

解决方案

您应该使用检查SQL事件探查器，如果对2个数据库生成的SQL是不同的。

我们只有在那里SQL Server 2005上跑的东西两个问题而不是SQL Server 2000的在这两种情况下，它是由于缺乏支持多活动结果集（MARS）SQL Server 2000中在一种情况下它导致在数据库锁定，在它导致降低的性能的另一种情况。

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.

这篇关于在嵌套LINQ查询“无效的列名[的ColumnName]'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！