EF Core 2.1 RC 1 GroupBy still evaluates locally

.net-core c# entity-framework-core

Question

I upgraded an ASP.NET Core project from version 2.0.x to 2.1.0-rc1 by following the official guide from ASP.NET Blog. The EF Core packages were updated to the same version as well. The database provider that i am using is Microsoft.EntityFrameworkCore.SqlServer. I was waiting for this upgrade with anticipation since i needed a lot the LINQ GroupBy translation feature. One of my queries is the following:

await _db.Documents
         .ApplyFilter(options)
         .Where(x => x.SubscriptionId == subscriptionId && x.Status != DocumentStatus.Deleted)
         .GroupBy(document => new { document.SubscriptionId })
         .Select(group => new {
            Total = group.Sum(x => x.Total),
            TotalDiscount = group.Sum(x => x.TotalDiscount),
            TotalNet = group.Sum(x => x.TotalNet != null ? x.TotalNet.Value * (decimal)x.CurrencyRate : 0),
            SubTotal = group.Sum(x => x.SubTotal != null ? x.SubTotal.Value * (decimal)x.CurrencyRate : 0),
            TotalSalesTax = group.Sum(x => x.TotalSalesTax != null ? x.TotalSalesTax.Value * (decimal)x.CurrencyRate : 0),
            TotalTax = group.Sum(x => x.TotalTax != null ? x.TotalTax.Value * (decimal)x.CurrencyRate : 0),
            TotalPayable = group.Sum(x => x.TotalPayable != null ? x.TotalPayable.Value * (decimal)x.CurrencyRate : 0)
        })
        .ToListAsync();

When this query runs i still receive a warning that states:

EFCore Linq Query Could not be translated and will be evaluated locally.

So i would like to ask if am i doing something (terribly) wrong or this feature is not yet available as it is advertised? Has any of you tried something similar? Thank you in advance for your help.


Edit

For the shake of testing the problem is tried the following query (removed the conditional checks from Sum method) as Ivan Stoev suggested in the comments, but the result is the same.

await _db.Documents
         .ApplyFilter(options)
         .Where(x => x.SubscriptionId == subscriptionId && x.Status != DocumentStatus.Deleted)
         .GroupBy(document => new { document.SubscriptionId })
         .Select(group => new {
            Total = group.Sum(x => x.Total),
            TotalDiscount = group.Sum(x => x.TotalDiscount),
            TotalNet = group.Sum(x => x.TotalNet),
            SubTotal = group.Sum(x => x.SubTotal),
            TotalSalesTax = group.Sum(x => x.TotalSalesTax),
            TotalTax = group.Sum(x => x.TotalTax),
            TotalPayable = group.Sum(x => x.TotalPayable)
          })
          .ToListAsync();

Solution

After experimenting i figured out the following. I post it in case someone has the same problem. The following query in perfectly translated into a T-SQL statement and thus not evaluating on the client.

await (from d in _db.Documents.ApplyFilter(options)
       where d.SubscriptionId == subscriptionId && d.Status != DocumentStatus.Deleted
       group d by d.SubscriptionId into g
       select new {
          Total = g.Sum(x => x.Total/* * Convert.ToDecimal(x.CurrencyRate)*/),
          TotalDiscount = g.Sum(x => x.TotalDiscount/* * Convert.ToDecimal(x.CurrencyRate)*/),
          TotalNet = g.Sum(x => x.TotalNet/* * Convert.ToDecimal(x.CurrencyRate)*/),
          SubTotal = g.Sum(x => x.SubTotal/* * Convert.ToDecimal(x.CurrencyRate)*/),
          TotalSalesTax = g.Sum(x => x.TotalSalesTax/* * Convert.ToDecimal(x.CurrencyRate)*/),
          TotalTax = g.Sum(x => x.TotalTax/* * Convert.ToDecimal(x.CurrencyRate)*/),
          TotalPayable = g.Sum(x => x.TotalPayable/* * Convert.ToDecimal(x.CurrencyRate)*/)
       }).ToListAsync();

The obvious difference is that i use the LINQ query syntax instead of the extension methods. Also i commented out the multiplication with the CurrencyRate property as it also results in evaluating the query locally. This seems quite strange as the query syntax also translates in extension methods under the hood.

I also opened an issue in the Github repository of Entity Framework Core which you can find here

1
6
5/16/2018 10:55:51 AM

Popular Answer

In my case the grouping was evaluated client-side for this query:

await context.MyCollection
.GroupBy(x => x.Tag.Id)
.ToDictionaryAsync(x => x.Key, x => x.Count());

Changing it to the below one made it evaluated server-side:

await context.MyCollection
.GroupBy(x => x.Tag.Id)
.Select(g => new {g.Key, Count = g.Count()})
.ToDictionaryAsync(arg => arg.Key, arg => arg.Count);
1
4/23/2019 9:37:06 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow