EF核心Linq groupby并具有总计数-无法翻译，将在本地进行评估 [英] EF core Linq groupby and having sum count - could not be translated and will be evaluated locally

问题描述

在.net核心EF核心之后，Linq无法翻译，将在本地进行评估.你能给我个建议吗?

Following .net core EF core, Linq cannot be translated and will be evaluated locally. Can you please give me an advise?

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表达式'从{来自IGrouping 2 g in {from OrderShippingOrder so in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable 1 [ECommerce.API.Models.OrderShippingOrder]的{从价o的订单o(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable 1[ECommerce.API.Models.Order]) where ([o].ShopId == __queryObj_ShopId_Value_0) join <>f__AnonymousType18 1 s)加入匿名对象_o orderby [so] .OrderId asc，[so] .OrderId asc选择[so] => GroupBy([so] .OrderId，[so])}其中({来自[g]中的OrderShippingOrder x，其中[[x] .IsSent == True)选择[x] => Count()} == {[g] => Count()})选择新的<> f__AnonymousType18 1(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), Nullable 1)'无法翻译，将在本地进行评估.

The LINQ expression '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 s in {from IGrouping2 g in {from OrderShippingOrder so in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1[ECommerce.API.Models.OrderShippingOrder]) orderby [so].OrderId asc, [so].OrderId asc select [so] => GroupBy([so].OrderId, [so])} where ({from OrderShippingOrder x in [g] where ([x].IsSent == True) select [x] => Count()} == {[g] => Count()}) select new <>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)' could not be translated and will be evaluated locally.

推荐答案

如果可能，请升级到EF Core 2.1(或2.2)以获得改进的

If possible, upgrade to EF Core 2.1 (or 2.2) in order to get improved LINQ GroupBy translation.

在2.1版之前，在EF Core中，始终会在内存中评估GroupBy LINQ运算符.现在，在大多数情况下，我们支持将其转换为SQL GROUP BY子句.

Before version 2.1, in EF Core the GroupBy LINQ operator would always be evaluated in memory. We now support translating it to the SQL GROUP BY clause in most common cases.

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

There is nothing you can do in previous EF Core versions.

升级后，为了获得SQL转换，必须将GroupBy查询修改为使用中间投影和条件Sum而不是条件Count，像这样:

After upgrading, in order to get SQL transation, the GroupBy query must be modified to use intermediate projection and conditional Sum instead of conditional Count like this:

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)的原因)

(unfortunately the more natual group so and g.Sum(x => x.IsSent ? 1 : 0) does not translate, that's why we need the group new { SendCount = so.IsSent ? 1 : 0 } and g.Sum(x => x.SendCount))

P.S.如果您具有从Order到OrderShippingOrder的集合导航属性(类似于public ICollection<OrderShippingOrder> Shipping { get; set; })，则可以避免所有这些GroupBy复杂性并只需使用:

P.S. In case you have collection navigation property from Order to OrderShippingOrder (something like public ICollection<OrderShippingOrder> Shipping { get; set; }), then you can avoid all these GroupBy complications and use simply:

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

这篇关于EF核心Linq groupby并具有总计数-无法翻译，将在本地进行评估的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！