Search code examples
nhibernateprojectionqueryover

NHibernate QueryOver projection on many-to-one

I am trying to get a QueryOver working using a Projection on a many-to-one.

The class "Post" has a property many-to-one "Creator".

Using

session.QueryOver(Of Post).
Select(Projections.
Property(of Post)(Function(x) x.Creator).
WithAlias(Function() postAlias.Creator)).
TransformUsing(Transformers.AliasToBean(Of Post)()).
List()

works BUT each creator is retrieved by a single query rather than using a join like it is done when not using a select/projection. So if there are 5 posts with 5 different creators, 6 queries will be run 1 for the list of posts and 5 for the creators.

I tried to get it working using a JoinAlias but nothing really did the job.

I already searched for a solution, but all solutions I found did use the Linq-Provider which does not really fit since the actual "field list" is passed via a parameter.

Does anyone know if there is a solution to this other than the linq provider?

Solution

  • There is a solution, we can use projections for many-to-one and then custom result transformer.

    DISCLAIMER: I can read VB syntax but do not have enough courage to write... I expect that you can read C# and convert it into VB....

    So we can have projection like this:

    // aliases
Post root = null;
Creator creator = null;

// projection list
var columns = Projections.ProjectionList();

// root properties
columns.Add(Projections.Property(() => root.ID).As("ID"));
columns.Add(Projections.Property(() => root.Text).As("Text"));

// reference properties
columns.Add(Projections.Property(() => creator.ID).As("Creator.ID"));
columns.Add(Projections.Property(() => creator.FirstName).As("Creator.FirstName"));

// so our projections now do have proper ALIAS
// alias which is related to domain model 
//  (because "Creator.FirstName" will be use in reflection)

var query = session.QueryOver<Post>(() => root)
    .JoinAlias(() => root.Creator, () => creator)
    .Select(columns)

    Now we would need smart Transformer, our own custome one (plugability is power of NHibernate). Here you can find one:

    public class DeepTransformer

    And we can continue like this

    var list = query
    .TransformUsing(new DeepTransformer<Post>())
    .List<Post>()

    Check also this: