How to seed identity seed value in entity framework code first for several tables

Question

I have seen this and this . I simply wish to seed the start value for ID columns for my code first ( EF6.1) tables. Now I can do this

public class CustomInitializer : CreateDatabaseIfNotExists<FormsDbContext>
{
    protected override void Seed(FormsDbContext context)
    {
        context.Database.ExecuteSqlCommand("DBCC CHECKIDENT ('MyTable', RESEED, 1000)");
    }
}

But as I have lots and lots of tables, I find it odd ( and it feels almost wrong) that I would have to repeat the above line for ALL of those. I haven't been able to find any way to do this with fluent configuration . Is this the correct way to do the seed?

Thanks

If you want to reset everything why not drop and recreate the database? — Hamid Pourjam, Mar 07 '15 at 22:43
but then I'd lose all the data. I don't want to lose the data. I just want all my columns IDs to start at, e.g 5000 — iAteABug_And_iLiked_it, Mar 07 '15 at 22:47
you can get name of all tables in your context and execute the command for them. — Hamid Pourjam, Mar 07 '15 at 22:52
thx, well yes I can but that would mean quite a few of the reseed lines and my question really is if there is a neater way to do this — iAteABug_And_iLiked_it, Mar 07 '15 at 22:53

score 4 · Accepted Answer · answered Mar 08 '15 at 01:43

You could try using this:

    internal class DefaultMigrationSqlGenerator : SqlServerMigrationSqlGenerator
    {
        protected override void Generate(AlterTableOperation alterTableOperation)
        {
            base.Generate(alterTableOperation);
            // If the tables you want to reseed have an Id primary key...
            if (alterTableOperation.Columns.Any(c => c.Name == "Id"))
            {
                string sqlSeedReset = string.Format("DBCC CHECKIDENT ({0}, RESEED, 1000) ", alterTableOperation.Name.Replace("dbo.", ""));

                base.Generate(new SqlOperation(sqlSeedReset));
            }
        }
    }

You can use a multitude of different options instead of AlterTableOperation, add a column, rename a column etc. to trigger this to run on every table. Unfortunately, you will have to do something that updates every table to be able to inject your own action on each of them.

The alternative is to script it in SQL Server - it's not like it would be a daily or weekly occurrence. Iterate through the tables, resetting the seed on each. But if you need something to happen on all tables regularly, and not in SQL Server, then I think the above is the way to go.

I liked this approach, this will do it for me. Thanks :) – iAteABug_And_iLiked_it Mar 08 '15 at 09:28 — iAteABug_And_iLiked_it, Mar 08 '15 at 09:28

How to seed identity seed value in entity framework code first for several tables

1 Answers1

Linked