Linq to Sql with lambda sum as a where condition

Question

I have the following query

from p in _context.Products
where p.Purchases.Sum(item => item.CCAmount) > 0 && p.Purchases.Sum(item => item.CCAmount) > p.PayOuts.Sum((item => item.AmountPaid)
select p;

Basically I am trying to retrieve all products that have a summed purchase amount greater than 0 and whose summed purchase amount is greater than the amount we have paid out (we are selling products on behalf of other people and pay them either in full or part payments). The problem is that if there are no entries in the payouts table for a particular product then that product does not appear in the resultant list. However if I insert a payout into the payouts table then that product will appear in the product list. Its almost as if using sum on an empty collection will not evaluate as one would expect i.e. as 0. Am I missing something here?

Thanks for your help.

Answer 1

The problem is that SUM in SQL returns null if there are no records to sum.

You need to cheat a little bit.

Try:

((int?)p.PayOuts.Sum(item => item.AmountPaid)).GetValueOrDefault()

or written in a little bit different way

((int?)p.PayOuts.Sum(item => item.AmountPaid) ?? 0)