如何强制实体框架插入标识列？

Question

我想编写一些 C# 代码来使用种子数据初始化我的数据库。显然，这将需要能够在插入时设置各种标识列的值。我正在使用代码优先方法。默认情况下，DbContext 处理数据库连接，因此无法使用 SET IDENTITY_INSERT [dbo].[MyTable] ON。因此，到目前为止，我所做的是使用允许我指定要使用的 DB 连接的 DbContext 构造函数。然后，在该 DB 连接中设置 IDENTITY_INSERT 为 ON，然后尝试使用实体框架插入记录。以下是我目前的示例：

public class MyUserSeeder : IEntitySeeder {
    public void InitializeEntities(AssessmentSystemContext context, SqlConnection connection) {
        context.MyUsers.Add(new MyUser { MyUserId = 106, ConceptPersonId = 520476, Salutation = "Mrs", Firstname = "Novelette", Surname = "Aldred", Email = null, LoginId = "520476", Password="28c923d21b68fdf129b46de949b9f7e0d03f6ced8e9404066f4f3a75e115147489c9f68195c2128e320ca9018cd711df", IsEnabled = true, SpecialRequirements = null });
        try {
            connection.Open();
            SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT [dbo].[MyUser] ON", connection);
            int retVal = cmd.ExecuteNonQuery();
            context.SaveChanges();
        }
        finally {
            connection.Close();
        }
    }
}

虽然cmd.ExecuteNonQuery()工作正常，但当我运行context.SaveChanges()时，我收到了以下信息：“在将IDENTITY_INSERT设置为ON或复制用户插入NOT FOR REPLICATION身份列时，必须为表'MyUser'中的标识列指定显式值。”因此，近在咫尺却难以实现。

大概是因为MyUserId（它是MyUser表中的Identity列）是主键，当我调用context.SaveChanges()时，实体框架不会尝试设置它，即使我为MyUser实体的MyUserId属性提供了一个值。

那么有办法强制实体框架尝试为实体插入甚至主键值吗？还是有一种方法可以暂时将MyUserId标记为不是主键值，这样EF就会尝试插入它？

- Jez

---

这个可能会有帮助：http://stackoverflow.com/a/7063876/9446 - Michal Klouda

@MichalKlouda 这个答案似乎是从数据库优先而不是代码优先的角度来处理事情的。 - Jez

可能是IDENTITY_INSERT ON not being respected for Entity Framework DBSet.Add method的重复问题。 - AakashM

Answer 1

EF 6 方法，使用 MSDN 文章：

using (var dataContext = new DataModelContainer())
using (var transaction = dataContext.Database.BeginTransaction())
{
    var user = new User()
    {
        ID = id,
        Name = "John"
    };

    dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] ON");

    dataContext.User.Add(user);
    dataContext.SaveChanges();

    dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] OFF");

    transaction.Commit();
}

更新： 为了避免出现错误“在将IDENTITY_INSERT设置为ON时，表'TableName'的标识列必须指定显式值，或者当复制用户插入NOT FOR REPLICATION标识列时”，您应该在模型设计器中将标识列的StoreGeneratedPattern属性的值从Identity更改为None。

请注意，将StoreGeneratedPattern更改为None会导致在未指定id的情况下（正常方式）插入对象失败，并出现错误“在将IDENTITY_INSERT设置为OFF时，无法为表'TableName'的标识列插入显式值”。

- Roman O

---

这完全没有解决 EF 没有插入键的问题，而是解决了如何使其具有事务性。 - emragins

尝试时我遇到了错误："在表'myTable'中的标识列必须显式指定一个值，不论是在将IDENTITY_INSERT设置为ON时还是在复制用户插入非用于复制的标识列时。"（即使我在调用SaveChanges之前为我的表记录对象的标识字段设置了一个值。） - Jon Schneider

@RomanO 感谢您的帮助！但是，我已经检查过我的标识列上的“不用于复制”属性设置为false。我怀疑问题在于EF没有尝试将我指定的ID值发送到数据库（正如在Jones医生答案的“更新”中讨论的那样）。 - Jon Schneider

