CODEWITHSUNDEEP
c#entity-frameworklinqentity-framework-core
Frank B
Frank B

Reputation: 73

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

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?

Upvotes: 3

Views: 757

Answers (4)

Frank B
Frank B

Reputation: 73

So based on the information provided by Kit and madreflection:

I just reseeded the DB and ensured that there ARE rows in the 3 child tables: Payments, JobExtras and Tips.

Voila, the query works with both IQueryable version as well as IEnumerable version. I get the same number of rows.

So, to summarize, the answer to the question is that the problem with the IQueryable query is that if there are no children rows in the DB for the objects that I am trying to SUM, SQL returns NULL instead of 0

Thank you for the help!

That behavior is an interesting GOTCHA.

So now, is there a way to fix the IQueryable query to contend with the possibility that the children objects (Tips, Payments, JobExtras) could be 'empty sets' (No rows) ?

============= Working solution (credit to @Steve Py & @madreflection)

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

// Even better solution [cleaner SQL generated] provided by: @madreflection

   .Where(a => (a.Payments.Sum(b => (decimal?)b.Amount) ?? 0M)
               - (a.Price ?? 0M)
               - a.Discount
               - (a.JobExtras.Sum(c => (decimal?)c.Price) ?? 0M)
               - (a.Tips.Sum(d => (decimal?)d.Amount) ?? 0M)
               > 0M);

Upvotes: 1

Steve Py
Steve Py

Reputation: 34908

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

Upvotes: 2

Ciro Corvino
Ciro Corvino

Reputation: 2128

Considering Jobs.Price as the only nullable value in the query then Iqueryable linq query could be rewritten as follow:

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

Upvotes: 0

Kit
Kit

Reputation: 21719

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

Upvotes: 5

Related Questions