in-memory DB not supporting DefaultValue mapping in unit testing

Question

Please advise how to set default value mapping for a property while writing in-memory unit test.

Database: SqlServer Repository method -

public async Task<long> CreatePerson(CreatePersonRequest person)
        {
            try
            {
                Person newPerson = new Person
                {
            // few properties here  
                };
                _context.Person.Add(newPerson);
                await _context.SaveChangesAsync(); // Getting error on this line (Refer error below for more details)
        }
         }

Error:

Microsoft.EntityFrameworkCore.DbUpdateException: 'Required properties '{'IsActive'}' are missing for the instance of entity type 'Person'. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the entity key value.'

Note: This is causing an issue only in unit testing.

property causing problem is defined as follows in dbcontext, modelBuilder along with other properties

modelBuilder.Entity<Person>(entity =>
        {
entity.Property(e => e.IsActive)
                .IsRequired()
                .HasDefaultValueSql("((1))")
                .HasColumnName("Is_Active");
    });


Test case

 public PersonRepositoryTest()
        {
            DbContextOptions contextOptions = new DbContextOptionsBuilder<DBContext>()
                .UseInMemoryDatabase("DBContextTest")
                .ConfigureWarnings(b => b.Ignore(InMemoryEventId.TransactionIgnoredWarning))
                .Options;

            IConfiguration configuration = new Mock<IConfiguration>().Object;

            _context = new DBContext(configuration, contextOptions);
            _it = new PersonRepository(_context, _identityProvider, _lookupRepository);

    }



   [Fact]
        public async void Should_Create_Person()
        {
            CreatePersonRequest request = _fixture.Create<CreatePersonRequest>();
            long id = await _it.CreatePerson(request);
            Assert.True(id > 0);
        }

Answer 1

This scenario falls more into an Integration Testing scenario, or an end-to-end test where you would ideally use a fresh or known state database to run through scenarios end to end. You don't need to Unit Test the fact that a DbContext creates a entity or that the database actually assigns a new PK.

The purpose of a Repository pattern is to serve as a boundary to allow business logic to be more easily tested. Introducing a Repository gives you something to substitute with a Mock. For example, rather than trying to test that the Repository, and EF by extension does what it should, you would be testing code that results in a call to the Repository. (or doesn't)

var mockRepository = new Mock<IPersonRepository>();
mockRepository.Setup(x => x.CreatePerson(It.IsAny<CreatePersonRequest>())
    .Verify(person => 
    { // Here you can add inspection logic that the CreatePersonRequest has the details you expect the code under test to pass...
    });

var serviceUnderTest = new Service { PersonRepository = mockRepository.Object };
serviceUnderTest.DoWhatYouDo();

mockRepository.Verify(x => x.CreatePerson(It.IsAny<CreatePersonRequest>(), Times.Once);

As a very basic example. The goal is to test the business logic which Repositories do not participate in other than to serve low level data actions. The mocked repository captures expected calls, reports on unexpected calls, and simulates potential scenarios like if you want to assert behaviour if the Repository throws an exception.

When it comes to end-to-end testing then you can substitute the database with an in-memory option, but personally I recommend using the same database engine as is used in production since there are provider-specific differences between many database providers that tests will get tripped up by, or miss. Integration tests commonly take a fair bit longer to run and would be triggered less often than unit tests which should be run regularly during development and completely run prior to any code commits.