title: Entity Framework appears to be needlessly joining the same table twice
body: <p><strong>Update</strong> This may already be fixed: <a href="http://entityframework.codeplex.com/workitem/486" rel="nofollow">http://entityframework.codeplex.com/workitem/486</a></p>
  
  <p>...</p>
  
  <p>A fairly straightforward LINQ statement against my entities is resulting in unnecessarily complex SQL.  More on that later, here's the setup:</p>
  
  <p><strong>Tables</strong></p>
  
  <p><em>Publication</em></p>
  
  <ul>
  <li>PublicationId (pk)</li>
  <li>TopicId (fk to a Topic table)</li>
  <li>ReceiptCount (denormalized for query performance)</li>
  <li>DateInserted</li>
  </ul>
  
  <p><em>Receipt</em></p>
  
  <ul>
  <li>ReceiptId (pk)</li>
  <li>PublicationId (fk to the table above)</li>
  <li>DateInserted</li>
  </ul>
  
  <p><strong>LINQ</strong></p>
  
  <pre><code>var query = from r in context.Receipts.Include("Publication")
              where r.DateInserted &lt; lagDate
              &amp;&amp; r.ReceiptId &gt; request.AfterReceiptId
              &amp;&amp; r.Publication.TopicId == topicEntity.TopicId
              &amp;&amp; r.Publication.ReceiptCount &gt; 1
              select r;
  </code></pre>
  
  <p><strong>SQL</strong></p>
  
  <pre><code>exec sp_executesql N'SELECT TOP (25) 
  [Project1].[ReceiptId] AS [ReceiptId], 
  [Project1].[PublicationId] AS [PublicationId], 
  [Project1].[DateInserted] AS [DateInserted], 
  [Project1].[DateReceived] AS [DateReceived], 
  [Project1].[PublicationId1] AS [PublicationId1], 
  [Project1].[PayloadId] AS [PayloadId], 
  [Project1].[TopicId] AS [TopicId], 
  [Project1].[BrokerType] AS [BrokerType], 
  [Project1].[DateInserted1] AS [DateInserted1], 
  [Project1].[DateProcessed] AS [DateProcessed], 
  [Project1].[DateUpdated] AS [DateUpdated], 
  [Project1].[PublicationGuid] AS [PublicationGuid], 
  [Project1].[ReceiptCount] AS [ReceiptCount]
  FROM ( SELECT 
      [Extent1].[ReceiptId] AS [ReceiptId], 
      [Extent1].[PublicationId] AS [PublicationId], 
      [Extent1].[DateInserted] AS [DateInserted], 
      [Extent1].[DateReceived] AS [DateReceived], 
      [Extent3].[PublicationId] AS [PublicationId1], 
      [Extent3].[PayloadId] AS [PayloadId], 
      [Extent3].[TopicId] AS [TopicId], 
      [Extent3].[BrokerType] AS [BrokerType], 
      [Extent3].[DateInserted] AS [DateInserted1], 
      [Extent3].[DateProcessed] AS [DateProcessed], 
      [Extent3].[DateUpdated] AS [DateUpdated], 
      [Extent3].[PublicationGuid] AS [PublicationGuid], 
      [Extent3].[ReceiptCount] AS [ReceiptCount]
      FROM   [dbo].[Receipt] AS [Extent1]
      INNER JOIN [dbo].[Publication] AS [Extent2] ON [Extent1].[PublicationId] = [Extent2].[PublicationId]
      LEFT OUTER JOIN [dbo].[Publication] AS [Extent3] ON [Extent1].[PublicationId] = [Extent3].[PublicationId]
      WHERE ([Extent2].[ReceiptCount] &gt; 1) AND ([Extent1].[DateInserted] &lt; @p__linq__0) AND ([Extent1].[ReceiptId] &gt; @p__linq__1) AND ([Extent2].[TopicId] = @p__linq__2)
  )  AS [Project1]
  ORDER BY [Project1].[ReceiptId] ASC',N'@p__linq__0 datetime,@p__linq__1 int,@p__linq__2 int',@p__linq__0='2012-09-05 19:39:21:510',@p__linq__1=4458824,@p__linq__2=90
  </code></pre>
  
  <p><strong>Problem</strong></p>
  
  <p>Publication gets joined twice:</p>
  
  <ol>
  <li>LEFT OUTER JOIN because of <code>.Include("Publication")</code></li>
  <li>INNER JOIN because of the <code>where</code>.</li>
  </ol>
  
  <p>If I remove [Extent2] from the SQL entirely, and change the WHERE bits to use [Extent3], I get the same results back.  Since I'm not using Lazy Loading on my entities, I have to <code>.Include("Publication")</code>... is there any solution for this?</p>
  
  <p>I was using EF4, but grabbed EF5 from NuGet to see if it was perhaps fixed, but it produces the same result (although I have no idea how to tell if my EDMX is really using EF5).</p>