A few years ago, I rearchitected a project using Clean Architecture, Entity Framework and MediatR. If you're unfamiliar with CA, freeCodeCamp has a decent explanation. CA isn't suited for every project, but for this product's expected lifetime, it was well received by the devs as we transitioned to a test-driven development workflow.

The product made use of other data sources in addition to SQL, so even though Entity Framework is based on the repository and unit of work patterns, we abstracted our data layer to accommodate unit tests for our use case handlers.

More recently, I joined an existing project where Entity Framework was injected and used directly in the use case handlers.

But should we not abstract our data layer? Let's take into consideration

• Producing an abstraction is an overhead. There's nothing wrong with this as long as the architecture is appropriate for the project.

• Entity Framework is already an abstraction.

• For many projects, it's unlikely the system's storage implementation would change (think of designing a system utilizing a relational database vs a non-relational database).

Limiting the abstractions in a code base is ok as long as

📐 The abstractions in the system are appropriate for the architecture.

💰 Changes to the system are cheap.

🧪 Tests for the system are effective.

And it's in the testing with Entity Framework where the developer experience suffers.

You could implement unit tests with an in-memory provider, but it isn't recommended.

You could use a DbContext as a text fixture, seed it and pass it to your handler, but you will encounter troubling scenarios (like a test always passing because of EF's eager loading feature).

I want to hit this point once more by referring to the .NET Data Community Standup - Entity Framework Core FAQs (11 Jan 2023) when the presenters addressed the question "Why does the in-memory database not work in my tests". To summarize:

• The in-memory database doesn't work for testing because it doesn't support the same features as your production database system.

• The recommendation is to not use the in-memory database for tests. Test with the same system that is in production.

• If you want to swap the data layer within memory for testing, you have to implement an abstraction above Entity Framework.

I'm going to demonstrate how we can follow the FIRST principles of testing (Fast, Independent, Repeatable, Self-Validating, Timely) using Entity Framework and real database systems.

Let's take a look at our system. You can find the source code of the below example on GitHub.

Building the example App

Domain

This will hold our entities and DbContext. The data model should look familiar to anyone who's worked with the dotnet core WebApi project template. Of note is the OnModelCreating where data is generated with a constant seed.

Note: to support EF Migrations in this project, we needed to reference to Microsoft.EntityFrameworkCore.SqlServer. To achieve a clean separation between models and infrastructure in the real world, migrations should be in a separate project.

    public class Forecast
    {
        public int Id { get; set; }
        public DateTime Date { get; set; }
        public int TemperatureC { get; set; }
        public string? Sumary { get; set; }
    }

    public class WeatherContext : DbContext
    {

        public WeatherContext(DbContextOptions<WeatherContext> options) : base(options)
        {

        }

        public DbSet<Forecast> Forecasts { get; set; }

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

            string[] summaries = new[]
            {
                "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
            };

            var rand = new Random(4337);

            var seedData = Enumerable.Range(0, 30).Select(index =>

                new Forecast { Id = index + 1, Date = DateTime.Now.Date.AddDays(index * -1), TemperatureC = rand.Next(0,30), Sumary = summaries[rand.Next(0, summaries.Length)] }
            );

            modelBuilder.Entity<Forecast>().HasData(seedData);
        }

    }

Application

Our application layer will only hold one use case: GetWeatherforecast.

Here are our request and response types:

    public class GetWeatherforecastRequest : IRequest<GetWeatherforecastResponse>
    {
    }

    public class GetWeatherforecastResponse
    {
        public IEnumerable<WeatherForecast>? Forecasts { get; init; }
    }

    public record WeatherForecast(DateOnly Date, int TemperatureC, string? Summary)
    {
        public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);
    }

And here is our use case implementation. It simply returns all records but that's good enough for now.

    internal class GetWeatherforecast : IRequestHandler<GetWeatherforecastRequest, GetWeatherforecastResponse>
    {
        private readonly WeatherContext _context;

        public GetWeatherforecast(WeatherContext context)
        {
            _context = context;
        }               

        public async Task<GetWeatherforecastResponse> Handle(GetWeatherforecastRequest request, CancellationToken cancellationToken)
        {
            return new GetWeatherforecastResponse { 
                Forecasts = await _context.Forecasts
                .Select(x => new WeatherForecast(DateOnly.FromDateTime(x.Date), x.TemperatureC, x.Sumary))
                .ToListAsync()
            };

        }
    }

To make use of MediatR's dependency injection, we'll include an empty type ApplicationReference for the dependency extension to reference.

REST Api

Using dotnet's minimal API feature, we're able to define the WebApi in a single file. Note that we're applying database migrations on startup in this case.