似乎需要将其包装在一个事务中以使其正常工作。 - liang

ExecuteSqlCommand is out of date now. Use ExecuteSqlRaw or 'ExecuteSqlInterpolated` - Qudus

Answer 2

您无需对连接进行任何奇怪的操作，您可以省略中间人，直接使用ObjectContext.ExecuteStoreCommand实现目标。

然后，您可以通过以下方式实现所需：

context.ExecuteStoreCommand("SET IDENTITY_INSERT [dbo].[MyUser] ON");

我不知道有没有内置的方法可以告诉EF打开identity insert。

这并不完美，但比你当前的方法更加灵活，也不那么“hacky”。

更新:

我刚意识到你的问题还有第二部分。既然你告诉SQL要进行identity插入，EF甚至都不会尝试插入该identity的值（为什么要呢？我们还没有告诉它）。

我没有使用代码优先的经验，但是从一些快速查找中得知，似乎需要告诉EF您的列不应从存储生成。 您需要做类似于这样的事情。

Property(obj => obj.MyUserId)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)
    .HasColumnName("MyUserId");

希望这会指引你朝着正确的方向前进 :-)

- Doctor Jones

---

嗯，Property 代码必须在 OnModelCreating 中完成，对吧？在 OnModelCreating 执行后，EF 没有一些指示该信息的方法吗？ - Jez

大问题？ 如果它只是在您最初运行应用程序时一次性的，以种子现有数据，那就不是大问题。这对于快速设置开发数据库非常有用，而且在DB通用方式中也非常有用（当然，您可以使用TSQL来做到这一点，但在那里您会被锁定到SQL Server）。 - Jez

是的，我星期五下午脑子短路了，所以我删掉了我的评论。如果你找不到更好的方法，我仍然认为创建一个专门用于执行身份插入的实体是一个可能的选择。让我们知道你想出了什么，我会很感兴趣！ - Doctor Jones

您可以有条件地应用DatabaseGenerationOption。我在我的回答中解释了如何做到这一点。 - Chris

我认为使用HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)的问题在于，当您使用实体框架与应用程序一起使用时，插入将失败，因为EF现在期望在任何插入操作中提供一个ID。 - Gwasshoppa

Answer 3

可能有些晚了，但如果有人在使用EF5的DB First时遇到这个问题：我无法让任何一种解决方案起作用，但找到了另一个解决方法：

在运行.SaveChanges()命令之前，我重置了表的标识计数器：

Entities.Database.ExecuteSqlCommand(String.Format("DBCC CHECKIDENT ([TableNameHere], RESEED, {0})", newObject.Id-1););
Entities.YourTable.Add(newObject);
Entities.SaveChanges();

这意味着每次添加后都需要应用.SaveChanges() - 但至少它能够运作！

- Peter Albert

---

嘿，你的解决方案对于更新目的来说很有效，但是现在我这边出现了一个问题，就是它只从主键0开始添加第一条记录。 - Mahavirsinh Padhiyar

日常救星，修改列的解决方案并不理想，因为您需要两次更新生产服务器。这个方法非常完美。只需记住，当您知道将插入第一条记录时，请删除“-1”。 - CularBytes

为什么第一行没有插入正确的ID？ - bit

如果插入代码可能会同时运行（例如典型网站的一部分），请确保将SQL命令和行添加包装在事务中。否则，偶尔情况下，您的应用程序会认为新对象具有一个ID，而SQL Server实际上存储了另一个ID。调试起来非常有趣！ - Edward Brey

@EdwardBrey 当使用EF时，如何将上述三个命令包装在一个事务中？ - Peter Albert

使用内置于EF或EF Core的事务支持。 - Edward Brey

Answer 4

以下是问题的解决方案。我已经在EF6上尝试并且它对我起作用了。以下是一些伪代码，应该可以工作。

首先，您需要创建默认DBContext的重载。如果您检查基类，您会发现其中一个使用现有dbConnection传递的类。请参阅以下代码-

