title: Entity Framework - how to avoid query recompilation?
body: <p>I am working on optimization of our Entity Framework code, and currently I am facing an issue I'm not sure how to solve.</p>
  
  <p>We are using <strong>Azure SQL</strong> + <strong>Code-First Entity Framework 6.1.3</strong> + <strong>Asp.net Web Api v2</strong>. Endpoint is hosted in the cloud and I'm using it for testing.</p>
  
  <p>I have an API action, that is used for getting filtered, sorted and paged data. Here's a simplified code of what I do with data:</p>
  
  <pre><code>var entities = DbContext.Services
          .Include(q =&gt; q.Consumer.Building.Address.Country)
          .Include(q =&gt; q.Consumer.Building.Address.State);
  
  entities = entities.OrderBy(x =&gt; x.Consumer.RegisteredAt);
  
  entities = entities.Where(x =&gt; x.IsDeleted == false); 
  entities = entities.Where(x =&gt; userId.HasValue ? x.Owner.Id == userId ? true); //this part comes from deep internals, so I cannot change it quickly
  var page = entities = entities.Skip(skip).Take(take).ToList();
  var count = entities.Count();
  
  var dtoPage = Mapper.Map&lt;IEnumerable&lt;ServiceDto&gt;&gt;(page);
  
  return Page&lt;ServiceDto&gt;(dtoPage, count);
  </code></pre>
  
  <p>So the code does nothing exceptional - does not use <code>IN</code> clause, etc.., just simple filter, sorting and paging.</p>
  
  <p><strong>The problem:</strong> 
  Calling this method has unstable execution time. I used a simple script to call API endpoint that calls this code with different params: it gets pages 0..5 consequently 200 times, one after another. For each call (except for the first) I expected the call to take less than 300 ms. <strong>BUT:</strong> from 1200 overall calls, 36 call times exceeded 1 second - like the query was re-compiled again during this time. Average time for calls was <strong>250ms</strong>, but it spiked sometimes to 1000ms, it's 4 times difference. </p>
  
  <p>Common behavior for tests is:</p>
  
  <ol>
  <li><p>First query is slow</p></li>
  <li><p>Second query is faster, but still slower then rest</p></li>
  <li><p>Rest queries are pretty stable fast, except for some spikes every now and then.</p></li>
  </ol>
  
  <p>No one used testing environment except for me, so it's not load issue, I can reproduce it on any environment, even on fast local PC - i5 + 8gb RAM + SSD.</p>
  
  <p>Also even small delay between tests - </p>
  
  <p>So my question in short is:
  <strong>What does this issue come from?</strong></p>
  
  <ol>
  <li>Is this really recompilation issue, if not, what is the possible source of the issue?</li>
  <li>Is there a document about query cache invalidation for Entity Framework 6?</li>
  <li>Is there a way to keep a query in the cache for longer time, so that if I query the method now and in 30 minutes, it would not have to re-compile it?</li>
  </ol>