Is NHibernate FetchMany on more than two tables broken?

Question

Given this:

namespace TheEntities
{
    [DataContract(IsReference=true)]    
    public class Question
    {
        [DataMember] public virtual int QuestionId { get; set; }
        [DataMember] public virtual string Text { get; set; }
        [DataMember] public virtual string Poster { get; set; }

        [DataMember] public virtual IList<QuestionComment> Comments { get; set; }
        [DataMember] public virtual IList<Answer> Answers{ get; set; }



        [DataMember] public virtual byte[] RowVersion { get; set; }
    }

    [DataContract]
    public class QuestionComment
    {
        [DataMember] public virtual Question Question { get; set; }        

        [DataMember] public virtual int QuestionCommentId { get; set; }
        [DataMember] public virtual string Text { get; set; }
        [DataMember] public virtual string Poster { get; set; }
    }


    [DataContract(IsReference = true)]
    public class Answer
    {
        [DataMember] public virtual Question Question { get; set; }

        [DataMember] public virtual int AnswerId { get; set; }
        [DataMember] public virtual string Text { get; set; }
        [DataMember] public virtual string Poster { get; set; }

        [DataMember] public virtual IList<AnswerComment> Comments { get; set; }

    }

    [DataContract]
    public class AnswerComment
    {
        [DataMember] public virtual Answer Answer { get; set; }

        [DataMember] public virtual int AnswerCommentId { get; set; }
        [DataMember] public virtual string Text { get; set; }
        [DataMember] public virtual string Poster { get; set; }
    }

}

Entity Framework didn't produce duplicate objects for Answer, QuestionComment, AnswerComment while NHibernate does.

public Question OpenQuestion(int id)
{
    var repo = QuestionRepository;

    var query = repo.All.Where(y => y.QuestionId == id);

    if (QuestionRepository.GetType() == typeof(EfRepository<Question>))
    {                
        query = query
                .Include("Answers")
                    .Include("Answers.Comments")
                .Include("Comments");

        return query.Single();
    }
    else if (QuestionRepository.GetType() == typeof(NhRepository<Question>))
    {                
        // kinda sad, produces duplicate objects
        query = query
                .FetchMany(x => x.Answers)
                    .ThenFetchMany(x => x.Comments)
                .FetchMany(x => x.Comments);
    }
    else
        throw new Exception("Something unsupported");

    return query.Single();
}

This also produces duplicate objects (three levels deep, using three relations):

query = query
    .FetchMany(x => x.Answers)
    .ThenFetchMany(x => x.Comments)

This also produces duplicate objects(two levels deep only, but using three relations):

query = query
    .FetchMany(x => x.Answers)
    .FetchMany(x => x.Comments);

This doesn't produces duplicate objects, however the eager loading is for two levels deep only and two relations, i.e. from Question to Answer. For comments to question, and comments to answer, they are performed on separate query.

query = query
    .FetchMany(x => x.Answers);

If NHibernate can only do its job well for two levels FetchMany only with two relations only, why bother creating ThenFetchMany(used on three levels, but has error, has duplicate objects)? In fact, even FetchMany is useless too if you want to use it on three relations also, it produces duplicate objects too.

Can NHibernate team be bothered to remove ThenFetchMany anyway since it can't work properly?

There's no error in my mapping, things are working properly (i.e. doesn't produce duplicate objects) when I removed the fetching strategies.

you can do what you want - append .TrasformUsing(Transformers.DistinctRootentity). this is annoying sure. — Firo
That's available on QueryOver only, I'm using NH's Linq(IQueryable) however. What's the NH Linq extension method equivalent to .TransformUsing ? — Michael Buen
This is because Entity Framework creates a query using some UNIONs while NHibernate does a JOIN. The JOIN causes cartesian products, hence the duplicate entities. EF's strategy is better in this case because it allows to get much information in one query. — Sebazzz

Firo Firo · Accepted Answer · 2011-08-12T07:39:10

to get unique results do

for Linq:

.Distinct()

for QueryOver

.TrasformUsing(Transformers.DistinctRootentity)

for Criteria

.SetResulttransformer(Transformers.DistinctRootentity)

EDIT: effectivly this is a shortcoming of NH, it will issue a cartesian product, but this can be improved

repo.All.Where(y => y.QuestionId == id)
        .FetchMany(x => x.Answers)
            .ThenFetchMany(x => x.Comments)
        .Future()

query = repo.All.Where(y => y.QuestionId == id)
        .FetchMany(x => x.Comments)

var result = query.AsEnumerable().Single();

see http://ayende.com/blog/4367/eagerly-loading-entity-associations-efficiently-with-nhibernate

it looks a bit weird but should do

Is NHibernate FetchMany on more than two tables broken?

2 Answers