Efcore: EnsureDeleted does not reset "identity" columns for InMemory database provider

[kwarnke]

When unit testing using the InMemory provider, we noticed that using the context.Database.EnsureDeleted() method does successfully "delete" the database (and its data), but it does not reset the values that are assigned to new records for integer id columns.

This is using 7.0.0-rc1-final.

Thanks,
Kevin

Repro:

    public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class MyContext : DbContext
    {
        public MyContext()
        {

        }

        public MyContext(DbContextOptions options) : base(options)
        {

        }

        public virtual DbSet<Person> People { get; set; }
    }

    [Fact]
    public void ShowFailure()
    {
        var db1 = GetMyDb();
        var zach1 = db1.People.Where(p => p.Id == 1).FirstOrDefault();
        Assert.NotNull(zach1); // works

        var db2 = GetMyDb();
        var zach2 = db2.People.Where(p => p.Id == 1).FirstOrDefault();
        Assert.NotNull(zach2); // fails (because the DB is on 3 and 4 now)
    }

    private static MyContext GetMyDb()
    {
        var optionsBuilder = new DbContextOptionsBuilder<MyContext>();

        // This is the magic line
        optionsBuilder.UseInMemoryDatabase();

        var db = new MyContext(optionsBuilder.Options);

        db.Database.EnsureDeleted();

        db.People.Add(
            new Person
            {
                Name = "Zach"
            });
        db.People.Add(
            new Person
            {
                Name = "George"
            }
        );

        db.SaveChanges();
        return db;
    }

[rowanmiller]

This is by design. Having a generated column just means that you will automatically have unique values assigned to new entities when you don't supply a key value. Those values starting at 1, incrementing by 1, and being reset when you drop and re-create the database is how SQL Server IDENTITY works... but it is not a contract for how all data stores will work.

If you want to have specific key values saved to the store, then you can just set them on the new entities and EF will save those, rather than having one generated for you.

[josh-sachs]

This complicates using InMemory provider for unit testing - which I thought was one of the major points of the provider in the first place.

1. If you mock the database with explicit keys, sequencing/auto-increment doesn't happen at all. This breaks all unit tests that specifically test INSERT operations that expect keys to be generated.
2. If you delete a database, and then create a database, the implied behavior is that the newly created database would be unmolested by the previous one... It doesn't matter what data store you are using... a new repository should always start with fresh seeds.

[kwarnke]

@kudoz83 - what I've found myself doing when knowing the key values is important is defining the entities as class variables and with each test executed (e.g., in the constructor) I'll set them to new instances, add them to the in-memory db, then call save changes. At that point my class variables have the entity.id values populated with whatever value was used.

On other cases I'll query the in-memory database for the entity I want, then use it's current entity.id value.

It takes a little shift in mindset to not expect "the first value saved has an id of 1" but once you get over the hump it's actually not that bad.

[josh-sachs]

I appreciate the effort @kwarnke , but it isn't clear to me from what are describing, and I don't believe it really supports the argument from @rowanmiller that there is nothing to consider here - your initial submission I think was on point.

I'm currently working through the info here: http://docs.efproject.net/en/latest/miscellaneous/testing.html to try and create proper test cases, I think re-creating a service provider for each test will reset the data store - but I think that the InMemoryProvider should be re-visited to behave more intuitively rather than have this dismissed as "by design."

I think the crux of what brought me to this ticket is actually that you can't assign explicit Id's during the initial construction of the mock database and still expect auto-sequencing to work. Basically if I could turn off identity-insert when filling with pre-test data, and then turn it back on when it comes time for testing, this would be a non issue. Deleting the database and re-creating it was kind of a workaround for this - except that it doesn't work, because the identify/sequence seed isn't reset (which is counter intuitive).

[ajcvickers]

@kudoz83 The contract of the API does not provide any guarantee what the generated values will be, only that within the constraints of the service and database being used they will be unique. Therefore, your tests should not rely on the values that are generated.

Also, value generation for in-memory databases is a function of the in-memory database server, not the individual databases themselves. This is by design because it can actually help debug issues when values are not repeated across databases.

As @rowanmiller said, if you want really independent tests, then you will need to create a new service provider for each test.

[rowanmiller]

@kudoz83 in addition, if you do want specific values for the key properties then you can just set them before you save. If you explicitly set a value for the key property, then EF will just insert that rather than letting the database generate one for you. This is true for all providers and not just InMemory.

[ardalis]

I want to be able to test an ASPNET core mvc app using EF and inmemory. I'm finding that very difficult to do because of the fact there is no way to _really_ reset an inmemory database. There is also no easy way I have found to provide a new service provider into a TestServer via its Startup class. I shouldn't have to completely change how ASPNET core configures EF to do this, right? I had hopes that specifying a string to inmemory database would let me force it to use a new implementation, so for instance I tried using a new guid there, but that still didn't work and the id values still stepped on one another.

[SimonCropp]

please re-open this. it is a bug

[smitpatel]

@SimonCropp - The tracking issue is #6872 which is open and in backlog.