Why can't I reference child entities with part of the parent entities composite key

Question

I am trying to reference some child entities with part of the parents composite key not all of it, why cant I? This happens when I use the following mapping instead of that which is commented.

I get the following error

Foreign key in table VolatileEventContent must have same number of columns as referenced primary key in table LocationSearchView

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="JeanieMaster.Domain.Entities" assembly="JeanieMaster.Domain">
  <class name="LocationSearchView" table="LocationSearchView">

    <composite-id>
      <key-property name="LocationId" type="Int32"></key-property>
      <key-property name="ContentProviderId" type="Int32"></key-property>
      <key-property name="CategoryId" type="Int32"></key-property>
    </composite-id>

    <property name="CompanyName" type="String" not-null="true" update="false" insert="false"/>
    <property name="Description" type="String" not-null="true" update="false" insert="false"/>
    <property name="CategoryId" type="Int32" not-null="true" update="false" insert="false"/>
    <property name="ContentProviderId" type="Int32" not-null="true" update="false" insert="false"/>
    <property name="LocationId" type="Int32" not-null="true" update="false" insert="false"/>
    <property name="Latitude" type="Double"  update="false" insert="false" />
    <property name="Longitude" type="Double"  update="false" insert="false" />

    <bag name="Events" table="VolatileEventContent" where="DeactivatedOn IS NULL" order-by="StartDate DESC" lazy="false" cascade="none">
      <key>
        <column name="LocationId"></column>
        <column name="ContentProviderId"></column>
        <!--<column name="LocationId"></column>
        <column name="ContentProviderId"></column>
        <column name="CategoryId"></column>-->
      </key>
      <one-to-many class="Event" column="VolatileEventContentId"></one-to-many>
    </bag>

  </class>
</hibernate-mapping>

And VolatileEventContent mapping file

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="JeanieMaster.Domain.Entities" assembly="JeanieMaster.Domain">
  <class name="Event" table="VolatileEventContent" select-before-update="false" optimistic-lock="none">
    <composite-id>
      <key-property name="LocationId" type="Int32"></key-property>
      <key-property name="ContentProviderId" type="Int32"></key-property>
    </composite-id>

    <property name="Description" type="String" not-null="true" update="false" insert="false"/>

    <property name="StartDate" type="DateTime" not-null="true" update="false" insert="false" />
    <property name="EndDate" type="DateTime" not-null="true" update="false" insert="false" />

    <property name="CreatedOn" type="DateTime" not-null="true" update="false" insert="false" />
    <property name="ModifiedOn" type="DateTime" not-null="false" update="false" insert="false" />

    <many-to-one name="Location" class="Location" column="LocationId" />

    <bag name="Artistes" table="EventArtiste" lazy="false" cascade="none">
      <key name="VolatileEventContentId" />
      <many-to-many class="Artiste" column="ArtisteId" ></many-to-many>
    </bag>
  </class>
</hibernate-mapping>

Answer 1

The error is correct. I'm guessing that you are using SchemaExport to generate your tables based on the NHibernate mappings, since the error you are receiving sounds like it would occur during the creation of your tables and foreign keys. SchemaExport would produce tables similar to the following (please note the explanations scattered throughout the code):

CREATE TABLE LocationSearchView (
    LocationId int NOT NULL,
    ContentProviderId int NOT NULL,
    CategoryId int NOT NULL,

    /* ...other columns... */

    /* Note: Generated from LocationSearchView's "composite-id" element.  */
    PRIMARY KEY (LocationId, ContentProviderId, CategoryId)
);

/* Note: Table for the "Event" class. */
CREATE TABLE VolatileEventContent (
    LocationId int NOT NULL,
    ContentProviderId int NOT NULL,

    /* ...other columns... */

    /* Note: Generated from Event's "composite-id" element. */
    PRIMARY KEY (LocationId, ContentProviderId),
    /* Note: Generated from the "key" element of LocationSearchView's Events bag. */
    FOREIGN KEY (LocationId, ContentProviderId) REFERENCES LocationSearchView (LocationId, ContentProviderId)
);

... hence the error. A foreign key must point to a complete primary or unique key - not just part of a primary key. The whole key is 3 columns, not 2. Why would NHibernate use those columns for the foreign key? Because of the <key> element of LocationSearchView's Events bag. <key> specifies which columns from the child point back to the parent.

Let's consider what might happen when you (or NHibernate) try to select from these tables. Assume the following data:

TABLE LocationSearchView
LocationId ContentProviderId CategoryId
========== ================= ==========
1          3                 5
1          3                 6
1          4                 5
1          4                 6
2          3                 5
2          3                 6
2          4                 5
2          4                 6

TABLE VolatileEventContent
LocationId ContentProviderId
========== =================
1          3
1          4
2          3
2          4

It is not possible for "one" LocationSearchView to have "many" Events. Rather, it should be the other way around. Given these tables, there is really a one-to-many relationship from Event to LocationSearchView.

I don't know what the correct solution to this problem is, because I don't know what you are trying to accomplish, but hopefully this helps to illuminate what exactly the problem is.

Answer 2

I've never used nhibernate, but i guess mapping is quite similar the hibernate ones.

If you don't want your one-to-many (LocationSearchView to many VolatileEventContent) association to use the LocationSearchView id, you need to define on the key element of the bag the attribute "property-ref" that will define the property to use instead :

<bag name="Events" table="VolatileEventContent" ...>
  <key property-ref="partialId">
    <column name="LocationId"></column>
    <column name="ContentProviderId"></column>
  </key>
  <one-to-many class="Event" column="VolatileEventContentId"></one-to-many>
</bag>

(column attribute is valid on a one-to-many tag ?)

Now you need to define a property of that name, something like that :

<properties name="partialId" insert="false" update="false">
  <property name="LocationId" type="Int32" update="false" insert="false"/>
  <property name="ContentProviderId" type="Int32" update="false" insert="false"/>
</properties>

You have already defined LocationId and ContentProviderId. Just move the these two properties inside the properties element.

Answer 3

First of all you have an error in your mapping for "LocationSearchView" You define the CategoryId column both as a Property and part of the Composite-id. This is wrong but unfortunately it is not caught when building the mapping and is usually exposed when querying the object. check IndexOutOfRangeException Deep in the bowels of NHibernate

It could be confusing the mapping parser. And i am saying confusing because in both your mappings you rely on Reflection and Convention which imply a more disciplined programming methodology:

1. You don't explicitly define the class attribute of the many-to-one and bag elements but instead you expect from NHibernate to detect the class type from the class definition itself. If by any chance you have set in the class LocationSearchView the following IList<LocationSearchView> Events {get;set;} nhibernate would expect to find the LocationSearchView class for the defined bag and hence require the 3 columns to map the collection
2. You have the same property names for the column names which makes development easier (it actually only makes easier the creation of mappings which you only make once or twice anyway) but in case of errors or changes it makes it harder to detect what went wrong.

So, make your mappings richer and remove the error i mentioned with CategoryId, also include the classes in your post!