[SQL] Reset identity colums EF Core causing side effects

This is my seeding method:

public async Task Seed()
{
dbContext.Database.Migrate();

string dbName = dbContext.Database.GetDbConnection().Database;
if (!dbName.EndsWith("_Tests"))
{
var tableNames = dbContext.Model.GetEntityTypes()
.Select(t => t.GetTableName())
.Distinct()
.ToList();



tableNames.ForEach(tableName =>
{

dbContext.Database.ExecuteSqlRaw($"ALTER TABLE {tableName} NOCHECK CONSTRAINT ALL;");
});

tableNames.ForEach(tableName =>
{
dbContext.Database.ExecuteSqlRaw($"DELETE FROM {tableName};");
dbContext.Database.ExecuteSqlRaw(
$"IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('{tableName}') AND is_identity = 1) " +
$"BEGIN DBCC CHECKIDENT ('{tableName}', RESEED, 0); END");


});



tableNames.ForEach(tableName =>
{
dbContext.Database.ExecuteSqlRaw($"ALTER TABLE {tableName} CHECK CONSTRAINT ALL;");
});

//Seed/insert data
await SeedTable1();
await SeedTable2();
await SeedTable3();

}
}


Everything worked fine until I added this line:

dbContext.Database.ExecuteSqlRaw(
$"IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('{tableName}') AND is_identity = 1) " +
$"BEGIN DBCC CHECKIDENT ('{tableName}', RESEED, 0); END");


That line resets the identity columns in the db so every development restart the inserted items begin at PK 1.

However due to this line I am getting errors in my seeding functions SeedTable2/3 any one which has foreign keys. It is throwing the following error:

The value of 'CustomOptionMachineCategory (Dictionary<string, object>).AllowedOptionsId' is unknown when attempting to save changes. This is because the property is also part of a foreign key for which the principal entity in the relationship is not known.
​

This is a FK constraint, so I tried adding the following behind the inserting data/seeding.

tableNames.ForEach(tableName =>
{
dbContext.Database.ExecuteSqlRaw($"ALTER TABLE {tableName} CHECK CONSTRAINT ALL;");
});


But the error still continues.

What can I do to fix this?

Continue reading...