How to Force use of INNER JOIN rather than LEFT JOIN in Queries

9 posts, 0 answers
  1. Andrew
    Andrew avatar
    9 posts
    Member since:
    Mar 2012

    Posted 26 Jul 2012 Link to this post

    I am wondering if it possible to force the use of an INNER JOIN clause rather than an LEFT JOIN.   For an example, there are two Domain classes, DogType and Dog, where Dog.DogTypeId is a Foreign Key to DogType.Id and the Dog.DogTypeId property is not Nullable. The Load Behavior between the DogType and Dog entities is set to Eager, so when a query is executed for Dog it should ideally use an INNER JOIN. However, the SQL generated by the Open Access ORM always uses a LEFT JOIN.  Is it possible to force the use of an INNER JOIN?  Any help or suggestions would be appreciated.

    Thanks,

    Andrew
  2. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 30 Jul 2012 Link to this post

    Hello Andrew,

    I am afraid that currently there is no way to force an inner join to be used. This is already on our TODO list but at this point I am not able to give you a timeframe when it will be implemented.

    Greetings,
    Alexander
    the Telerik team
    OpenAccess ORM Q2'12 Now Available! Get your hands on all the new stuff.
  3. DevCraft banner
  4. Boris Rogge
    Boris Rogge avatar
    30 posts
    Member since:
    Mar 2006

    Posted 14 Mar 2013 Link to this post

    Hi Telerik,

    Is this issue already tackled? I also noticed that all include statements result in left joins. This defenitely has performance penalties.

    Thanks
  5. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 15 Mar 2013 Link to this post

    Hello Boris,

    I am afraid this optimization has not been implemented yet. You can vote for it here to higher its priority in our backlog.

    Greetings,
    Alexander
    the Telerik team
    OpenAccess ORM Q1 2013 is out featuring Multi-Diagrams, Persistent Data Stream Support and much more. Check out all of the latest highlights.
  6. Allen
    Allen avatar
    42 posts
    Member since:
    Nov 2014

    Posted 26 Dec 2014 in reply to Alexander Link to this post

    Hi, Alexander.

    Is this issue already tackled? I also noticed that all include statements result in left joins. This defenitely has performance penalties.

    Thanks
  7. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 29 Dec 2014 Link to this post

    This issue was not addressed yet, and I really wonder if there is such big performance difference to it. In the end, the database has to find and load both rows, regardless of the inner/outer difference. And in case of Include statements, the result should not depend on the existence of the included row as the Include statement is just a performance hint to the runtime (I will need this information later). In case you really want do ensure that there is only a result when the Included row exists, an explicit join or .Any() condition would be more appropriate.

    Happy Holidays!
    Regards,
    Thomas
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  8. Allen
    Allen avatar
    42 posts
    Member since:
    Nov 2014

    Posted 02 Jan 2015 in reply to Thomas Link to this post

    Hi, Thomas.
    Not performance difference problem, is data row difference problem.
    Thanks.
  9. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 05 Jan 2015 Link to this post

    You are right that this can affect the rows that you load. But this is only a having a difference in case that the foreign key is not set. You should be able to express that as an additional filter

    .Where(x => x.ForeignKey.HasValue) // Assuming there is a nullable FK value (or string property  == null)

    It won't be automatic and you will need to make the behavior more explicit in your query. I personally prefer this more explicit style.

    Regards,
    Thomas
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  10. Allen
    Allen avatar
    42 posts
    Member since:
    Nov 2014

    Posted 06 Jan 2015 in reply to Thomas Link to this post

    Hi. Thomas.
    You are right, I did not set the foreign key.
    Many of my items are not set foreign keys.
    Thanks
Back to Top
DevCraft banner