使用 EntityFramework 6 Code-First 播种期间的 IDENTITY_INSERT [英] IDENTITY_INSERT during seeding with EntityFramework 6 Code-First

问题描述

我有一个具有 Auto-identity (int) 列的实体.作为数据种子的一部分，我想在我的系统中为标准数据"使用特定的标识符值，之后我想让数据库整理出 id 值.

I have an entity that has an Auto-identity (int) column. As part of the data-seed I want to use specific identifier values for the "standard data" in my system, after that I want to have the database to sort out the id value.

到目前为止，我已经能够将 IDENTITY_INSERT 设置为 On 作为插入批处理的一部分，但 Entity Framework 不会生成包含 Id 的插入语句.这是有道理的，因为模型认为数据库应该提供值，但在这种情况下，我想提供值.

So far I've been able to set the IDENTITY_INSERT to On as part of the insert batch, but Entity Framework does not generate an insert statement that include the Id. This makes sense as the model thinks the database should provide the value, but in this case I want to provide the value.

模型(伪代码):

public class ReferenceThing
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id{get;set;}
    public string Name{get;set;}
}

public class Seeder
{
    public void Seed (DbContext context)
    {

        var myThing = new ReferenceThing
        {
            Id = 1,
            Name = "Thing with Id 1"
        };

        context.Set<ReferenceThing>.Add(myThing);

        context.Database.Connection.Open();
        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT ReferenceThing ON")

        context.SaveChanges();  // <-- generates SQL INSERT statement
                                //     but without Id column value

        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT ReferenceThing OFF")
    }
}

谁能提供任何见解或建议?

Anyone able to offer any insight or suggestions?

推荐答案

所以我可能已经解决了这个问题，方法是生成我自己的包含 Id 列的 SQL 插入语句.感觉就像一个可怕的 hack，但它确实有效:-/

So I might have resolved this one by resorting to generating my own SQL insert statements that include the Id column. It feels like a terrible hack, but it works :-/

public class Seeder
{
    public void Seed (DbContext context)
    {

        var myThing = new ReferenceThing
        {
            Id = 1,
            Name = "Thing with Id 1"
        };

        context.Set<ReferenceThing>.Add(myThing);

        context.Database.Connection.Open();
        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT ReferenceThing ON")

        // manually generate SQL & execute
        context.Database.ExecuteSqlCommand("INSERT ReferenceThing (Id, Name) " +
                                           "VALUES (@0, @1)", 
                                           myThing.Id, myThing.Name);

        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT ReferenceThing OFF")
    }
}

这篇关于使用 EntityFramework 6 Code-First 播种期间的 IDENTITY_INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！