title: How to optimize Entity Framework Queries
body: <p>I am using Linq-To-Entities to do a query which is returning only 947 rows but taking 18 seconds to run. I have done a "ToTraceString" to get the underlying sql out and ran the same thing directly on the database and get the same timing. </p>
  
  <p>I have used the tuning advisor and created a couple of indexes although with little impact.</p>
  
  <p>Looking at the query execution plan there are a couple of nested loops which are taking up 95% of the time but these are already working on the indexes?</p>
  
  <p>Does anyone have any ideas on how to force some optimisation into the EF query??</p>
  
  <p>EDIT: Supplying additional information</p>
  
  <p>A basic ER diagram with for the three tables is as follows:</p>
  
  <pre><code>People &gt;----People_Event_Link ----&lt; Events
  P_ID        P_ID                    E_ID
              E_ID
  </code></pre>
  
  <p>The linq that I am running is designed to get all Events back for a particular Person (using the P_ID):</p>
  
  <pre><code>        var query = from ev in genesisContext.Events
                      join pe in genesisContext.People_Event_Link
                      on ev equals pe.Event
                      where pe.P_ID == key
                      select ev;
          return query;
  </code></pre>
  
  <p>Here is the generated SQL (deep breath!):</p>
  
  <pre><code>SELECT 
  1 AS [C1], 
  [Extent1].[E_ID] AS [E_ID], 
  [Extent1].[E_START_DATE] AS [E_START_DATE], 
  [Extent1].[E_END_DATE] AS [E_END_DATE], 
  [Extent1].[E_COMMENTS] AS [E_COMMENTS], 
  [Extent1].[E_DATE_ADDED] AS [E_DATE_ADDED], 
  [Extent1].[E_RECORDED_BY] AS [E_RECORDED_BY], 
  [Extent1].[E_DATE_UPDATED] AS [E_DATE_UPDATED], 
  [Extent1].[E_UPDATED_BY] AS [E_UPDATED_BY], 
  [Extent1].[ET_ID] AS [ET_ID], 
  [Extent1].[L_ID] AS [L_ID]
  FROM  [dbo].[Events] AS [Extent1]
  INNER JOIN [dbo].[People_Event_Link] AS [Extent2] ON  EXISTS (SELECT 
      1 AS [C1]
      FROM    ( SELECT 1 AS X ) AS [SingleRowTable1]
      LEFT OUTER JOIN  (SELECT 
          [Extent3].[E_ID] AS [E_ID]
          FROM [dbo].[Events] AS [Extent3]
          WHERE [Extent2].[E_ID] = [Extent3].[E_ID] ) AS [Project1] ON 1 = 1
      LEFT OUTER JOIN  (SELECT 
          [Extent4].[E_ID] AS [E_ID]
          FROM [dbo].[Events] AS [Extent4]
          WHERE [Extent2].[E_ID] = [Extent4].[E_ID] ) AS [Project2] ON 1 = 1
      WHERE ([Extent1].[E_ID] = [Project1].[E_ID]) OR (([Extent1].[E_ID] IS NULL) AND ([Project2].[E_ID] IS NULL))
  )
  WHERE [Extent2].[P_ID] = 291
  </code></pre>