EF核心的Linq groupby和有sum count-不能被翻译，将被本地评估。[英] EF core Linq groupby and having sum count - could not be translated and will be evaluated locally

问题描述

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
                     );

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.

推荐答案

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

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.

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

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
             }
);

(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. 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());