Search code examples
nhibernatenhibernate-mapping

NHibernate legacy db many-to-one

I'm facing a mapping relation problem with NH and a legacy db.

We have two table 

Table1 [
   Id primary Key (sql identity)
   Cod1,
   Cod2 ]

then a second table

Table2 [
   CompositeCode primary key (assigned),
   Cod1,
   Cod2 ]

CompositeCode is a calculated field as Cod1 + Cod2 and is the key of the table.

i'm trying to map a readonly many-to-one relation between Table1 and Table2 this way

<class name="Table2"  >
  <id name="Id" >
     <generator class="identity" />
 </id>
 <many-to-one name="Table2" 
             formula="SELECT C.CompositeCode FROM Table2 C WHERE C.Cod1= Cod1 AND C.Cod2 = Cod2"/>
  <property name="Cod1" />
  <property name="Cod2" />
</class>

NHibernate gives me this error when hitting the db: 

'Incorrect syntax near the keyword 'SELECT'.

This is the SQL generated

 SELECT TOP (100) this_.Id as Id1_37_0_, 
 this_.Cod1 as Cod1l3_37_0_, 
 this_.Cod2 as Cod24_37_0_, 
 SELECT this_.CompositeCode  FROM Table2 C WHERE this_.Cod1 = C.OCd1 AND this_.Cod2 =  C.COd2 as formula0_0_ 
 FROM Table1 this_

What's wrong?

I've also tryied to change the formual as 

  formula="SELECT C.CompositeCode FROM Table2 C WHERE C.CompositeCode = Cod1 + Cod2"

but with no luck!

P.S. NH is version 3.1

Solution

  • Formula statement executing subselect must be enclosed in braces (SELECT ...):

    formula="(SELECT C.CompositeCode FROM Table2 C WHERE C.CompositeCode = Cod1 + Cod2)"

    NOTE: I tried similar scenario and it is working. Except the code generated is not

    ...
,(SELECT this_.CompositeCode  FROM Table2 C .. // this_.ComositeCode

    but

    ...
,(SELECT C.CompositeCode  FROM Table2 C .. // C.CompositeCode