NHibernate - Insert with identity_insert ON

Question

I have an issue where I am trying to re-insert an entity into my database. It is illustrated with the following unit test:

        // entity mapped to dbo.IdentityInsertTest table
        // dbo.IdentityInsertTest has an IDENTITY Primary Key, Id
        var id = (long)NHibernateSession1.Save(new IdentityInsertTest());
        NHibernateSession1.Flush();

        // delete previously created row
        ExecuteNonQuery("DELETE FROM dbo.IdentityInsertTest");

        try
        {
            // set entity insert off so that I can re-insert
            NHibernateSession2.CreateSQLQuery("SET IDENTITY_INSERT dbo.IdentityInsertTest ON").UniqueResult();

            // re-create deleted row with explicit Id
            NHibernateSession2.Save(new IdentityInsertTest { Id = id });
            NHibernateSession2.Flush();

            Assert.AreEqual(1, ExecuteScalar("SELECT COUNT(1) FROM dbo.IdentityInsertTest"));

            // this assert fails: expected 1, actual 2
            Assert.AreEqual(id, ExecuteScalar("SELECT TOP 1 [Id] FROM dbo.IdentityInsertTest"));
        }
        finally
        {
            NHibernateSession2.CreateSQLQuery("SET IDENTITY_INSERT dbo.IdentityInsertTest OFF").UniqueResult();
        }

My mapping is quite simple:

<class name="IdentityInsertTest" table="IdentityInsertTest">
    <id name="Id" type="long">
        <generator class="native" />
    </id>

    <property name="Data" type="int" not-null="false" />
</class>

The issue as far as I can see it is that the NHibernate generator is still somehow invoking the identity generation from SQL, even though I have switched it off. Is there any way around this?

Edit: I had originally forgotten to execute "UniqueResult()" when setting IDENTITY_INSERT, but this does not seem to be the root of the error. Still getting the same results

Are you wanting to red-add an entity somewhere in the middle of a table OR wanting to add an entity at the end? I really don't get your logic here, why do you need to do this? — Rippo, Nov 16 '12 at 15:18
As said in another comment, this is implementing undo/redo logic in a cloud app, with the lack of a natural key — Shane, Nov 16 '12 at 16:33
If ALL tables are identity columns and you have database integrity then why does the key have to go back to the original state? Also my comment was before your clarification... Sorry but I still don;' get your logic — Rippo, Nov 16 '12 at 16:36
If I change the Id I have to transmit that back to the client and things could get very messy. It would also be impossible for read only clients to execute work — Shane, Nov 16 '12 at 16:39

marc.d · Answer 1 · 2012-11-16T14:25:03.083

4

you are not actually executing your SQLQuery, this should do the trick

 IQuery sqlQry = NHibernateSession2.CreateSQLQuery("SET IDENTITY_INSERT dbo.IdentityInsertTest ON");
 object ret = sqlQry.UniqueResult();

edited Nov 16 '12 at 14:25

answered Nov 16 '12 at 14:18

marc.d

3,804
5
31
46

I have changed my code to include this, but it didn't change the outcome. Thanks for pointing it out though, it would have become a problem later. – Shane Nov 16 '12 at 14:32

Rippo · Answer 2 · 2012-11-16T15:34:18.443

Just wondering your logic here regarding deleting/re-adding as opposed to NOT deleting but just updating....

However if NHibernate is getting in your way and you can't change remove Identity column then there are some hideous work arounds...

If you want to add a record at the bottom then you could try this:-

var sql = "DECLARE @id long = 0;
    SELECT @id = MAX(Id) + 1 FROM IdentityInsertTest;
    DBCC CHECKIDENT(IdentityInsertTest, RESEED, @id);";

    NHibernateSession2.CreateSqlQuery(sql).ExecuteUpdate();

    ... now save the entity normally

OR If you want to add a record somewhere in the middle of the table then you will have to build the SQL by hand:-

