如何强制实体框架插入标识列? [英] How can I force entity framework to insert identity columns?

问题描述

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

I want to write some C# code to initialize my database with some seed data. Clearly, this is going to require the ability to be able to set the values of various Identity columns when inserting. I'm using a code-first approach. By default, DbContext handles the database connection and so you can't SET IDENTITY_INSERT [dbo].[MyTable] ON. So, what I've done so far is use the DbContext constructor that lets me specify a DB connection to be used. Then, I set IDENTITY_INSERT to ON in that DB connection, and then try to insert my records using entity framework. Here's an example of what I've got so far:

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' 中的标识列指定显式值."

So close and yet so far - because, although cmd.ExecuteNonQuery() works fine, when I then run context.SaveChanges(), I'm informed that "Explicit value must be specified for identity column in table 'MyUser' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column."

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

Presumably, because MyUserId (which is the Identity column in the MyUser table) is the primary key, entity framework doesn't try to set it when I call context.SaveChanges(), even though I gave the MyUser entity a value for the MyUserId property.

有没有办法强制实体框架尝试为实体插入主键值呢?或者可能是一种暂时将 MyUserId 标记为不是主键值的方法，以便 EF 尝试插入它?

Is there a way to force entity framework to try and insert even primary key values for an entity, then? Or maybe a way to temporarily mark MyUserId as not being a primary key value, so EF tries to insert it?

推荐答案

经过仔细考虑，我决定实体框架拒绝插入标识列是一个特性，而不是一个错误.:-) 如果我要在我的数据库中插入所有条目，包括它们的标识值，我还必须为实体框架为我自动创建的每个链接表创建一个实体！这不是正确的方法.

After careful consideration, I've decided that entity framework's refusal to insert identity columns is a feature, not a bug. :-) If I were to be inserting all entries in my database including their identity values, I'd also have to create an entity for every link table that entity framework had created automatically for me! It's just not the right approach.

所以我正在做的是设置只使用 C# 代码并创建 EF 实体的种子类，然后使用 DbContext 来保存新创建的数据.获取转储的 SQL 并将其转换为 C# 代码需要更长的时间，但是没有(也不应该)仅用于播种"数据的太多数据 - 它应该是具有代表性的少量数据可以快速放入新数据库以进行调试/开发的实时数据库中的数据类型.这确实意味着如果我想将实体链接在一起，我必须对已插入的内容进行查询，否则我的代码将不知道它们生成的标识值，例如.在我为 MyRoles 设置并完成 context.SaveChanges 之后，这种事情会出现在种子代码中:

So what I'm doing is setting up seeding classes that just use C# code and create EF entities, then use a DbContext to save the newly-created data. It takes a bit longer to take the dumped SQL and turn it into C# code, but there isn't (and shouldn't be) too much data just for "seeding" data - it should be a smallish amount of data which is representative of the kind of data that would be in a live DB that can quickly be put into a fresh DB for debugging/development purposes. This does mean that if I want to link entities together, I do have to do queries on what has already been inserted or my code wouldn't know their generated identity value, eg. This kind of thing will appear within the seeding code, after I have set up and done context.SaveChanges for MyRoles:

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 脚本也不会与数据库无关.

Doing it this way also makes it more likely I will update my seeding data when I change the schema, because I will likely break the seeding code when I change it (if I remove a field or entity, the existing seeding code that uses that field/entity will fail to compile). With a SQL script for doing seeding, that wouldn't be the case, and nor would the SQL script be database-agnostic.

所以我认为，如果您尝试设置实体的身份字段以进行数据库播种数据，那么您肯定采取了错误的方法.

So I think that if you're trying to set the identity fields of entities for doing DB seeding data, you've definitely taken the wrong approach.

如果我真的将大量数据从 SQL Server 拖到 PostgreSQL(一个完整的实时数据库，而不仅仅是一些种子数据)，我可以通过 EF 来完成，但我希望在同时，编写一些代码从源上下文中获取所有不同的实体并将它们放入目标上下文中，然后保存更改.

If I were actually dragging a load of data from say SQL Server to PostgreSQL (a full live DB, not just some seeding data), I could do it via EF, but I'd want to have two contexts open at the same time, and write some code to grab all the various entities from the source context and put them into the destination context, then save changes.

通常，唯一适合插入标识值的时间是当您从一个数据库复制到同一 DBMS 中的另一个数据库时(SQL Server -> SQL Server、PostgreSQL -> PostgreSQL 等)，然后您会在 SQL 脚本中执行，而不是 EF 代码优先(SQL 脚本不会与 DB 无关，但也不需要；您不会在不同的 DBMS 之间切换).

Generally, the only time it's appropriate to insert identity values is when you're copying from one DB to another DB within the same DBMS (SQL Server -> SQL Server, PostgreSQL -> PostgreSQL, etc.), and then you'd do it in a SQL script and not EF code-first (the SQL script wouldn't be DB-agnostic, but it wouldn't need to be; you're not going between different DBMSs).

这篇关于如何强制实体框架插入标识列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！