xUnit测试SQL Server LocalDB健康检查时出现异常求助

背景 #

我有一个基于ASP.NET Core 8.0的REST API，配套xUnit单元测试、集成测试和功能测试集合。

API启动时通过以下扩展方法注册健康检查：

using System;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;

public static IServiceCollection AddHealthCheckServices(
    this IServiceCollection serviceCollection,
    IConfiguration configuration)
{
    ArgumentNullException.ThrowIfNull(serviceCollection);
    ArgumentNullException.ThrowIfNull(configuration);

    serviceCollection.AddHealthChecks()
        .AddSqlServer(
            connectionString: configuration.GetConnectionStringOrThrow(nameof(ConnectionStrings.DatabaseConnection)),
            name: nameof(ConnectionStrings.DatabaseConnection)
        )
        .AddDbContextCheck<MyRestApplicationDbContext>(
            name: nameof(MyRestApplicationDbContext)
        );

    return serviceCollection;
}

其中AddSqlServer方法来自AspNetCore.Diagnostics.HealthChecks 8.0.2版本NuGet包，所有健康检查通过GET /health端点触发。

生产环境的appsettings.json中连接字符串指向SQL Server Express LocalDB：

"ConnectionStrings": {
  "DatabaseConnection": "Application Name='MyRestApplication.Local';Database='MyRestApplication';Server='(LocalDB)\\MSSQLLocalDB';Integrated Security=SSPI;MultipleActiveResultSets=False;Connection Timeout=15;"
}

测试环境通过WebApplicationFactory<Program>配置内存数据库，自定义扩展方法UseInMemoryDatabase替换了DbContext的注册：

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc.Testing;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.DependencyInjection.Extensions;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;

public class TestApplicationFactory : WebApplicationFactory<Program>
{
    protected override void ConfigureWebHost(IWebHostBuilder webHostBuilder)
    {
        webHostBuilder.ConfigureAppConfiguration(configurationBuilder =>
        {
            configurationBuilder
                .AddJsonFile("appsettings.json", optional: false, reloadOnChange: false)
                .AddJsonFile(
                    path: Path.Combine(Directory.GetCurrentDirectory(), "appsettings.test.json"),
                    optional: false,
                    reloadOnChange: false
                )
                .AddUserSecrets<Program>();
        });
        webHostBuilder.ConfigureServices((webHostBuilderContext, serviceCollection) =>
        {
            serviceCollection
                .Configure<ConnectionStrings>(webHostBuilderContext.Configuration.GetSection(nameof(ConnectionStrings)))
                .UseInMemoryDatabase();

            var serviceProvider = serviceCollection.BuildServiceProvider();

            // 创建作用域获取MyRestApplicationDbContext实例
            using var serviceScope = serviceProvider.CreateScope();
            var scopedServiceProvider = serviceScope.ServiceProvider;
            var scopedDbContext = scopedServiceProvider.GetRequiredService<MyRestApplicationDbContext>();
            var scopedLogger = scopedServiceProvider.GetRequiredService<ILogger<TestApplicationFactory>>();

            scopedDbContext.Database.EnsureCreated();

            try
            {
                SeedDbContext(scopedDbContext);
            }
            catch (Exception exception)
            {
                scopedLogger.LogError(
                    exception,
                    $"初始化{typeof(MyRestApplicationDbContext)}类型的DbContext时发生意外异常：{exception}"
                );
            }
        });
    }

    /// <summary>
    /// 向指定的<see cref="MyRestApplicationDbContext"/>填充测试数据
    /// </summary>
    /// <param name="dbContext"></param>
    private static void SeedDbContext(MyRestApplicationDbContext dbContext)
    {
        //dbContext.Sessions.Add(new Session { /* ... */ });
        //dbContext.SaveChanges();
    }
}

public static class IServiceCollectionExtensions
{
    public static IServiceCollection UseInMemoryDatabase(this IServiceCollection serviceCollection)
    {
        ArgumentNullException.ThrowIfNull(serviceCollection);

        // 移除已存在的DbContext注册
        var serviceDescriptor = serviceCollection.SingleOrDefault(
            x => x.ServiceType == typeof(DbContextOptions<MyRestApplicationDbContext>)
        );

        if (serviceDescriptor is not null)
            serviceCollection.Remove(serviceDescriptor);

        // 注册内存数据库替代
        serviceCollection.AddDbContext<MyRestApplicationDbContext>((options, context) =>
        {
            context.UseInMemoryDatabase("MyRestApplication");
        });

        return serviceCollection;
    }
}

问题现象 #

