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