CODEWITHSUNDEEP
nhibernateeager-loading
Øyvind
Øyvind

Reputation: 1610

NHibernate, fetch entity and eager load part of child collection

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?

Upvotes: 0

Views: 2159

Answers (4)

tmatuschek
tmatuschek

Reputation: 608

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

Upvotes: 1

Øyvind
Øyvind

Reputation: 1610

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.

Upvotes: 0

Jamie Ide
Jamie Ide

Reputation: 49251

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:

  1. 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.
  2. 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.

Upvotes: 2

Mariusz
Mariusz

Reputation: 3154

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();

Upvotes: 0

Related Questions