nhibernate force separate queries instead of join

Question

I'm new to nhibernate and I couldn't figure this one out. I have an entity similiar to below class;

public class MotherCollection
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }

    public virtual ISet<Class1> Collection1 { get; set; }
    public virtual ISet<Class2> Collection2 { get; set; }
    public virtual ISet<Class3> Collection3 { get; set; }
    public virtual ISet<Class4> Collection4 { get; set; }
}

There are numerous one to many relationships to other entities. I configure this relation with below mappings;

HasMany(d => d.Collection1).KeyColumn("McId");
HasMany(d => d.Collection2).KeyColumn("McId");
HasMany(d => d.Collection3).KeyColumn("McId");
HasMany(d => d.Collection4).KeyColumn("McId");

Child classes are configured similiary;

References(c1=>c1.MotherCollection).Column("McId");

and so on.

When I query this entity from db, fetching all relationships, I get a huge query similar to this one :

SELECT * FROM MotherCollection mc 
JOIN c1 on mc.Id=c1.mcId
JOIN c2 on mc.Id=c2.mcId
JOIN c3 on mc.Id=c3.mcId
JOIN c4 on mc.Id=c4.mcId

this query causes alot of duplicate rows and takes alot of time to execute.

I want nhibernate to somehow seperate this query to individual SELECT queries, like below

SELECT * FROM MotherCollection Where Id = @Id
SELECT * FROM c1 Where mcId = @Id

and such. A bit similar to how it happens when the collection is lazy loaded. I managed to achive this behaviour by setting my desired collections as lazy, and accessing their First() property just before it exits my datalayer. However, I'm guessing there must be a more elegant way of doing this in Nhibernate.

I've tried queries similar to this:

var data = session.QueryOver<DataSet>().Fetch(d=>d.Collection1).Eager.Fetch(d=>d.Collection2).Eager....

Thank you.

Answer 1

This is called lazy/eager loading. You have two choices to select from:

1. Lazy load with multiple queries:

This will generate multiple queries. While lazy loading, NHibernate first generates the query to get all MotherCollection data and only IDs (without data) from any dependent tables. Then it generates new query with WHERE clause for Primary Key on dependent table. So, this leads to famous N+1 issue.

With this, referenced collections will NOT be filled by default. Those will get filled up when you first access them while ISession is still valid. This is similar to calling First() as you mentioned in your question.

Look at your HasMany configuration; you have not mentioned LazyLoad but it is default. So, with your current mapping, this is what is happening.

This is recommended by NHibernate.

2. Eager load with single complex query:

If you want to avoid multiple queries and retrieve all the data in one go, try something like following:

HasMany(d => d.Collection1).KeyColumn("McId").Inverse().Not.LazyLoad().Fetch.Join();

With this, referenced collections will be filled up (if data present in database) automatically.

Please note that this is against the NHibernate recommendation. Refer this link.

Instead, we keep the default behavior, and override it for a particular transaction, using left join fetch in HQL. This tells NHibernate to fetch the association eagerly in the first select, using an outer join. In the ICriteria query API, you would use SetFetchMode(FetchMode.Join).

If you ever feel like you wish you could change the fetching strategy used by Get() or Load(), simply use a ICriteria query, for example:

User user = (User) session.CreateCriteria<User>()
                .SetFetchMode("Permissions", FetchMode.Join)
                .Add( Expression.Eq("Id", userId) )
                .UniqueResult();

A completely different way to avoid problems with N+1 selects is to use the second-level cache.

Duplicate rows and Performance

This is actually a different problem. There are multiple ways to handle this; but it will need additional inputs from you. Before that, you should choose one option from above two. Thus this deserves a new question to be asked.

Refer this answer: https://stackoverflow.com/a/30748639/5779732

Answer 2

You should issue 4 separate queries, each one fetching one collection. And you should use session.Query. QueryOver is an older way of doing it. To use it add using NHibernate.Linq. I usually use the following extension method to prefetch collections:

static public void Prefetch<T>(this IQueryable<T> query)
{
    // ReSharper disable once ReturnValueOfPureMethodIsNotUsed
    query.AsEnumerable().FirstOrDefault();
}

And then use:

var data = session.Query<DataSet>().Fetch(d=>d.Collection1).ToList();
session.Query<DataSet>().Fetch(d=>d.Collection2).Prefetch();
session.Query<DataSet>().Fetch(d=>d.Collection3).Prefetch();
session.Query<DataSet>().Fetch(d=>d.Collection4).Prefetch();

Make sure to run the 4 queries before accessing the collections. That way when you access them they will all be initialized already. If you use regular lazy loading you will be initializing one collection for one object at a time.