我正在将数据库从使用ODBC的MySQL迁移到MS SQL,我想要将SQL查询“翻译”为LINQ。有人能帮我吗(它应该对每个位置的Charge列求和,并按月份分组结果):
SELECT
sum(case when Location="Location1" then Charge else 0 end) as Location1,
sum(case when Location="Location2" then Charge else 0 end) as Location2,
sum(case when Location="Location3" then Charge else 0 end) as Location3,
MAKEDATE(YEAR(OrderTime),DAYOFYEAR(OrderTime)) AS date FROM Sales
GROUP BY YEAR(OrderTime),MONTH(OrderTime)
ORDER BY OrderTime DESC
?
输出应该像这样:
Location1 | Location2 | Location3 | date
编辑:
我尝试使用这里的LINQ示例:
var query = context.log_sales
.GroupBy(c => c.OrderTime)
.Select(g => new
{
Date = g.Key,
Location1 = g.Where(c => c.Location == "Location1").Sum(c => c.Charge) ?? 0,
Location2 = g.Where(c => c.Location == "Location2").Sum(c => c.Charge) ?? 0
}).ToList();
这几乎是我所需要的,但还需要按年份分组,我不知道如何做到这一点。