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.
