title: Linq to Sql with lambda sum as a where condition
body: <p>I have the following query </p>
  
  <pre><code>from p in _context.Products
  where p.Purchases.Sum(item =&gt; item.CCAmount) &gt; 0 &amp;&amp; p.Purchases.Sum(item =&gt; item.CCAmount) &gt; p.PayOuts.Sum((item =&gt; item.AmountPaid)
  select p;
  </code></pre>
  
  <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?</p>
  
  <p>Thanks for your help.</p>