How to Partially Project a Child Object with Many Fields in Nhibernate

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

We can indeed use custom transformer. There is one, which I am using for a really very very deep projections (inlcuding dynamic objects - 5.1.13. component, dynamic-component)

• DeepTransformer<TEntity>

Take it (if needed adjust it) and your final query could be like this

// just the last lines are different
var query = session.QueryOver<CourseItem>()
    .JoinAlias(c => c.Teacher, () => teacherAlias)
    .Where(c => c.CourseID.IsInsensitiveLike(strNumber, option))
    .SelectList(list => list
           .Select(c => c.CourseID).WithAlias(() => courseAlias.CourseID)
           .Select(c => c.IsActive).WithAlias(() => courseAlias.IsActive)
           .Select(c => c.CourseDesc).WithAlias(() => courseAlias.CourseDesc)

           // the native WitAlias would not work, it uses expression
           // to extract just the last property
           //.Select(c => c.Teacher.ID).WithAlias(() => courseAlias.Teacher.ID)
           //.Select(c => c.Teacher.Name).WithAlias(() => courseAlias.Teacher.Name))

           // so we can use this way to pass the deep alias
          .Select(Projections.Property(() => teacherAlias.ID).As("Teacher.ID"))
          .Select(Projections.Property(() => teacherAlias.Name).As("Teacher.Name"))

           // instead of this
           // .TransformUsing(Transformers.AliasToBean<CourseItem>())
           // use this
           .TransformUsing(new DeepTransformer<CourseItem>())

And in case, that your aliases do match to property names, that transformer will built the object tree...

NHibernate: Projecting child entities into parent properties throws an exception

NHibernate doesn't know how to access a child property's child through the parent entity. A useful thing to remember about QueryOver is that it gets translated directly into SQL. You couldn't write the following SQL:

select [Section].[Department].[Name]

right? Therefore you can't do the same thing in QueryOver. I would create an alias for the Department entity you start on and use that in your projection list:

Department department;
Section sections;    

var result = _session.QueryOver<Department>(() => department)
    .Where(d => d.Company.Id == companyId)
    .Left.JoinQueryOver(x => x.Sections, () => sections)
    .Select(
            Projections.ProjectionList()
                .Add(Projections.Property(() => department.Name).WithAlias(() => sectionModel.DepartmentName))
                .Add(Projections.Property(() => sections.Name).WithAlias(() => sectionModel.SectionName))
           )
    .TransformUsing(Transformers.AliasToBean<SectionModel>())
    .List<SectionModel>();

I noticed in your comment you'd like an order by clause. Let me know if you need help with that and I can probably come up with it.

Hope that helps!

NHibernate QueryOver fill property object in parent object with projections

What we can do is to use different than default result transformer, e.g. DeepTransformer.

In that case, the query must use Alias similar to the DTO model. So, if we have domain property ei.FirstName belonging to JoinAlias - j.EmployeeInfo - the alias must be reflecting the DTO ContactInfo - "EmployeeInfo.FirstName"

.Select(        
    Projections.Property<Job>(j => ei.FirstName)         // the mapped domain model
                          .As("EmployeeInfo.FirstName"), // the path in DTO/view model
    ...
)
.TransformUsing(DeepTransformern<ContactInfo>()) // the DTO

So now, the path "EmployeeInfo.FirstName" will be used to populate the Employee as a property EmployeeInfo and its property FirstName

And this Result transformer

• DeepTransformer

DeepTransformer will use Alias to build up the reference tree. Could be used for references/many-to-one as well for IDictionary ... (but not for collections)

NOTE: The .Is() method comes from some extensions, and can be replaced with != null like

public static partial class Ext
{
    public static bool Is(this object value)
    {
        return value != null;
    }
....

Can I use NHibernate Criteria to project an entity and its child collection onto a class?

Ok, I think I've resolved this upgrading to NHibernate 3 and using QueryOver. Here's what my code looks like now:

//Declare entities
Email email = null;
EmailAttachment attachment = null;
UploadedFile file = null;
Byte[] fileData = null;

//Select data from parent and child objects
var results = mSession.QueryOver<QueuedEmail>(() => email)
   .JoinAlias(() => email.Attachments, () => attachment, JoinType.LeftOuterJoin)
   .JoinAlias(() => attachment.File, () => file, JoinType.LeftOuterJoin)
   .JoinAlias(() => file.Data, () => fileData, JoinType.LeftOuterJoin)
   .TransformUsing(Transformers.DistinctRootEntity)
   .List<Email>()

   //Loop through results projecting fields onto POCO
   .Select(x => new EmailDataModel()
   {
       Id = x.Id,
       Body = x.Body,
       AttachmentCount = x.Attachments.Count(),
       FromAddress = x.FromAddress,
       //Loop through child collection projecting fields onto POCO
       Attachments = x.Attachments.Select(attach => new EmailAttachmentDataModel()
       {
           Data = attach.File.Data.Data,
           Filename = attach.File.Filename,
           Id = attach.Id
       }).ToArray() //NB Now projecting this collection as an array, not a list
   }).ToArray();

So there it is. Our result is a flattened class which contains the data we need, plus a collection of attachments (which each contain just two fields from our data structure - nicely flattened).

Why should you do this?

1. It simplifies the result by flattening into only the fields I really want.

2. My data is now safely encapsulated in a class which can be passed around without fear of accidentally updating my data (which could happen if you just pass back NH data entities).

3. Finally (and most importantly), because the code above only generates one SELECT statement. Had I stuck with my original Criteria query, it would have generated one SELECT for each row, plus more for the children further down the chain. That's fine if you're dealing with small numbers, but not if you're potentially returning thousands of rows (as I will in this instance - it's a web service for an email engine).

I hope this has been useful for anybody wishing to push a bit further into NHibernate querying. Personally I'm just happy I can now get on with my life!

Linq to NHibernate: how to get a list of child objects without having the lists nested

If you map the other side of the navigation e.g have a UserProfile property on the UserMessage class, your can start from UserMessage:

var messages = 
    from UserMessage um in _Session.Query<UserMessage>()
    where um.UserProfile.UserName == userName
    select um;

Otherwise you need to use SelectMany() to get a flattened out list.

NHibernate QueryOver with SelectList

We do not need alias for filling SelectList. We can profit from the Type parameters coming with IQueryOver<TRoot,TSubType>:

//private void MapPropertiesOfEntityToResult(EntityClass entity
//   , ResultDto resultAlias, IQueryOver<EntityClass, EntityClass> query)
private void MapPropertiesOfEntityToResult( // no need for entity
      ResultDto resultAlias, IQueryOver<EntityClass, EntityClass> query)
{
  query.SelectList(list => list
   //.Select(() => entity.PrimaryID).WithAlias(() => resultAlias.PrimaryID)
   //.Select(() => entity.SecondaryID).WithAlias(() => resultAlias.SecondaryID)
   .Select(entity => entity.PrimaryID).WithAlias(() => resultAlias.PrimaryID)
   .Select(entity => entity.SecondaryID).WithAlias(() => resultAlias.SecondaryID)
    );
}

The entity is now a parameter of the passed Function and its type is coming from IQueryOver definition

Dynamic DTO from nhibernate projections criteria

What we need is transformer:

criteria
    .SetResultTransformer(
      NHibernate.Transform.Transformers.AliasToBean<MyEntity>())

or without generic

criteria
    .SetResultTransformer(
      NHibernate.Transform.Transformers.AliasToBean(this.modelType))

The point with transformers is to use aliasing (see the .As()):

.SetProjection(Projections.ProjectionList()
  .Add(Projections.Property("Property1").As("Property1"))
  .Add(Projections.Property("Property2").As("Property2"))
  .Add(Projections.Property("Property3").As("Property3"))
)

Check more here e.g.:

• How to partially project a child object with many fields in nHibernate
• NHibernate AliasToBean transformer associations

How can I force load a proxy object in NHibernate?

If you just all the ParentItem names you can use a select.

var queryOver = session.QueryOver<Parent>()
               .Select(p => p.Name))
               .List<string>()

---

Related Topics