将SQL查询转换为Linq
将SQL查询转换为Linq
提问于 2020-07-06 21:37:35
我在将SQL查询转换成Linq时遇到了一个问题(特别是在"group by“部分)。
SQL：
SELECT
year(ord.OrderDT) as ODate1,
month(ord.OrderDT) as ODate2,
cus.Company as Company,
sum(ord.InvoiceTotal) as TotalRate,
count(ord.InvoiceTotal) as CountOrd
FROM [dbo].[Customer] as cus
join [dbo].[Order] as ord on cus.CustomerID=ord.CustomerID
group by year(ord.OrderDT), month(ord.OrderDT), Company
order by ODate1 desc, ODate2 desc, Company我试过了：
var result = (from cus in dbf.Customer
join ord in dbf.Order on cus.CustomerId equals ord.CustomerId
select new {
Year=ord.OrderDt.Year,
Month=ord.OrderDt.Month,
Company=cus.Company,
Rate=ord.InvoiceTotal
} into t1
group t1 by new {t1.Year, t1.Month,t1.Company} into t2
select new
{
Year=t2.FirstOrDefault().Year,
Month=t2.FirstOrDefault().Month,
Customer=t2.FirstOrDefault().Company,
TotalRate=t2.Sum(c=>c.Rate)
}
).Take(10).ToList();但是我得到的错误提示是".FirstOrDefault()‘无法被翻译。或者以一种可以翻译的形式重写查询“。如果我试图去掉"FirstOrDefault“，那么我就没有用于t2的智能感知
实体：
public Customer()
{
public int CustomerId { get; set; }
public string Company { get; set; }
}
public Order()
{
public int OrderNo { get; set; }
public int CustomerId { get; set; }
public DateTime OrderDT { get; set; }
public decimal InvoiceTotal { get; set; }
}回答 2
Stack Overflow用户
回答已采纳
发布于 2020-07-06 22:50:45
尝试下一条select语句：
select new
{
Year=t2.Key.Year,
Month=t2.Key.Month,
Customer=t2.Key.Company,
TotalRate=t2.Sum(c=>c.Rate)
} Stack Overflow用户
发布于 2020-07-06 23:12:04
最后的查询如下所示。
var result = (from cus in dbf.Customer
join ord in dbf.Order on cus.CustomerId equals ord.CustomerId
select new {
Year=ord.OrderDt.Year,
Month=ord.OrderDt.Month,
Company=cus.Company,
Rate=ord.InvoiceTotal
} into t1
group t1 by new {t1.Year, t1.Month,t1.Company} into t2
orderby t2.Key.Year descending, t2.Key.Month descending,t2.Key.Company ascending
select new
{
Year=t2.Key.Year,
Month=t2.Key.Month,
Customer=t2.Key.Company,
TotalRate=t2.Sum(c=>c.Rate),
Count=t2.Count()
}
).ToList();页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62757311
复制相关文章
相似问题
腾讯云开发者
