none
How to clear all data on a table and reset identity column ? RRS feed

  • Question

  • Hello !

    i'm using vb.net 2010 / entity framework / sql server 2008r2. I want to clear all data on a table and after to reset identity column.

    Is there a command on entity framework to do this ?

    Thank you.

    Tuesday, September 4, 2012 4:52 PM

Answers

  • Hi,

    No EF doesn't know nothing about identity columns. You'll have to sent a SQL statement such as http://msdn.microsoft.com/en-us/library/ms176057.aspx to reset those values. Note though that it shouldn't really matter. Technically speaking insert 10 rows, deleting those rows and starting from 11 for the next id is the expected and desirable behavior as it ensure it uses a key never used before (else if someone asked to delete the row whose identity number is 1, you don't know if this is the one you deleted earlier or the one you inserted after reseeding the table).

    The only reason I see would be to reset a db after some testing so that the customer doesn't feel you gave him a database that was already used previously (but anyway those keys should likely never be visible in the UI ?)


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    • Marked as answer by Alexander Sun Wednesday, September 19, 2012 6:58 AM
    Tuesday, September 4, 2012 5:23 PM
  • Hi dcode25; 

    To your question, "Is there a command on entity framework to do this ?", no there is not. What you can do is write a T-SQL script to Drop the table and then recreate it. Once you have the script placed in a string variable then you can execute an EF command as follows:

    var ctx = new ObjectContext();
    var sqlScript = @"Place script here";
    
    ctx.ExecuteStoreCommand( sqlScript, null );

      

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Alexander Sun Wednesday, September 19, 2012 6:58 AM
    Tuesday, September 4, 2012 5:52 PM

All replies

  • Hi,

    No EF doesn't know nothing about identity columns. You'll have to sent a SQL statement such as http://msdn.microsoft.com/en-us/library/ms176057.aspx to reset those values. Note though that it shouldn't really matter. Technically speaking insert 10 rows, deleting those rows and starting from 11 for the next id is the expected and desirable behavior as it ensure it uses a key never used before (else if someone asked to delete the row whose identity number is 1, you don't know if this is the one you deleted earlier or the one you inserted after reseeding the table).

    The only reason I see would be to reset a db after some testing so that the customer doesn't feel you gave him a database that was already used previously (but anyway those keys should likely never be visible in the UI ?)


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    • Marked as answer by Alexander Sun Wednesday, September 19, 2012 6:58 AM
    Tuesday, September 4, 2012 5:23 PM
  • Hi dcode25; 

    To your question, "Is there a command on entity framework to do this ?", no there is not. What you can do is write a T-SQL script to Drop the table and then recreate it. Once you have the script placed in a string variable then you can execute an EF command as follows:

    var ctx = new ObjectContext();
    var sqlScript = @"Place script here";
    
    ctx.ExecuteStoreCommand( sqlScript, null );

      

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Alexander Sun Wednesday, September 19, 2012 6:58 AM
    Tuesday, September 4, 2012 5:52 PM