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 Query:
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 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) _
}
The error generated is:
An error occurred while executing the command definition. See the inner exception for details.
The inner exception is:
Unknown column 'GroupBy1.K1' in 'field list'
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
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?
Thank you