This is a migrated thread and some comments may be shown as answers.
How to Force use of INNER JOIN rather than LEFT JOIN in Queries
8 Answers 76 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Andrew
Top achievements
Rank 1
Andrew asked on 26 Jul 2012, 03:27 PM
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

8 Answers, 1 is accepted

Sort by
0
Alexander
Telerik team
answered on 30 Jul 2012, 12:51 PM
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.
0
Boris Rogge
Top achievements
Rank 1
answered on 14 Mar 2013, 01:01 PM
Hi Telerik,

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

Thanks
0
Alexander
Telerik team
answered on 15 Mar 2013, 03:59 PM
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.
0
Allen
Top achievements
Rank 1
answered on 27 Dec 2014, 03:22 AM
Hi, Alexander.

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

Thanks
0
Thomas
Telerik team
answered on 29 Dec 2014, 09:41 AM
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.
 
0
Allen
Top achievements
Rank 1
answered on 03 Jan 2015, 01:11 AM
Hi, Thomas.
Not performance difference problem, is data row difference problem.
Thanks.
0
Thomas
Telerik team
answered on 05 Jan 2015, 09:27 AM
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.
 
0
Allen
Top achievements
Rank 1
answered on 06 Jan 2015, 12:52 PM
Hi. Thomas.
You are right, I did not set the foreign key.
Many of my items are not set foreign keys.
Thanks
Tags
LINQ (LINQ specific questions)
Asked by
Andrew
Top achievements
Rank 1
Answers by
Alexander
Telerik team
Boris Rogge
Top achievements
Rank 1
Allen
Top achievements
Rank 1
Thomas
Telerik team
Share this question
or