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

Question

I'm using NH Criteria to retrieve an entity and project selective fields onto a custom class (a bit like projecting data onto a ViewModel for display on an MVC view).

This is easy enough using ProjectionList:

var emailCriteria = mSession.CreateCriteria<Email>();
emailCriteria.SetProjection(
    Projections.ProjectionList()
        .Add(Projections.Property("Subject"), "Subject")
);
emailCriteria.SetResultTransformer(Transformers.AliasToBean<EmailDataModel>());
var result = emailCriteria.List<EmailDataModel>();

However, my entity contains a collection, and I want to bring that back too, and project it as a collection onto my custom class.

My domain model looks (in simplified form) like this:

public class Email {
    public string Subject
    public List<EmailAttachment> Attachments
    etc...
}

public class EmailAttachment {
    public UploadedFile File
}

public class UploadedFile {
    public string Filename
    public UploadedFileData Data
}

public class UploadedFileData {
    public byte[] Data
}

Here's the "data model" classes I want to project onto:

public class EmailDataModel {
    public string Subject
    public List<EmailAttachmentDataModel> Attachments
}

public class EmailAttachmentDataModel {
    public string Filename
    public byte[] Data
}

Now I know these models look very similar, and you'd be forgiven for thinking "what's the point?", but that's because I've simplified them. It's nice to be able to flatten my domain objects into handy data models.

My big problem is figuring out how to access the necessary fields from deep down in my child objects (in this case, UploadedFile.Filename and UploadedFileData.Data), and project them as an EmailAttachmentDataModel collection onto my EmailDataModel.

I've read a lot of articles online which discuss accessing child collections - using either EmailCriteria.CreateAlias or EmailCriteria.CreateQuery - but I haven't found anything which explains how to project a child collection AS a collection.

I hope this will be a useful exercise for anyone interested in tinkering with NH Criteria queries.

score 6 · Accepted Answer · edited Apr 12 '12 at 01:47

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?

It simplifies the result by flattening into only the fields I really want.
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).
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!

I just ran into something similar and although this technically works, I wanted to add as a note that everything past your .List() call is actually being done locally with LINQ. This means you've already pulled ALL of your joined data from the DB and into memory. This may not be a problem in your example but could easily be a problem in some situations. Unfortunately I don't think this is actually possible to do in NHibernate directly as you're going to end up either filtering duplicate joined data locally like you've done here or running another query to get the child data. — Chris Stavropoulos, Jun 06 '11 at 14:57

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

1 Answers1