I have list of documents in my database. Every document has a collection of child elements. Now, my scenario is that: - read documents (only from document table) - close session and let the user do some work.
But when I do that, Document tries to load child elements in some places. I don't want it. I want to read child elements only explicitily. For the first part, I need to read just simple document values.
So is there any way to say to nHibernate - "hey, never read this collection!"?
Set your collection's Lazy loading to Lazy or Extra, perhaps yours is set to NoLazy(a.k.a. eager-loading).
It's better to set it to Extra instead of Lazy only. As it will prevent NHibernate from fetching the rows for the child collection when you only want to get the .Count() or .Any() of the child collection. Extra is like a more lazy version of lazy :)
With NoLazy / eager-loading:
var post = session.Get<Post>(1);
That will read one row from post table and row(s) from comments table from the database, even if you didn't access the child collection comments of the post from your application.
Using Lazy, var post = session.Get<Post>(1) will only load the one row from posts table, NHibernate will not read the post's child collection comments from the database.
Using Lazy:
var commentsCount = post.Comments.Count()
That will load the post's comments from the database:
select * from comments where post_id = 1;
And the .Count(), happens on application-side only.
Using Extra, var commentsCount = post.Comments.Count(), NHibernate will issue count query only, instead of reading all the rows.
select count(*) from comments where post_id = 1
Here's an example configuration to set the child collection's loading mechanism if you are using NHibernate's automapping, you set that settings on BeforeMapSet event:
And when you need to eager-load the child collection that are configured as Lazy or Extra, use FetchMany
As a temporary solution I created a simple hack:
public class Document
{
IList<Periods> periods;
public virtual IList<Period> Periods
{
get { return periods; }
set { periods = value; }
}
public virtual void ResetPeriods()
{
periods = new List<Period>();
}
}
And this is how I get the documents:
db.BeginTransaction();
IList<Document> list = db.Get<Document>();
db.CommitTransaction();
List<Document> result = new List<Document>();
foreach (var item in list)
{
item.ResetPeriods(); //todo: HACK! Preventing from lazy load of periods
result.Add(item);
}
return result;
Of course this collection is mapped as lazy. Child collection (Periods) has to be defined as back variable, because it prevents NHibernate Proxy from using property getter.
I found what causes the Document's Periods to load from database even if you don't access the Document's Periods property.
namespace NHibernateFetchJoinTest2
{
using System;
using NHibernateFetchJoinTest2.DomainMapping;
using NHibernateFetchJoinTest2.Domains;
class MainClass
{
public static void Main(string[] args)
{
using (var session = Mapper.SessionFactory.OpenSession())
{
Console.WriteLine("SQL produced: ");
var d = session.Get<Document>(1);
Console.ReadLine();
//Console.WriteLine("Document's periods: ");
//foreach (var period in d.Periods)
//{
// Console.WriteLine($"* {period.PeriodDescription}");
//}
Console.ReadLine();
}
}
}
}
Produces this:
SQL produced:
NHibernate:
SELECT
document0_.Id as id1_0_1_,
document0_.DocumentDescription as documentdescription2_0_1_,
periods1_.DocumentId as documentid3_1_3_,
periods1_.Id as id1_1_3_,
periods1_.Id as id1_1_0_,
periods1_.PeriodDescription as perioddescription2_1_0_
FROM
Document document0_
left outer join
Period periods1_
on document0_.Id=periods1_.DocumentId
WHERE
document0_.Id=@p0;
@p0 = 1 [Type: Int32 (0:0:0)]
Your mappings resemble the following. Your child collection Lazy-loading is set to Lazy (as opposed to NoLazy), yet its Fetch strategy is set to Join. To wit:
namespace NHibernateFetchJoinTest2.DomainMapping.Mappings
{
using NHibernate.Mapping.ByCode.Conformist;
using NHibernateFetchJoinTest2.Domains;
public class DocumentMapping : ClassMapping<Document>
{
public DocumentMapping()
{
Id(x => x.Id);
Property(x => x.DocumentDescription);
Bag(x => x.Periods, collectionMapping =>
{
collectionMapping.Inverse(true);
collectionMapping.Key(k => k.Column("DocumentId"));
collectionMapping.Lazy(NHibernate.Mapping.ByCode.CollectionLazy.Lazy);
// Remove this. This causes Document's Periods to load,
// even if child collection Periods is not accessed yet.
// This is evident in SQL log, it shows LEFT JOIN Period.
collectionMapping.Fetch(NHibernate.Mapping.ByCode.CollectionFetchMode.Join);
}, mapping => mapping.OneToMany());
}
}
public class PeriodMapping: ClassMapping<Period>
{
public PeriodMapping()
{
Id(x => x.Id);
Property(x => x.PeriodDescription);
}
}
}
If this is removed...
collectionMapping.Fetch(NHibernate.Mapping.ByCode.CollectionFetchMode.Join);
...child collection Periods is not prematurely-fetched by its parent (Document):
SQL produced:
NHibernate:
SELECT
document0_.Id as id1_0_0_,
document0_.DocumentDescription as documentdescription2_0_0_
FROM
Document document0_
WHERE
document0_.Id=@p0;
@p0 = 1 [Type: Int32 (0:0:0)]
Repro steps used: https://github.com/MichaelBuen/NHibernateFetchJoinTest2