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

Question

I have the following nHibernate query that select a course based on its course id and then return selected fields for the course object on the initial fetch, and the query executes with no issues.

MatchMode option = ... 
CourseItem courseAlias  = null;
TeacherItem teacherAlias = null;

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)
               .Select(c => c.Teacher).WithAlias(() => courseAlias.Teacher))
               .TransformUsing(Transformers.AliasToBean<CourseItem>())
.List<CourseItem>();

I wanted to go a step further with the query to only return a partial teacher object, let's say i just wanted to return the ID and Name. So, I updated the projected list to as follows:

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)
               .Select(c => c.Teacher.ID).WithAlias(() => courseAlias.Teacher.ID)
               .Select(c => c.Teacher.Name).WithAlias(() => courseAlias.Teacher.Name))
               .TransformUsing(Transformers.AliasToBean<CourseItem>())
.List<CourseItem>();

The query doesn't work because nHibernate has no idea how to resovled based on Teacher.ID and Teacher.Name. Any thoughts on whether it's possible to NOT fetch the entire child object back to a parent object?

I've tried the following query and it works this is not my fully desired outcome

var query = session.QueryOver<CourseItem>(() => courseAlias)
    .JoinAlias(() => courseAlias.Teacher, () => teacherAlias)
    .Where(() => courseAlias.CourseID.IsInsensitiveLike(strNumber, option))
    .SelectList(list => list
               .Select(() => courseAlias.CourseID)
               .Select(() => courseAlias.IsActive)
               .Select(() => courseAlias.CourseDesc)
               .Select(() => teacher.ID)
               .Select(() => teacher.Name))
    .List<object[]>();

I can query the right values but unable to transform it back correctly to the Course / teacher data type.

Any thoughts?

thanks!

this might be of some help (see "Creating your own transformer") http://blog.andrewawhitaker.com/blog/2014/06/19/queryover-series-part-4-transforming/ — jbl, Nov 12 '14 at 21:16
Not sure of the context of this query, but is it possible for you to use another POCO or DTO to project the query results to? Typically querying like this is best for populating a specific type that's used for a certain task — Andrew Whitaker, Nov 13 '14 at 01:36
Yes - that's a possibility, or to load the child object in its entirety using fetch instead of projecting the attributes out. I guess the main issue is that the child object has a lot of fields and if my parent list is huge, it'll take a performance hit. — aggietech, Nov 13 '14 at 02:25

score 4 · Accepted Answer · edited May 23 '17 at 12:01

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...

do have a quick question about the code from your previous answer .. dictionary.Is() - is that an extension method? if so, what is it supposed to do? — aggietech, Nov 13 '14 at 14:10
Yeah ;) ;) sorry ... seems so ;) it should be `.IsNull()` is representing `== null`, `.Is()` is the `!= null`. ;) You know this is "write into language..." *(same could be in JavaScript...)* Great spot. I really did not realize that ! — Radim Köhler, Nov 13 '14 at 14:30
This is a really neat solution Radim, you should write up a blog post on this!! — aggietech, Nov 13 '14 at 17:20
I am really excited if that helped a bit to you. Enjoy awesome NHibernate :) — Radim Köhler, Nov 13 '14 at 17:26

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

1 Answers1

Linked