NHibernate filter grand children collection

Question

I have the following objects

public class Parent {
    int Id;
    Object Obj1;
    Object Obj2;
    DateTime Date1;
    IList<Child> Children;
}

public class Child {
    int Id;
    Parent Parent;
    IList<GrandChild> GrandChildren;
}

public class GrandChild {
    int Id;
    Child Child;
    DateTime Date2;
}

I need to limit the GrandChild list by Date2. I tried the following

Parent parentAlias = null;
Child childAlias = null;
GrandChild grandChildAlias = null;

ICriterion dateCriterion = Restrictions.Where<GrandChild>(
    g => g.Date2.Date >= fromDate && g.Date2.Date <= toDate
);

var query = QueryOver.Of(() => parentAlias)
    .Inner.JoinAlias(() => parentAlias.Children, () => childAlias)
    .Inner.JoinAlias(() => childAlias.GrandChildren, () => grandChildAlias, dateCriterion)
    .Where(p => p.Obj1.Id == param1 && p.Obj2.Id == param2 && p.Date1 == paramDate);

var result = query.GetExecutableQueryOver(session).SingleOrDefault();

However this does not filter the grand child collection.

Your input is greatly appreciated.

Thank you.

score 1 · Answer 1 · edited May 23 '17 at 12:24

1

If you want a filtered collection you should query directly on GrandChildren and return a list of those objects. You can then join in the Parents and select a list of those in linq after you have fetched all of the Grandchildren

You may also be able to check out this article if this does not work for you: NHibernate - Retrieve parent / children with criteria applied only to children

edited May 23 '17 at 12:24

Community

1
1

answered Apr 06 '16 at 12:03

Cole W

15,123
6
51
85

Stefan Steinegger · Answer 2 · 2016-04-06T13:45:19.120

I'm not sure what your problem is. Probably you expect the lists to be filtered. This is not the case. Objects are always returned complete. Your query as you have it is filtering parents for properties of at least one of the grand children, while always returning complete objects.

(If this would be different, it would be strange to add grand children to the filtered list. Objects returned by the ORM are for working in an OOP way, not just read-only data as returned by SQL for instance.)

You could start the query with grans children, getting a (flat) filtered list of grand children. From each child, you could navigate to its parents by its properties:

var query = QueryOver.Of<GrandChild>()
    .Where(p => p.Obj1.Id == param1 && p.Obj2.Id == param2 && p.Date1 == paramDate);

When now navigating back to parents, you will find them complete again:

var countAllSiblings = queriedGrandChild.Child.GrandChildren.Count()

score 0 · Accepted Answer · answered Apr 06 '16 at 23:26

Afeter a bit more research all I had to do was change the grand child Inner to Left and got the desired effect.

var query = QueryOver.Of(() => parentAlias)
    .Inner.JoinAlias(() => parentAlias.Children, () => childAlias)
    .Left.JoinAlias(() => childAlias.GrandChildren, () => grandChildAlias, dateCriterion)
    .Where(p => p.Obj1.Id == param1 && p.Obj2.Id == param2 && p.Date1 == paramDate);

This limited the GrandChild list items in the Child object's list to those between the dates.

Hope this helps.

NHibernate filter grand children collection

3 Answers3