18

Is There any way to RESEED a LocalDB Table using EF?

I'd prefer not to use this SQL Command :

DBCC CHECKIDENT('TableName', RESEED, 0)

FYI : I'm using EF 6.1.

Thanks alot.

Mahdi Rashidi
  • 1,359
  • 3
  • 18
  • 33

2 Answers2

27

I assume you're trying to reset the primary key on the table? If so, no there is no way in EF to do this.

As you stated, you would have to use a SQL command such as:

context.Database.ExecuteSqlCommand("DBCC CHECKIDENT('TableName', RESEED, 0)")

But I have to ask why you're trying to do this anyway? It shouldn't matter to you what value is in your primary key field.

Darren Lamb
  • 1,535
  • 14
  • 18
  • Thanks @Darren, I used to show the Primary Key as Record Number in DataGridView, so i wanted to RESEED Tables. – Mahdi Rashidi Oct 31 '14 at 08:48
  • 1
    before that I would call a truncate (context.Database.ExecuteSqlCommand("TRUNCATE TABLE [TableName]"); ) or delete (context.Database.ExecuteSqlCommand("DELETE FROM [TableName]"); ) to wipe out old values so you won't have any duplicates. – Dan Csharpster Aug 08 '15 at 13:19
2

With the help of Rui Jarimba's nice answer, it's better to write an extension method like below if you need DBCC CHECKIDENT everywhere:

public static class ContextExtensions
{
    public static void DbccCheckIdent<T>(this DbContext context, int? reseedTo = null) where T : class
    {
        context.Database.ExecuteSqlCommand(
            $"DBCC CHECKIDENT('{context.GetTableName<T>()}',RESEED{(reseedTo != null ? "," + reseedTo: "")});" +
            $"DBCC CHECKIDENT('{context.GetTableName<T>()}',RESEED);");
    }

    public static string GetTableName<T>(this DbContext context) where T : class
    {
        var objectContext = ((IObjectContextAdapter) context).ObjectContext;
        return objectContext.GetTableName<T>();
    }

    public static string GetTableName<T>(this ObjectContext context) where T : class
    {
        var sql = context.CreateObjectSet<T>().ToTraceString();
        var regex = new Regex(@"FROM\s+(?<table>.+)\s+AS");
        var match = regex.Match(sql);
        var table = match.Groups["table"].Value;
        return table;
    }
}

And enjoy simple call to it:

using (var db = new LibraryEntities())
{
    db.DbccCheckIdent<Book>(); //which Book is one of your entities
    db.DbccCheckIdent<Book>(0); //if you want to pass a new seed
}
Nick Cox
  • 6,164
  • 2
  • 24
  • 30
ABS
  • 2,626
  • 3
  • 28
  • 44
  • The problem is that after you invoke DBCC CHECKIDENT you will start getting the following errors on the table you just reseeded: ': The instance of entity type '___' cannot be tracked because another instance with the same key value for {'Id'} is already being tracked.' Definitely verified with EF Core. – Radek Strugalski Nov 14 '18 at 17:52
  • 1
    Don't forget to check if table is not empty before reseed, otherwise you can have first row with Id 0 instead of 1. – Muflix May 15 '21 at 08:05