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.
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...
ReplyDeleteIn 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!
@twoflower I see you have found a workaround, use the linq provider. Good work.
ReplyDeletein 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