NHibernate N+1 select : architectural solution ?

Question

Considering the following "database" :

Post -> User

The entities :

class User
{
    public virtual string Name { get; set; }
    public virtual IList<Post> Posts { get; set; }
}

class Post
{
    public virtual string Title { get; set; }
    public virtual User Author { get; set; }
}

And the following service Layer:

class UserService
{
    IRepositoryUsers repositoryUsers;

    IList<User> GetUsers()
    {
        return this.repositoryUsers.GetAllUsers();
    }
}

When I want to print all users, with associated post count, I get (no surprise here) a N+1 select problem, as for each line, it will create a select to get the posts for the users.

Now here is my question : what is the "best" way to handle this, as there are some cases when I don't want to eager load each user's posts.

Should I create as many methods in my repository (and service) to match those scenarios ?

Answer 1

In general, there's a few ways to get rid of a Select N+1 problem. With NHibernate, one of my favorite ways to do this is to use the Criteria API, which is very sensible and well thought-out.

session.CreateCriteria(typeof(Fruit))            // Get me some fruit.
    .SetFetchMode("CitrusType", FetchMode.Eager) // Just get this one field.
    .Add(Expression.Eq("Basket", 47))            // Only fruit that are in this basket.
    .List();                                     // Get 'em all.

To read more about this and other strategies, I recommend a look at this interesting blog post.

Answer 2

One thing you could do is to have a fetching strategy for every scenario in which you need to retrieve the data. This blog post explains a possible solution for this type of problem, the author uses his own version of IRepository but you use the same ideas in your repository (or check out his NCommon project to get some inspiration)