LINQ生成错误的查询，误差未知列（VB.NET的MySQL） [英] LINQ Generating Bad Query, Error Unknown Column (VB.NET MySQL)

问题描述

我在使用VB.NET和MySQL MVC2开发，遇到了一个问题想简单的SQL查询转换为LINQ。

I'm developing in MVC2 using VB.NET and MySQL and ran into a problem trying to convert a simple SQL query to LINQ.

SQL查询：

SELECT Location_Number, sum(Column1) as totalC1, sum(Column2) as totalC2
FROM MyTable 
WHERE year = 2010 and month = 8
GROUP BY Location_Number 
ORDER BY Location_Number 

LINQ查询：

LINQ Query:

From r In MyTable _
Where r.Year = 2010 And r.Month = 8 _
Group r By LN = r.Location_Number Into l = Group _
Order By LN _
Select New With { _
  .Location_Number = LN, _
  .DepositCount = l.Sum(Function(r) r.Column1), _
  .OtherCount = l.Sum(Function(r) r.Column2) _
}

产生的错误是：

在执行命令定义时出错。详情请参阅内部异常。

An error occurred while executing the command definition. See the inner exception for details.

内的例外是：

未知列'GroupBy1.K1在字段列表

Unknown column 'GroupBy1.K1' in 'field list'

下面是LINQ生成的SQL：

Here is the SQL generated by LINQ:

SELECT  `Project1`.`Location_Number`,   `Project1`.`C1`,   `Project1`.`C2`  
FROM (
    SELECT  `GroupBy1`.`A1` AS `C1`,   `GroupBy1`.`A2` AS `C2`,   `GroupBy1`.`K1` AS `Location_Number`
    FROM (
            SELECT  Sum(`Column1`) AS `A1`,   Sum(`Column2`) AS `A2`  
            FROM `MyTable` AS `Extent1`  
            WHERE (`Extent1`.`Year` = @p__linq__0) AND (`Extent1`.`Month` = @p__linq__1)   
            GROUP BY   `Extent1`.`Location_Number`
    ) AS `GroupBy1`
) AS `Project1`   
ORDER BY   `Location_Number` ASC

望着那查询其容易发现什么导致了错误。简单地说，最内层查询只返回两列，而查询的正上方是试图选择3，因此未知列错误。那么，为什么会出现这种情况？有什么不对我的LINQ查询？

Looking at that query its easy to spot whats causing the error. Simply, the most inner query only returns 2 columns, while the query right above it is trying to SELECT 3, thus the Unknown Column Error. So why is this happening? What is wrong with my LINQ query?

感谢您

推荐答案

这是一个MySQL连接错误：的 http://bugs.mysql.com/bug.php?id=46742

It is a MySQL connector bug: http://bugs.mysql.com/bug.php?id=46742

时的不固定去年（6.3.5）版本。

Is not fixed in last (6.3.5) version.

这篇关于LINQ生成错误的查询，误差未知列（VB.NET的MySQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！