C#•2mo ago

Reset identity colums EF Core error.

Hi all, 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();

    }
}

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

    }
}

8 Replies

Jiry_XDOP•2mo ago

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

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

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

But the error still continues. What can I do to fix this?

Joschi•2mo ago

You could consider alternatives to what you are doing now. Both are more or less a variant on "don't manually try to reset your database state". The first would be to call context.EnsureDeleted() followed by context.EnsureCreated() at the start of your tests. This would delete the database and then create one from your code first context. (Note this does not use migrations, it just creates it from the existing config directly). The second option would be to use test containers, which creates a fresh DB as a docker container for your tests. MS has a whole series on articles with guidance on how to test applications using efcore https://learn.microsoft.com/en-us/ef/core/testing/

Overview of testing applications that use EF Core - EF Core

Overview of testing applications that use Entity Framework Core

Joschi•2mo ago

The third option could be, to just not reset your identity columns and write your tests in a way that are agnostic of specific ids. Which should be the case in any way.

Jiry_XDOP•2mo ago

For my tests I use respawn, the seeding is just for the development environment.

Joschi•2mo ago

Ohh sorry I somehow assumed it was for tests.

Jiry_XDOP•2mo ago

No problem 😉 I'd rather have it just work, since its better for development so that I don't have to use id 999 😛 One thing that fixes it is setting the reset value to from 0 to 1. But then it starts at id 2... From

dbContext.Database.ExecuteSqlRaw(
CHECKIDENT ('{tableName}', RESEED, 0); END");

dbContext.Database.ExecuteSqlRaw(
CHECKIDENT ('{tableName}', RESEED, 0); END");

dbContext.Database.ExecuteSqlRaw(
CHECKIDENT ('{tableName}', RESEED, 1); END");

dbContext.Database.ExecuteSqlRaw(
CHECKIDENT ('{tableName}', RESEED, 1); END");

Joschi•2mo ago

Maybe try to create a new dbContext just before your seed methods? Because the error sounds a bit like efCore getting confused. But I'm just guessing here. I mean SeedTableX().

Jiry_XDOP•2mo ago

Hmm that sounds like a viable option, I'll try in a sec Sorry for the late response, doesn't seem to work One thing I should note is that this error only occurs when the db is freshly dropped, and it gets run the first time. After that no errors occur anymore It is very odd. Drop the DB. Remove the Database.Migrate line, so it doesnt migrate Run migrations manually with command Error Start again Works OR Drop the DB. RUN Error Start again Works So I don't know I think it has to do with the initial state of the db which doesnt like the identity reset So, every time PENDING migrations run that has a corresponding seed with fk, either manually or via Database.Migrate the error occurs.

Gaming

Programming

Reset identity colums EF Core error.