Thursday 14 July 2011

QueryOver with multiple joins into a DTO

Consider the following NHibernate HQL. As you can see it is joining three tables and pulling back three columns from two different tables.

The [Organisation] table joins to the [OrganisationType] which in turn joins to the [IncomeType] table.
hql = @"
 select 
  t.Name, ot.LocalRespend, ot.LocalRespendOutArea
 from
  organisation o 
 inner join
  organisationincometype ot on ot.OrganisationId = o.Id
 inner join
  incometype t on ot.IncomeTypeId = t.Id
 where 
  o.Id = :orgId and t.Id = :incomeTypeId;";
How would you do the following using the QueryOver syntax?

Simple when you know how:-
var oit = null;
var  it = null;

return Session.QueryOver<organisation>()
   .Where(w => w.Id == orgId)
  .JoinAlias(r => r.OrganisationIncomeTypeList, () => oit)
  .JoinAlias(r => oit.IncomeTypeList, () => it)
   .Where(() => it.Id == incomeTypeId)
  .Select(
   Projections.Property(() => it.Name),
   Projections.Property(() => oit.LocalRespend),
   Projections.Property(() => oit.LocalRespendOutArea)
  )
  .TransformUsing(Transformers.AliasToBean<organisationincometypedto>())
  .SingleOrDefault<organisationincometypedto>();
The secret is that we are using JoinAlias into temporary local variables and then projecting these aliases into our DTO. The beauty of using the QueryOver syntax is that we are losing all the magic strings that we would get with HQL or Criteria.

18 comments:

  1. Hi, thank you for great posts on NHibernate! This one was very helpful. However, there is one thing I've been struggling all day to find solution to and maybe you would know...

    In this post you are projecting into a custom DTO object, which is nice but is there way to have an entire entity in the projection? I mean something like this

    .Select(
    Projections.?Entity?(() => it),
    Projections.Property(() => oit.LocalRespend),
    Projections.Property(() => oit.LocalRespendOutArea)
    )

    Thank you for any suggestion!

    ReplyDelete
  2. @twoflower I see you have found a workaround, use the linq provider. Good work.

    ReplyDelete
  3. in your example, using same were and join clause, how can I do if I want to get a list of organisation or a list of OrganisationIncomeType without DTO or transformation?

    ReplyDelete