Nhibernate - Create SQLQuery - IndexOutOfRangeException

Question

Hi, we have a table named Customer which has three sub classes / tables viz. Cust1, Cust2 and Cust3 which are linked by hbm using joined-sub-class.

all the 4 tables have CustomerId as the primary Key. A record in customer table will have one record either in Cust1 or Cust 2 or Cust3 table.

fetching the list using the standard Nhibernate List() fetches the classes and its subclasses correctly.

However, in order to optimimize our query, w e had to use CreateSQlQuery method.

On goolging, we found that the correct way to fetch class and its sub classes is to have a select query such as

var sqlQuery = Session.CreateSqlQuery(
select C.*,
case if C1.CustId is not null then 1
else if C2.CustId is not null then 2
....
from Customer C
left join Cust1 C1 on C1.CustId = C1.CustId
left join Cust2 C2 on C2.CustId

where C.CustID in (x,y,z,blah,blah).).

sqlQuery.AdddEntity("C",typeof(Customer));

sqlQuery.List();

the case and alais change is needed to differentiate the CUstId columns between the 4 tables when the Nhibernate genereates a query internally, else an error with clazz would be thrown..

On running the query, we get, the Nhibernate Exception as

"IndexOutOfRangeException - Duration"

Cust1 (child class) table has a column named Duration. I renamed the table column to Duration_BE to check if the column name is an Issue, , then it threw the error

"IndexOutOfRangeException - Duration-BE"

The reference for this mode of work is.. http://www.methodicmadness.com/2009/01/nhibernate-what-is-heck-clazz.html

Can any one help me.

Answer 1

Make sure you are selecting ALL the fields from ALL your tables.

e.g.

var sqlQuery = Session.CreateSqlQuery(@"
  select 
    C.*, 
    C1.*,
    C2.*, -- You need all of the fields from the joined tables
    case 
      if C1.CustId is not null then 1 
      else if C2.CustId is not null then 2
    end as clazz_
  from 
    Customer C 
    left join Cust1 C1 on C.CustId = C1.CustId
    left join Cust2 C2 on C.CustId = C2.CustId
  where 
    C.CustID in (x,y,z)"
);