Efcore: How to reset Autoincrement of entity id's?

[julienshepherd]

Steps to reproduce

Add data to a table and delete it. Do this a couple of times. Now when you add data again, the Id of the added row is incremented off the last row id (which was deleted).

The issue

If I have 10 rows of data in a table with id's from 1 to 10, then I add another 5 rows with id's from 11 to 15, and delete the last 5 rows, thus the table remaining with the initial 10 rows, I was expecting that the next row I will add will have the id of 11, not 16.

When testing adding data to a database and once you finished the testing you want to delete existing table data and add real data, you would expect the id's to start from 1 again.

How do I reset the autoincrement behavior of id's to start from 1 again once I delete all table rows?

Further technical details

EF Core version: 1.0.0
Operating system: Windows 10 1607
Visual Studio version: VS 2015 Community Edition Update 3, version 14.0.25425.01

[divega]

If you are using SQL Server you can issue the following statement to reset the identity:

DBCC CHECKIDENT('tableName', RESEED, 0)

You can do that from EF Core using the context.Database.ExecuteSqlCommand(...).

If this is a testing scenario you may as well consider deleting and recreating the actual table or the entire database.

Providing a uniform API to reset value generation across data stores is outside of the intended scope of EF Core.

[julienshepherd]

I'm actually using SQLite. In the end I managed to workaround a solution that does not depend on the id's. Maybe someone else in the same situation would find this question useful.

Thank you for the answer.

[michaelrinderle]

Recommend reopening...

using dbSet.ClearAndReseed();.

Doesn't work and stalls xamarin forms / sqlite app.

using ctx.Database.ExecuteSqlCommand("DBCC CHECKIDENT('tableName', RESEED, 0));"

results in a null exception.

still not working.