none
Inefficient generated queries with includes and order bys RRS feed

  • Question

  • 1) First issue I'm having is if you do an include and then an order by the SQL generated generates an inner join and an outer join

    var query = from l in Lead.Include("Contact")
    orderby l.Contact.FirstName
    select l;
     INNER JOIN [dbo].[Contact] AS [Extent2] ON [Extent1].[ContactId] = [Extent2].[ContactId]
    LEFT OUTER JOIN [dbo].[Contact] AS [Extent3] ON [Extent1].[ContactId] = [Extent3].[ContactId]
    ORDER BY [Extent2].[FirstName] ASC
    Which makes for a slightly inefficient query
    2) if I do multiple includes it always does the second one as an outer join so like
    Lead.Include("OneToOne").Include("OtherOneToOne")     <- in this scenario OtherOneToOne is an outer join and OneToOne is an inner join
    Lead.Include("OtherOneToOne").Include("OneToOne")    <- in this scenario OneToOne is an outer join and OtherOneToOne is an inner join
    is that just how it works?

    • Edited by shaen Thursday, December 15, 2011 10:56 AM
    Thursday, December 15, 2011 10:06 AM

Answers

  • Hi Shaen,

    The Entity Framework exists as a new part of the ADO.NET family of technologies. It is clear that there is an opportunity for improvement. So, please be patient with the new technology, I think it will have a good future.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, December 20, 2011 2:20 AM
    Moderator

All replies

  • Hi shaen,

    Welcome to MSDN Forum!

    I have tested on my computer and found no matter one Include or many Include contained in the query statement, the generated T-SQL are left outer join. I think it may caused by the difference version of Entity Framework, my test version is EF4.1.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Monday, December 19, 2011 6:49 AM
    Moderator
  • Hmmm so 4.1 does it even more wrong?   The two FK's for me are non nullable so I'd prefer if it generated Inner Joins seeing as how they are known...  I actually think this is fixed in the June CTP release but since I'm using RIA I can't use the June CTP release since it doesn't work with RIA ... Since MS was more focused on getting 4.2 and 4.1 working with RIA  and 4.2 and 4.1 don't have the core fixes that the June CTP release has :-(

     

    SOOOOO I think I'm just SOL for right now until I get a new version with the June CTP stuff that works with RIA...

    Monday, December 19, 2011 8:06 AM
  • Hi Shaen,

    The Entity Framework exists as a new part of the ADO.NET family of technologies. It is clear that there is an opportunity for improvement. So, please be patient with the new technology, I think it will have a good future.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, December 20, 2011 2:20 AM
    Moderator