public MyDbContext(DbConnection existingConnection, bool contextOwnsConnection)
        : base(existingConnection, contextOwnsConnection = true)
    {
        //optional
        this.Configuration.ProxyCreationEnabled = true;
        this.Configuration.LazyLoadingEnabled = true;
        this.Database.CommandTimeout = 360;
    }

在 OnModelCreating 中移除数据库生成选项，例如：

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyTable>()
            .Property(a => a.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        base.OnModelCreating(modelBuilder);
    }

现在在代码中需要显式传递一个连接对象，

using (var connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString))
        {
            connection.Open();
            using (var context = new MyDbContext(connection, true))
            {
                context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[MyTable] ON");
                context.MyTable.AddRange(objectList);
                context.SaveChanges();
                context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[MyTable] OFF");
            }

            connection.Close();
        }

- Pavvy

---

这种方法在https://zeffron.wordpress.com/2016/06/03/using-sql-identity-insert-with-entity-framework-making-it-work/中有详细描述。 - Jonny

Answer 5

这个想法只在目标表为空或插入的记录id高于表中已有记录的id时才能可靠地工作！ 三年后，我遇到了类似的问题，需要将生产数据转移到测试系统中。用户希望可以随时将生产数据复制到测试系统中，因此我不是在SQL Server中设置传输任务，而是寻找一种使用现有EF类在应用程序中完成传输的方法。这样，我就可以为用户提供一个菜单项，在他们想要的时候启动传输。

该应用程序使用MS SQL Server 2008数据库和EF 6。由于两个数据库通常具有相同的结构，所以我认为可以通过使用AsNoTracking()读取每个实体的记录，并将记录添加（或AddRange()）到目标DbContext实例的适当属性中来轻松地从一个DbContext实例中转移数据到另一个DbContext实例。

以下是一个包含一个实体的DbContext示例：

public class MyDataContext: DbContext
{
    public virtual DbSet<Person> People { get; set; }
}

为了复制People数据，我按照以下步骤进行：

private void CopyPeople()
{
    var records = _sourceContext.People.AsNoTracking().ToArray();
    _targetContext.People.AddRange(records);
    _targetContext.SaveChanges();
}

只要正确复制表格（避免外键约束问题），这个方法可以很好地工作。但是，对于使用identity列的表格，情况有点棘手，因为EF忽略了id值并让SQL Server插入下一个identity值。对于具有identity列的表格，我最终采取了以下措施：

1. 读取给定实体的所有记录
2. 按照id升序排序记录
3. 将表格的标识种子设置为第一个id的值
4. 跟踪下一个identity值，逐个添加记录。如果id与预期的下一个identity值不同，则将标识种子设置为下一个所需值

只要表格为空（或所有新记录的id都比当前最高id大），并且id按升序排列，EF和MS SQL就会插入所需的id，两个系统都不会抱怨。

以下是一些代码示例：

private void InsertRecords(Person[] people)
{
    // setup expected id - presumption: empty table therefore 1
    int expectedId = 1;

    // now add all people in order of ascending id
    foreach(var person in people.OrderBy(p => p.PersonId))
    {
        // if the current person doesn't have the expected next id
        // we need to reseed the identity column of the table
        if (person.PersonId != expectedId)
        {
            // we need to save changes before changing the seed value
            _targetContext.SaveChanges();

            // change identity seed: set to one less than id
            //(SQL Server increments current value and inserts that)
            _targetContext.Database.ExecuteSqlCommand(
                String.Format("DBCC CHECKIDENT([Person], RESEED, {0}", person.PersonId - 1)
            );

            // update the expected id to the new value
            expectedId = person.PersonId;
        }

        // now add the person
        _targetContext.People.Add(person);

        // bump up the expectedId to the next value
        // Assumption: increment interval is 1
        expectedId++;
    }

    // now save any pending changes
    _targetContext.SaveChanges();
}

利用反射，我能够编写一个Load和Save方法，这些方法适用于DbContext中的所有实体。

这有点像是一个黑客技巧，但它允许我使用标准的EF方法来读取和写入实体，并解决了如何在一组给定情况下将标识列设置为特定值的问题。

