Databases
StackOverflow2013
Postsdbo
POEntity Framework appears to be needlessly joining the same table twice
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEntity Framework appears to be needlessly joining the same table twice
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLangdon
    1. USLangdon
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POEntity Framework appears to be needlessly joining the same table twice
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POEntity Framework appears to be needlessly joining the same table twice
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POEntity Framework appears to be needlessly joining the same table twice
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI think you are exactly right the where and include are both doing joins and its not simplifying to the left join. You should post this as an improvement to the EF Codeplex project. However I imagine it will be pretty low priority as I imagine SQL will be pretty efficient with this
      singulars
      1. POEntity Framework appears to be needlessly joining the same table twice
      1. USLuke McGregor
    2. CO"Even" NHibernate has issues like this. Consider it a peculiarity of automated query generation. The database engine will probably optimize it out. Nice observation though.
      singulars
      1. POEntity Framework appears to be needlessly joining the same table twice
      1. USGert Arnold
    3. CO@GertArnold It actually is causing performance issues. SQL Profiler is reporting Duration to be 700-900 ms, where as if I remove it, I get 0-6 ms.
      singulars
      1. POEntity Framework appears to be needlessly joining the same table twice
      1. USLangdon
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload