title: Why does the Contains() operator degrade Entity Framework's performance so dramatically?
body: <p>UPDATE 3: According to <a href="http://blogs.msdn.com/b/adonet/archive/2012/12/10/ef6-alpha-2-available-on-nuget.aspx" rel="noreferrer">this announcement</a>, this has been addressed by the EF team in EF6 alpha 2.</p>
  
  <p>UPDATE 2: I've created a suggestion to fix this problem. To vote for it, <a href="http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions/suggestions/2598644-improve-the-performance-of-the-contains-operator" rel="noreferrer">go here</a>.</p>
  
  <p>Consider a SQL database with one very simple table.</p>
  
  <pre><code>CREATE TABLE Main (Id INT PRIMARY KEY)
  </code></pre>
  
  <p>I populate the table with 10,000 records.</p>
  
  <pre><code>WITH Numbers AS
  (
    SELECT 1 AS Id
    UNION ALL
    SELECT Id + 1 AS Id FROM Numbers WHERE Id &lt;= 10000
  )
  INSERT Main (Id)
  SELECT Id FROM Numbers
  OPTION (MAXRECURSION 0)
  </code></pre>
  
  <p>I build an EF model for the table and run the following query in LINQPad (I am using "C# Statements" mode so LINQPad doesn't create a dump automatically).</p>
  
  <pre><code>var rows = 
    Main
    .ToArray();
  </code></pre>
  
  <p>Execution time is ~0.07 seconds. Now I add the Contains operator and re-run the query.</p>
  
  <pre><code>var ids = Main.Select(a =&gt; a.Id).ToArray();
  var rows = 
    Main
    .Where (a =&gt; ids.Contains(a.Id))
    .ToArray();
  </code></pre>
  
  <p>Execution time for this case is <strong>20.14 seconds</strong> (288 times slower)!</p>
  
  <p>At first I suspected that the T-SQL emitted for the query was taking longer to execute, so I tried cutting and pasting it from LINQPad's SQL pane into SQL Server Management Studio.</p>
  
  <pre><code>SET NOCOUNT ON
  SET STATISTICS TIME ON
  SELECT 
  [Extent1].[Id] AS [Id]
  FROM [dbo].[Primary] AS [Extent1]
  WHERE [Extent1].[Id] IN (1,2,3,4,5,6,7,8,...
  </code></pre>
  
  <p>And the result was</p>
  
  <pre><code>SQL Server Execution Times:
    CPU time = 0 ms,  elapsed time = 88 ms.
  </code></pre>
  
  <p>Next I suspected LINQPad was causing the problem, but performance is the same whether I run it in LINQPad or in a console application.</p>
  
  <p>So, it appears that the problem is somewhere within Entity Framework.</p>
  
  <p>Am I doing something wrong here? This is a time-critical part of my code, so is there something I can do to speed up performance?</p>
  
  <p>I am using Entity Framework 4.1 and Sql Server 2008 R2.</p>
  
  <p>UPDATE 1:</p>
  
  <p>In the discussion below there were some questions about whether the delay occurred while EF was building the initial query or while it was parsing the data it received back. To test this I ran the following code,</p>
  
  <pre><code>var ids = Main.Select(a =&gt; a.Id).ToArray();
  var rows = 
    (ObjectQuery&lt;MainRow&gt;)
    Main
    .Where (a =&gt; ids.Contains(a.Id));
  var sql = rows.ToTraceString();
  </code></pre>
  
  <p>which forces EF to generate the query without executing it against the database. The result was that this code required ~20 secords to run, so it appears that almost all of the time is taken in building the initial query.</p>
  
  <p>CompiledQuery to the rescue then? Not so fast ... CompiledQuery requires the parameters passed into the query to be fundamental types (int, string, float, and so on). It won't accept arrays or IEnumerable, so I can't use it for a list of Ids.</p>