我怎样才能迫使实体框架插入标识列？ [英] How can I force entity framework to insert identity columns?

问题描述

我想写一些C＃code一些种子数据初始化我的数据库。显然，这将需要能够在插入时设定各种标识列的值的能力。我使用的是code-第一种方法。默认情况下，的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（），我得知显式值必须为标识列'MYUSER'被指定或者当IDENTITY_INSERT设置为ON时或当复制用户插入到NOT FOR REPLICATION标识列。

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."

presumably，因为MyUserId（这是在MYUSER表中标识列）是主键，实体框架不尝试设置它，当我叫 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＃code和创造EF实体播种类，然后用的DbContext 保存新创建数据。它需要更长的时间来采取倾销的SQL，并把它变成C＃code，但没有（也不应该）太多的数据只是播种的数据 - 它应该是数据量短小这是重样的数据，这将是中，能够迅速被放入一个新的DB用于调试/开发目的的现场DB的presentative。这是否意味着，如果我要联系在一起的实体，我必须做的查询什么已经插入或我的code不知道自己生成的标识值，例如。这种事情会出现在播种code之内，我已经建立并完成后， context.SaveChanges 为 MyRoles ：

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

这样做，这样也使得它更容易当我改变的模式，我会更新我的播种数据，因为我很可能会打破播种code，当我改变它（如果我删除字段或实体，现有的使用该字段/实体将无法编译播种code）。随着播种做一个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.

所以我认为，如果你想设置实体的标识字段做DB播种数据，你肯定采取了错误的做法。

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（全活的DB，而不只是一些播种数据），我可以通过EF做到这一点，但我想有两种情况在开放同时，和写一些code抢从源环境中的所有各种实体，并把它们到目标范围内，然后保存更改。

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.

一般情况下，这是适当的插入标识值的唯一时间是当你复制从一个数据库到同一数据库管理系统中的另一个数据库（SQL服务器 - > SQL Server中，PostgreSQL的 - > PostgreSQL的，等等），然后你倒是做在一个SQL脚本，而不是EF code-第一（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屋！