Why is my EF Core Group by query with outer join is evaluating locally?

Question

I'm attempting to perform a group by query against SQL Server using EF Core 2.2, but it appears to be performing the outer join portion locally.

Here's the query:

var payerIds = new int[]{...}
var query = from sr in ctx.DataContext.StatementRecipients
                join pf1 in ctx.DataContext.PaymentFacts on sr.RecipientId equals pf1.PayerId into outerJoinPaymentFacts 
                from pf in outerJoinPaymentFacts.DefaultIfEmpty()
                where  payerIds.Contains(sr.RecipientId)
                group  pf.AmountRemaining by sr.RecipientId
                into g
                select new
                {
                    UserId = g.Key,
                    UnappliedAmountTotal = g.Sum()
                };

And the warnings:

    The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally.
16:32:28 WRN] The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally.
[16:32:28 WRN] The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'from PaymentFactEntity pf in {[outerJoinPaymentFacts] => DefaultIfEmpty()}' could not be translated and will be evaluated locally.
[16:32:28 WRN] The LINQ expression 'from PaymentFactEntity pf in {[outerJoinPaymentFacts] => DefaultIfEmpty()}' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'GroupBy([sr].RecipientId, [pf]?.AmountRemaining)' could not be translated and will be evaluated locally.
[16:32:28 WRN] The LINQ expression 'GroupBy([sr].RecipientId, [pf]?.AmountRemaining)' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally.
[16:32:28 WRN] The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally.
[warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally.
16:32:28 WRN] The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally.
[warn16:32:28: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'from PaymentFactEntity pf in {[outerJoinPaymentFacts] => DefaultIfEmpty()}' could not be translated and will be evaluated locally.
 WRN] The LINQ expression 'from PaymentFactEntity pf in {[outerJoinPaymentFacts] => DefaultIfEmpty()}' could not be translated and will be evaluated locally.
[warn: Microsoft.EntityFrameworkCore.Query[20500]

what gives?

Thanks to @Gert Arnold for the tip on implicit grouping. My revised query now looks like this:

But, as he said, its still evaluating locally.

  var query = from sr in ctx.DataContext.StatementRecipients
            join pf1 in ctx.DataContext.PaymentFacts on sr.RecipientId equals pf1.PayerId into outerJoinPaymentFacts
            from pf in outerJoinPaymentFacts.DefaultIfEmpty()
            where  payerIds.Contains(sr.RecipientId)
            select new
            {
                UserId = sr.RecipientId,
                UnappliedAmountTotal = outerJoinPaymentFacts.Sum(x => x.AmountRemaining)
            };

Here are a few additional things I've tried;

1) replaced the array of payerids with a single int payerid value. I was wondering if the array was forcing the local evaluation. It wasn't. Even when I filter to a single recipient/payer id, the query still evaluates localy

2) Before I run the query I did a DbContext.SaveChangesAsync(), to flush all the changes in the local context back to the db. My theory was that there was some uncommitted changes to the payment fact table, and those uncommitted changes forced local evaluation so they could be included in the result. My theory didn't pan out, as the query still evaluated locally.

Is there some way/diagnostic we can get from the EF engine as to why it feels it needs to run these portions locally?

Your query is more complex than necessary. `join - into` [already creates a grouping](https://stackoverflow.com/a/15599143/861716). You can remove `from pf in ` and the `group by` and use `outerJoinPaymentFacts.Sum()` to get the same data. Even then, EF-core will want to evaluate it client-side :( — Gert Arnold, Jun 05 '19 at 21:17
You should still remove `from pf in outerJoinPaymentFacts.DefaultIfEmpty()`. I think it's ignored (because `pf` isn't used) but it's confusing because, when used, it flattens the grouping. — Gert Arnold, Jun 06 '19 at 13:31
Great catch. I wasn't aware of the implicit grouping that happens during the join. I thought DefaultIfEmpty() into {var} was the way to do outer joins in EF. Is that not correct? What differentiates an inner vs outer join for EF queries? Thanks. btw - removed that extra "from pf in outerjoinPaymentFacts.DefaultIfEmpty()" line, and it still insists on performing the SUM locally. — cdarrigo, Jun 06 '19 at 17:08

Why is my EF Core Group by query with outer join is evaluating locally?

0 Answers0