Entity Framework force id[key] insert


I have json data with Ids, my goal is to take the json data and insert into database using Entity Framework, with the same id set as I'm using them as a foreign key in different table. But the problem is I'm getting error

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'ProductTypes' when IDENTITY_INSERT is set to OFF.

which means IDENTITY_INSERT is set to OFF and I don't get why as I'm using

dbContext.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.ProductTypes ON");

command to make sure it's set to on.

public class ApplicationDbContextSeed
    public static async Task SeedAsync(ApplicationDbContext dbContext, ILoggerFactory loggerFactory)
            if (!dbContext.ProductTypes.Any())
                var typesData = File.ReadAllText("../Infrastructure/Data/SeedData/types.json");

                var types = JsonSerializer.Deserialize<List<ProductType>>(typesData);

                dbContext.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.ProductTypes ON");
                foreach (var item in types)

                await dbContext.SaveChangesAsync();
                dbContext.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.ProductTypes OFF");
        catch (Exception ex)
            var logger = loggerFactory.CreateLogger<ApplicationDbContextSeed>();
asked on Stack Overflow Feb 14, 2021 by user13490951 • edited Feb 14, 2021 by marc_s

2 Answers


EF will Open/Close your connection for each statement by default, and so your SET IDENTITY_INSERT is not surviving until SaveChanges. If you force the connection open the DbContext will use the open connection for all operations and close it for you when the DbContext is Disposed.


using var db = new Db();

var con = db.Database.GetDbConnection();
db.Database.ExecuteSqlRaw("drop table if exists test; create table test(id int identity, a int)");
db.Database.ExecuteSqlRaw("set identity_insert test on");
db.Database.ExecuteSqlRaw("insert into test(id,a) values (1,1)");


If the key columns are declared as "Id" or with an "Id" suffix then EF will automatically assume these are identity columns which the DB will populate.

One option to consider if the table is always going to be populated by an external source and you don't need to create rows where an ID would be expected to be generated: Configuring EF to not expect the identity:

I.e. using modelBuilder on the DbContext.OnModelCreating:

modelBuilder.Entity<ProductType>(e =>
    e.HasKey(x => x.Id)
        .Property(x => x.Id)

This tells EF to expect the code inserting the record to provide the ID. This means removing the Identity from the column. If the app would be creating product types then it would be better to configure a separate DbContext with the above disabled Identity (plus migrations disabled if you are using those with your main application DbContext) and issue a temporary direct SQL statement to enable the identity insert before the insert operations, then turn identity insert off afterwards.

answered on Stack Overflow Feb 15, 2021 by Steve Py

User contributions licensed under CC BY-SA 3.0