我希望这对于其他遇到类似问题的人也有所帮助。

- roadkill

---

我认为格式应该是 $"DBCC CHECKIDENT ('{tableName}', RESEED, {actualId - 1});"。 - Ackdari

Answer 6

经过仔细考虑，我决定Entity Framework拒绝插入标识列不是缺陷，而是一种特性。如果我要插入包含标识值的所有条目到数据库中，我还必须为Entity Framework自动创建的每个链接表创建一个实体！这不是正确的方法。

因此，我正在设置种子类，只使用C#代码创建EF实体，然后使用DbContext保存新创建的数据。将转储的SQL转换为C#代码需要更长的时间，但是“种子”数据的数量不应该太多——它应该是代表在实时数据库中可能存在的数据的小型数据集，可以快速放入新的数据库中以进行调试/开发。这意味着如果我想链接实体，我必须查询已经插入的内容，否则我的代码将无法知道它们生成的标识值。例如，在设置并完成MyRoles的context.SaveChanges之后，此类内容将出现在播种代码中：

var roleBasic = context.MyRoles.Where(rl => rl.Name == "Basic").First();
var roleAdmin = context.MyRoles.Where(rl => rl.Name == "Admin").First();
var roleContentAuthor = context.MyRoles.Where(rl => rl.Name == "ContentAuthor").First();

MyUser thisUser = context.MyUsers.Add(new MyUser {
    Salutation = "Mrs", Firstname = "Novelette", Surname = "Aldred", Email = null, LoginUsername = "naldred", Password="c1c966821b68fdf129c46de949b9f7e0d03f6cad8ea404066f4f3a75e11514748ac9f68695c2128e520ca0275cd711df", IsEnabled = true, SpecialRequirements = null
});
thisUser.Roles.Add(roleBasic);

以这种方式进行操作还可以增加我在更改模式时更新播种数据的可能性，因为当我更改它时（如果我删除一个字段或实体，则使用该字段/实体的现有播种代码将无法编译），我很可能会破坏播种代码。通过使用 SQL 脚本进行播种，情况就不是这样了，而且 SQL 脚本也不具备数据库通用性。

所以我认为，如果您正在尝试设置实体的标识字段以进行 DB 播种数据，则肯定采取了错误的方法。

如果我实际上要从 SQL Server 拖动大量数据到 PostgreSQL（完整的实时 DB，而不仅仅是一些播种数据），我可以通过 EF 进行操作，但我需要同时打开两个上下文，并编写一些代码来从源上下文中获取所有各种实体，并将它们放入目标上下文中，然后保存更改。

通常，只有在将身份值从一个 DB 复制到同一 DBMS 中的另一个 DB 时（SQL Server -> SQL Server、PostgreSQL -> PostgreSQL 等）才适合插入标识值，然后您将在 SQL 脚本中执行此操作，而不是使用 EF 代码优先（SQL 脚本不会具有数据库通用性，但它不需要具有；您不会在不同的 DBMS 之间进行转换）。

- Jez

---

这样做有其合理的情况，例如从另一个系统导入数据。 - Jonny

Answer 7

在这个网站尝试了几个选项后，以下代码对我有效（EF 6）。请注意，如果项目已经存在，它首先尝试正常更新。如果不存在，则尝试正常插入，如果错误是由于IDENTITY_INSERT，则尝试解决此问题。还要注意，db.SaveChanges将失败，因此需要使用db.Database.Connection.Open()语句和可选的验证步骤。请注意，这不会更新上下文，但在我的情况下不是必需的。希望这可以帮助你！

