为什么不在ID 4.1中为ID设置IDENTITY_INSERT OFF？ [英] Why doesn't SET IDENTITY_INSERT OFF work for me in EF 4.1?

问题描述

我正在使用Entity Framework 4.1 Code First，而且我有一个具有IDENTITY键的表，因为该表中的所有新条目都应该有一个自动生成的ID（ID列称为AccountNumber）。我需要从这个系统的以前的化身导入一些数据 - 这些帐号需要保留。

在以前的问题，我学到了我必须按顺序设置IDENTITY_INSERT ON保留旧帐号。这个想法是，当导入旧客户时，我将打开IDENTITY_INSERT ON，运行一个原始的SQL插入语句，然后将其关闭并使用EF实体正常进行。

所以，我有以下代码：

  public const string InsertQuery =INSERT INTO dbo.Businesses（AccountNumber，Name，Active ，CreatedBy，CreatedOn，ModifiedBy，ModifiedOn）VALUES（{0}，{1}，{2}，{3}，{4}，{5}，{6}） 
  

...

  dbContext.Database.ExecuteSqlCommand（SET IDENTITY_INSERT dbo.Businesses ON）; 
 dbContext.Database.ExecuteSqlCommand（InsertQuery，customerData.AccountNumber，customerData.Name，customerData.Active，
 m_userContextManager.GetCurrentUserName（），Now，
 m_userContextManager.GetCurrentUserName（），Now）; 
 dbContext.Database.ExecuteSqlCommand（SET IDENTITY_INSERT dbo.Businesses OFF）; 
 
 //加载实体并映射其余的属性
 dbContext.SaveChanges（）; 
  

当我执行第二个语句时，我会收到以下令人愤怒的错误（因为我刚刚将其设置为OFF或所以我想）：当IDENTITY_INSERT设置为OFF时，不能在表企业中插入标识列的显式值。

语句的返回值为-1，但因为 MSDN上的ExecuteSqlCommand文档真的不够，我不知道那意味着什么。如果由于某种原因导致语句失败，我会期待抛出异常。有没有人知道这里发生了什么？

解决方案

没有必要使用简单的旧ADO.NET;把所有东西都包装成一个命令：

  public const string InsertQuery = @
 SET IDENTITY_INSERT dbo.Businesses ON; 
 INSERT INTO dbo.Businesses（AccountNumber，Name，Active，CreatedBy，CreatedOn，ModifiedBy，ModifiedOn）VALUES（{0}，{1}，{2}，{3}，{4}，{5 }，{6}）; 
 SET IDENTITY_INSERT dbo.Businesses OFF; 
; 
 
 dbContext.Database.ExecuteSqlCommand（InsertQuery，customerData.AccountNumber，customerData.Name，customerData.Active，
 m_userContextManager.GetCurrentUserName（），Now，
 m_userContextManager.GetCurrentUserName（），现在）; 
 
 //加载实体并映射其余的属性
 dbContext.SaveChanges（）; 
  

此外，您可以删除 SET IDENTITY_INSERT dbo.Businesses OFF （由于IDENTITY_INSERT在命令结束时关闭）和 dbContext.SaveChanges（），因为ExecuteSqlCommand立即执行命令;它不等待SaveChanges（）。

I'm using Entity Framework 4.1 Code First and I have a table that has an IDENTITY key, because all new entries in this table should have an auto-generated ID (the ID column is called AccountNumber). I need to import some data from the previous incarnation of this system - these account numbers need to be preserved.

In a previous question, I learned I have to SET IDENTITY_INSERT ON in order to preserve the old account numbers. The idea is that when importing old customers, I'm going to turn IDENTITY_INSERT ON, run a raw SQL insert statement, then turn it off and proceed normally using EF entities.

So, I have the following code:

    public const string InsertQuery = "INSERT INTO dbo.Businesses (AccountNumber, Name, Active, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn) VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6})";

...

            dbContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Businesses ON");
            dbContext.Database.ExecuteSqlCommand(InsertQuery, customerData.AccountNumber, customerData.Name, customerData.Active,
                                                 m_userContextManager.GetCurrentUserName(), Now,
                                                 m_userContextManager.GetCurrentUserName(), Now);
            dbContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Businesses OFF");

            // load the entity and map the rest of the attributes
            dbContext.SaveChanges();

When I get to executing the second statement, I get the following infuriating error (because I've just set it to OFF or so I think):

Cannot insert explicit value for identity column in table 'Businesses' when IDENTITY_INSERT is set to OFF.

The return value from the statement is -1, but because the documentation on MSDN for ExecuteSqlCommand is really inadequate, I have no idea what that means. I would expect an exception to be thrown if the statement failed for some reason. Does anyone know what's going on here?

解决方案

There is no need to use plain old ADO.NET; the trick is packing everything into a single command:

public const string InsertQuery = @"
    SET IDENTITY_INSERT dbo.Businesses ON;
    INSERT INTO dbo.Businesses (AccountNumber, Name, Active, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn) VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6});
    SET IDENTITY_INSERT dbo.Businesses OFF;
";

dbContext.Database.ExecuteSqlCommand(InsertQuery, customerData.AccountNumber, customerData.Name, customerData.Active,
                                     m_userContextManager.GetCurrentUserName(), Now,
                                     m_userContextManager.GetCurrentUserName(), Now);

// load the entity and map the rest of the attributes
dbContext.SaveChanges();

Additionally, you can drop SET IDENTITY_INSERT dbo.Businesses OFF (since IDENTITY_INSERT is turned off at the end of the command anyway), and dbContext.SaveChanges(), as ExecuteSqlCommand executes the command immediately; it doesn't wait for SaveChanges().

这篇关于为什么不在ID 4.1中为ID设置IDENTITY_INSERT OFF？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！