Why does "IQueryable.Where()" provide different results than "IEnumerable.Where()" in this query?

Question

Why does "IQueryable.Where()" provide different results than "IEnumerable.Where()" in this query?

c# entity-framework entity-framework-core linq

Question

I am trying to use linq to filter some rows from an EF Core dataset and I don't understand why filtering an IEnumerable vs filtering an IQueryable are giving me different results.

        var query = _db.Jobs
            .IsDelivered()
            .Include(a => a.JobExtras)
            .Include(a => a.Tips)
            .Include(a => a.Payments)
            .HasPayments();

        var query1 = query
            .ToList()
            .Where(a => a.Payments.Sum(b => b.Amount)
                         - a.Price.Value
                         - a.Discount
                         - a.JobExtras.Sum(c => c.Price)
                         - a.Tips.Sum(d => d.Amount)
                         > 0);

        var query2 = query
           .Where(a => a.Payments.Sum(b => b.Amount)
                         - a.Price.Value
                         - a.Discount
                         - a.JobExtras.Sum(c => c.Price)
                         - a.Tips.Sum(d => d.Amount)
                         > 0);

        Debug.WriteLine($"Record Count[Before Where Clause]: {query.Count()}");
        Debug.WriteLine($"Record Count[ToList() version]: {query1.Count()}");
        Debug.WriteLine($"Record Count[w/out ToList()]: {query2.Count()}");

Here is the output:

Record Count[Before Where Clause]: 8379
Record Count[ToList() version]: 5921
Record Count[w/out ToList()]: 0

Why is the IEnumerable version producing 5921 records and the IQueryable version producing 0 records?

Frank B

Popular Answer

Ok, had a couple things to try, but I read that you found that the issue was the missing children.

I had a quick test and the following should work in your case:

  var query2 = query
       .Where(a => a.Payments.Sum(b => b.Amount)
                     - a.Price.Value
                     - a.Discount
                     - (a.JobExtras.Any() ? a.JobExtras.Sum(c => c.Price) : 0)
                     - (a.Tips.Any() ? a.Tips.Sum(d => d.Amount) : 0)
                     > 0);

Steve Py

View more on Stack Overflow

Kit · Accepted Answer · 9/11/2019 8:04:58 PM

The first query executes in memory using .NET types, and Enumerable.Sum(). Any nulls passed into Sum are skipped, so some of your data is passing the > 0 test.

The second query executes in the database and uses SQL's SUM. If any part of the expression in your Where clause contains a null, that expression will evaluate as null. Taking that expression and comparing it (null > 0) always evaluates as false in the SQL, so all your rows are filtered.

Your second query might be fixable. I have no proof of this as I don't know your model (i.e. what's nullable or not), and am not sure if EF can translate this:

.Where(a => a.Payments.Where(x => x != null).Sum(b => b.Amount)
     - a.Price.Value
     - a.Discount
     - a.JobExtras.Where(x => x != null).Sum(c => c.Price)
     - a.Tips.Where(x => x != null).Sum(d => d.Amount)
     > 0);

Why does "IQueryable.Where()" provide different results than "IEnumerable.Where()" in this query?

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related