NHibernate QueryOver - Join without path (or with "reversed" path) -

i trying fetch several entities without having single root entity ( directed graph of entity-relations weakly connected graph, not connected) , cant figure out how in nhibernates queryover api (or linq, seems weaker)

these relations have:

clienttaxentity references 1 client , n manufacturers

instructiontemplate references 1 client , 1 manufacturer

i want result possible client-manufacturer pairs (possible = within clienttaxentity) , fetch template them if exists (null otherwise)

this have tried far:

        client client = null;         manufacturer manufacturer = null;         instructiontemplate template = null;         clienttaxentity taxentity = null;          instructiontemplateinfo info = null;          var query =session.queryover<clienttaxentity>(() => taxentity)                    .joinalias(x => x.client, () => client)                    .joinalias(x => x.manufacturers, () => manufacturer)                    .left                    .joinqueryover(() => template, () => template,() => template.client == client && template.manufacturer == manufacturer);         var result = query             .selectlist(builder => builder                         .select(() => client.name).withalias(() => info.clientname)                         .select(() => client.id).withalias(() => info.clientid)                         .select(() => manufacturer.name).withalias(() => info.manufacturername)                         .select(() => manufacturer.id).withalias(() => info.manufacturerid)                         .select(() => template.id).withalias(() => info.templateid)                         .select(() => template.type).withalias(() => info.type)             )             .transformusing(transformers.distinctrootentity)             .transformusing(transformers.aliastobean<instructiontemplateinfo>())             .list<instructiontemplateinfo>(); 

the info object result like.

however syntax .joinqueryover(() => template not seem valid path parameter (exception says : could not resolve property: template of: clienttaxentity

to result want when writing query in sql necessary write along lines of:

select client_id, client_name, manufacturer_id, manufacturer_name (   select client.id client_id, client.name client_name,           manufacturer.id manufacturer_id, manufacturer.name manufacturer_name   clienttax   inner join client on client.id = clienttax.client_id   inner join manufacturer on manufacturer.id = manufacturer.manufacturer_id    union    select client.id, client.name, manufacturer.id, manufacturer.name   instructiontemplate   inner join client on client.id = instructiontemplate.client_id   inner join manufacturer on manufacturer.id = instructiontemplate.manufacturer_id ) group client_id, client_name, manufacturer_id, manufacturer_name; 

unfortunately converting such query 1 of nhibernate's query apis not possible because nhibernate not support union statement*. see question , feature request nh-2710 in nhibernate's bug tracker.

*except when using union-subclass. see docs further details

the options can see are

1. perform sql query , map dto

   public class instructiontemplateinfo {   public int client_id { get; set; }   public string client_name { get; set; }   public int manufacturer_id { get; set; }   public string manufacturer_name { get; set; } } 

   then

   var result = session        .createsqlquery(thesqlquerystring)        .setresulttransformer(transformers.aliastobean<instructiontemplateinfo>())        .list<instructiontemplateinfo>(); 

2. create view in database , map normal entity.

3. if dbms supports multiple results sets, sql server, write 2 queries mark them both future , merge 2 results set in code, i.e.

   var resultset1 = session.queryover<clienttaxentity>(() => taxentity)            .joinalias(x => x.client, () => client)            .joinalias(x => x.manufacturers, () => manufacturer)            .selectlist(builder => builder              .selectgroup((() => client.name).withalias(() => info.clientname)              .selectgroup((() => client.id).withalias(() => info.clientid)              .selectgroup((() => manufacturer.name).withalias(() => info.manufacturername)              .selectgroup((() => manufacturer.id).withalias(() => info.manufacturerid)           .transformusing(transformers.aliastobean<instructiontemplateinfo>())           .future<instructiontemplateinfo>;  var resultset2 = session.queryover<instructiontemplate>(() => taxentity)            .joinalias(x => x.client, () => client)            .joinalias(x => x.manufacturers, () => manufacturer)            .selectlist(builder => builder              .selectgroup((() => client.name).withalias(() => info.clientname)              .selectgroup((() => client.id).withalias(() => info.clientid)              .selectgroup((() => manufacturer.name).withalias(() => info.manufacturername)              .selectgroup((() => manufacturer.id).withalias(() => info.manufacturerid)           .transformusing(transformers.aliastobean<instructiontemplateinfo>())           .future<instructiontemplateinfo>;  var result = resultset1.concat(resultset2 )      .groupby(x=>x.clientid+"|"+x.manufacturerid)      .select(x=>x.first()); 

   the advantage of approach dbms hit once. see ayende's blog post further details feature.