nhibernatemulti-query

NHibernate Multiquery for eager loading without joins

Is it possible to use a multiquery and have two hql queries returning two different sets of entities where one of the sets are used in the other and that the session "fixes" this via the first level cache?

E.g. scenario (a dumb one and it could be solved with joins)

public class Room
{
  ...
  public virtual ISet<Bookings> Bookings {get;set;}
  public virtual bool IsAvailible {get;set;}
  ...
}

public class Booking
{
  ...
}

After executing a multicriteria with two hql's:

  1. returning all rooms where IsAvailible = true
  2. returning all bookings having a room that has a room that IsAvailible

when accessing a room from the result and its bookings I want them to be resolved from the second resultset via the firstlevel cache of the session and there by avoiding n+1.

Solution

  • Generally speaking, NHibernate can use the cache to "combine" the results from queries executed through Multiquery. However, it should be noted that this usually only applies to cases where lazy collections are loaded with no restrictions whatsoever.

    Examples:

    Invoice iAlias = null;
InvoiceDetails idAlias = null;

// Base-Query: get Invoices with certain condition
var invoices = session.QueryOver<Invoice>()
    .Where(i => i.Number == "001")
    .Future<Invoice>();

// Option 1: this will still cause N+1 if we iterate through invoices,
// because it doesn't know better
var invoicedetails = session.QueryOver<InvoiceDetails>()
    .JoinAlias(a => a.Invoice, () => iAlias)
    .Where(() => iAlias.Number == "001")
    .Future<InvoiceDetails>();

// Option 2: this will still cause N+1 if we iterate through invoices,
// because we limited the possible results using a where-condition
var invoices2 = session.QueryOver<Invoice>()
    .Left.JoinAlias(i => i.Details, () => idAlias)
    .Where(i => i.Number == "001")
    .And(() => idAlias.Quantity > 5)
    .Future<Invoice>();

// Option 3: this will work without N+1, because we don't use a filter
// -> NHibernate will use the collection in cache
var invoices3 = session.QueryOver<Invoice>()
    .Left.JoinAlias(i => i.Details, () => idAlias)
    .Where(i => i.Number == "001")
    .Future<Invoice>();

foreach (Invoice i in invoices)
{
    int count = i.Details.Count;
}

    If we comment out two of the three options and execute the code, we will see that only option 3 will prevent a N+1, the other two will still load the InvoiceDetails for each Invoice in the loop.

    Of course this is a very simple example and it is obvious that Option 3 could also be executed without the Base-query and still return the same result, but I hope you get the idea.

    In the case where we load two different sets of entities, i.e. the root class is different as in Option 1, this "combining" will most likely not work.

    Sorry, if I used QueryOver instead of HQL, but the same rules apply.