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

本文是小编为大家收集整理的关于EF核心的Linq groupby和有sum count-不能被翻译,将被本地评估。的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

跟随.NET核心EF核心,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表达式"加入AnonymousObject _O在{from of of of of over o值(microsoft.entityframeworkcore.query.internal.entityQueryQueryBore 1[ECommerce.API.Models.Order]) where ([o].ShopId == __queryObj_ShopId_Value_0) join <>f__AnonymousType18 1 S中{来自Igrouping 2 g in {from OrderShippingOrder so in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable 1 [ecommerce.api.models.Ordershiper]) OrderBy [So] .OrderID ASC,[SO] .ORDID ASC选择[SO] => GroupBy([SO] .ORDERID,[SO])}其中({来自来自[g]中的[g]中的x] where([x] .ensent == true)选择[x] => count()}} == {[g] => count()})选择new <> f_sanonamoustype18 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)'无法翻译,并将在本地进行评估.

推荐答案

如果可能的话,升级到EF Core 2.1(或2.2),以便改进 linq groupby翻译

在2.1之前,在EF核心中,GroupBy LINQ运算符将始终在内存中进行评估.我们现在支持将其转换为大多数常见情况下的SQL Group By子句.

您可以在以前的EF核心版本中做任何事情.

在升级后,为了获得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
             }
);

(遗憾的是,Natual 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());

本文地址:https://www.itbaoku.cn/post/1556823.html

问题描述

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