c# - Entity Framework 中的 LINQ to SQL 查询问题

Question

我有以下查询在 LINQ to SQL 中运行良好。现在我想把它改成Entity Framework

var _sale = from emp in setupEmployees
            join sales in vwSaleTargets on emp.EmployeeID equals sales.EmployeeID
            join price in vwPeriodPricings
               on new { sales.SKUID, sales.PeriodID } 
               equals new { SKUID = (int?)price.SKUID, PeriodID = (int?)price.PeriodID }
            join sk in setupSKUs on sales.SKUID equals sk.SKUID
            join br in setupBrands on sk.BrandID equals br.BrandID
            where emp.EmployeeID == 123 && sales.StartDate.Year == 2012 
            select new { emp, sales, price, sk, br };

var lstSale = _sale.ToList(); //to avoid n+1 queries in case of grouping
var sale2 = from x in lstSale
            group x by new { x.sk, x.emp } into grouping
            select new 
            {
                 EmployeeName = grouping.Key.emp.EmployeeName,
                 SKUID = grouping.Key.sk.SKUID,
                 SKUName = grouping.Key.sk.Title,
                 MonthSale =(double?)grouping
                          .Where(x => x.sales.StartDate.Month == 2 && 
                                      x.sales.StartDate.Year == 2012)
                          .Select(t=>t.sales.SalesQuantity)
                          .Sum(t=>t.Value)?? 0,
                 MonthSaleValue = (double?)grouping
                          .Where(x => x.sales.StartDate.Month == 2 && 
                                      x.sales.StartDate.Year == 2012)
                          .Sum(x => x.sales.SalesQuantity * x.price.ExFactoryPrice)  
                             ?? 0,
            };
Console.WriteLine(sale2.OrderBy(x => x.SKUName).ToList());

在 Entity Framework 中它给我这样的结果

Name SKUID SKUName MonthSale MonthSaleValue 
EMP1  36    SKU1     113     61375.95 
EMP1  17    SKU2     113     6656.83 
EMP1  18    SKU3     113     9984.68 
EMP1  19    SKU4     113     15169.12 

在 L2S 中我得到这样的正确结果

Name SKUID SKUName MonthSale MonthSaleValue 
    EMP1  36    SKU1     74     40193.1 
    EMP1  17    SKU2     113     6656.83 
    EMP1  18    SKU3     461     40733.96
    EMP1  19    SKU4     2     268.48

问候

Answer 1

作为寻找答案的方法...

要按照@Jon Skeet 的建议进行诊断，您需要简化它并查看您在 lstSale 中获得的内容，以将 LINQ to SQL 与 EntityFramework 进行比较。

因此，以下内容可能会有所帮助(不一定在语法上正确，因为我没有检查所有源对象，但我只是查看查询并在您可以的地方将其简化)

var _sale = from emp in setupEmployees
            join sales in vwSaleTargets on emp.EmployeeID equals sales.EmployeeID
            join price in vwPeriodPricings
               on new { sales.SKUID, sales.PeriodID } 
               equals new { SKUID = (int?)price.SKUID, PeriodID = (int?)price.PeriodID }
            join sk in setupSKUs on sales.SKUID equals sk.SKUID
            where emp.EmployeeID == 123 && sales.StartDate.Year == 2012 && sales.StartDate.Month == 2
            select new 
            { 
                EmployeeName = emp.EmployeeName, 
                StartDate = sales.StartDate,
                SalesQuantity = sales.SalesQuantity, 
                ExFactoryPrice = price.ExFactoryPrice, 
                SKUID = sk.SKUID,
                SKUName = sk.SKUName 
            };

var lstSale = _sale.ToList(); //to avoid n+1 queries in case of grouping

// Run through lstSale here
foreach(var item in lstSale)
{
  Console.WriteLine(item);
}

var sale2 = from x in lstSale
            group x by new { x.SKUID, x.EmployeeName } into grouping
            select new 
            {
                 EmployeeName = grouping.Key.EmployeeName,
                 SKUID = grouping.Key.SKUID,
                 SKUName = grouping.SKUName,
                 MonthSale =(double?)grouping
                          .Where(x => x.StartDate.Month == 2 && 
                                      x.StartDate.Year == 2012)
                          .Select(t=>t.SalesQuantity)
                          .Sum(t=>t.Value)?? 0,
                 MonthSaleValue = (double?)grouping
                          .Where(x => x.StartDate.Month == 2 && 
                                      x.StartDate.Year == 2012)
                          .Sum(x => x.SalesQuantity * x.ExFactoryPrice)  
                             ?? 0,
            };
Console.WriteLine(sale2.OrderBy(x => x.SKUName).ToList());

更改(可能并非全部有效):
1. 删除了品牌，因为它没有在第二个查询中使用(您可以在第一个查询中将其用作连接，但如果它是限制的一部分，则不能添加到新类型)
2. 简化了第一个查询生成的匿名类型中包含的内容 - 如果您只使用 emp/sales/price 的一部分，那么它可能会更清楚发生了什么
3. 在第一部分(你在第二部分做什么)添加了对 SalesMonth 的限制，因为这可能会减少你的数据，提高性能并让你专注于实际出错的地方(我已经保留了第二个 SalesMonth 限制)
4. 我假设 SKUID 是 sk 分组的相关部分，并不是所有的对象都是必需的