Linq to NHibernate generating multiple joins to the same table

Question

When I have a reference to the same table in my select as in my where clause, linq to Nhibernate generates two joins, one for the select and one for the where. i.e.

from child in Session.Query<Child>()
where child.Parent.Name == "Bob" 
select new Info 
{ 
   ParentAge = child.Parent.Age, 
   ChildName = child.Name
};

Generates SQL like:

Select this_.Name,
       parent1.Age
From Child this_
     left join Parent parent1 on child.ParentId = parent1.Id,
Parent parent2

Where child.ParentId = parent2.Id and parent2.Name = 'Bob'

I would have thought I should get SQL more like:

Select this_.Name,
       parent1.Age
From Child this_
         inner join Parent parent1 on child.ParentId = parent1.Id
Where parent1.Name = 'Bob'

Is there a way to structure the query to get this? Does it matter?

Answer 1

You can prevent NHibernate from doing this by using a transparent identifier, so that your query looks like this:

from child in Session.Query<Child>()
let p = child.Parent
where p.Name == "Bob" 
select new Info { 
    ParentAge = p.Age, 
    ChildName = child.Name
};

Answer 2

Have you tried comparing the query execution plan for each in SSMS? If the duplicated join is eliminated in SQL Server, then it doesn't matter. I've found that to be the case in a few instances where I thought the generated query was going to be very inefficient, but after optimization it ends up exactly the same as a query that looks much better.