EF core Linq groupby和具有sum count-无法翻译，将在本地进行评估

Question

继.net core EF core之后，Linq无法翻译，将在本地进行评估。你能给我个建议吗？

var temp1= (from so in context.OrderShippingOrders
group so by so.OrderId into g
where g.Count(x=> x.IsSent == true ) == g.Count()
select new {
g.Key
}
);
query = (from o in context.Orders
join s in temp1
on o.Id equals s.Key
select o
);

LINQ表达式"join AnonymousObject _o in｛from Order o in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1[ECommerce.API.Models.Order]) where ([o].ShopId == __queryObj_ShopId_Value_0) join <>f__AnonymousType181 1 s in｛from IGrouping2 g in {from OrderShippingOrder so in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1[ECommerce.neneneba API.Models.OrderShippingOrder](orderby[so].OrderId asc，[so].OrderId asc select[so]=>GroupBy([so].OrderId，[so](｝其中(｛from OrderShipping Order x in[g]where([x].IsSent==True(select[x]=>Count((｝={[g]=>计数((｝(选择新的<>f_AnonymousType181(Key = [g].Key)} on [o].Id equals [s].Key orderby EF.Property(?[o]?, "Id") asc select new AnonymousObject(new [] {Convert(EF.Property(?[o]?, "Id"), Object)}) => Skip(__p_1) => Take(__p_2) => Distinct()} on Property([o.OrderDetails], "OrderId") equals Convert([_o].GetValue(0), Nullable1('无法翻译，将在本地进行评估。

Answer 1

如果可能，升级到EF Core 2.1(或2.2(以获得改进的LINQ GroupBy翻译。

2.1版本之前，在EF Core中，GroupBy LINQ运算符将始终在内存中求值。现在，我们支持在大多数常见情况下将其转换为SQL GROUP BY子句。

在以前的EF Core版本中您无能为力。

升级后，为了获得SQL事务，必须将GroupBy查询修改为使用中间投影和条件Sum，而不是像下面这样的条件Count：

var temp1 = (from so in context.OrderShippingOrders
group new { SendCount = so.IsSent ? 1 : 0 } by so.OrderId into g
where g.Sum(x => x.SendCount) == g.Count()
select new
{
g.Key
}
);

(不幸的是，更自然的group so和g.Sum(x => x.IsSent ? 1 : 0)不能翻译，这就是为什么我们需要group new { SendCount = so.IsSent ? 1 : 0 }和g.Sum(x => x.SendCount)(

---

p。S.如果您有从Order到OrderShippingOrder的集合导航属性(类似于public ICollection<OrderShippingOrder> Shipping { get; set; }(，那么您可以避免所有这些GroupBy的复杂性，并简单地使用：

var query = context.Orders
.Where(o => o.Shipping.Count(so => so.IsSent) == o.Shipping.Count());