NHibernate and optimising with lazy='extra'

I came across a stack over flow post today that asked how you could retrieve the count of children from a parent without having to load the entire collection.

The answer is quite simple all you need is to add lazy='extra' on your mappings. Lets put this to the test, for the following domain:-

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

The only mapping of importance is the mapping for the movie:-

public class MovieMapping : SubclassMapping<Movie> {
  public MovieMapping() {
    Property(x => x.Name, x => x.NotNullable(true));
    Property(x => x.Director, x => x.NotNullable(true));
    Bag(x => x.ActorList, bag => {
      bag.Key(k => { 
         k.Column(col => col.Name("MovieId"));
         k.NotNullable(true); 
      });
      bag.Cascade(Cascade.All | Cascade.DeleteOrphans);
      bag.BatchSize(10);
    }, action => action.OneToMany());
  }
}

We now have the following code that retrieves a Movie and then the count of all actors:-

var movie = Session.Query<Movie>()
              .Where(w => w.Id == id).SingleOrDefault();
var actorCount = movie.ActorList.Count();

This actually will send two queries to the database:-

-- statement #1
select movie0_.Id          as Id1_,
       movie0_.Name        as Name1_,
       movie0_.Director    as Director1_
from   Movie movie0_
where  movie0_.Id = 'ffffffff-eeee-dddd-cccc-000000000005' /* @p0 */

-- statement #2
SELECT actorlist0_.MovieId as MovieId1_,
       actorlist0_.Id      as Id1_,
       actorlist0_.Id      as Id2_0_,
       actorlist0_.Name    as Name2_0_,
       actorlist0_.Role    as Role2_0_
FROM   ActorRole actorlist0_
WHERE  actorlist0_.MovieId = 'ffffffff-eeee-dddd-cccc-000000000005' /* @p0 */

As you can see this is NOT the intended results we would expect. We have the first SQL query that retrieves a Movie then the second SQL query performs a select * from ActorRole. This means that the second query selects the whole collection and performs the count in memory.

Is there a easy solution that we can use to instruct NHibernate to use a select count(*) from rather than a select * ? If you read the title of this Blog and are still awake, you are probably screaming out lazy='extra'. So how do we use this?

If you are using XML mappings then you would use:-

   ...

If you are using the new mapping by code then you can achieve the same by adding:-

Bag(x => x.ActorList, bag => {
  bag.Key(k => { 
    k.Column(col => col.Name("MovieId"));
    k.NotNullable(true); 
  });
  bag.Cascade(Cascade.All | Cascade.DeleteOrphans);
  bag.BatchSize(10);
  bag.Lazy(CollectionLazy.Extra);
}, action => action.OneToMany());

Now lets look at the SQL that gets sent to the database:-

-- statement #1
select movie0_.Id          as Id1_,
       movie0_.Name        as Name1_,
       movie0_.Director    as Director1_
from   Movie movie0_
where  movie0_.Id = 'ffffffff-eeee-dddd-cccc-000000000005' /* @p0 */

-- statement #2
SELECT count(Id)
FROM   ActorRole
WHERE  MovieId = 'ffffffff-eeee-dddd-cccc-000000000005' /* @p0 */

It should also be noted that it does not matter if you are using HQL, Query, QueryOver or ICriteria to query your data the outcome is exactly the same. Don't you just love it when being able to tweak the mappings so as to optimise your code.