Queryover dynamic fetch with joins

Question

iam trying a new query with nhibernate and find a new problem :(

take this as model:

public class D { int id; }
public class C { int id; }
public class B {
    int id;
    ICollection<C> Cs;
    ICollection<D> Ds;
}
public class A {
    int id;
    ICollection<B> Bs;
}

i want A object that have a particular B object and dinamically eager fetch Cs or Ds collection of selected B:

public virtual A Read(int idB, params Expression<Func<Attivita, object>>[] eagerFields)

i start with

IEnumerable<A> query = _session.QueryOver<A>()
                            .JoinQueryOver(a => a.Bs)
                            .Where(b => b.Id == idB)
                            .Future<A>();

foreach (Expression<Func<A>, object>> field in eagerFields)
    _session.QueryOver<A>()
        .Fetch(field).Eager
        .Future<A>();

return query.First();   

but eager load is not applyed: if i test this:

Read(12, a => a.Bs, a.Bs.First().Cs, a.Bs.First().Ds)

i see many query executed and Cs and Ds throw lazy inizializazion error

i found this and read that eager have problem without leftJoin so switch first part to this:

B BB= null;
IEnumerable<A> query =_session.QueryOver<A>()
        .Fetch(a => a.Bs).Eager
        .Left.JoinAlias(a => a.Bs, () => BB)
        .Where(() => BB.Id == idB)
        .Future<A>();

but have same problem

looking at similar fetch done in other case seem that possible cause can be a.Bs.First().Ds as parameter selection for fetch

EDIT: just to clarify:

this works:

IEnumerable<A> query = _session.QueryOver<A>()
.Left.JoinAlias(a => a.Bs, () => BB)
.Where(() => BB.Id == IdB)
.Fetch(a => a.Bs).Eager
.Fetch(a => a.Bs.First().Cs).Eager
.Future<A>();
return query.First();

while this no:

IEnumerable<A> query = _session.QueryOver<A>()
                        .JoinQueryOver(a => a.Bs)
                        .Where(b => b.Id == idB)
                        .Future<A>();

foreach (Expression<Func<A>, object>> field in eagerFields)
    _session.QueryOver<A>()
        .Fetch(field).Eager
        .Future<A>();

return query.First();   

called in this way: Read(12, a => a.Bs, a.Bs.First().Cs, a.Bs.First().Ds)

Indebtedness

My way: never use eager fetching... always rely on related collection lazy loading - with native batching - see Avoid N+1 Select with list of NHibernate entities or Fluent NHibernate N+1 issue with complex objects – Scrappy

problem is in webform i read much about session lifecycle, but best compromise is to eager load all i need in page load and close session immediatly after. all works fine with this exception and have 90% of my application just done in this way – Indebtedness

No, that may be best compromise for EF, but not for NHibernate. Have your read posts linked by Radim? Here is yet another one explaining why. If you use the entities after having closed the session, then you need to trigger the lazy load before this closing, eventually by iterating other your entities. But usually I would rather fetch a view model with entities data instead, then close the session, and only use the view model from that point. – Bartie

still working on webForm, no MVC :( previuos version was doing .count() for every needed collection when session is open, but swtiched all to dynamic eager (see #40629416). all works fine, except for second level of property – Indebtedness

question updated to show that problem is not in eager fetch but in dynamic creation of future query – Indebtedness

Working with webform does not prevent you of using a view model pattern. Indeed we may even consider the code behind as kind of (bloated and mixed up up with controller) view model. When having to deal with webform, I usually put in code behind a public DTO property (sometimes many) for acting as a view model for the aspx template. – Bartie

Answer 1

Looking at your actual trouble, eager loading, I do not see why you put future in it that way. Your current code should logically be wrong: it issues a query with your filtering criteria then a bunch of "load all A entities with an eager loaded property" queries...

If your fetched properties are not collections (or only one is a collection), you should write it that way:

IQueryOver<A, A> query = _session.QueryOver<A>()
    .JoinQueryOver(a => a.Bs)
    .Where(b => b.Id == idB);

foreach (Expression<Func<A>, object>> field in eagerFields)
    query = query
        .Fetch(field).Eager;

return query.List().First();

This is a single query, immediately executed after being created: if you do not have other Future query awaiting execution, there is no point calling it with Future.

If you have many collections to eager load, this would result in a Cartesian product. To avoid it you then need to split them in many queries. There Future could be useful. (But once again as said in your question comments, lazy-loading fare better for this: no need to bloat the loading logic with eager loading considerations, just setup batching size in mappings, and ensure you are done using your entities before closing the session.)

var queryBase = _session.QueryOver<A>()
    .JoinQueryOver(a => a.Bs)
    .Where(b => b.Id == idB);

var queries = new List<IEnumerable<A>>();
foreach (Expression<Func<A>, object>> field in eagerFields)
    queries.Add(queryBase
        .Fetch(field).Eager
        .Future());

return queries.Count == 0 ? queryBase.List().First() : queries[0].First();

Please note that with NHibernate 5 and above, if your data provider does not actually support future (multiple queries in a single SQL command), futures which have not been explicitly executed will be discarded without being executed. (Previous versions were executing future queries immediately at the Future call with data providers not actually supporting them.)

For having them executed even with data provider not supporting futures, change the last line to:

if (queries.Count == 0)
    return queryBase.List().First();

List<A> result;
foreach (var q in queries)
{
    // Using the IFutureEnumerable directly as an IEnumerable is deprecated.
    result = q.GetEnumerable()
        // Due to a bug, GetEnumerable is not yet enough to trigger execution.
        .ToList();
}

return result.First();

Millennial