Thursday, 15 September 2011

Queryover, eager loading parent and children with a filter

Sometimes we want to use eager loading as:-

1. The dataset returned is small
2. We don't care about the cartesain product.
3. We want to load the parents
4. We want to load the children
5. We want to filter the results based on a where clause on the children

So how do we go about this, the domain model for this example looks like this:-
public class Movie : Entity
{
  public virtual string Director { get; set; }
  public virtual IList<Actor> ActorList { get; set; }
}

public class Actor : Entity
{
  public virtual string Name { get; set; }
  public virtual string Role { get; set; }
}
When using NHibernate and QueryOver we would specify eager loading by using:-
Actor actor = null;

return Session.QueryOver<Movie>()
  .Fetch(f => f.ActorList).Eager
  .Left.JoinAlias(f => f.ActorList, () => actor)
  .Where(() => actor.Name.Like("m%"))
  .List();
Lets look at the SQL output that gets generated:-

Excellent this is exactly what I require, one sql statement is sent to the database and both parents and children are hydrated at the same time.

However, there is one caveat here, look closely at my c# code and you will see that I am specifying a left join.
Now for me this is where the some confusion crops up. If I take out the .left out of my code so that it is:-
return Session.QueryOver<Movie>()
  .Fetch(f => f.ActorList).Eager
  .JoinAlias(f => f.ActorList, () => actor)
  .Where(() => actor.Name.Like("m%"))
  .List();
and rerun my example the sql that gets generated is:-
As you can see we have resorted back to lazy loading, this is quite strange and I am not 100% sure if this is a bug or the intended behaviour.

So it looks like if you want to take advantage of eager loading and queryover then you always need to specify a .left join in your code!

5 comments:

  1. Very good, had the same problem because the LEFT was not working

    ReplyDelete
  2. Thanks. You saved my day :)

    ReplyDelete
  3. Still a useful article in 2016, thank you!

    ReplyDelete
  4. Just came across this problem, and I agree that this article is still relevant. Solved the issue I was having

    ReplyDelete
  5. Great. Still an issue in 2017.

    ReplyDelete