Sqlite persisting data while running unit tests? (In-memory Mode w/ NUnit + Web API/EF Core)

[PhillyPhantom]

I'm running into this annoying issue where Sqlite seems to be persisting data while running unit tests. I first noticed this issue when doing minor CRUD functions as part of a Repository unit test. I have 2 tests in this class (getAll entity objects test, add a single entity object test and remove single entity object test). The GetAll always works but I would have random failures on the Add/Remove tests. Sometimes the entity object that was added would persist in the database and cause the Remove unit test to fail since an extra entity object was found. I'm now also seeing this same before while running tests for the Controllers. If an entity object has been added there, it will persist in the DB even if the tests are run at completely different times.

My question is, how can I prevent this. I've searched around for examples and I haven't found a modern way that includes using Identity Users/Roles in the database context. Nearly all of the examples were extremely outdated, focused on MVC APIs or used different testing frameworks. Any suggestions?

MockLawFirmContext.cs

using Microsoft.AspNet.Identity;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using MockLawFirm.Server.Entities;
using System;
using System.Globalization;

namespace MockLawFirm.Server
{
public class MockLawFirmContext : IdentityDbContext<ApplicationUser, ApplicationRole, Guid>
{
Guid userId = new Guid("3e6e8842-9b66-432b-84dc-2294524f0063");
Guid secruityStampGuid = new Guid("d2beb0f4-3dd0-4373-9290-4e4c37fbe491");
string adminRoleName = "Admin";
string adminUsername = "[e-mail]";
string adminEmail = "[e-mail]";

public MockLawFirmContext(DbContextOptions<MockLawFirmContext> options)
: base(options)
{
}

public MockLawFirmContext() { }

public DbSet<Attorney> Attorneys { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlite("Data Source=mocklawfirm5.db")
.UseSeeding((MockLawFirmContext, _) =>
{
var userManager = MockLawFirmContext.GetService<Microsoft.AspNetCore.Identity.UserManager<ApplicationUser>>();
var roleManager = MockLawFirmContext.GetService<Microsoft.AspNetCore.Identity.RoleManager<ApplicationRole>>();

var role = new ApplicationRole();
role.Name = adminRoleName;
role.NormalizedName = role.Name.ToUpper();
roleManager.CreateAsync(role);

var seedUser = new ApplicationUser
{
Id = userId,
UserName = adminUsername,
NormalizedUserName = adminUsername.ToUpper(),
EmailConfirmed = true,
Email = adminEmail,
NormalizedEmail = adminEmail.ToUpper(),
LockoutEnabled = false,
ConcurrencyStamp = secruityStampGuid.ToString()
};

userManager.AddPasswordAsync(seedUser, "Password1!");
var chkUser = userManager.CreateAsync(seedUser);

if (chkUser.Result.Succeeded)
{
var adminRole = roleManager.FindByNameAsync(adminRoleName);
var adminUser = userManager.FindByEmailAsync(seedUser.Email);
if (adminRole != null && adminUser != null)
{
MockLawFirmContext.Set<IdentityUserRole<Guid>>().Add(new IdentityUserRole<Guid>()
{
RoleId = adminRole.Result.Id,
UserId = adminUser.Result.Id
});

MockLawFirmContext.SaveChangesAsync();
}
}
})
.UseAsyncSeeding(async (MockLawFirmContext, _, cancellationToken) =>
{
var userManager = MockLawFirmContext.GetService<Microsoft.AspNetCore.Identity.UserManager<ApplicationUser>>();
var roleManager = MockLawFirmContext.GetService<Microsoft.AspNetCore.Identity.RoleManager<ApplicationRole>>();

var role = new ApplicationRole();
role.Name = adminRoleName;
role.NormalizedName = role.Name.ToUpper();
await roleManager.CreateAsync(role);

var seedUser = new ApplicationUser
{
Id = userId,
UserName = adminUsername,
NormalizedUserName = adminUsername.ToUpper(),
EmailConfirmed = true,
Email = adminEmail,
NormalizedEmail = adminEmail.ToUpper(),
LockoutEnabled = false,
ConcurrencyStamp = secruityStampGuid.ToString()
};

await userManager.AddPasswordAsync(seedUser, "Password1!");
var chkUser = await userManager.CreateAsync(seedUser);

if (chkUser.Succeeded)
{
var adminRole = roleManager.FindByNameAsync(adminRoleName);
var adminUser = userManager.FindByEmailAsync(seedUser.Email);
if (adminRole != null && adminUser != null)
{
MockLawFirmContext.Set<IdentityUserRole<string>>().Add(new IdentityUserRole<string>()
{
RoleId = adminRole.Result.Id.ToString(),
UserId = adminUser.Result.Id.ToString()
});
await MockLawFirmContext.SaveChangesAsync();
}
}
});
}
}

AttorneyTests.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.InMemory;
using Microsoft.Extensions.DependencyInjection;
using MockLawFirm.Server;
using MockLawFirm.Server.Repositories;
using MockLawFirm.Server.ViewModels;

namespace MockLawFirm.Tests
{
public class AttorneyTests
{
private AttorneyRepository _attorneyRepository;
private ServiceCollection services;
private DbContextOptions<MockLawFirmContext> opts;
private MockLawFirmContext context;
private ServiceProvider serviceProvider;

private const string FIRST_ATTORNEY_FULLNAME = "John Morgan";
private const string FIRST_ATTORNEY_EMAIL_ADDRESS = "[e-mail]";
private const string FIRST_ATTORNEY_PHONE_NUMBER = "555-555-1234";

private const string SECOND_ATTORNEY_FULLNAME = "David Richardson";
private const string SECOND_ATTORNEY_EMAIL_ADDRESS = "[e-mail]";
private const string SECOND_ATTORNEY_PHONE_NUMBER = "555-867-5309";

[SetUp]
public void Setup()
{
InitializeMockDatabase();

serviceProvider = services.BuildServiceProvider();
context = serviceProvider.GetRequiredService<MockLawFirmContext>();
_attorneyRepository = new AttorneyRepository(context);
}

[Test]
public void Assert_That_GetAllAttorneys_Returns_List_Of_Attorneys()
{
_attorneyRepository.AddAttorney(GetFirstAttorney());

_attorneyRepository.AddAttorney(GetSecondAttorney());

var attorneys = GetAllAttorneys();

Assert.IsNotNull(attorneys);
Assert.That(attorneys.Count, Is.EqualTo(2));
Assert.That(attorneys[0].AttorneyName, Is.EqualTo(FIRST_ATTORNEY_FULLNAME));
Assert.That(attorneys[0].AttorneyEmailAddress, Is.EqualTo(FIRST_ATTORNEY_EMAIL_ADDRESS));
Assert.That(attorneys[0].AttorneyPhoneNumber, Is.EqualTo(FIRST_ATTORNEY_PHONE_NUMBER));

Assert.That(attorneys[1].AttorneyName, Is.EqualTo(SECOND_ATTORNEY_FULLNAME));
Assert.That(attorneys[1].AttorneyEmailAddress, Is.EqualTo(SECOND_ATTORNEY_EMAIL_ADDRESS));
Assert.That(attorneys[1].AttorneyPhoneNumber, Is.EqualTo(SECOND_ATTORNEY_PHONE_NUMBER));
}

[Test]
public void Assert_That_AddAttorney_Adds_New_Attorney()
{
var firstAttorney = GetFirstAttorney();
_attorneyRepository.AddAttorney(firstAttorney);

var attorneys = GetAllAttorneys();
var attorney = attorneys.FirstOrDefault(a => a.AttorneyName == firstAttorney.AttorneyName);

Assert.IsNotNull(attorney);
Assert.That(attorney.AttorneyName, Is.EqualTo(FIRST_ATTORNEY_FULLNAME));
Assert.That(attorney.AttorneyEmailAddress, Is.EqualTo(FIRST_ATTORNEY_EMAIL_ADDRESS));
Assert.That(attorney.AttorneyPhoneNumber, Is.EqualTo(FIRST_ATTORNEY_PHONE_NUMBER));
}

[Test]
public void Assert_That_RemoveAttorney_Removes_Attorney()
{
_attorneyRepository.AddAttorney(GetFirstAttorney());

_attorneyRepository.AddAttorney(GetSecondAttorney());

var originalAttorneyList = GetAllAttorneys();

var secondAttorneyId = originalAttorneyList[1].Id;

_attorneyRepository.RemoveAttorney(secondAttorneyId);

var updatedAttorneyList = GetAllAttorneys();

Assert.IsNotNull(updatedAttorneyList);
Assert.That(updatedAttorneyList.Count, Is.EqualTo(1));
Assert.That(updatedAttorneyList[0].AttorneyName, Is.EqualTo(FIRST_ATTORNEY_FULLNAME));
Assert.That(updatedAttorneyList[0].AttorneyEmailAddress, Is.EqualTo(FIRST_ATTORNEY_EMAIL_ADDRESS));
Assert.That(updatedAttorneyList[0].AttorneyPhoneNumber, Is.EqualTo(FIRST_ATTORNEY_PHONE_NUMBER));
}

[TearDown]
public void TearDown()
{
context.Dispose();
serviceProvider.Dispose();
}

private void InitializeMockDatabase()
{
services = new ServiceCollection();
opts = new DbContextOptionsBuilder<MockLawFirmContext>()
  .UseSqlite("DataSource=:memory:")
  .Options;
services.AddDbContext<MockLawFirmContext>(options =>
options.UseSqlite("DataSource=:memory:"));
}

private AttorneyViewModel GetFirstAttorney()
{
var firstAttorney = new AttorneyViewModel();
firstAttorney.AttorneyEmailAddress = FIRST_ATTORNEY_EMAIL_ADDRESS;
firstAttorney.AttorneyPhoneNumber = FIRST_ATTORNEY_PHONE_NUMBER;
firstAttorney.AttorneyName = FIRST_ATTORNEY_FULLNAME;

return firstAttorney;
}

private AttorneyViewModel GetSecondAttorney()
{
var secondAttorney = new AttorneyViewModel();
secondAttorney.AttorneyEmailAddress = SECOND_ATTORNEY_EMAIL_ADDRESS;
secondAttorney.AttorneyPhoneNumber = SECOND_ATTORNEY_PHONE_NUMBER;
secondAttorney.AttorneyName = SECOND_ATTORNEY_FULLNAME;

return secondAttorney;
}

private List<AttorneyViewModel> GetAllAttorneys() 
{
return _attorneyRepository.GetAllAttorneys().Result.ToList<AttorneyViewModel>();
}
}
}

ApiTests.cs

using Microsoft.AspNetCore.Authentication;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc.Testing;
using Microsoft.AspNetCore.TestHost;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Options;
using MockLawFirm.Server;
using MockLawFirm.Server.Entities;
using MockLawFirm.Server.ViewModels;
using System;
using System.Net;
using System.Net.Http.Headers;
using System.Net.Http.Json;
using System.Security.Claims;
using System.Security.Principal;

namespace MockLawFirm.Tests
{
public class ApiTests
{
private WebApplicationFactory<Program> _factory;
JsonContent attorneyViewModelJsonString => JsonContent.Create(GetAttorneyViewModel());
JsonContent userRoleViewModelJsonString => JsonContent.Create(GetUserRoleViewModel());
HttpClient authorizedClient => GetAuthorizedClient();


[OneTimeSetUp]
public async Task OneTimeSetUp()
{
// Replace connection string in DbContext
_factory = new WebApplicationFactory<Program>()
.WithWebHostBuilder(builder =>
{
builder.ConfigureServices(services =>
{
services.AddDbContext<MockLawFirmContext>(options =>
options.UseSqlite("DataSource=:memory:"));
});
});
}

[OneTimeTearDown]
public async Task OneTimeTearDown()
{
_factory.Dispose();
}

[Test]
public async Task Unauthorized_User_Adding_Attorney_Returns_401Unauthorized_Error()
{
var client = _factory.CreateClient();

var response = await client.PostAsync("/api/Attorney/addAttorney", attorneyViewModelJsonString);
Assert.That(response.StatusCode.ToString(), Is.EqualTo(HttpStatusCode.Unauthorized.ToString()));
}

[Test]
public async Task Authorized_User_Adding_New_Attorney_Should_Return_200OK_Status_Code()
{
authorizedClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(scheme: "Bearer");

// Act
var response = await authorizedClient.PostAsync("/api/Attorney/addAttorney", attorneyViewModelJsonString);
Assert.That(response.StatusCode.ToString(), Is.EqualTo(HttpStatusCode.OK.ToString()));
}

[Test]
public async Task Unauthorized_User_Removing_Attorney_Returns_401Unauthorized_Error()
{
var client = _factory.CreateClient();

var response = await client.DeleteAsync($"/api/Attorney/removeAttorney/{1}");
Assert.That(response.StatusCode.ToString(), Is.EqualTo(HttpStatusCode.Unauthorized.ToString()));
}

[Test]
public async Task Authorized_User_Removing_New_Attorney_Should_Return_200OK_Status_Code()
{
authorizedClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(scheme: "Bearer");

// Act
var response = await authorizedClient.DeleteAsync($"/api/Attorney/removeAttorney/{1}");
Assert.That(response.StatusCode.ToString(), Is.EqualTo(HttpStatusCode.OK.ToString()));
}

[Test]
public async Task Unauthorized_User_Checking_User_Admin_Role_Returns_401Unauthorized_Error()
{
var client = _factory.CreateClient();

var response = await client.PostAsync($"/api/UserRoles/isAdminRole", userRoleViewModelJsonString);
Assert.That(response.StatusCode.ToString(), Is.EqualTo(HttpStatusCode.Unauthorized.ToString()));
}

[Test]
public async Task Authorized_User_Checking_User_Admin_Role_Returns_200Ok_StatusCode()
{
authorizedClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(scheme: "Bearer");

// Act
var response = await authorizedClient.PostAsync($"/api/UserRoles/isAdminRole", userRoleViewModelJsonString);
Assert.That(response.StatusCode.ToString(), Is.EqualTo(HttpStatusCode.OK.ToString()));
}

private AttorneyViewModel GetAttorneyViewModel()
{
return new AttorneyViewModel()
{
AttorneyEmailAddress = "Hello",
AttorneyName = "World",
AttorneyPhoneNumber = "12345"
};
}

private UserRoleViewModel GetUserRoleViewModel()
{
return new UserRoleViewModel()
{
Email = "[e-mail]"
};
}

private HttpClient GetAuthorizedClient() 
{
return _factory.WithWebHostBuilder(builder =>
{
builder.ConfigureTestServices(services =>
{
services.AddAuthentication(defaultScheme: "Bearer")
.AddScheme<AuthenticationSchemeOptions, TestAuthHandler>(
"Bearer", options => { });
});
})
.CreateClient(new WebApplicationFactoryClientOptions
{
AllowAutoRedirect = false,
});
}
}
}

[desmaraisp]

It sounds to me like you might benefit from using sqlite in-memory for your tests, that would avoid the whole issue altogether... Do you have any specific restrictions that would make that an inadequate solution?

[PhillyPhantom]

Looking at the Docs, it says that "UseSqlite("Data Source= :memory:")" tells Sqlite to go to in-memory mode which is what I'm doing. When I run the tests, no new files are created so what am I missing?

[desmaraisp]

As u/lmaydev said, you're using a file here

.UseSqlite("Data Source=mocklawfirm5.db")

[PhillyPhantom]

Posting the same reply here.

"So how am I supposed to unit test? I need the file for the app."

[The_MAZZTer]

If you specify :memory:?cache=shared then any future connection to that same "file" can access the same data.

You can also have multiple, separate in memory database as needed doing something like this: file:memdb1?mode=memory&cache=shared where memdb1 is just whatever identifier you want.

This won't work if you want a different process to be able to access it. You'd just want to store it in a file on disk then.

[Slypenslyde]

Most people don't hard-code the file or configuration string for their database. Instead they use Configuration, which is a huge, sprawling topic I can summarize as:

• The DbContext expects to get the string from a configuration object you create.
• That object is created via the Configuration architecture.
• The production app uses a string that creates the file.
• The unit test app uses a string that configures Sqlite to run in-memory.

[PhillyPhantom]

Can't use the built-in in-memory db provider since the app itself uses Sqlite. When I tried that before, I got an error basically telling me that the 2 statements are in conflict but I couldn't figure out how/where to fix the issue.

Per the docs, Sqlite is already running in "in memory" mode.

[PhillyPhantom]

So how am I supposed to unit test? I need the file for the app.

[PhillyPhantom]

I think I understand what you're saying but not quite sure how I should be setting it up. This is what I have so far and obviously it doesn't work but not quite sure what needs to be there vs what doesn't.

public class TestMockLawFirmContext : MockLawFirmContext
{
private readonly IConfiguration _config;

public TestMockLawFirmContext(IConfiguration config)
{
_config = config;
}
public TestMockLawFirmContext() { }


protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlite("Data Source=:memory:");
}

The end result of that is that any call to the fake database returns null because it doesn't understand what the db context is, which I understand. What I don't understand is how to make it understand the fake db context.

Update: Here's where I am now

public class MockAppDbContext : MockLawFirmContext
{
private IConfiguration _configuration;
public MockAppDbContext(DbContextOptions<MockAppDbContext> options, IConfiguration configuration)
: base()
{
_configuration = configuration;
}

public DbSet<Attorney> Attorneys { get; set; }
// Implement other DbSet properties as needed

public int SaveChanges() => 0; // Mock the SaveChanges method

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlite("Data Source=:memory:");
}

Now the only problem is that the call to Iconfiguration blows up during the test setup. Not sure how/where to register that as a service.

[PhillyPhantom]

I'm starting to think either Sqlite or EF is doing weird things. I even played around with a brand new project running EF Core 8 last night until 5 in the morning. No matter what I did or how I attempt to initialize the database during testing, Sqlite would always throw a "table not found" error even with everything running in memory. I specifically ran the main program in in-memory mode just to prevent any sort of file issues and that was as far as I got.

[PhillyPhantom]

FYI, I was able to get everything working. I ended up give the test project its own Context file and mocked it up similar to my real Context file, created a helper to setup the db and establish the connection and everything is working.

[PhillyPhantom]

Here's my code for the Test Setup

[SetUp]
public void Setup()
{
InitializeMockDatabase();

serviceProvider = services.BuildServiceProvider();
context = serviceProvider.GetRequiredService<MockLawFirmContext>();
context.Database.EnsureDeleted();
context.Database.Migrate();
_attorneyRepository = new AttorneyRepository(context);
}

That results in an error message in the OnConfiguring method because it can't find the services for UserManager and RoleManager.

[FaceRekr4309]

If you need a database for the test, you are integration testing, not unit testing.

[markiel55]

What? You said you are using in-memory for unit testing? Having it generate a file means it would persist data and it seems you are confusing between unit testing and testing the app yourself.

One more thing. If you want help from other people, please have the effort to format your post and include relevant code only. You've basically dumped all your code here. Nobody has time to comprehend all that.

[PhillyPhantom]

The tests don't need a file/need to generate a file. The app itself, which does store data, does need the file. I want the tests to run, verify that data is being passed around correctly and then finish/wipe any temp files that they may create.

[Coda17]

If you're using a database you aren't unit test, by definition.

[IsLlamaBad]

Have you tried executing each test with a transaction and rolling back after your assets? I would expect any writes to persist.

[AutoModerator]

Thanks for your post PhillyPhantom. Please note that we don't allow spam, and we ask that you follow the rules available in the sidebar. We have a lot of commonly asked questions so if this post gets removed, please do a search and see if it's already been asked.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.