title: Optimize the number of accesses to a database when working with IQueryable<T> and custom functions
body: <p>In my <strong>C# Class Library project</strong> I have a method that needs to compute some statistics <code>GetFaultRate</code>, that, given a <code>date</code>, computes the number of products with faults over the number of products produced. </p>
  
  <pre><code>float GetFaultRate(DateTime date)
  {
      var products = GetProducts(date);
      var faultyProducts = GetFaultyProducts(date);
  
      var rate = (float) (faultyProducts.Count() / products.Count());
  
      return rate;
  }
  </code></pre>
  
  <p>Both methods, <code>GetProducts</code> and <code>GetFaultyProducts</code> take the data from a <strong>Repository</strong> class <code>_productRepository</code>.   </p>
  
  <pre><code>IEnumerable&lt;Product&gt; GetProducts(DateTime date)
  {
      var products = _productRepository.GetAll().ToList();
  
      var periodProducts = products.Where(p =&gt; CustomFunction(p.productionDate) == date);
  
      return periodProducts;
  }
  
  IEnumerable&lt;Product&gt; GetFaultyProducts(DateTime date)
  {
      var products = _productRepository.GetAll().ToList();
  
      var periodFaultyProducts = products.Where(p =&gt; CustomFunction(p.ProductionDate) == date &amp;&amp; p.Faulty == true);
  
      return periodFaultyProducts;
  }
  </code></pre>
  
  <p>Where <code>GetAll</code> has signature:</p>
  
  <pre><code>IQueryable&lt;Product&gt; GetAll();
  </code></pre>
  
  <p>The products in the database are many and it takes a lot of time to retrieve them and convert <code>ToList()</code>. I need to enumerate the collection since any custom function such as <code>CustomFunction</code>, cannot be executed in a <code>IQueryable&lt;T&gt;</code>. </p>
  
  <p>My application gets stuck for a long time before obtaining the fault rate. I guess it is because of the large number of objects to be retrieved. I can indeed remove the two functions <code>GetProducts</code> and <code>GetFaultyProducts</code> and implement the logic inside <code>GetFaultRate</code>. However since I have other functions that use <code>GetProducts</code> and <code>GetFaultyProducts</code>, with the latter solution I have only one access to the database but a lot of duplicate code.</p>
  
  <p>What can be a good compromise? </p>