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

Question

I am trying to fetch several entities without having a single root entity ( the directed graph of entity-relations is only a weakly connected graph, not strongly connected) and I cant figure out how to do it in Nhibernates QueryOver api (or Linq, but that seems to be even weaker)

These are the relations I have:

ClientTaxEntity references 1 Client and N Manufacturers

InstructionTemplate references 1 Client and 1 Manufacturer

I want to get a result with all possible Client-Manufacturer pairs (possible = they are together within a ClientTaxEntity) and fetch a Template to them if it exists (null otherwise)

This is what I have tried so 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 is the result I would like.

However the syntax .JoinQueryOver(() => template does not seem to be valid for a path parameter (exception says : could not resolve property: template of: ClientTaxEntity

I know I could do it in two queries since I have two root entities, but I am looking for a single DB query solution. — Tomas Grosup, Apr 19 '13 at 09:44
Is there a relationship betweeen `ClientTaxEntity` and `InstructionTemplate`? — mickfold, Apr 19 '13 at 21:21
No, there is not. I also tried it to replace the join with a subselect, but it fails if the result of sub-select yields more then 1 value — Tomas Grosup, Apr 20 '13 at 08:14

score 1 · Accepted Answer · edited May 23 '17 at 12:31

To get the result you want when writing a query in SQL it would be necessary to write something along the lines of:

SELECT Client_Id, Client_Name, Manufacturer_Id, Manufacturer_Name
FROM
(
  SELECT Client.Id as Client_Id, Client.Name as Client_Name, 
         Manufacturer.Id as Manufacturer_Id, Manufacturer.Name as Manufacturer_Name
  FROM 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
  FROM InstructionTemplate
  INNER JOIN Client on Client.Id = InstructionTemplate.Client_Id
  INNER JOIN Manufacturer on Manufacturer.Id = InstructionTemplate.Manufacturer_id
) a
GROUP BY Client_Id, Client_Name, Manufacturer_Id, Manufacturer_Name;

Unfortunately converting such a query to one of NHibernate's query APIs is not possible because NHibernate does not support the UNION statement*. See this question and the feature request NH-2710 in NHibernate's bug tracker.

*Except when using union-subclass. See the docs for further details

The only options I can see are

Perform an SQL Query and map this to a 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>();

Create a view in the database and map this like a normal entity.

If the DBMS supports multiple results sets, like SQL Server, you could write two queries but mark them both as Future and then merge the two 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 this approach is that the DBMS will only be hit once. See Ayende's blog post for further details about this feature.

I went with the Future approach, its exactly what I needed. thank you — Tomas Grosup, Apr 20 '13 at 10:10
Its a shame that NHibernate doesn't support unions but it looks like it would require a lot of changes to make it work. Fortunately as NHibernate is so flexible there's pretty much always more than one way to address an issue. — mickfold, Apr 20 '13 at 10:28

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

1 Answers1