NHibernate ThenFetchMany is retrieving duplicate children

Question

I have a parent object with a child collection containing one element, the child collection contains a "grandchild" collection containing 3 elements.

I am loading the parent object from the database using NHibernate as follows

Parent parentObject = session.Query<Parent>()
    .FetchMany(x => x.Children)
    .ThenFetchMany(x => x.GrandChildren)
    .Where(x => x.Id = "someparentid")
    .Single();

What I'm finding is that there are duplicate children objects (3 in total) attached to the parent object when there should be only one. (There are 3 grandchild objects correctly attached to each child.) Eager loading the children collection only works correctly.

Do you know how I can achieve loading of the full parent object without duplicate children?

score 5 · Answer 1 · answered Jun 01 '11 at 05:19

5

If you map Children and GrandChildren as set, you can avoid the cartesian product. You need to define Children and Grandchildren as collections:

public class Parent
{
    ...
    public virtual ICollection<Child> Children { get; set; }
    ...
}

public class Child
{
    ...
    public virtual ICollection<GrandChild> GrandChildren { get; set; }
    ...
}

And in the mapping (using FluentNHibernate):

public class ParentMapping : ClassMap<Parent>
{
    public ParentMapping()
    {
        ...
        HasMany(x => x.Children)
            .KeyColumn("ParentID")
            .Inverse
            .AsSet()
        ...
    }
}

public class ChildMapping : ClassMap<Child>
{
    public ChildMapping()
    {
        ...
        HasMany(x => x.GrandChildren)
            .KeyColumn("ChildID")
            .Inverse
            .AsSet()
        ...
    }
}

answered Jun 01 '11 at 05:19

kay.herzam

3,053
3
26
37

To be precise, the query still delivers the cartesian product, but you will get a single Perent. – kay.herzam Jun 01 '11 at 05:21
Is there a downside to this approach that you wouldn't experience by using QueryOver? – 2-bits Mar 01 '12 at 22:23
Thanks for this. I found that in a similar case the AsSet directive was enough to warrant no more duplicates – mabian69 Aug 27 '12 at 15:58
This just stopped a 2 days hunt for a solution. Thanks a lot. – Thomas Lazar Feb 03 '15 at 13:21

score 2 · Answer 2 · answered Aug 25 '12 at 11:18

2

If you are using Linq, you can simplify it with this:

int parentId = 1;
var p1 = session.Query<Parent>().Where(x => x.ParentId == parentId);

p1
.FetchMany(x => x.Children)
.ToFuture();

sess.Query<Child>()
.Where(x => x.Parent.ParentId == parentId);
.FetchMany(x => x.GrandChildren)
.ToFuture();

Parent p = p1.ToFuture().Single();

Detailed explanation here: http://www.ienablemuch.com/2012/08/solving-nhibernate-thenfetchmany.html

answered Aug 25 '12 at 11:18

Michael Buen

38,643
9
94
118

Thanks Michael, though I haven't sample code to prove it, all those examples linked to, fail when the children being fetched (who also have grandchildren to fetch) is in a many-to-many relationship with the parent. I've tried all that example (and many others) and all of them still produce cartesian products in this case – PandaWood Sep 13 '16 at 06:29

score 2 · Accepted Answer · edited May 23 '17 at 12:08

2

I was able to use the answer here using QueryOver, it correctly loads the objects while generating efficient SQL (selects per table instead of one huge join).

edited May 23 '17 at 12:08

Community

1
1

answered Jun 01 '11 at 21:27

Simon

1,499
3
17
23

score 1 · Answer 4 · edited Jun 01 '11 at 00:11

1

You can't do it with NHibernate (I don't think you can do it with EF4 either) since your result is a Cartesian product. You're getting all results from all tables.

NHibernate doesn't know how to map the results from both collections back to the root. So for each Children, you get the same number of GrandChildren, and for each GrandChildren you end up with the same number of Children.

edited Jun 01 '11 at 00:11

csano

13,266
2
28
45

answered May 31 '11 at 23:57

Phill

18,398
7
62
102

I've profiled the SQL query being generated and there are two left joins, with a join condition on primary key and foreign key on both, so I am fairly sure it is not a cartesian product. I have achieved this using LinqToSql in the past. – Simon Jun 01 '11 at 00:15
@Simon - Take the query and put it into SQL Server Management Studio and run the query, you will see all results from all tables. It IS possible to achieve the same result but you would have to write HQL. See Ayende's blog for deep object graphs - http://ayende.com/blog/2580/efficently-loading-deep-object-graphs – Phill Jun 01 '11 at 00:21
2

You can do it with Entity Framework. Currently, I happen to be making a repository interface that supports both EF and NHibernate. It's a big disappointment that NHibernate's ThenFetchMany is next to useless; EF can fetch the whole object graph with just one query(without the excuses of Cartesian product), yet there's no resulting duplicates in parent, nor children, nor grandchildren – Michael Buen Aug 13 '11 at 23:56
Have to agree with Michael here, I've been bouncing around trying to fix a cartesian product on grandchildren of a many-to-many relationship and that EF can do it and NHibernate "cannot" is definitely disappointing. If you're looking this problem up and have the option to use EF, then switch to it now. – PandaWood Sep 13 '16 at 06:24

NHibernate ThenFetchMany is retrieving duplicate children

4 Answers4

Linked