title: Optimize LINQ instead of creating new collections/loops
body: <p>I have two tables:</p>
  
  <p><strong>Invoices</strong> (InvoiceID, InvoiceNumber)<br />
  <strong>Invoices_Products</strong> (InvoiceID, ProductID, IsFinalized)</p>
  
  <p>I show a list of all invoices, and there are buttons to filter by "finalized" or "not finalized" invoices. A finalized invoice is one where <em>every</em> product on it is <code>IsFinalized==true</code>.</p>
  
  <p>At the moment I have the following code which is performing quite slowly:</p>
  
  <pre><code>IEnumerable&lt;Invoice&gt; invoices = db.Invoices;
  
  if (isFinalized) // filter by finalized invoices
  {
      List&lt;Invoice&gt; unfinalizedInvoices = new List&lt;Invoice&gt;();
  
      foreach (var invoice in invoices)
      {
          int invoicesProductsCountTotal = db.Invoices_Products.Where(l =&gt; l.InvoiceID == invoice.InvoiceID).Count();
          int invoicesProductsCountFinalized = db.Invoices_Products.Where(l =&gt; l.InvoiceID == invoice.InvoiceID &amp;&amp; l.IsFinalized == true).Count();
  
          if (invoicesProductsCountTotal != invoicesProductsCountFinalized)
          {
              unfinalizedInvoices.Add(invoice);
          }
      }
  
      invoices = invoices.Except(unfinalizedInvoices);
  }
  else
  {
      List&lt;Invoice&gt; finalizedInvoices = new List&lt;Invoice&gt;();
  
      foreach (var invoice in invoices)
      {
          int invoicesProductsCountTotal = db.Invoices_Products.Where(l =&gt; l.InvoiceID == invoice.InvoiceID).Count();
          int invoicesProductsCountFinalized = db.Invoices_Products.Where(l =&gt; l.InvoiceID == invoice.InvoiceID &amp;&amp; l.IsFinalized == true).Count();
  
          if (invoicesProductsCountTotal == invoicesProductsCountFinalized &amp;&amp; invoicesProductsCountFinalized &gt; 0)
          {
              finalizedInvoices.Add(invoice);
          }
      }
  
      invoices = invoices.Except(finalizedInvoices);
  }
  </code></pre>
  
  <p>I realize this isn't optimal but I like spreading out my LINQ so that I can read and understand it.<br /><strong>My question:</strong> Is there any way I could make this query faster using <code>.All</code> or <code>.Any</code> or something, or do I need to rethink my database design (possibly adding an extra column to the Invoices table)</p>
  
  <p><strong>edit:</strong> Third table is <strong>Products</strong> (ProductID, ProductNumber) but you knew that</p>