Integration Tests with Entity Framework and SQLite in .NET Core

I have recently completed a new .NET core API and created a SQLite in memory database so I could do effective integration testing. Given how complex some of the linq we write is mocking it out and just saying yeah its going to return that seems pretty unreliable to me so I built an in memory database and wrote tests against it. So far we have written 120 test that create an in memory database, perform the test and then do a tear down. I takes about 2 minutes to complete everything which I think is pretty good all things considered. The snippet below shows how I setup the creation of the DbContextOptions, this is important because any data context created using this will reference the same in memory database. That's pretty cool if you think about it. Note that I am creating and disposing of one such data context in the setup.

        private readonly DbContextOptions _dbOptions;

        public TestSetup()
        {         
            var optionsBuilder = new DbContextOptionsBuilder();
            optionsBuilder.UseSqlite(new SqliteConnection("DataSource=:memory:"));           
            _dbOptions = optionsBuilder.Options;

             using (var setupContext = new DataContext(_dbOptions))
            {
                var bd = new BaseData(setupContext);
                setupContext.Database.GetDbConnection().Open();
                setupContext.Database.EnsureCreated();
                bd.Initialise();
            }
        }

        public DataContext GetDataContext()
        {
            return new DataContext(_dbOptions);
        }

The bd.Initialize() method simply goes off and adds entities to the dbcontext and does a save. A couple of snippets from base data are

  public void Initialise()
        {
            InitialseAudit();
            InitialiseSecurity();
            InitialiseCategories();
            InitialiseGoals();
            _dbContext.SaveChanges();
        }   

  private void InitialiseCategories()
        {
            var workCategory = new Category { Id = (int)CategoryEnum.Work, Name = "Work" };
            _dbContext.Categories.Add(workCategory);
            var homeCategory = new Category { Id = (int)CategoryEnum.Home, Name = "Home" };
            _dbContext.Categories.Add(homeCategory);
        }

I have also used enums for the primary keys that I am interested in. This way they are really easy to reference and reuse in teh system and the tests. Usage would be something like the following, this is just as an rudimentary example of saving a goal to a list for a user.

        public GoalServiceShould()
        {       
            var testSetup = new TestSetup();    


            var dataContext = testSetup.GetDataContext();
            _uow = new UnitOfWork(dataContext);
            _goalRepository = new Repository(dataContext);
            
            _goalService = new GoalService(_uow, _goalRepository);
        }

        [Fact]
        public async void AddNewGoal()
        {
            var stamp = DateTime.Now;
            var goal = new GoalDto
            {
                UserId = 1,
                Note = $"New Note {stamp}",
                DueDate = new DateTime(2017, 1, 6),
                Urgent = true,
                ModifiedById = 1
            };
            var returnedGoal = (await _goalService.SaveGoal(goal)).Content;
            returnedGoal.Id.ShouldBeGreaterThan(0);

            var savedGoal = await _goalRepository.GetAsync(p => p.Id == returnedGoal.Id);
            savedGoal.Note.ShouldBe($"New Note {stamp}");           
        }

I could share a full example if anybody is interested, send me a message or leave a comment and I will get back to you. I am using Shouldly for the asserts in the test.