所有测试均通过，唯独调用GET /health端点的测试失败，抛出Microsoft.Data.SqlClient.SqlException：

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot open database "MyRestApplication" requested by the login. The login failed.
Login failed for user 'AzureAD\StevenVolckaert'.
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool, Func`3 accessTokenCallback)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
--- End of stack trace from previous location ---
   at HealthChecks.SqlServer.SqlServerHealthCheck.CheckHealthAsync(HealthCheckContext context, CancellationToken cancellationToken) in /_/src/HealthChecks.SqlServer/SqlServerHealthCheck.cs:line 28
ClientConnectionId:f1289a1e-0e0b-4b5f-b389-d715e7214424
Error Number:4060,State:1,Class:11

其中AzureAD\StevenVolckaert是当前登录的Windows账号。修改appsettings.json中的连接字符串指向可用的SQL Server实例时，测试可通过，说明健康检查确实在尝试连接LocalDB而非内存数据库。

失败的xUnit测试代码如下：

using System.Linq;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Diagnostics.HealthChecks;

public class DatabaseConnectionHealthCheck_CheckHealthAsync : IClassFixture<TestApplicationFactory>
{
    private readonly TestApplicationFactory factory;

    public DatabaseConnectionHealthCheck_CheckHealthAsync(TestApplicationFactory factory)
    {
        this.factory = factory;
    }

    [Fact]
    public async Task ReturnsHealthy()
    {
        // 准备
        var healthCheckService = this.factory.Services.GetRequiredService<HealthCheckService>();

        // 执行
        var healthReport = await healthCheckService.CheckHealthAsync(
            predicate: registration => registration.Name == nameof(ConnectionStrings.DatabaseConnection)
        );

        // 断言
        healthReport.Should().NotBeNull();
        healthReport.Status.Should().Be(HealthStatus.Healthy); // 失败，实际状态为HealthStatus.Unhealthy
    }
}

异常原因 #

• 你注册了两个独立的数据库健康检查：AddSqlServer直接读取配置文件中的LocalDB连接字符串并创建独立的数据库连接；AddDbContextCheck依赖MyRestApplicationDbContext的实例。
• 测试环境中你仅替换了MyRestApplicationDbContext为内存数据库，但**AddSqlServer注册的健康检查并未被修改**，它依然使用原始配置中的LocalDB连接字符串尝试建立连接，而当前Windows账号没有该LocalDB数据库的访问权限，最终导致登录失败。

解决方案 #

提供四种可行方案，可根据需求选择：

方案1：测试环境中移除AddSqlServer健康检查 #

在TestApplicationFactory的ConfigureServices方法中，精准移除指定名称的健康检查注册：

webHostBuilder.ConfigureServices((context, services) =>
{
    // 保留现有配置DbContext和内存数据库的代码...

    // 移除指定名称的健康检查
    var healthCheckRegistrations = services.Where(s => s.ServiceType == typeof(IHealthCheckRegistration)).ToList();
    foreach (var regService in healthCheckRegistrations)
    {
        if (regService.ImplementationInstance is HealthCheckRegistration reg && 
            reg.Name == nameof(ConnectionStrings.DatabaseConnection))
        {
            services.Remove(regService);
            break;
        }
    }

    // 保留后续初始化DbContext和测试数据的代码...
});

方案2：统一使用DbContextCheck替代AddSqlServer #

AddDbContextCheck<MyRestApplicationDbContext>会验证DbContext的连接可用性，测试环境中它会自动使用内存数据库，生产环境则使用配置的SQL Server连接。可以直接移除AddSqlServer健康检查，只保留DbContextCheck，实现生产和测试环境的统一验证逻辑。

方案3：测试环境中替换AddSqlServer为模拟健康检查 #

如果需要保留该健康检查的名称，可在测试环境中将其替换为一个总是返回健康状态的模拟实现：

webHostBuilder.ConfigureServices((context, services) =>
{
    // 保留现有配置DbContext和内存数据库的代码...

    // 移除原有的SqlServer健康检查
    var healthCheckRegistrations = services.Where(s => s.ServiceType == typeof(IHealthCheckRegistration)).ToList();
    foreach (var regService in healthCheckRegistrations)
    {
        if (regService.ImplementationInstance is HealthCheckRegistration reg && 
            reg.Name == nameof(ConnectionStrings.DatabaseConnection))
        {
            services.Remove(regService);
            break;
        }
    }

    // 添加模拟的健康检查
    services.AddHealthChecks()
        .AddCheck(nameof(ConnectionStrings.DatabaseConnection), 
            () => HealthCheckResult.Healthy("测试环境模拟SQL Server健康检查"));

    // 保留后续初始化DbContext和测试数据的代码...
});

方案4：修改测试环境的连接字符串 #

在appsettings.test.json中配置一个不会实际连接的无效字符串（或指向测试可用的SQL Server实例）：

"ConnectionStrings": {
  "DatabaseConnection": "Server=(local);Database=DummyTestDB;Trusted_Connection=True;Connect Timeout=1;"
}

注意：这种方式可能仍会触发连接尝试，只是快速失败，不如直接移除或替换健康检查可靠。

内容的提问来源于stack exchange，提问作者Steven Volckaert