Linq to NHibernate - eager load grandchildren but not children

Question

I am using NHibernate 3.2 with FluentNHibernate and Linq to NHibernate. I want use Linq to NHibernate to eager load all of the grandchildren of a collection without having to load the children. For example, say that I have the following classes:

public class Parent
{
    public virtual int Id { get; set; }
    public virtual IList<Child> Children { get; set; }
}

public class ParentMap : ClassMap<Parent>
{
    Id(x => x.Id);
    HasManyToMany(x => x.Children).ExtraLazyLoad();
}

public class Child
{
    public virtual int Id { get; set; }
    public virtual IList<Parent> Parents { get; set; }
    public virtual IList<Grandchild> Grandchildren { get; set; }
    public virtual ProhibitivelyLargeType ProhibitivelyLargeField { get; set; }
    public virtual ProhibitivelyLargeType RarelyUsedLargeField { get; set; }
}

public class ChildMap : ClassMap<Child>
{
    Id(x => x.Id);
    HasManyToMany(x => x.Parents).ExtraLazyLoad();
    HasManyToMany(x => x.Grandchildren).ExtraLazyLoad();
    Map(x => x.ProhibitivelyLargeField);
    Map(x => x.RarelyUsedField).LazyLoad();
}

public class Grandchild
{
    public virtual int Id { get; set; }
    public virtual IList<Child> Children { get; set; }
    public virtual int Age { get; set; }
}

public class GrandchildMap : ClassMap<Grandchild>
{
    Id(x => x.Id);
    HasManyToMany(x => x.Children).ExtraLazyLoad();
    Map(x => x.Age);
}

For each Parent, I want to find out the total combined age of all of that parent's grandchildren. I could do so using the following method:

Dictionary<Parent, int> grandchildAges = session.Query<Parent>()
    .FetchMany(p => p.Children)
    .ThenFetchMany(c => c.Grandchildren)
    .AsEnumerable()
    .ToDictionary(
        p => p,
        p => p.Children.SelectMany(c => c.Grandchildren).Sum(g => g.Age)
    );

This method produces the correct results. However, it necessitates loading all of the Child objects. Child includes a field of type ProhibitivelyLargeType, which is not lazy loaded, so I would really prefer to not load anything about Child but its ID. If I don't use FetchMany/ThenFetchMany, however, then I have the N + 1 problem and it takes a trip to the database for each Child and Grandchild, which is also unacceptable.

Alternatively, I could make ProhibitivelyLargeField LazyLoad. However, most applications that use the Child class need to use ProhibitivelyLargeField, but they do not want to have to load RarelyUsedLargeField, which is already LazyLoad. As I understand it, loading one LazyLoad property causes all of them to be loaded, so this solution would bog down the normal use case.

Is there a way to get the just the information that I am looking for using Linq to NHibernate, or do I have to use the Criteria Query API?

Thanks!

edited to give an example of why making ProhibitivelyLargeField LazyLoad might be undesirable

Answer 1

I haven't used nhibernate, but I have used linq to entities, and from what I see you are doing tons of database queries. You should instead do a single line query which returns only the data you want:

from parent in session.Parents
let children = parent.Children
select new {parent = parent, children.SelectMany(c => c.Grandchildren).Sum(gc => gc.Age)}

Apologies if I got something wrong. Haven't done C# in a while and I'm on my phone.

If this approach doesn't work someone just tell me and I'll delete it.

Answer 2

the following is QueryOver. its only to show the idea of loading the results in two smaller steps. Maybe you can translate it into LINQ

// inititialize the dictionary
Grandchild grandchild = null;
Dictionary<Parent, int> dict = session.QueryOver<Parent>()
    .JoinQueryOver(p => p.Childs)
    .JoinAlias(c => c.GrandChilds, () => grandchild)
    .Select(Projections.Group<Parent>(p => p.Id), Projections.Sum(() => grandchild.Age))
    .AsEnumerable()
    .Cast<object[]>()
    .ToDictionary(
        array => session.Load<Parent>(array[0]),
        array => (int)array[1]
    );

// initialize all Parent proxies
session.QueryOver<Patient>()
    .WhereProperty(p => p.Id).In(dict.Keys.Select(p => p.Id))
    .ToList();