EF Core 2.1 evaluates locally when subquery and aggregate after Grouping

Question

So EF Core 2.1 evaluates GroupBy LINQ expressions on the SQL server (when using the SQL provider).

This is awesome however I have an issue when the query gets a little more complex.

The model in use for these queries is:

public class Invoice
{
    public string Status {get; set;}
    public string InvoiceType {get; set;}
    public decimal InvoicePayments {get; set;}
    public decimal EligibleValue {get; set;}
}

This LINQ statement is run completely in SQL Server:

data
    .GroupBy(i => new { i.Status, i.InvoiceType })
    .Select(i => new 
    {
        i.Key, 
        Count = i.Count(), 
        Total = i.Sum(x => x.EligibleValue)
    });

and generates the following SQL

SELECT 
    [i].[Status], 
    [i].[InvoiceType], 
    COUNT(*) AS [Count], 
    SUM([i].[EligibleValue]) AS [Col1]
FROM [Invoice] AS [i]
GROUP BY [i].[Status], [i].[InvoiceType]

This LINQ statement works but performs the GroupBy in memory:

data
    .GroupBy(i => new { i.Status, i.InvoiceType })
    .Select(i => new 
    { 
        i.Key, 
        Count = i.Count(), 
        TotalLessThan100 = i.Where(x => x.InvoicePayments < 100).Sum(y => y.EligibleValue),
        TotalLessThan500 = i.Where(x => x.InvoicePayments < 500).Sum(z => z.EligibleValue)
    });

I get some warnings in the Output window:

    The LINQ expression 'GroupBy(new <>f__AnonymousType0`2(Status = [i].Status, InvoiceType = [i].InvoiceType), [i])' could not be translated and will be evaluated locally.

The LINQ expression 'Count()' could not be translated and will be evaluated locally.

The LINQ expression 'where ([x].InvoicePayments < 100)' could not be translated and will be evaluated locally.

The LINQ expression 'where ([x].InvoicePayments < 500)' could not be translated and will be evaluated locally.

The LINQ expression 'Sum()' could not be translated and will be evaluated locally.

And the generated SQL does not have the GroupBy, just the initial query.

Is there any way I can define this query to be fully executed on the SQL Server?

Answer 1

The first rule to follow is to avoid Where and predicate version of Count on GroupBy result and use the conditional Sum where possible. EF6 was able to translate such constructs, but with very inefficient SQL.

So in general you need to rewrite the query like this:

data
    .GroupBy(i => new { i.Status, i.InvoiceType })
    .Select(g => new
    {
        g.Key,
        Count = g.Count(),
        TotalLessThan100 = g.Sum(i => i.InvoicePayments < 100 ? i.EligibleValue : 0),
        TotalLessThan500 = g.Sum(i => i.InvoicePayments < 500 ? i.EligibleValue : 0)
    });

However EF Core 2.1 GroupBy translation improvements does not include Sum with other than a simple property selector, so the above still uses client evaluation. Most likely it will be fixed in some future release, but until then, can use the following trick - add intermediate projection (Select) before GroupBy containing all the fields needed later, including the calculated, and then use them inside the aggregates after the GroupBy:

data
    .Select(i => new
    {
        i.Status,
        i.InvoiceType,
        LessThan100 = i.InvoicePayments < 100 ? i.EligibleValue : 0,
        LessThan500 = i.InvoicePayments < 500 ? i.EligibleValue : 0,
    })
    .GroupBy(i => new { i.Status, i.InvoiceType })
    .Select(g => new
    {
        g.Key,
        Count = g.Count(),
        TotalLessThan100 = g.Sum(i => i.LessThan100),
        TotalLessThan500 = g.Sum(i => i.LessThan500)
    });

which is translated to:

SELECT [i].[Status], [i].[InvoiceType], COUNT(*) AS [Count], SUM(CASE
    WHEN [i].[InvoicePayments] < 100.0
    THEN [i].[EligibleValue] ELSE 0.0
END) AS [TotalLessThan100], SUM(CASE
    WHEN [i].[InvoicePayments] < 500.0
    THEN [i].[EligibleValue] ELSE 0.0
END) AS [TotalLessThan500]
FROM [Invoice] AS [i]
GROUP BY [i].[Status], [i].[InvoiceType]