title: How to speed up querying same table many times with different conditions?
body: <p>EF6, asp mvc core and SQL Server are used on the background.</p>
  
  <p>I have to do many queries to the same table with different conditions, f.e.</p>
  
  <pre><code>SELECT COUNT(*) FROM Table1 WHERE a = true
  SELECT COUNT(*) FROM Table1 WHERE b = true
  SELECT COUNT(*) FROM Table1 WHERE a = true || b = true
  SELECT a FROM Table1 WHERE b = true
  </code></pre>
  
  <p>So 4 queries to <code>Table1</code> with different conditions. I think that as result I have to read the entire <code>Table1</code> four times. In pseudo code it might be looking like this.</p>
  
  <pre><code>var res1 = new list();
  
  foreach(var rec in Table1) 
  {
      // read Table1 first time
      if(rec.a == true) 
      {
          res1.push(rec);
      }
  }
  
  var res2 = new list();
  
  foreach(var rec in Table1) 
  {
      // read Table1 second time
      if(rec.b == true) 
      {
          res2.push(rec);
      }
  }
  
  var res3 = new list();
  
  foreach(var rec in Table1) 
  {
       // read Table1 third time
       if(rec.a == true || rec.b == true) 
       {
           res3.push(rec);
       }
  }
  
  var res4 = new list();
  
  foreach(var rec in Table1) 
  { 
      // read Table1 fourth time
      if(rec.b == true) 
      {
          res4.push(rec);
      }
  }
  </code></pre>
  
  <p>I want to know how to read the <code>Table1</code> only one time and get four different results, like this:</p>
  
  <pre><code>var res1 = new List();
  var res2 = new List();
  var res3 = new List();
  var res4 = new list();
  
  foreach(rec in Table1) 
  {
       // read Table1 first time
       if(a == true) 
       {
          res1.push(rec);
       } 
  
       if(b == true) 
       {
          res2.push(rec);
       }
  
       if(a == true || b == true) 
       {
          res3.push(rec);
       }
  
       if(b == true) 
       {
          res4.push(rec);
       }  
  }  
  </code></pre>
  
  <p>Also the challenge, that those queries are dynamic sql, I mean that <code>a = true</code>, <code>b = true</code>, <code>a = true || b = true</code> are stored in database. And queries are running in this way:</p>
  
  <pre><code>string query = "SELECT Count(*) FROM Table1 WHERE" + condition;
  var count = ExecuteSql(query);
  </code></pre>
  
  <p>The sample above is simplified, but in reality all the query is split and stored in database.</p>
  
  <p>PS. Actually I want to speed up the page, which makes 30-40 requests to the server and each request is the query to the same table. I think if I can replace them with one request instead of 40 requests. </p>