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

问题描述

我有一个具有自动身份（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不会生成包含编号。这是有道理的，因为模型认为数据库应该提供价值，但在这种情况下，我想提供价值。

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插入语句来解决这个问题。感觉像一个可怕的黑客，但它的作品： - /

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播种期间IDENTITY_INSERT 6 Code-First的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！