var sql = "SET IDENTITY_INSERT dbo.IdentityInsertTest ON; 
    INSERT INTO IdentityInsertTest(Id, Data) Values (:id, :data)
    VALUES (:id, :data);
    SET IDENTITY_INSERT dbo.IdentityInsertTest OFF;";

    NHibernateSession2.CreateSqlQuery(sql)
      .SetInt64("id", id)
      .SetInt32("data", data)
      .ExecuteUpdate();

This answer pointed me in the right direction. The problem (or one of them) was that NHibernate wasn't executing the whole thing in a concurrent statement like you have above — Shane, Nov 16 '12 at 16:50

Shane · Accepted Answer · 2012-11-20T10:13:54.257

NOTE: I have marked this as the answer as it directly answers the question, however, in the end I went with a soft delete option as commented above

The problem was that

I wasn't specifying the Id explicitly in the save method

even if I had, the set identity_insert would have been executed in another query. That one is fixed by using a transaction

// entity mapped to dbo.IdentityInsertTest table
// dbo.IdentityInsertTest has an IDENTITY Primary Key, Id
var id = (long)NHibernateSession1.Save(new IdentityInsertTest());
NHibernateSession1.Flush();

// delete previously created row
ExecuteNonQuery("DELETE FROM dbo.IdentityInsertTest");

try
{
    NHibernate.ITransaction txn;
    using (txn = SelectSession1.BeginTransaction())
    {
        // set entity insert off so that I can re-insert
        NHibernateSession2.CreateSQLQuery("SET IDENTITY_INSERT dbo.IdentityInsertTest ON").UniqueResult();

        // re-create deleted row with explicit Id
        NHibernateSession2.Save(new IdentityInsertTest(), id);
        NHibernateSession2.Flush();

        txn.Commit();
    }

    Assert.AreEqual(1, ExecuteScalar("SELECT COUNT(1) FROM dbo.IdentityInsertTest"));

    // this assert fails: expected 1, actual 2
    Assert.AreEqual(id, ExecuteScalar("SELECT TOP 1 [Id] FROM dbo.IdentityInsertTest"));
}
finally
{
    NHibernateSession2.CreateSQLQuery("SET IDENTITY_INSERT dbo.IdentityInsertTest OFF").UniqueResult();
}

score 1 · Answer 4 · answered Nov 16 '12 at 14:57

1

Choose another key generation strategy, what you're attempting to do is a really bad idea. An identity column is an artificial primary key and it should not have any meaning.

answered Nov 16 '12 at 14:57

Jamie Ide

48,427
16
81
117

That is a pretty big step to take at this stage and I don't fully understand your argument "An identity column is an artificial primary key". Do you know of any articles I could read on this for clarification? – Shane Nov 16 '12 at 15:08
Google surrogate vs natural primary key. Natural primary keys, such as social security number, are rarely used in modern systems. A SQL identity column is a surrogate key; this type of key has no meaning outside of uniquely identifying the record. A well designed system should not need to insert a record with a particular surrogate key. What's the problem you're trying to solve? – Jamie Ide Nov 16 '12 at 16:04
I see what you are getting at, but this data has no natural key. The entire issue stems from implementing the memento undo pattern in a cloud (browser based) app. I am using the identity as a natural key because I have no other – Shane Nov 16 '12 at 16:27
OK, then I would suggest using a soft delete instead, or don't actually delete until the user no longer has the possibility of un-doing the operation. – Jamie Ide Nov 16 '12 at 22:49

score 0 · Answer 5 · answered Jul 30 '15 at 02:27

I have to say, this issue blocked me long time. Even through I exec sql "SET IDENTITY_INSERT dbo.IdentityInsertTest ON" before and then run Nihbernate code, it was stil does not work. there are 2 points need to take more attation.

First, you must use Transaction in you code.

NHibernate.ITransaction txn;
using (txn = SelectSession1.BeginTransaction())
{
    NHibernateSession2.CreateSQLQuery("SET IDENTITY_INSERT dbo.IdentityInsertTest ON").UniqueResult();

    ...

    NHibernateSession2.Flush();
    txn.Commit();
}

Second, you must use "Id(x => x.Id).GeneratedBy.Assigned().Column("Id");" in your mapping section.

NHibernate - Insert with identity_insert ON

5 Answers5

Linked