Entity framework and sql identity issue

Question

I need to reset my ID (identity) of my table because each time I make an update, I have to begin my IDs on 1 due to they increase it into more than 60 000 records each time. How can I do this?

using (DailyContext context= DailyContext.Create())
{
    //cleaning old prices
    foreach (var price in context.Prices)
    {
        context.DeleteObject(price);
    }
    context.SaveChanges();


    for (int i = 0; i < newElements.Total; i++)
    {
        var newPrice = new Price()
        {

            Date = newElements.From.AddDays(i),
            PriceFrom = newElements.Price,
            TotalNights = newElements.TotalNights,
            Profit = newElements.Profit
        };
        context.AddToPrices(newPrice);
    }

    context.SaveChanges();
}

DailyContext is type global::System.Data.Objects.ObjectContext — ArlanG, Aug 07 '15 at 16:46
If you find you are needing to reset your Identity values perhaps you should look in to if your table needs a design change. Perhaps you should use a `bigint` or drop identity entirely and use `uniqueidentifer` with a `newsequentialid()` default value. — Scott Chamberlain, Aug 07 '15 at 18:12
Yeah, that smells, but if you must: http://stackoverflow.com/questions/26155856/how-to-reseed-localdb-table-using-entity-framework — Steve Greene, Aug 07 '15 at 18:16

score 1 · Accepted Answer · answered Aug 07 '15 at 19:35

1

Instead of looping through and deleting the prices individually in EF just use ExecuteSqlCommand to TRUNCATE the table.

This both empties it (more efficiently than DELETE) and resets the IDENTITY column value.

answered Aug 07 '15 at 19:35

Martin Smith

438,706
87
741
845

Scott Chamberlain · Answer 2 · 2015-08-07T20:41:21.550

To reset the identity you need to do it outside of EF and you must do raw queries. The query you need to run is DBCC CHECKIDENT with the RESEED parameter.

using (DailyContext context= DailyContext.Create())
{
    //cleaning old prices
    foreach (var price in context.Prices)
    {
        context.DeleteObject(price);
    }
    context.SaveChanges();

    //Reseed the identity to 0.
    context.Database.ExecuteSqlCommand("DBCC CHECKIDENT (Prices, RESEED, 0)");
    //Roll the identity forward till it finds the last used number.
    context.Database.ExecuteSqlCommand("DBCC CHECKIDENT (Prices, RESEED)");

    for (int i = 0; i < newElements.Total; i++)
    {
        var newPrice = new Price()
        {

            Date = newElements.From.AddDays(i),
            PriceFrom = newElements.Price,
            TotalNights = newElements.TotalNights,
            Profit = newElements.Profit
        };
        context.AddToPrices(newPrice);
    }

    context.SaveChanges();
}

EDIT:

Here is a version that just does it with SqlCommand and has no dependency on Entity Framework.

using (var connection = new SqlConnection(_connectionString))
{
    connection.Open();
    using (SqlTransaction trans = connection.BeginTransaction("PricesCleanup"))
    using (var command = new SqlCommand("", connection, trans))
    {
        //Reseed the identity to 0.
        command.CommandText = "DBCC CHECKIDENT (Prices, RESEED, 0)";
        command.ExecuteNonQuery();

        //Roll the identity forward till it finds the last used number.
        command.CommandText = "DBCC CHECKIDENT (Prices, RESEED)";
        command.ExecuteNonQuery();
        trans.Commit();
    }
}

i have a problem. When you refer context.Database.ExecuteSqlCommand that don't work for me Database don't appear, I create the context using reverse creation of an edmx file from a table of my database. How can I do that? — ArlanG, Aug 07 '15 at 20:27
I don't know why you don't have a `Database` property, but all that really matters is you just run those two lines of SQL. I added a version that just uses SqlCommand. — Scott Chamberlain, Aug 07 '15 at 20:41

Entity framework and sql identity issue

2 Answers2