What's the problem with NHibernate's ThenFetchMany? Simple answer:
It Doesn't Work™
Contrast with Entity Framework's sophisticated(complex?) union approach, NHibernate merely join tables blindly, resulting to unintended duplicate objects, aka cartesian product
So while this work out-of-the-box in Entity Framework. It's debatable though if one would want how Entity Framework is able to
seamlessly achieve that feat
static void TestEF()
{
using (var db = new EfMapping())
{
var q = db.Set<Question>()
.Include("AskedBy")
.Include("QuestionModifiedBy")
.Include("QuestionComments")
.Include("Answers")
.Include("Answers.AnswerComments")
.Where(x => x.QuestionId == 1)
.Single();
Console.WriteLine("{0}", q.QuestionText);
Console.ReadKey();
Console.WriteLine("{0}", q.AskedBy.PersonId);
Console.ReadKey();
Console.WriteLine("{0}", q.AskedBy.PersonName);
Console.ReadKey();
Console.WriteLine("Question Comments Count should be 2: {0}", q.QuestionComments.Count());
Console.ReadKey();
Console.WriteLine("Answers Count should be 3: {0}", q.Answers.Count());
Console.ReadKey();
Console.WriteLine("Answer #1 Comments Count should be 5: {0}", q.Answers[0].AnswerComments.Count());
Console.ReadKey();
Console.WriteLine("Answer #2 Comments Count should be 4: {0}", q.Answers[1].AnswerComments.Count());
Console.ReadLine();
}
}
This doesn't work in NHibernate:
static void TestNhProb2()
{
using (var sess = NhMapping.GetSessionFactory().OpenSession())
{
var q1 = sess.Query<Question>()
.Fetch(x => x.AskedBy)
.Fetch(x => x.QuestionModifiedBy)
.FetchMany(x => x.Answers)
.ThenFetchMany(x => x.AnswerComments) // Doesn't work as advertised. This causes duplicate/cartesianed rows
.FetchMany(x => x.QuestionComments)
.Where(x => x.QuestionId == 1);
var q = q1.Single();
Console.WriteLine("{0}", q.QuestionText);
Console.ReadKey();
Console.WriteLine("{0}", q.AskedBy.PersonId);
Console.ReadKey();
Console.WriteLine("{0}", q.AskedBy.PersonName);
Console.ReadKey();
Console.WriteLine("Question Comments Count should be 2: {0}", q.QuestionComments.Count());
Console.ReadKey();
Console.WriteLine("Answers Count should be 3: {0}", q.Answers.Count());
Console.ReadKey();
Console.WriteLine("Answer #1 Comments Count should be 5: {0}", q.Answers[0].AnswerComments.Count());
Console.ReadKey();
Console.WriteLine("Answer #2 Comments Count should be 4: {0}", q.Answers[1].AnswerComments.Count());
}
}
Though you can fix NHibernate eager loading(Fetch,FetchMany) problem with ToFuture, its ThenFetchMany still causes duplicate rows, hence resulting to duplicate objects. In the following code, though QuestionComments will have correct count, the count for Answers and AnswerComments will all be wrong.
static void TestNhProb()
{
using (var sess = NhMapping.GetSessionFactory().OpenSession())
{
var q1 = sess.Query<Question>().Where(x => x.QuestionId == 1);
q1
.FetchMany(x => x.QuestionComments)
.ToFuture();
q1
.Fetch(x => x.AskedBy)
.Fetch(x => x.QuestionModifiedBy)
.FetchMany(x => x.Answers)
.ThenFetchMany(x => x.AnswerComments) // Doesn't work as advertised. This causes duplicate/cartesianed rows
.ToFuture();
var q = q1.ToFuture().Single();
Console.WriteLine("{0}", q.QuestionText);
Console.ReadKey();
Console.WriteLine("{0}", q.AskedBy.PersonId);
Console.ReadKey();
Console.WriteLine("{0}", q.AskedBy.PersonName);
Console.ReadKey();
Console.WriteLine("Question Comments Count should be 2: {0}", q.QuestionComments.Count());
Console.ReadKey();
Console.WriteLine("Answers Count should be 3: {0}", q.Answers.Count());
Console.ReadKey();
Console.WriteLine("Answer #1 Comments Count should be 5: {0}", q.Answers[0].AnswerComments.Count());
Console.ReadKey();
Console.WriteLine("Answer #2 Comments Count should be 4: {0}", q.Answers[1].AnswerComments.Count());
}
}
To really fix that, you have to do an extra hoops, you need to separate the eager fetching of AnswerComments from the Answer by filtering them from the Answer of the Question.
static void TestNhOk()
{
using (var sess = NhMapping.GetSessionFactory().OpenSession())
{
int id = 1;
var q1 = sess.Query<Question>().Where(x => x.QuestionId == id);
q1
.FetchMany(x => x.QuestionComments)
.ToFuture();
q1
.Fetch(x => x.AskedBy)
.Fetch(x => x.QuestionModifiedBy)
.FetchMany(x => x.Answers)
.ToFuture();
sess.Query<Answer>()
.Where(x => x.Question.QuestionId == id)
.FetchMany(x => x.AnswerComments)
.ToFuture();
var q = q1.ToFuture().Single();
Console.WriteLine("{0}", q.QuestionText);
Console.ReadKey();
Console.WriteLine("{0}", q.AskedBy.PersonId);
Console.ReadKey();
Console.WriteLine("{0}", q.AskedBy.PersonName);
Console.ReadKey();
Console.WriteLine("Question Comments Count should be 2: {0}", q.QuestionComments.Count());
Console.ReadKey();
Console.WriteLine("Answers Count should be 3: {0}", q.Answers.Count());
Console.ReadKey();
Console.WriteLine("Answer #1 Comments Count should be 5: {0}", q.Answers[0].AnswerComments.Count());
Console.ReadKey();
Console.WriteLine("Answer #2 Comments Count should be 4: {0}", q.Answers[1].AnswerComments.Count());
Console.ReadLine();
}
}
Models:
using System.Collections.Generic;
namespace NHibernateFetchProblems.Models
{
public class Question
{
public virtual int QuestionId { get; set; }
public virtual string QuestionText { get; set; }
public virtual Person AskedBy { get; set; }
public virtual Person QuestionModifiedBy { get; set; }
public virtual IList<QuestionComment> QuestionComments { get; set; }
public virtual IList<Answer> Answers { get; set; }
}
public class QuestionComment
{
public virtual Question Question { get; set; }
public virtual int QuestionCommentId { get; set; }
public virtual string QuestionCommentText { get; set; }
public virtual Person QuestionCommentBy { get; set; }
}
public class Answer
{
public virtual Question Question { get; set; }
public virtual int AnswerId { get; set; }
public virtual string AnswerText { get; set; }
public virtual Person AnsweredBy { get; set; }
public virtual Person AnswerModifiedBy { get; set; }
public virtual IList<AnswerComment> AnswerComments { get; set; }
}
public class AnswerComment
{
public virtual Answer Answer { get; set; }
public virtual int AnswerCommentId { get; set; }
public virtual string AnswerCommentText { get; set; }
public virtual Person AnswerCommentBy { get; set; }
}
public class Person
{
public virtual int PersonId { get; set; }
public virtual string PersonName { get; set; }
}
}
NHibernate mapping:
using System;
using System.Collections.Generic;
using System.Linq;
using NHibernate;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Automapping;
using FluentNHibernate.Conventions;
using FluentNHibernate.Conventions.Instances;
using NHibernateFetchProblems.Models;
namespace NHibernateFetchProblems.DbMapping
{
public static class NhMapping
{
private static ISessionFactory _isf = null;
public static ISessionFactory GetSessionFactory()
{
if (_isf != null) return _isf;
var cfg = new StoreConfiguration();
var sessionFactory = Fluently.Configure()
.Database(MsSqlConfiguration.MsSql2008.ShowSql().ConnectionString(
"Server=localhost; Database=NhFetch; Trusted_Connection=true; MultipleActiveResultSets=true"
))
.Mappings(m =>
m.AutoMappings
.Add(AutoMap.AssemblyOf<Person>(cfg)
.Conventions.Add<ReferenceConvention>()
.Override<Question>(x => x.HasMany(y => y.QuestionComments).KeyColumn("Question_QuestionId").Cascade.AllDeleteOrphan().Inverse())
.Override<Question>(x => x.HasMany(y => y.Answers).KeyColumn("Question_QuestionId").Cascade.AllDeleteOrphan().Inverse())
.Override<Answer>(x => x.HasMany(y => y.AnswerComments).KeyColumn("Answer_AnswerId").Cascade.AllDeleteOrphan().Inverse())
)
)
.BuildSessionFactory();
_isf = sessionFactory;
return _isf;
}
}
public class StoreConfiguration : DefaultAutomappingConfiguration
{
readonly IList<Type> _objectsToMap = new List<Type>()
{
// whitelisted objects to map
typeof(Person), typeof(Question), typeof(QuestionComment), typeof(Answer), typeof(AnswerComment)
};
public override bool IsId(FluentNHibernate.Member member)
{
// return base.IsId(member);
return member.Name == member.DeclaringType.Name + "Id";
}
public override bool ShouldMap(Type type) { return _objectsToMap.Any(x => x == type); }
}
public class ReferenceConvention : IReferenceConvention
{
public void Apply(IManyToOneInstance instance)
{
instance.Column(
instance.Name + "_" + instance.Property.PropertyType.Name + "Id");
}
}
}
Entity Framework mapping:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using NHibernateFetchProblems.Models;
namespace NHibernateFetchProblems.DbMapping
{
public class EfMapping : DbContext
{
public EfMapping()
{
this.Configuration.ProxyCreationEnabled = true;
}
public DbSet<Person> Persons { get; set; }
public DbSet<Question> Questions { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
}
}
}
DDL:
use NhFetch;
drop table AnswerComment;
drop table Answer;
drop table QuestionComment;
drop table Question;
drop table Person;
create table Person
(
PersonId int identity(1,1) primary key,
PersonName nvarchar(100) not null
);
create table Question
(
QuestionId int identity(1,1) primary key,
QuestionText nvarchar(100) not null,
AskedBy_PersonId int not null references Person(PersonId),
QuestionModifiedBy_PersonId int null references Person(PersonId)
);
create table QuestionComment
(
Question_QuestionId int not null references Question(QuestionId),
QuestionCommentId int identity(1,1) primary key,
QuestionCommentText nvarchar(100) not null,
QuestionCommentBy_PersonId int not null references Person(PersonId)
);
create table Answer
(
Question_QuestionId int not null references Question(QuestionId),
AnswerId int identity(1,1) primary key,
AnswerText nvarchar(100) not null,
AnsweredBy_PersonId int not null references Person(PersonId),
AnswerModifiedBy_PersonId int null references Person(PersonId)
);
create table AnswerComment
(
Answer_AnswerId int not null references Answer(AnswerId),
AnswerCommentId int identity(1,1) primary key,
AnswerCommentText nvarchar(100) not null,
AnswerCommentBy_PersonId int not null references Person(PersonId)
);
insert into Person(PersonName) values('John');
declare @john int = SCOPE_IDENTITY();
insert into Person(PersonName) values('Paul');
declare @paul int = SCOPE_IDENTITY();
insert into Person(PersonName) values('George');
declare @george int = SCOPE_IDENTITY();
insert into Person(PersonName) values('Ringo');
declare @ringo int = SCOPE_IDENTITY();
insert into Person(PersonName) values('Brian');
declare @brian int = SCOPE_IDENTITY();
insert into Person(PersonName) values('Ely');
declare @ely int = SCOPE_IDENTITY();
insert into Person(PersonName) values('Raymund');
declare @raymund int = SCOPE_IDENTITY();
insert into Person(PersonName) values('Buddy');
declare @buddy int = SCOPE_IDENTITY();
insert into Person(PersonName) values('Marcus');
declare @marcus int = SCOPE_IDENTITY();
insert into Question(QuestionText,AskedBy_PersonId) values('What''s the answer to life and everything?',@john);
declare @question1 int = SCOPE_IDENTITY();
insert into QuestionComment(Question_QuestionId,QuestionCommentText,QuestionCommentBy_PersonId) values(@question1,'what is that?',@paul);
insert into QuestionComment(Question_QuestionId,QuestionCommentText,QuestionCommentBy_PersonId) values(@question1,'nice question',@george);
insert into Answer(Question_QuestionId,AnswerText,AnsweredBy_PersonId) values(@question1,'42',@ringo);
declare @answer1q1 int = SCOPE_IDENTITY();
insert into Answer(Question_QuestionId,AnswerText,AnsweredBy_PersonId) values(@question1,'9',@brian);
declare @answer2q1 int = SCOPE_IDENTITY();
insert into Answer(Question_QuestionId,AnswerText,AnsweredBy_PersonId) values(@question1,'76',@brian);
declare @answer3q1 int = SCOPE_IDENTITY();
insert into AnswerComment(Answer_AnswerId,AnswerCommentText,AnswerCommentBy_PersonId) values(@answer1q1, 'I think so', @ely);
insert into AnswerComment(Answer_AnswerId,AnswerCommentText,AnswerCommentBy_PersonId) values(@answer1q1, 'I''m sure', @raymund);
insert into AnswerComment(Answer_AnswerId,AnswerCommentText,AnswerCommentBy_PersonId) values(@answer1q1, 'But not quite', @raymund);
insert into AnswerComment(Answer_AnswerId,AnswerCommentText,AnswerCommentBy_PersonId) values(@answer1q1, '42 = 7 * 6. Answer to life is 76!', @buddy);
insert into AnswerComment(Answer_AnswerId,AnswerCommentText,AnswerCommentBy_PersonId) values(@answer1q1, '@buddy makes sense! you have same thoughts as @brian', @marcus);
insert into AnswerComment(Answer_AnswerId,AnswerCommentText,AnswerCommentBy_PersonId) values(@answer2q1, 'Really 9?', @ely);
insert into AnswerComment(Answer_AnswerId,AnswerCommentText,AnswerCommentBy_PersonId) values(@answer2q1, 'Maybe 10?', @raymund);
insert into AnswerComment(Answer_AnswerId,AnswerCommentText,AnswerCommentBy_PersonId) values(@answer2q1, 'Maybe 12?', @buddy);
insert into AnswerComment(Answer_AnswerId,AnswerCommentText,AnswerCommentBy_PersonId) values(@answer2q1, 'Make it baker''s dozen, make it 13', @buddy);
insert into Question(QuestionText,AskedBy_PersonId) values('Is the sky blue?',@john);
declare @question2 int = SCOPE_IDENTITY();
insert into QuestionComment(Question_QuestionId,QuestionCommentText,QuestionCommentBy_PersonId) values(@question2,'maybe white',@paul);
insert into Answer(Question_QuestionId,AnswerText,AnsweredBy_PersonId) values(@question2,'green',@ringo);
declare @answer1q2 int = SCOPE_IDENTITY();
select * from Question;
select * from QuestionComment;
select * from Answer;
select * from AnswerComment;
Demo code:
https://github.com/MichaelBuen/Demo_NHibernate_ThenFetchMany_Problem_And_WorkAround
