NHibernate mapping a reference table

Question

I have a rather convoluted scenario/design to sort out and would much appreciate some advice or insight.

I am trying to map the following with not much success.

<class name="CalEvent" table="tb_calEvents" lazy="false" >

<id name="id" column="id">
  <generator class="hilo"/>
</id>

<property name="summary"/>
---snip----

<bag name="categories"  table="tb_calEvent_category" lazy="false">
  <key column="parentID"/>
  <one-to-many class="Category"/>
</bag>

//////////////////

<class name="Category" table="tb_calEvent_category" lazy="false">

<id name="id" column="id" unsaved-value="0">
  <generator class="hilo"/>
</id>

<property name="categoryID"/>

<property name="parentID"/>

<property name="categoryType"/>

category table

[id] bigint NULL [parentID] bigint NULL [categoryType] nvarchar(255) NULL [categoryID] int NULL CONSTRAINT [tb_calEvent_category_fk] FOREIGN KEY ([parentID])

The tb_calEvent_category table above would appear to have been set up as a kind of lookup table. There are 3 different kinds of categories each in their own table; The catergoryType is a reference to which table the category lives and the categoryID to the primary key of that table.

I have no problem retrieving the calEvents but can't cleanly add or update.

Does anyone have any thought of how I might go about mapping this? Is it possible? Or am I going to have to jump through a few hoops to successfully add and update the calEvents to the db?

I presume this table has been set up like this to cut down on joins or maybe so other categories can be added. Hmmm..

thanks

Answer 1

I think this is your main problem:

<id name="id" column="id" unsaved-value="0">   
    <generator class="hilo"/> 
</id>

<property name="categoryID"/>

As you're trying to set the category id twice.

This is probably more like what you need:

 <id name="id" column="categoryID" unsaved-value="0">   
    <generator class="hilo"/> 
 </id>

Plus it's probably better to use the sqlserver identity generator (native), make sure the column is set to identifier in sqlserver, and make sure that the FK relationships are as they should be.