Prevent SQL Server Connection in ASP.Net Integration test

I have a standard ASP.Net Web API Project with a DB setup that looks something like this:

Startup.cs

services.AddDbContext<ProjectDbContext>(opts =>
    opts.UseSqlServer(Configuration.GetConnectionString("ProjectDb")));

This works as intended.

I also have an integration test setup, where I attempt to replace the SQL Server connection with an In-Memory one, for a clean slate every test run:

ProjectWebApplicationFactory.cs

public class ProjectWebApplicationFactory : WebApplicationFactory<Startup> {
    protected override void ConfigureWebHost(IWebHostBuilder builder) {
        builder.ConfigureServices(services => {
            var prodDb = services.SingleOrDefault(s => s.ServiceType == typeof(ProjectDbContext));
            services.Remove(prodDb);
            services.AddDbContext<ProjectDbContext>(opts => {
                opts.UseInMemoryDatabase(Guid.NewGuid().ToString());
            });
    }
}

This again works correctly when running locally. However, when pushing this code to a build / test server it begins to fail. The initial SQL Server DB Context attempts to connect to a DB prior to getting replaced with the in memory version. Since the server is available locally, but not on the test server, this causes all integration tests to fail before even being able to run.

What is the proper way to prevent the SQL Server Context from attempting to connect while running an integration test?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The issue was a typo in ProjectWebApplicationFactory.cs

This line:

var prodDb = services.SingleOrDefault(s => s.ServiceType == typeof(ProjectDbContext));

Should have been:

var prodDb = services.SingleOrDefault(s => s.ServiceType == typeof(DbContextOptions<ProjectDbContext>));

The SQL Sever DB was still being used when running the integration test. This caused it not to fail not on startup, but when endpoints were actually called.

Also related, the in memory database should not be created with a random Guid string, but instead a static value, e.g.

opts.UseInMemoryDatabase("ProjectDb");

Method 2

You are integration testing, but not with the connection string in your project. This means you have your API set up with your production database? If so, don’t do that.

The way I handle this is with a couple of techniques, all designed to invert dependencies.

1. Create an interface on top of the class that has the persistence code. As you don’t likely have a class, make that as well and move the code over
2. Create mocks or stubs or fakes for your interface for unit testing
3. Swap out implementations using IoC containers
4. Move the connection string loading to instantiation of the persistence class. This can also be done with an IoC container

This gives you flexibility to integration test wherever you want and avoid embedding your production strings in your applications, if that is what you have done.

In this case, you are following some patterns I see a lot, but I find this blending of concerns ends in problems when you don’t understand the nature of the problem.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0