How to auto increment a column using an Entity Framework core in memory database?

Question

I am using an in memory EF Core database like this:

            var options = new DbContextOptionsBuilder<DmpContext>()
            .UseInMemoryDatabase("test")
            .Options;

        var context = new CustomContext(options);

I am adding some random data using Bogus.

            var resources = new Faker<Resource>()
            .RuleFor(x => x.Name, x => x.Name.FullName())
            .Generate(20);

In this resources collection all the ids are still zero.

When I execute the AddRange I would expect the ids to be generated:

                context.Resources.AddRange(resources);
                context.SaveChangesAsync();

But they are not generated.

System.AggregateException : One or more errors occurred. (The instance of entity type 'Resource' cannot be tracked because another instance with the same key value for {'ResourceId'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached.

So I thought, then let me just set them and make an auto increment mechanism myself. That worked.

However when I then try and test my Add methods (these methods insert records). I am not able to set the IDs in these methods, since the ID is not even in the interface. Again I would expect EF to auto generate the ID but it tries to insert ID 1 (which is already taken, because I already have generated ID 1 until 20).

Does anyone know how to tell an EF core in memory database to always auto increment it's keys?

Answer 1

In the in-memory database, EF can't detect the identity property of an entity, I have overridden

InMemoryIntegerValueGenerator

class for handling this problem. The below code shows how to do that.

public abstract class BaseEntity
{
    public long ID { get; set; }
}

public class RoleResource : BaseEntity
{
    public long RoleID { get; set; }
    public long ResourceID { get; set; }
}

public class CASDbContext : DbContext
{
    public CASDbContext(DbContextOptions options) : base(options) { }
    public DbSet<RoleResource> RoleResources { get; set; }
}


public class CustomInMemoryIntegerValueGenerator<TEntity> 
: InMemoryIntegerValueGenerator<long> where TEntity : BaseEntity
{
    private long _current;
    public override bool GeneratesTemporaryValues => false;
    public override long Next(EntityEntry entry)
    {
        long result;
        if (entry.Properties.Any(x => x.Metadata.ValueGenerated == ValueGenerated.OnAdd))
        {
            _current = entry.Context.Set<TEntity>()
                                    .Select(x => x.ID)
                                    .DefaultIfEmpty(0)
                                    .Max(x => x);
            var res = Interlocked.Increment(ref _current);
            result = res;
        }
        else
            result = base.Next(entry);
        return result;
    }
}
public class InMemoryDbContext : CASDbContext
{
    public InMemoryDbContext(DbContextOptions options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<RoleResource>(rr =>
        {
            rr.HasKey(e => e.ID);
            rr.Property(e => e.ID)
           .HasValueGenerator<CustomInMemoryIntegerValueGenerator<RoleResource>>();
        });
    }
}

    [TestInitialize]
    public void Setup()
    {
        srvCollection.AddScoped<SeedData>();
        srvCollection.AddTransient<CASDbContext, InMemoryDbContext>();
        srvCollection.AddDbContext<InMemoryDbContext>(x => 
          x.UseInMemoryDatabase("In-Memory")).AddEntityFrameworkInMemoryDatabase();

        _serviceProvider = srvCollection.BuildServiceProvider();

    }
/* The packages version
<PackageReference Include="Microsoft.NET.Test.Sdk" Version="16.5.0" />
<PackageReference Include="MSTest.TestAdapter" Version="2.1.0" />
<PackageReference Include="MSTest.TestFramework" Version="2.1.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.2.6" />
<PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="2.2.6" />
*/