public static bool UpdateLeadTime(int ltId, int ltDays)
{
    try
    {
        using (var db = new LeadTimeContext())
        {
            var result = db.LeadTimes.SingleOrDefault(l => l.LeadTimeId == ltId);

            if (result != null)
            {
                result.LeadTimeDays = ltDays;
                db.SaveChanges();
                logger.Info("Updated ltId: {0} with ltDays: {1}.", ltId, ltDays);
            }
            else
            {
                LeadTime leadtime = new LeadTime();
                leadtime.LeadTimeId = ltId;
                leadtime.LeadTimeDays = ltDays;

                try
                {
                    db.LeadTimes.Add(leadtime);
                    db.SaveChanges();
                    logger.Info("Inserted ltId: {0} with ltDays: {1}.", ltId, ltDays);
                }
                catch (Exception ex)
                {
                    logger.Warn("Error captured in UpdateLeadTime({0},{1}) was caught: {2}.", ltId, ltDays, ex.Message);
                    logger.Warn("Inner exception message: {0}", ex.InnerException.InnerException.Message);
                    if (ex.InnerException.InnerException.Message.Contains("IDENTITY_INSERT"))
                    {
                        logger.Warn("Attempting workaround...");
                        try
                        {
                            db.Database.Connection.Open();  // required to update database without db.SaveChanges()
                            db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT[dbo].[LeadTime] ON");
                            db.Database.ExecuteSqlCommand(
                                String.Format("INSERT INTO[dbo].[LeadTime]([LeadTimeId],[LeadTimeDays]) VALUES({0},{1})", ltId, ltDays)
                                );
                            db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT[dbo].[LeadTime] OFF");
                            logger.Info("Inserted ltId: {0} with ltDays: {1}.", ltId, ltDays);
                            // No need to save changes, the database has been updated.
                            //db.SaveChanges(); <-- causes error

                        }
                        catch (Exception ex1)
                        {
                            logger.Warn("Error captured in UpdateLeadTime({0},{1}) was caught: {2}.", ltId, ltDays, ex1.Message);
                            logger.Warn("Inner exception message: {0}", ex1.InnerException.InnerException.Message);
                        }
                        finally
                        {
                            db.Database.Connection.Close();
                            //Verification
                            if (ReadLeadTime(ltId) == ltDays)
                            {
                                logger.Info("Insertion verified. Workaround succeeded.");
                            }
                            else
                            {
                                logger.Info("Error!: Insert not verified. Workaround failed.");
                            }
                        }
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        logger.Warn("Error in UpdateLeadTime({0},{1}) was caught: {2}.", ltId.ToString(), ltDays.ToString(), ex.Message);
        logger.Warn("Inner exception message: {0}", ex.InnerException.InnerException.Message);
        Console.WriteLine(ex.Message);
        return false;
    }
    return true;
}

- David

Answer 8

有没有一种方法可以强制实体框架尝试插入实体的主键值？

是的，但不像我希望看到的那样干净。

假设您正在使用自动生成的标识键，EF将完全忽略您存储键值的尝试。这似乎是“按设计”为许多详细原因，但仍然有时候您想要完全控制种子数据（或初始加载）。我建议EF在将来的版本中适应这种播种方式。但在他们这样做之前，只需编写一些工作在框架内并自动化混乱细节的代码即可。

即使供应商ID被EF忽略，您仍然可以使用基本循环和计数来确定在活动记录之间添加多少占位符记录。当它们被添加时，占位符被分配下一个可用的ID号。一旦您的实际记录具有所请求的ID，您只需要删除垃圾即可。

    public class NewsprintInitializer: DropCreateDatabaseIfModelChanges<NewsprintContext>
    {
        protected override void Seed(NewsprintContext context)
        {
            var vendorSeed = new List<Vendor>
            {
                new Vendor { VendorID = 1, Name = "#1 Papier Masson / James McClaren" },
                new Vendor { VendorID = 5, Name = "#5 Abitibi-Price" },
                new Vendor { VendorID = 6, Name = "#6 Kruger Inc." },
                new Vendor { VendorID = 8, Name = "#8 Tembec" }
            };

            //  Add desired records AND Junk records for gaps in the IDs, because .VendorID is ignored on .Add
            int idx = 1;
            foreach (Vendor currentVendor in vendorSeed)
            {
                while (idx < currentVendor.VendorID)
                {
                    context.Vendors.Add(new Vendor { Name = "**Junk**" });
                    context.SaveChanges();
                    idx++;
                }
                context.Vendors.Add(currentVendor);
                context.SaveChanges();
                idx++;
            }
            //  Cleanup (Query/Find and Remove/delete) the Junk records
            foreach (Vendor del in context.Vendors.Where(v => v.Name == "**Junk**"))
            {
                context.Vendors.Remove(del);
            }
            context.SaveChanges();

            // setup for other classes

        }
    }

它按预期工作，但我不得不频繁地执行“SaveChanges”以保持ID顺序。

- Greg

Answer 9

我可以帮您进行翻译。这段内容是关于编程的，讲述了创建一个继承上下文的工作：

我通过创建一个继承上下文来完成这项工作：

我的常规上下文与 EF 迁移：

public class MyContext : DbContext
{
    public MyContext() : base("name=MyConnexionString")
    {...}

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // best way to know the table names from classes... 
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        ...
    }
}

我的备选上下文用于覆盖身份标识。

不要将此上下文注册到 EF 迁移中 （我用它来从另一个数据库传输数据）：

public class MyContextForTransfers : MyContext
{
    public MyContextForTransfers() : base()
    {
        // Basically tells the context to take the database as it is...
        Database.SetInitializer<MyContextForTransfers >(null);
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
         // Tells the Context to include Isd in inserts
         modelBuilder.Conventions.Remove<StoreGeneratedIdentityKeyConvention>();
         base.OnModelCreating(modelBuilder);
    }
}

如何插入（错误管理大大简化...）：

public void Insert<D>(iEnumerable<D> items)
{
    using (var destinationDb = new MyContextForTransfers())
    {
        using (var transaction = destinationDb.Database.BeginTransaction())
        {
            try
            {
                destinationDb.Database.ExecuteSqlCommand($"SET IDENTITY_INSERT [dbo].[{typeof(D).Name}] ON");
                destinationDb.Set<D>().AddRange(items);
                destinationDb.SaveChanges();
                destinationDb.Database.ExecuteSqlCommand($"SET IDENTITY_INSERT [dbo].[{typeof(D).Name}] OFF");
                transaction.Commit();
             }
             catch
             {
                transaction.Rollback();
             }
         }
    }
}

在“常规”上下文和配置中，在任何事务之前检查迁移可能是个好主意：

- Dubbs777

Answer 10

我只是一个数据库管理员，但每当出现这样的情况，我就认为这是代码异味。也就是说，为什么你要依赖于某些行具有特定的标识值？也就是说，在您上面的示例中，为什么Novelette女士需要具有106的标识值？与其依赖于这种情况始终存在，不如获取她的标识值，并在任何硬编码106的地方使用它。这样有点麻烦，但更加灵活（在我看来）。

- Ben Thul

---

因为我正在从另一个数据库初始化数据。保持来自该数据库的标识值和外键引用相同会更加简单。 - Jez

标识值应始终被视为任意的。如果它们具有某些固有值（正如您所描述的那样），请勿将列设置为标识列。 - Ben Thul

通常情况下适用，但这是使用某些非常特定的数据进行数据种植。我认为在这种特殊情况下不适用。事实上，如果您从 SQL 脚本中播种数据库，您可能会在插入数据时将 IDENTITY_INSERT 打开，并指定标识字段值。 - Jez

@BenThul 这是标准做法，当软件带有开箱即用/系统值时，客户通过软件添加自己的值。例如，系统值具有负身份列值。在使用过程中，添加正值。然后您需要升级软件并向脚本添加更多值-这些都是负值，并且不会干扰客户的值。 - T.S.

@BenThul认为这样做。如果我有一些样板元数据，我们知道ID，然后用户添加他们自己的，然后我们进行升级，我们不知道使用自动增量会有什么ID。现在，在这种情况下，我们需要有2个表。但是，如果我们的ID为负数，并且客户使用自动增量，我们彼此之间不会有影响。特别重要的是，当某些应用程序代码在专门的对象中硬编码了它们的ID时。或者，比如enum。模式与反模式...有争议。 - T.S.