C# – Letting the database pick the primary key in nhibernate

cnetnhibernate

How do I just simply allow MySQL to assign a primary key to an inserted object with nhibernate? It seems I would want to set the generator as a type "identity", but the documentation states that using this "..require[s] two SQL queries to insert a new object." Why would it do that? Is there some way to get this functioning like a normal insert sql statement?

Best Answer

The reason that it requires two queries is that with an identity the value of the column is not defined until the row is inserted. Therefore it requires a select after insert to get the column value for the inserted object. This is pretty standard and I wouldn't let it stop me from using autogenerated keys as my primary key. The other option is to pre-generate the key -- say a GUID for the new object before persisting it to the database. For the most part I don't really see an advantage to this unless there are other mitigating circumstances, such as having to merge data from separate databases where autogenerated keys might collide.

Related Solutions

R – Fetch child without primary key NHibernate

Well, I found it eventually. The parent object is drawn from a view giving three columns and no key. I can map a composite key to the HoleId and PlayerId instead of the evil guid that I found when I looked at the code. This is great as I can easily map the Score objects I need and then lazy load them using NHibernateUtil.Initialize.

My mapping xml needs to look like this

<class name="ParentObject">
    <composite-id>
      <key-property name="HoleId" column="HoleId" />
      <key-property name="PlayerId" column="PlayerId" />      
    </composite-id>
    <property name="EvilGuid" column="Id" />
    <bag name="ScoreCard">
      <key>
        <column name="HoleId"/>
       <column name="PlayerId"/>
      </key>  
      <one-to-many class="Score" not-found="ignore"/>
    </bag>
</class>

I got my inspiration from this post, please also pay attention to Stefan's answer as I feel I had a lucky break here, and the design could be made better with more thought about DDD.

Thanks for your help.

R – Is it possible to map a composite key in fluent nhibernate where half of the key is an identity field in the database

If it's really an identity column, then it's guaranteed to be unique for every record, right?

If so, you can ignore the tenancy ID completely for mapping purposes. Your identity fields will be unique in your primary table - AND your foreign key references will contain this unique Id, so the tenancy ID is actually irrelevant.

NHibernate doesn't really care whether you're mapping your database schema exactly. I've worked with legacy databases before where I've defined a unique constraint on a non-key column and then told NHibernate to use that as the primary key - it works, and it can neatly sidestep all kinds of horrific composite-key relationship mappings.

Think about it. Counter-intuitive, but it will probably work.

Best Answer

Related Solutions

R – Fetch child without primary key NHibernate

R – Is it possible to map a composite key in fluent nhibernate where half of the key is an identity field in the database

Related Topic