7

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?

Sly
  • 15,046
  • 12
  • 60
  • 89
Stuart L
  • 125
  • 3

2 Answers2

4

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
};
Matt Enright
  • 7,245
  • 4
  • 33
  • 32
1

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.

Joel C
  • 5,547
  • 1
  • 21
  • 31
  • Thanks Joel, In this case certainly the SQL Server optimiser generates effectively the same execution plan for each query so it doesn't really matter. For more complex cases, I might just have to try it and see. – Stuart L Apr 21 '11 at 08:48