CODEWITHSUNDEEP
c#sqllinqfluent-nhibernateautomapping
Maate
Maate

Reputation: 1000

Make IQueryable via NHibernate prefer Join over Exists

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?

Upvotes: 0

Views: 1318

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109079

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()

Upvotes: 1

Steve Mallory
Steve Mallory

Reputation: 4283

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();

Upvotes: 1

Related Questions