I'm trying to write unit test of a fairly simple webservice based on ASP.Net core. For persistence I use entity framework core to access a SQL databse.
In the unit tests I obviously don't want a database that could bleed from one test into another. After some searching I found this article https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/in-memory. My test code looks now something like this:
[Fact]
public void Test_method()
{
var options = new DbContextOptionsBuilder<ServiceContext>()
.UseInMemoryDatabase(databaseName: "Test_method") // Unique name for each test
.Options;
using (var context = new ServiceContext(options))
{
// Add test data
context.Dataset.Add(new ...);
context.SaveChanges();
}
using (var context = new ServiceContext(options))
{
// Perform tests
var controller = new Controller(new Service(context));
...
}
}
That almost works, each test case starts out with an empty DB. But the IDs the testdata elements get assigned does not reset. So if I have one test that adds one element to its DB and another test that adds two, the test element in the first test might get ID 1 or 3, depending on the order in which those two tests are executed.
Is there a way to make sure IDs always start with 1 within a single test method? Testing code that does ID based lookup is really quite ugly when the IDs depend on whether or not other tests ran before the current one.
The Entity Framework inmemory database
does not reset its autoincrement counter.
Here is the statement from the Github issue:
The InMemory provider doesn't the Identity pattern you would get in a relational database. When a column is configured as ValueGeneratedOnAdd it uses values from a single generator for all tables. This would be similar to having all tables setup to use a single sequence in a relational database. This raises one of the important design principles of our InMemory provider, which is that we are not trying to emulate a relational database. This becomes more important as we start to light up non-relational providers.
If you want to have a database that acts like a relational database, but doesn't have the overhead of I/O, then we'd recommend using an In-Memory SQLite database - http://www.sqlite.org/inmemorydb.html.
We're going to update our testing docs to make the SQLite option more prominent.
Source: https://github.com/aspnet/EntityFrameworkCore/issues/6872
So you could either think about resetting the counter manually each time you run a test (something like ALTER TABLE mytable AUTO_INCREMENT = 1;
), or using a different sql provider as mentioned in the post.
Just use something random as your database name like Guid.NewGuid().ToString()
, that will reset everything, everytime, because it will be an entirely new "database" in memory.
var options = new DbContextOptionsBuilder<ServiceContext>()
.UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString()) // Unique name for each test
.Options;