Make IQueryable via NHibernate prefer Join over Exists

Question

If I write the code below:

session.Query<Parent>.Count( p => p.Children.Any( c => c.Name == "Child" && c.Age == 10 ) );

The generated SQL is:

select cast(count(*) as INT) as col_0_0_ from "Parent" parent0_ 
where exists (select children1_.Id from "Child" children1_ where parent0_.Id=children1_.Parent_id and children1_.Name=? and children1_.Age=?)

Due to performance observation in SQL CE 4.0, I would prefer a join, i.e. something like:

select cast(count(*) as INT) as col_0_0_ from "Parent" parent0_
join "Child" children1_ on parent0_.Id = children1_.Parent_id 
where children1_.Name=? and children1_.Age=?

I'm sure I'm weak on NHibernate, but after numerous attempts, I still can't figure out how to get it to join the tables without reverting to QueryOver or HQL, but I really like having the dependency defined by IQueryable<T>. Could someone hint me in the right direction?

score 1 · Answer 1 · answered Jun 17 '14 at 19:34

1

I think querying on the child, then referencing the parent will get you what you want.

session.Query<Child>()
       .Where( c => c.Name == "Child")
       .Where( c => c.Age == 10)
       .Select( c=> c.Parent)
       .Count();

answered Jun 17 '14 at 19:34

Steve Mallory

4,245
1
28
31

score 1 · Answer 2 · answered Jun 17 '14 at 20:14

Your second query doesn't do the same as the first one: it's counting the number of children, not parents.

If you want a join that counts the parents, you must make the query return one child per parent. I don't know the correct LINQ to NHibernate syntax, but in plain LINQ it would look like

(from p in Parents
 from c in p.Children.Where(c => c.Name == "Child" && c.Age == 10)
            .Take(1)
 select p).Count()

If there is no navigation property (although it's preferable to use that):

(from p in Parents
 from c in Children.Where(c => c.Name == "Child" && c.Age == 10
                            && c.ParentId == p.Id)
                   .Take(1)
 select p).Count()

Make IQueryable via NHibernate prefer Join over Exists

2 Answers2