SQL查询到LINQ到实体框架

Question

我想知道是否甚至可以将下面的SQL查询写为LINQ to Entity语句。以下是我试图找出的一个真实世界问题的简化示例：

Select 
c.CustomerID, 
c.CustomerName, 
(Select count(p.ProductID) from Products p 
    where p.CustomerID = c.CustomerID and p.Category = 'HomeAppliance') as ApplianceCount, 
(Select count(p.ProductID) from Products p 
    where p.CustomerID = c.CustomerID and p.Category = 'Furnishing') as FurnishingCount 
from Customer c 
where 
c.CustomerMarket = 'GB' 
order by c.CustomerID desc; 

任何建议，将不胜感激。需要考虑LINQ to Entity的性能，因为它涉及到检索很多行。

Answer 1

喜欢的东西（假设明显的情况下）：在LINQ到实体

var res = await (from c in dbCtx.Customers 
       where c.CustomerMarket = "GB" 
       let homeCount = c.Products.Where(p => p.Category = "HomeAppliance").Count() 
       let furnCount = c.Products.Where(p => p.Category = "Furnishing").Count() 
       orderby c.CustomerID descending 
       select new { 
        CustomerID = c.CustomerID, 
        CustomerName = c.CustomerName, 
        ApplianceCount = homeCount, 
        FurnishingCount = furnCount 
       }).ToListAsync(); 

性能将需要考虑，因为这将涉及大量的检索行。

之后，您需要确认生成的SQL是否合理（帮助获取的列数不是您需要的最佳方法），然后再考虑服务器运行该SQL的性能。

Answer 2

是的，这是可能的：

customers 
    .Where(cust => cust.CustomerMarket == "GB") 
    .Select(cust => new 
    { 
     cust.CustomerId, 
     cust.CustomerName, 
     ApplianceCount = products 
      .Where(prod => prod.CustomerId == cust.CustomerId && prod.Category == "HomeAppliance") 
      .Select(prod => prod.ProductId) 
      .Count(), 
     FurnishingCount = products 
      .Where(prod => prod.CustomerId == cust.CustomerId && prod.Category == "Furnishing") 
      .Select(prod => prod.ProductId) 
      .Count(), 
    }); 

这里既有customers和products是IQueryable<T> S中的相应的类型的。