Use NHibernate AliasToBean Transformer launch n+1 query

Question

Before edit my question I'm sorry for my poor english.

I have two classes:

public class News
{
    public virtua int Id { get; set; }
    public virtual string Title { get; set; }
    public virtual string Content { get; set; }                
    public virtual LearningCenter LearningCenter { get; set; }
}

public class LearningCenter
{
    public virtua int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string Address { get; set; }
    public virtual string ZipCode { get; set; }
    public virtual string PhoneNumber { get; set; }
    public virtual string Mail { get; set; }
    public virtual string DDECode { get; set; }
}

I get a list of news using a main code like:

var query = Session
                .QueryOver<News>()
                .JoinAlias(x => x.Language, () => language, JoinType.LeftOuterJoin)
                .JoinAlias(x => x.LearningCenter, () => learningCenter, JoinType.LeftOuterJoin)
                .List<News>();

The code above is translated from NHibernate to a single sql query that looks something like "SELECT ... FROM News LEFT JOIN LearningCenter WHERE ..."

It's working fine. But I want to select a group of fields, so I make something like... (Note news and newsDTO object has same type)

News news = null;
LearningCenter learningCenter = null;
Language language = null;
News newsDTO = null;

var query = Session
                .QueryOver(() => news)
                .JoinAlias(() => news.LearningCenter, () => learningCenter, JoinType.LeftOuterJoin)
                .JoinAlias(() => news.Language, () => language, JoinType.LeftOuterJoin)
                .SelectList(l => l
                .Select(x => x.Id).WithAlias(() => newsDTO.Id)
                .Select(x => x.LearningCenter).WithAlias(() => newsDTO.LearningCenter))
                .TransformUsing(Transformers.AliasToBean<News>())
                .List<News>();

But the issue is NHibernate translate the code above to n+1 query that looks something like...

"SELECT ... FROM News LEFT JOIN LearningCenter WHERE..."
"SELECT ... FROM LearningCenter WHERE CenterId = 388"
"SELECT ... FROM LearningCenter WHERE CenterId = 389"
...

Do you know how can I make for NHibernate translate the code to a single query?

Thanks!!!

score 1 · Accepted Answer · edited May 23 '17 at 10:10

In general there are two ways.

1) we have to use Projections, and project all properties to be selected explicitly. Then we have to use some deeper transformation e.g. Deep transformer here

2) we can use batch fetching, which will load all related relations in few batches. That will lead to 1 + 2(4) queries, to load all relations in separated (few) queries

For more details about projections:

How to partially project a child object with many fields in nHibernate

For more details about batch fetching you can check this:

Use NHibernate AliasToBean Transformer launch n+1 query

1 Answers1

Linked