Linq to Sql with lambda sum as 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 try to get all the products with a total purchase amount in excess of 0, and the total purchase amount is greater than the amount we paid (we sell products on behalf of other people and pay them in full or in part payments). The problem is that if there are no entries in the payout table for a particular product, then this product does not appear in the resulting list. However, if I insert a payout into the paytable, this product will appear in the product list. Its almost as if using the amount in an empty collection would not be evaluated, as you might expect, for example, 0. Did I miss something here?

Thanks for your help.

Answer 1

The problem is that SUM in SQL returns null if there are no sums.

You need to cheat a little.

Try:

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

or written a little differently.

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