CODEWITHSUNDEEP
c#sqlnhibernatenhibernate-mapping
Olegs Jasjko
Olegs Jasjko

Reputation: 2088

How to use SELECT in NHibernate mapping?

Right now I have a second code in my mapping:

  <class name="Order" table="tOrder">
    <id name="Id">
      <column name="Ref" sql-type="int"/>
      <generator class="native"/>
    </id>
    <property name="OrderNo" column="OrderNo"/>
    <property name="CustomerId" column="CustomerId"/>
    <join table="tOffer">
      <key column="Ref" unique="true"/>
      <component name="User" class="User" >
        <property name="Id" column="OfferId"/>
        <property name="UserName" column="UserName"/>
      </component>
      <component name="Driver" class="Contact">
        <property name="FirstName" column="FirstName"/>
        <property name="LastName" column="LastName"/>
        <property name="Email" column="DriverEMail"/>
        <property name="Fax" column="DriverFax"/>
        <property name="Phone" column="DriverTelephone"/>
      </component>
    </join>

Seems fine (and it is, its working without problem), there are issue.

Some fields for Driver class are incorrect (FirstName, LastName). They should take data for those field from different database. In theory it should look something like this:

<join table="tDriver">
      <key column="Ref" unique="true"/>
      <component name="Driver" class="Contact">
        <property name="FirstName" column="Firstname"/>
        <property name="LastName" column="Lastname"/>
      </component>
</join>

But I can't do that because Driver was already declared in mapping. This issue can be easily fixed with View help, but there is another Issue, I shouldn't make any changes in database (can't create View). As I understand, there are possibility to use:

<sql-query name="...">...</sql-query>

And inside it should be selected data for Driver from tOffer and additional two fields from tDriver. So, my question is, how should it look correctly and will it work at all? I mean, will there we any issues with another mapping properties?

EDIT:

Inside should be something like that I guess, but how to use it...

  Select d.Firstname, d.Lastname, o.DriverEMail, o.DriverFax , o.DriverTelephone
  FROM tOffer as o WITH (nolock)
  INNER JOIN tDriver as d WITH (nolock) on d.Ref = o.Ref

P.S Sorry about my English.

Upvotes: 1

Views: 852

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

NHibernate supports the view-like mapping. Instead of using attribute table="tableName" (targeting the table or view) we can use another approach <subselect>

The mapping then could look like this:

<join table=""> <!-- table="" attribute should be there, even empty-->
  <subselect>
      Select d.Firstname    AS FirstName
       , d.Lastname         AS LastName
       , o.DriverEMail      AS Email
       , o.DriverFax        AS Fax
       , o.DriverTelephone  AS Phone
       , d.Ref              AS Ref
      FROM tOffer as o WITH (nolock)
      INNER JOIN tDriver as d WITH (nolock) on d.Ref = o.Ref
  </subselect>
  <key column="Ref" unique="true"/>
  <component name="Driver" class="Contact">
    <!-- NOTE if the name and column do fit... the column def is redundant.. -->
    <property name="FirstName" column="FirstName"/>
    <property name="LastName"  column="LastName"/>
    <property name="Email"     column="Email"/>
    <property name="Fax"       column="Fax"/>
    <property name="Phone"     column="Phone"/>
  </component>
  ...
</join>

Do NOT forget to also return the column REF which will be the way how to JOIN both entities

Upvotes: 1

Related Questions