Superfluous join when querying by foreign key given Id with LINQ
Description
I have a simple one-way one to many relationship, say comment *-- author
when I do
var comments = session.Query<Comment>().Where(c=>c.Author.Id == authorId).ToList();
The SQL that gets generated ends up doing superfulous join to the Person table (where Author lives):
select * from [Comments] c0_
inner join [Person] person1_
on c0_.AuthorId = person1_.Id
where person1_.Id = 2 /* @p0 */
Why do the join, instead of just doing:
select * from Comments c0 where c0.AuthorId = 2
What makes it worse in my case is that in real scenario, what I show here as Person is a table that is mapped (via <join/> mapping) to two tables, so I end up doing not one but two joins here.
The complete query I think was "from line in orderlines where line.order.customer.id == 10 select line", i.e. nothing else present that would make it need the customer table.
Alex Zaytsev
February 4, 2012 at 3:35 PM
> where line.Order.Customer.Id = 10
Could you tell me what test I should check?
Oskar Berggren
February 4, 2012 at 11:44 AM
I'm looking at the pull request now. Two cases:
where line.Order.Id = 5 will correctly avoid joining with order in your patch, but:
where line.Order.Customer.Id = 10 will join with customer despite that this is not needed.
I have a simple one-way one to many relationship, say comment *-- author
when I do
var comments = session.Query<Comment>().Where(c=>c.Author.Id == authorId).ToList();
The SQL that gets generated ends up doing superfulous join to the Person table (where Author lives):
select *
from [Comments] c0_
inner join [Person] person1_
on c0_.AuthorId = person1_.Id
where person1_.Id = 2 /* @p0 */
Why do the join, instead of just doing:
select * from Comments c0 where c0.AuthorId = 2
What makes it worse in my case is that in real scenario, what I show here as Person is a table that is mapped (via <join/> mapping) to two tables, so I end up doing not one but two joins here.