Fluent NHibernate N+1 issue with complex objects

Question

I'm having a problem with NHibernate querying the database way too many times. I just realized it likely relates to the n+1 problem but I can't figure out how to change my mappings to solve the problem.

As you will see, my attempts involve specifying not to lazy load other objects, but it doesn't seem to do the trick.

This is the query:

public IQueryable<Report> ReadAll(DateTime since)
{
    return m_session.QueryOver<Report>()
       .JoinQueryOver(r => r.Mail)
       .Where(m => m.Received >= since)
       .List()
       .AsQueryable();
}

Thanks in advance for any response! If you need any more information about my objects or mappings, please let me know.

Simplified objects graph (some omitted):

public class Report : EntityBase
{
    public virtual Product Product { get; set; }
    public virtual StackTrace StackTrace { get; set; }
    public virtual Mail Mail { get; set; }

    public virtual IList<ClientUser> ReadBy { get; set; }
}

-

public class Product : EntityBase
{
    public virtual string Name { get; set; }
    public virtual Version Version { get; set; }
    public virtual IList<Report> Reports { get; set; }
    public virtual IList<StackTrace> StackTraces { get; set; }
}

-

public class StackTrace : EntityBase
{
    public virtual IList<StackTraceEntry> Entries { get; set; }
    public virtual IList<Report> Reports { get; set; }
    public virtual Product Product { get; set; }
}

Mapping examples:

public class ReportMap : ClassMap<Report>
{
    public ReportMap()
    {
        Table("Report");

        References(x => x.User)
          .Column("EndUserId")
          .Not.LazyLoad();

        References(x => x.Product)
          .Column("ProductId")
          .Not.LazyLoad();

        References(x => x.StackTrace)
          .Column("StackTraceId")
          .Not.LazyLoad();

        HasManyToMany(x => x.ReadBy)
          .Cascade.SaveUpdate()
          .Table("ClientUserRead")
          .ParentKeyColumn("ReportId")
          .ChildKeyColumn("ClientUserId")
          .Not.LazyLoad().BatchSize(200);
    }
}

-

public class StackTraceMap : ClassMap<StackTrace>
{
    public StackTraceMap()
    {
        Table("StackTrace");

        References(x => x.Product)
          .Column("ProductId");

        HasMany(x => x.Entries)
          .KeyColumn("StackTraceId")
          .Not.LazyLoad()
          .Cascade
          .All().BatchSize(500);

        HasMany(x => x.Reports)
          .KeyColumn("StackTraceId")
          .Inverse().BatchSize(100);
    }
}

Answer 1

The way to go is to use batch fetching. Read more about it here:

How to Eager Load Associations without duplication in NHibernate?

On every entity mapping apply BatchSize (for many-to-one relation - avoiding 1 + N)

public ReportMap()
{
   Table(...)
   BatchSize(25);
   ...

And on every collection (solves issue with one-to-many 1 + N)

HasMany(x => x.Reports)
      .KeyColumn("StackTraceId")
      .BatchSize(25)
      ...

Answer 2

You can specify fetch paths in the query. For example, this fetch path can tell the query to eagerly join product and main objects, as well as an imaginary association on the collection of clients:

public IQueryable<Report> ReadAll(DateTime since)
{
    return m_session.QueryOver<Report>()
       .JoinQueryOver(r => r.Mail)
       .Where(m => m.Received >= since)
       .Fetch(x => x.Product).Eager
       .Fetch(x => x.Mail).Eager
       .Fetch(x => x.ReadBy.First().SomeProp).Eager
       .List()
       .AsQueryable();
}

If you want that always to happen, try using .Fetch.Join() instead of .Not.LazyLoad() for the assocations. But I would not recommend that because it can cause simple queries to become huge. Batching or subqueries can help too.