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

[Andrew]

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

[Alexander]

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

[Boris Rogge]

Hi Telerik,

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

Thanks

[Alexander]

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

[Allen]

Hi, Alexander.

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

Thanks

[Thomas]

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

 

 

[Allen]

Hi, Thomas.
Not performance difference problem, is data row difference problem.
Thanks.

[Thomas]

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

 

 

[Allen]

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