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

Answer 1

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

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

2. 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();
   }