Inserting a record with a Composite Key using NHibernate

Question

Hey all,

I am working with a legacy database that uses composite keys. And I am trying to use NHibernate to insert a new record into the database. NHibernate specifies that I have to create the Id manually, but when I try to insert with this id I get the message:

System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'tablename' when IDENTITY_INSERT is set to OFF.

I cannot touch any of the db settings as they are administered by head office in USA.

I found that I can do a db insert via:

insert into tablename (tablename_country_id, /*extra fields here*/) values (16, /*extra values here*/)

and the tablename_id column is automatically incremented.

Is it possible to write some sort of a handler that allows me to create an ID object with the CountryId set and have it auto-increment the Id property.

Cheers.

Example Code:

Table Definition:

CREATE TABLE [dbo].[tablename](
    [tablename_country_id] [int] NOT NULL,
    [tablename_id] [int] IDENTITY(1,1) NOT NULL,

    -- more fields here

    CONSTRAINT [pk_tablename] PRIMARY KEY
    (
        [tablename_country_id] ASC,
        [tablename_id] ASC
    )
)

Class Files:

public class ModelObject
{
    public ID { get; set; }
    // more properties here
}

public class ID : INHibernateProxy
{
    public int Id { get; set; }
    public int CountryId { get; set; }
    public ILazyInitializer HibernateLazyInitializer { get { throw new ApplicationException(); } }
}

Mapping File:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Model" assembly="API">
    <class name="ModelObject" table="dbname.dbo.tablename" lazy="false">
        <composite-id name="Id" class="ID">
            <key-property name="Id" column="tablename_id" type="int" />
            <key-property name="CountryId" column="tablename_country_id" type="int" />
        </composite-id>
        <!-- more properties here -->
    </class>
</hibernate-mapping>

Answer 1

I Think you can set an identity column to one of your keys in the composite key.

i know that in fluentnhibernate i have a identity column name for a omposite key

Answer 2

perhaps this may work. If you add the update statement explicitly, Also if you need to change it to a sProc or something like that you can.

https://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/querysql.html#querysql-cud

here is an example of the sproc concept.

<class name="Person">
    <id name="id">
        <generator class="increment"/>
    </id>
    <property name="name" not-null="true"/>
    <sql-insert>exec createPerson ?, ?</sql-insert>
    <sql-delete>exec deletePerson ?</sql-delete>
    <sql-update>exec updatePerson ?, ?</sql-update>
</class>

Answer 3

I understand that you cannot alter the database table or constraints, but are you absolutely sure that the database objects really imply that you have to use an NHibernate composite key?

An NHibernate composite key should be used for those tables where the uniqueness of a row is determined by more than one column. In the example that you provided in turns out that the each row's uniqueness is actually only determined by the single tablename_id column. The primary key turns out to be superfluous: that is, it cannot be violated because one of its constituent elements is an identity column which will always be different.

Perhaps the primary key has been placed there by your colleagues for some other reason (indexing etc.).

This means that your NHibernate mapping should really collapse to something quite simple: a normal POCO with a single ID mapping to tablename_id using a simple NHibernate native id generator. The CountryID property then joins your ellided properties as a normal property to achive your aim: CountryID is set with ID auto-incremented by the database.

public class ModelObject
{
    public int ID { get; set; }
    public int CountryID { get; set; }
    // more properties here
}

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Model" assembly="API">
    <class name="ModelObject" table="dbname.dbo.tablename" lazy="false">
        <id name="ID" column="tablename_id" type="int">
        <generator class="native" />
    </id>
        <property name="CountryID" column="tablename_country_id" type="int" />
        <!-- more properties here -->
    </class>
</hibernate-mapping>