title: Why does this query take so long in Entity Framework, when it's so fast in SQL?
body: <p>I have a table named GeographicalLocations, which contains about 57K rows. If I run the following query in SQL Server Management Studio, it takes about 1-2 seconds to complete...</p>
  
  <pre><code>select * from GeographicalLocations where Active=1
  </code></pre>
  
  <p>If I do the equivalent in LinqPad, it takes about 2-3 seconds...</p>
  
  <pre><code>GeographicalLocations.Where (gl =&gt; gl.Active)
  </code></pre>
  
  <p>However, the same code in a repository that uses EF4 takes about 10-11 seconds...</p>
  
  <pre><code>List&lt;GeographicalLocation&gt; geographicalLocations = new SalesTrackerCRMEntities()
    .CreateObjectSet&lt;GeographicalLocation&gt;()
    .Where(g =&gt; g.Active)
    .ToList();
  </code></pre>
  
  <p>By the way, we're stuck with EF4 for the moment, so please don't suggest upgrading. I'd love to, but there are other factors that are stopping us for the moment.</p>
  
  <p>Any idea how I can speed this up? The users are complaining about the slow response. It's obviously not the database, as the SQL query itself is very fast. It looks like something to do with EF, but I don't know what.</p>
  
  <p>I ran SQL Profiler on it, and could see the SQL that was sent. That took less than a second to run in SSMS, but the profiler showed 10 a second gap between the start and end of the batch. Not sure what's going on here, as I would have thought that EF would pull out all the data, then materialise it into entities, but it looks like it might be doing it as it goes along.</p>
  
  <p>Anyway, I would be grateful for any ideas as to how I can improve the performance here.</p>