using EFTestingWithSql.Application.Queries.GetWeatherforecast;
using EFTestingWithSql.Application;
using MediatR;
using Microsoft.EntityFrameworkCore;
using EFTestingWithSql.Data;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddDbContext<WeatherContext>(opt => opt.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddMediatR(typeof(ApplicationReference));
var app = builder.Build();

// Apply migrations
using var scope = app.Services.CreateScope();
using var context = scope.ServiceProvider.GetRequiredService<WeatherContext>();
context.Database.Migrate();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.MapGet("/weatherforecast", async (IMediator mediator, ILogger<Program> logger) =>
{
    try
    {
        var result = await mediator.Send(new GetWeatherforecastRequest());
        return result.Forecasts;
    }
    catch (Exception ex)
    {
        logger.LogError(ex, "Failed to fetch forcasts.");
        throw;
    }

})
.WithName("GetWeatherForecast")
.WithOpenApi();

app.Run();

public partial class Program { } // Makes this class public for the console app template.

Running the project

Because migrations are not run on startup, we need to run them manually from the project's root.

dotnet ef database update --project EFTestingWithSql.Domain --startup-project EFTestingWithSql.WebApi

With that success, we should be able to run the project and invoke the /weatherforecast endpoint and move on to our testing.

Implementing tests

Because we're going to be interacting with actual database systems, these will be implemented as integration tests, which are orders of magnitude slower than unit tests.

For the solution

• Add an xUnit test project to test the WebApi

• Add the package Microsoft.AspNetCore.Mvc.Testing to the test project.

• Include a reference to our WebAPI.

A basic test will follow the Arrange-Act-Assert pattern

1. Arrange: Compose a test server with the desired circumstances (essentially the SUT or System Under Test)

2. Act: Perform our operation against the test server.

3. Assert: Verify the outcome against our expectations.

And for this, we'll use a WebApplicationFactory provided by Microsoft.AspNetCore.Mvc.Testing

Customizing WebApplicationFactory

WebApplicationFactory can be customized and injected as a fixture to our tests. The _factory provides several methods (like accessing the IServiceProvider) but we're most interested in CreateClient, which gives us an HttpClient pointing to our test server.

 public class CustomWebApplicationFactory<TProgram> 
        : WebApplicationFactory<TProgram> where TProgram : class
    {
        protected override void ConfigureWebHost(IWebHostBuilder builder)
        {
            // Perform any configuration here that applies to all tests
            // such as replacing an external data store with a fake.
        }
    }

    public class BasicTests
        : IClassFixture<CustomWebApplicationFactory<Program>>
    {
        private readonly CustomWebApplicationFactory<Program> _factory;

        public BasicTests(CustomWebApplicationFactory<Program> factory)
        {
            _factory = factory;
        }

        [Theory]
        [InlineData("/weatherforecast")]
        public async Task BasicTest(string url)
        {            
            // Arrange
            var client = _factory.CreateClient();            

            // Act
            var response = await client.GetAsync(url);

            // Assert
            response.EnsureSuccessStatusCode(); // Status Code 200-299            
        }
    }

But we want to isolate our tests, so we'll make use of WithWebHostBuilder, which lets us further configure the test server. In the below code, CreateMyClient will still produce an HttpClient, but one where we've replaced the DbContext options.

    public class CustomWebApplicationFactory<TProgram> 
        : WebApplicationFactory<TProgram> where TProgram : class
    {
        protected override void ConfigureWebHost(IWebHostBuilder builder)
        {
            // Perform any configuration here that applies to all tests
            // such as replacing an external data store with a fake.
        }

        public HttpClient CreateMyClient()
        {
            return this.WithWebHostBuilder(config =>
            {
                // Perofrm any 
                config.ConfigureTestServices(services =>
                {
                    var dbContextDescriptor = services.SingleOrDefault(d => d.ServiceType ==typeof(DbContextOptions<WeatherContext>));

                    ArgumentNullException.ThrowIfNull(dbContextDescriptor);

                     services.Remove(dbContextDescriptor);

                    services.AddDbContext<WeatherContext>(opt => opt.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=WeatherForecast_test;Trusted_Connection=True;MultipleActiveResultSets=true"));

                });                

            }).CreateClient();
        }
    }

All we need now is a means of producing a single database per test, seeding it and passing the connectionString to CreateMyClient.

Initializing temporary database

Producing temporary databases isn't a complicated problem, and it's one that's already solved.

ThrowawayDb can create disposable databases and is specifically created with integration tests in mind. ThrowawayDatabase implements IDisposable; when it's disposed, the database is automatically deleted.

        internal ThrowawayDatabase CreateThrowawayDb()
        {
            // Instance could be configured through environment variables.           
            // If feasible, we could create the database in this step instead of relying on the WebApi to run migrations.
            return ThrowawayDatabase.FromLocalInstance("(localdb)\\mssqllocaldb", "TEST_"); 

        }

Our CreateMyClient can then take the db as a parameter (and perhaps be more sensibly named).

        public HttpClient CreateClientForDatabase(ThrowawayDatabase db)
        {
            return this.WithWebHostBuilder(config =>
            {
                // Perofrm any 
                config.ConfigureTestServices(services =>
                {
                    var dbContextDescriptor = services.SingleOrDefault(d => d.ServiceType ==typeof(DbContextOptions<WeatherContext>));

                    ArgumentNullException.ThrowIfNull(dbContextDescriptor);

                     services.Remove(dbContextDescriptor);

                    services.AddDbContext<WeatherContext>(opt => opt.UseSqlServer(db.ConnectionString));

                });                

            }).CreateClient();
        }

Now we can update our test to make use of the provisioned database.

        [Theory]
        [InlineData("/weatherforecast")]
        public async Task BasicTest(string url)
        {
            // Arrange
            using var db = _factory.CreateThrowawayDb();
            var client = _factory.CreateClientForDatabase(db);            

            // Act
            var response = await client.GetAsync(url);

            // Assert
            response.EnsureSuccessStatusCode(); // Status Code 200-299            
        }

Note: As long as the throwaway databased is disposed, it will get removed (the using clause should achieve this even in the event of unhandled exceptions), but it's still possible for database removal to fail or to abort disposal.

If you set a breakpoint within the test, hit it at runtime and then stop debugging, it's likely the throwaway databases will remain (with a name like TEST_66be6d488f).

A recommendation is to set the databaseNamePrefix when creating the database and consider a post-test cleanup job to ensure the environment kept clean.

Improving our test fixture

There are a few things we can iterate on

• Having the WebApi automatically run migrations is convenient but not always practical for real-world applications, such as migrations being run by a CICD process. Our test fixture should have the means of performing all steps of getting the database ready.

• We want an easy means of configuring the database for the test so we can arrange the data for the test's purpose (one concept per test).

Let's refactor the shared logic of our factory into a common method.

        private WebApplicationFactory<TProgram> GetFactoryForDatabase(ThrowawayDatabase db) =>
            WithWebHostBuilder(config =>
            {
                config.ConfigureTestServices(services =>
                {
                    var dbContextDescriptor = services.SingleOrDefault(d => d.ServiceType == typeof(DbContextOptions<WeatherContext>));

                    ArgumentNullException.ThrowIfNull(dbContextDescriptor);

                    services.Remove(dbContextDescriptor);

                    services.AddDbContext<WeatherContext>(opt => opt.UseSqlServer(db.ConnectionString));

                });

            });

Our HttpClient method becomes a simple invocation and we can also expose an IServiceProvider with the the configured database, which we'll use later.

        internal HttpClient CreateClientForDatabase(ThrowawayDatabase db) 
            => GetFactoryWithDatabaseConfig(db).CreateClient();

        internal IServiceProvider GetServiceProviderForDatabase(ThrowawayDatabase db)
            => GetFactoryWithDatabaseConfig(db).Services;

Our CreateThrowawayDb has the means of invoking the migrations (or performing any other steps needed). We can drop the automatic migration from the WebApi at this point.

        private WebApplicationFactory<TProgram> GetFactoryForDatabase(ThrowawayDatabase db) =>
            WithWebHostBuilder(config =>
            {                
                config.ConfigureTestServices(services =>
                {
                    var dbContextDescriptor = services.SingleOrDefault(d => d.ServiceType == typeof(DbContextOptions<WeatherContext>));

                    ArgumentNullException.ThrowIfNull(dbContextDescriptor);

                    services.Remove(dbContextDescriptor);

                    services.AddDbContext<WeatherContext>(opt => opt.UseSqlServer(db.ConnectionString));

                });

            });

We're now in a position to use Entity Framework and manipulate our data source for each test

        [Fact]
        public async Task Given_No_Forecasts_Then_Should_Return_Empty_List()
        {
            // Arrange
            using var db = _factory.CreateThrowawayDb();

            using var scope = _factory.GetServiceProviderForDatabase(db).CreateScope();
            using var context = scope.ServiceProvider.GetRequiredService<WeatherContext>();
            var forcasts = await context.Forecasts.ToListAsync();
            context.Forecasts.RemoveRange(forcasts);
            await context.SaveChangesAsync();


            var client = _factory.CreateClientForDatabase(db);

            // Act
            var response = await client.GetAsync("/weatherforecast");

            // Assert
            response.EnsureSuccessStatusCode(); // Status Code 200-299            
            var result = await JsonSerializer.DeserializeAsync<IEnumerable<WeatherForecast>?>(response.Content.ReadAsStream());
            Assert.NotNull(result);
            Assert.Empty(result);
        }

Conclusions

• Reliable unit tests with Entity Framework require the ORM to sit behind an abstraction.

• When testing with Entity Framework, the recommendation is to test with the same system that is in production or as close an approximation as is feasible.

• There are other tools for preparing databases for tests (such as Respawn) but regardless of your toolset and strategy, it's best to follow the FIRST principles. Keep tests independent and repeatable and allow them to be run in parallel.