Return parents based on a child filter with QueryOver

Sometimes an ORM doesn't quite do what you think it should. One classic example of this when we need to get a parent entity based on a where condition on the child.

Given the following domain:-

public class Movie : Entity {
  public virtual string Name { get; set; }
  public virtual string Description { 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; }
}

We have a Movie (parent) that has a collection of Actor (children). Now comes the challenge we want to get a list of movies where an actors name starts with the letter m, at first glance this is fairly trivial and we would sit down and construct the following query:-

return Session
  .QueryOver<movie>()
  .Inner.JoinQueryOver<actor>(a => a.ActorList)
  .Where(a => a.Name.IsLike("m%"))
  .List();

What do you think will get returned here?

Lets take one step back and look at the sql that gets generated:-

SELECT this_.Id          as Id0_1_,
       this_.Name        as Name0_1_,
       this_.Description as Descript4_0_1_,
       actor1_.Id        as Id2_0_,
       actor1_.Name      as Name2_0_,
       actor1_.Role      as Role2_0_
FROM   Movie this_
       inner join ActorRole actor1_
         on this_.Id = actor1_.MovieId
WHERE  actor1_.Name like 'm%' /* @p0 */

This then returns the following rows:-

As you can see 4 rows are returned but the same movie appears twice as there are two actors that start with the letter M for The Dark Knight. You would probably expect NHibernate to hydrate 3 parent entities but in actual fact it hydrates 4. After a bit of head scratching you realise NHibernate is doing what it should it is taking the 4 rows from the database and hydrating the results. The problem now is that your UI would display 4 results rather than 3.

So how do we instruct NHibernate to filter duplicates from the parent. One very simple approach is to specify a ResultTransformer:-

return Session
  .QueryOver<movie>()
  .Inner.JoinQueryOver<Actor>(a => a.ActorList)
  .Where(a => a.Name.IsLike("m%"))
  .TransformUsing(CriteriaSpecification.DistinctRootEntity)
  .List();

Now there is oneimportant thing to note here. This only performs the filter on the client side. You still get 4 rows returned from the database BUT only distinct parents are hydrated. Depending on a number of factors like you don't require paging and size of the children collections are small this might be suitable.

So is there another solution to this problem? Can we only get three parents returned from NHibernate? Maybe we want to page the results and don't like the idea of transmitting a lot of repeated data down the wire. Well there is a solution we need to introduce a sub query. If we were to sit down and write this in SQL we may come up with something like:-

SELECT this_.Id          as Id0_0_,
       this_.Name        as Name0_0_,
       this_.Description as Descript4_0_0_,
FROM   Movie this_
WHERE  this_.Id in (SELECT distinct this_0_.Id as y0_
                        FROM   Product this_0_
                               inner join ActorRole actor1_
                                 on this_0_.Id = actor1_.MovieId
                        WHERE  actor1_.Name like 'm%' /* @p0 */)

Ok this is not sub optimal SQL but you get the picture, we want to return distinct movies that have a at least one actor beginning with the letter a. The corresponding QueryOver code would look like this:-

var moviesWithActor = QueryOver.Of<Movie>()
  .JoinQueryOver<Actor>(m => m.ActorList)
  .Where(a => a.Name.IsLike("m%"))
  .Select(Projections.Distinct(Projections.Property<Movie>(m => m.Id)));

return Session
  .QueryOver<Movie>()
  .WithSubquery
  .WhereProperty(m => m.Id)
  .In(moviesWithActor)
.List();

This now returns 3 rows down the wire and we require no client side transfomations (filtering):-

Is this the final solution? In reality the optimum SQL might look something like this:-

SELECT distinct
  m.Id,
  m.Name,
  m.Description,
  m.UnitPrice
FROM
  Movie m
inner join 
  ActorRole a on m.Id = a.MovieId
WHERE a.Name like 'm%'

However I will leave this to another day!