NHibernate, fetch entity and eager load part of child collection

Question

I've come unstuck on how to do this:

I have a country entity. A country has zero or more facts. A fact has property called Year.

How can I load a Country entity by its name and eagerly fetch all its facts from a given year, e.g. 2011 (but not load all the other facts)? Is this even possible?

score 2 · Answer 1 · answered Nov 21 '12 at 15:32

I think this is possible using filtering but it's not a good idea because it violates separation of concerns: your domain model should not be used to present a special view of the data.

Two possibly better options:

Fetch all the facts and filter them by year using an extension method on IEnumerable<Fact>. I would pick this option unless I had a measurable performance issue.
Create a view class that encapsulates the result set. You can construct the two queries to retrieve the Country and Facts for a year using Future so that it only requires one trip to the database.

Thanks Jamie. I agree that filtering isn't the right approach. — Øyvind, Nov 21 '12 at 17:17

score 1 · Answer 2 · edited May 23 '17 at 10:28

1

I believe this is possible by using filters in nHibernate. Start out by reading this previous StackOverflow question:

NHibernate - Retrieve parent / children with criteria applied only to children

And also have a look at the nHibernate documentation for more reference:

http://nhibernate.info/doc/nh/en/index.html#filters

edited May 23 '17 at 10:28

Community

1
1

answered Nov 21 '12 at 13:37

tmatuschek

608
4
15

Mariusz · Answer 3 · 2012-11-21T14:56:09.547

0

EDIT:

 var query = session.QueryOver<Country>()
        .Fetch(c => c.Facts)
        .Eager()
        .TransformUsing(NHibernate.Transform.Transformers.DistinctRootEntity)
        .Future();
    session.QueryOver<Facts>()
        .Where(f => f.Year == 2011)
        .Future();
    var country = query.List().FirstOrDefault();

edited Nov 21 '12 at 14:56

answered Nov 21 '12 at 13:58

Mariusz

3,054
2
20
31

Hi Mariusz, thanks for your answer. Unfortunately this doesn't seem to compile? "Method, delegate or event is expected." Also, the expression f => f.Year == 2011 does not compile as "f" is not understood. – Øyvind Nov 21 '12 at 14:34
OK - the call to Eager should be a call to a property, not a method. But the Where() clause doesn't compile becase f is Country, not Fact – Øyvind Nov 21 '12 at 14:43
Mariusz, thanks for this. This runs two queries in one db hit but the returned result contains all facts, not just those from 2011. – Øyvind Nov 22 '12 at 09:26

Øyvind · Answer 4 · 2012-12-04T11:30:38.083

UPDATE: I have not solved this. The below HQL only works when facts are present. If there are no facts for a given year then the result is null. I think the correct solution, given my context, is to treat Fact as an aggregate root and issue two queries using Future().

Original post

I have solved this using HQL:

var country = Session.CreateQuery("from Country country left join fetch country.Facts as Facts where Facts.Year in (:years) and country.Name = :name ");
country.SetParameter("name", name);
country.SetParameterList("years", new List<int>() {2012, 2011});

Thanks for the other suggestions. I would be interested in a non-HQL solution to the problem, but this solves it well.

NHibernate, fetch entity and eager load part of child collection

4 Answers4