NHibernate: Two foreign keys in the same table against same column

Question

I am working on a consignment booking software. The consignment object has the following structure:

public class Consignment
{
   //Other properties

   public virtual Station FromStation{get;set;}

   public virtual Station ToStation{get;set;}
}

and here is the station object:

public class Station
{
   public virtual int StationId{get;set;}

   public virtual string StationName{get;set;}

    //I don't want this property but I have to keep it.
    public virtual Iesi.Collections.Generic.ISet<Consginment> ConsginmentFrom
    {
        get;
        set;
    }

    //I don't want this property but I have to keep it here.
    public virtual Iesi.Collections.Generic.ISet<Consginment> ConsginmentTo
    {
        get;
        set;
    }
}

In the database side, there would be a Station table, and the Consignment table would have two int columns (called FromStation and ToStation), storing the ids of the station.

I am not much of an NHibernate guy, after googling and reading for half a day I came up with the following Mapping Files:

Station.hbm.xml

<class name="Station" table="Station">
    <id name="StationId" >
      <column name="STATION_ID" not-null="true" />
      <generator class="identity" />
    </id>
    <property name="StationName" >
      <column name="STATION_NAME" not-null="true"  />
    </property>
    <set name="ConsginmentFrom" inverse="true" lazy="true" generic="true">
      <key>
        <column name="StationId" />
      </key>
      <one-to-many class="Consignment" />
    </set>
    <set name="ConsignmentTo" inverse="true" lazy="false" generic="true">
      <key>
        <column name="StationId" />
      </key>
      <one-to-many class="Consignment" />
    </set>
  </class>

Consignment.hbm.xml

<class name="Consignment" abstract="true"
           table="Consignment" lazy="false">
    <id name="ConsignmentId">
      <generator class="hilo"/>
    </id>

  <!--Column mapping for other properties-->

    <many-to-one name="FromStation" class="Station">
      <column name="STATION_ID" not-null="true" />
    </many-to-one>

    <many-to-one name="ToStation" class="Station">
      <column name="STATION_ID" not-null="true" />
    </many-to-one>


  </class>

But the above is generating strange DB structure. I have to do it xml mapping files as a lot has already been written in xml. Am I doing it correctly? Can there be a better way?

Thanks for reading this.

What is the schema for the generated DB structure? – mickfold Apr 12 '13 at 10:34 — mickfold, Apr 12 '13 at 10:34

mickfold · Accepted Answer · 2013-04-12T12:25:27.847

3

There are a few things I can see wrong with the mappings.

The FromStation and ToStation properties map to the same column in the Consignment table. They should map to different columns such as FROM_STATION_ID and TO_STATION_ID:

<many-to-one name="FromStation" class="Station">
  <column name="FROM_STATION_ID" not-null="true" />
</many-to-one>

<many-to-one name="ToStation" class="Station">
  <column name="TO_STATION_ID" not-null="true" />
</many-to-one>

The Set for ConsignmentFrom and ConsignmentTo in Station maps to StationID instead of Station_Id. Also you need to use the FROM_STATION_ID AND TO_STATION_ID as the key columns.

<set name="ConsignmentFrom" inverse="true" lazy="true" generic="true">
  <key column="FROM_STATION_ID" />
  <one-to-many class="Consignment" />
</set>

<set name="ConsignmentTo" inverse="true" lazy="false" generic="true">
  <key colum="TO_STATION_ID" />
  <one-to-many class="Consignment" />
</set>

Also consignment is misspelt in some places which may also cause some confusion.

edited Apr 12 '13 at 12:25

answered Apr 12 '13 at 11:03

mickfold

2,003
1
14
20

Sorry about the misspelt consignments :) I tried the code provided by you. But now it is creating a STATION_ID column in the CONSIGNMENT table. Which is apart from the FROM_STATION_ID and TO_STATION_ID. – James Apr 12 '13 at 11:22
Seems the second point above is creating the Station_Id column in the Consignment table. – James Apr 12 '13 at 11:28
@James Sorry I did the mapping from memory. Please check my updated answer. – mickfold Apr 12 '13 at 12:26
Yes I will do that! You might have got an idea by now how NHibernate-illiterate I am! – James Apr 12 '13 at 12:27
Yes that obnoxious Station_Id is gone! Thanks for the answer. It has actually solved the same problem I had at couple more places. From my brief encounter with mighty NHibernate I have learnt a couple things: 1) xml mapping is messy! Try go for fluent NHibernate. The code is more readable than xml. 2) No existing tool can generate correct mapping for complex scenarios. You will need some tweaks in the mapping files generated by them. – James Apr 12 '13 at 12:35
Fluent NHibernate is nice. It pretty much does everything you would need and it's possible to export the mappings it generates to hbm files so it can be used in conjunction with old project that use XML mapping files. – mickfold Apr 12 '13 at 12:43
Btw a good beginners book is [NHibernate 3 Beginner's Guide](http://www.packtpub.com/nhibernate-3-beginners-guide/book). Also [Ayende Rahien's blog](http://ayende.com/blog) is an excellent resource for NHibernate. – mickfold Apr 12 '13 at 12:43

NHibernate: Two foreign keys in the same table against same column

1 Answers1

Linked