Help with composite key mapping

pdegenhardt · **Joined:** Thu Apr 12, 2007 10:35 pm **Posts:** 11

Hi,

I am trying to use NHibernate for a fairly straight forward object model.

A Country may have zero or more StateProvs
An Address must have a Country
An Address may have a StateProv

I am dealing with legacy tables however, and am constrained to using assigned rather than generated primary keys on the Country and StateProv tables. The primary key for StateProv is a composite key of CountryCode+StateProvCode.

I am trying to modify the following mapping to have only one instance of the CountryCode column in the Address table. (i.e., if a StateProv is provided, the Country can be derived OR if no StateProv is provided, then Country must be provided on its own...)

Code:

<class name="Country" table="tblCountry" >
  <id name="Code" type="String" length="2" >
    <generator class="assigned" />
  </id>
  <property name="Name" type="String" length="50" not-null="true" />
  <bag name="StateProvs" inverse="true" lazy="true" cascade="all" >
    <key column="CountryCode" />
    <one-to-many class="StateProv" />
  </bag>
</class>

<class name="StateProv" table="tblStateProv" >
  <composite-id name="StateProvKey" class="StateProvKey" >
    <key-many-to-one name="Country" class="Country" column="CountryCode" />
    <key-property name="Code" type="String" length="12" />
  </composite-id>
  <property name="Name" type="String" length="50" not-null="true" />
</class>

<class name="Address" table="tblAddress">
  <id name="Id" type="Guid" >
    <generator class="guid" />
  </id>
  <property name="AddressLine_0" type="String"/>
  <property name="AddressLine_1" type="String"/>
  <property name="CityName" type="String" length="30" />
  <property name="PostalCode" type="String" length="12" />
  <property name="County" type="String" length="30" />
  <many-to-one name="StateProv" class="StateProv" >
    <!-- TODO: This column should merge with CountryCode-->
    <column name="StateProvCountryCode" />
    <column name="StateProvCode" />
  </many-to-one>
  <many-to-one name="Country" class="Country" not-null="true" column="CountryCode" />
</class>

If I simply change the column name from "StateProvCountryCode" to "CountryCode" (the one immediately below the TODO) the table appears to be generated ok, but attempting to insert an address gives the following NHibernate.ADOException:

Code:

could not insert: [TestProj.Address#8c989ba1-949b-45a9-a347-6e12e8584694][SQL: INSERT INTO tblAddress (AddressLine_0, AddressLine_1, CityName, PostalCode, County, CountryCode, StateProvCode, CountryCode, Id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)]

Column name 'CountryCode' appears more than once in the result column list.

Any ideas?

Thanks,
Phil

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I can't find my old hibernate2 DTD, but try either of these:

1: Change the offending <column> to "<formula>CountryCode</formula>", or
2: Add 'insert="false" update="false" to it.

I'm confident but not certain that they both work in NHibernate. One of them is bound to.

pdegenhardt · **Joined:** Thu Apr 12, 2007 10:35 pm **Posts:** 11

Thanks but according to the latest schema, the only child element permitted under <many-to-one> element is <column>.

Unfortunately, the <column> element does not allow "insert" or"update" attributes.

???

Cheers,
Phil

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Nasty. I recommend upgrading to java :)

I found the DTD. I see that many-to-one itself accepts insert="false" update="false", so you could make the Country many-to-one read-only, and make StateProv mandatory. This would mean that you'd need a fake state for all countries that don't have actual states/provinces. You could put that in business logic, to hide that bit of awkwardness from the user. So when they select Ireland they're not given a choice of province (like anyone cares if you live in Munster or Connaught, yea right), instead their state is set to State.IRELAND (or however it's done in C#).

pdegenhardt · **Joined:** Thu Apr 12, 2007 10:35 pm **Posts:** 11

Like the "upgrade" option. hehehe :-) I'll put that to the customer - "to eliminate 2 bytes of redundant data per row in your Address table, we need to make a slight adjustment to our development direction... and hire a new project team..."
;-)

Interesting solution, but the fudge factor's a bit high for my liking.

Cheers,
Phil

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Your final option is to use synthetic keys. Hibernate works best when every table has its own ID.. composite ids frequently gum up the works.