CODEWITHSUNDEEP
c#unit-testingasp.net-coreentity-framework-core
PixelSupreme
PixelSupreme

Reputation: 415

Unit test with entity framework DB context: Does not reset ID counter

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://learn.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.

Upvotes: 4

Views: 3934

Answers (2)

Marvin Klar
Marvin Klar

Reputation: 1917

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.

Upvotes: 3

Chris Pratt
Chris Pratt

Reputation: 239460

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;

Upvotes: 2

Related Questions