NHibernate-在identity_insert为ON的情况下插入 [英] NHibernate - Insert with identity_insert ON

问题描述

我在尝试将实体重新插入数据库时​​遇到问题.以下单元测试对此进行了说明:

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

我的映射非常简单:

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

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

据我所知，问题是NHibernate生成器仍然以某种方式从SQL调用身份生成，即使我已将其关闭.有什么办法解决吗?

我最初忘记设置IDENTITY_INSERT时执行"UniqueResult()"，但这似乎不是错误的根源.仍然得到相同的结果

解决方案

注意:我将其标记为答案，因为它直接回答了问题，但是最后，我还是使用了上面提到的软删除选项

问题是

1. 我没有在save方法中明确指定ID

2. 即使我有，集合identity_insert也将在另一个查询中执行.通过交易解决了这一问题

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

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

解决方案

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

这篇关于NHibernate-在identity_insert为ON的情况下插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！