仓储模式下SQL存储过程调用的单元测试与Mock实现咨询

针对你的问题，我来一步步拆解如何为这个LoadRepository类编写单元测试和Mock，同时聊聊它的可测试性问题：

一、先说说当前代码的可测试性 #

你的代码直接依赖EF Core的DbSet.FromSql方法调用原生SQL和存储过程，这确实会给单元测试带来挑战——因为DbSet是EF的具体实现类，而非抽象接口，默认情况下用普通的Moq很难Mock它的FromSql方法。不过别担心，我们有两种解决思路：直接Mock EF Core的DbSet，或者重构代码提升可测试性。

二、方案1：用Moq.EntityFrameworkCore直接Mock DbSet #

如果你不想立刻重构代码，可以借助Moq.EntityFrameworkCore这个库，它扩展了Moq的能力，支持MockDbSet的FromSql等EF Core特有的方法。

步骤1：安装必要的NuGet包 #

首先在测试项目中安装这些包：

Install-Package Moq
Install-Package Moq.EntityFrameworkCore
Install-Package xunit  # 或者你常用的测试框架，比如NUnit

步骤2：编写单元测试示例 #

下面是针对两个方法的测试代码：

using Moq;
using Moq.EntityFrameworkCore;
using Xunit;
using System.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

public class LoadRepositoryTests
{
    private readonly Mock<DbContext> _mockDbContext;
    private readonly Mock<IUSCanadaCityStateRepository> _mockCityStateRepo;
    private readonly LoadRepository _targetRepo;

    // 构造函数初始化Mock对象和测试目标
    public LoadRepositoryTests()
    {
        _mockDbContext = new Mock<DbContext>();
        _mockCityStateRepo = new Mock<IUSCanadaCityStateRepository>();
        _targetRepo = new LoadRepository(_mockDbContext.Object, _mockCityStateRepo.Object);
    }

    [Fact]
    public void GetPickUpLocations_WhenCalled_ReturnsCorrectLocationCount()
    {
        // Arrange
        int testLoadId = 123;
        // 构造假的Load数据，模拟查询返回的结果
        var fakeLoads = new List<Load>
        {
            new Load { LoadLocations = new List<LoadLocation> { new(), new(), new() } } // 模拟3个取货点
        };
        // Mock DbSet<Load>
        var mockDbSet = new Mock<DbSet<Load>>();
        // 设置FromSql的返回值为假数据集合
        mockDbSet.Setup(db => db.FromSql(It.IsAny<string>(), It.IsAny<object[]>()))
                 .ReturnsDbSet(fakeLoads);
        // 让Mock的DbContext返回这个Mock的DbSet
        _mockDbContext.Setup(ctx => ctx.Set<Load>()).Returns(mockDbSet.Object);

        // Act
        int result = _targetRepo.GetPickUpLocations(testLoadId);

        // Assert
        Assert.Equal(3, result);
        // 验证FromSql是否被正确调用，参数是否匹配
        mockDbSet.Verify(db => db.FromSql(
            "SELECT COUNT(LocationID) as locationCount FROM vPickupLoc WHERE vPickupLoc.LoadID = @p0",
            testLoadId), Times.Once);
    }

    [Fact]
    public void UpdateCarrierInfo_WhenCalled_ReturnsUpdatedInfo()
    {
        // Arrange
        var testCarrierInfo = new LoadCarrierInfo
        {
            LoadId = 456,
            Dispatcher = "John Doe",
            AssistingPortalId = 789,
            AssistingEmployeeId = "emp_123"
        };
        // 模拟存储过程返回的结果
        var fakeReturnedInfo = new LoadCarrierInfo { LoadId = testCarrierInfo.LoadId };
        var mockDbSet = new Mock<DbSet<Load>>();
        // 设置FromSql返回一个空的Load集合，因为方法里会通过Select映射到LoadCarrierInfo
        mockDbSet.Setup(db => db.FromSql(It.IsAny<string>(), It.IsAny<object[]>()))
                 .ReturnsDbSet(new List<Load> { new Load() })
                 .Returns(() => new List<Load>().AsQueryable());
        _mockDbContext.Setup(ctx => ctx.Set<Load>()).Returns(mockDbSet.Object);

        // Act
        LoadCarrierInfo result = _targetRepo.UpdateCarrierInfo(testCarrierInfo);

        // Assert
        Assert.NotNull(result);
        // 验证存储过程调用的参数是否完全匹配
        mockDbSet.Verify(db => db.FromSql(
            "UpdatePickCarrier @CarrierID,@LoadID, @Primary,@AssistingPortalID,@AssistingEmployeeID",
            It.Is<SqlParameter>(p => p.ParameterName == "LoadID" && (int)p.Value == testCarrierInfo.LoadId),
            It.Is<SqlParameter>(p => p.ParameterName == "Dispatcher" && (string)p.Value == testCarrierInfo.Dispatcher),
            It.Is<SqlParameter>(p => p.ParameterName == "EmployeeID" && (int)p.Value == 1),
            It.Is<SqlParameter>(p => p.ParameterName == "AssistingPortalID" && (int)p.Value == testCarrierInfo.AssistingPortalId),
            It.Is<SqlParameter>(p => p.ParameterName == "AssistingEmployeeID" && (string)p.Value == testCarrierInfo.AssistingEmployeeId)), 
            Times.Once);
    }
}

三、方案2：重构代码提升长期可测试性 #

如果你的项目中有大量类似的原生SQL调用，更推荐重构代码——把原生SQL/存储过程的操作封装到专门的抽象服务中，这样Repository就不用直接依赖EF Core的细节，单元测试会更简洁，也符合单一职责原则。

步骤1：创建SQL操作的抽象接口和实现 #

// 定义抽象接口
public interface ILoadSqlQueryService
{
    int GetPickUpLocationCount(int loadId);
    LoadCarrierInfo UpdateCarrierInfo(LoadCarrierInfo loadCarrierInfo);
}

// 实现接口，封装原生SQL调用
public class LoadSqlQueryService : ILoadSqlQueryService
{
    private readonly DbContext _dbContext;

    public LoadSqlQueryService(DbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public int GetPickUpLocationCount(int loadId)
    {
        var count = _dbContext.Set<Load>().FromSql(
            "SELECT COUNT(LocationID) as locationCount FROM vPickupLoc WHERE vPickupLoc.LoadID = @p0", 
            loadId).ToArray();
        return count[0].LoadLocations.Count;
    }

    public LoadCarrierInfo UpdateCarrierInfo(LoadCarrierInfo loadCarrierInfo)
    {
        return _dbContext.Set<Load>().FromSql(
            "UpdatePickCarrier @CarrierID,@LoadID, @Primary,@AssistingPortalID,@AssistingEmployeeID", 
            new SqlParameter("LoadID", SqlDbType.Int) { Value = loadCarrierInfo.LoadId }, 
            new SqlParameter("Dispatcher", SqlDbType.VarChar) { Value = loadCarrierInfo.Dispatcher }, 
            new SqlParameter("EmployeeID", SqlDbType.Int) { Value = 1 }, 
            new SqlParameter("AssistingPortalID", SqlDbType.Int) { Value = loadCarrierInfo.AssistingPortalId }, 
            new SqlParameter("AssistingEmployeeID", SqlDbType.VarChar) { Value = loadCarrierInfo.AssistingEmployeeId }) 
            .Select(x => new LoadCarrierInfo { }).FirstOrDefault();
    }
}

步骤2：修改LoadRepository依赖这个服务 #

public class LoadRepository : Repository<Load, LoadModel>, ILoadRepository 
{ 
    private readonly IUSCanadaCityStateRepository _USCanadaCityStateRepository;
    private readonly ILoadSqlQueryService _loadSqlQueryService;

    // 构造函数注入新的服务
    public LoadRepository(
        DbContext dbContext, 
        IUSCanadaCityStateRepository USCanadaCityStateRepository,
        ILoadSqlQueryService loadSqlQueryService) 
        : base(dbContext) 
    { 
        _USCanadaCityStateRepository = USCanadaCityStateRepository;
        _loadSqlQueryService = loadSqlQueryService;
    } 

    public int GetPickUpLocations(int loadId) 
    { 
        // 委托给服务处理
        return _loadSqlQueryService.GetPickUpLocationCount(loadId);
    } 

    public LoadCarrierInfo UpdateCarrierInfo(LoadCarrierInfo loadCarrierInfo) 
    { 
        // 委托给服务处理
        return _loadSqlQueryService.UpdateCarrierInfo(loadCarrierInfo);
    } 
}

步骤3：编写更简洁的单元测试 #

现在只需要MockILoadSqlQueryService，完全不用关心EF Core的细节：

[Fact]
public void GetPickUpLocations_WhenCalled_ReturnsCountFromQueryService()
{
    // Arrange
    int testLoadId = 123;
    int expectedCount = 5;
    var mockSqlService = new Mock<ILoadSqlQueryService>();
    mockSqlService.Setup(s => s.GetPickUpLocationCount(testLoadId)).Returns(expectedCount);
    
    var repo = new LoadRepository(
        _mockDbContext.Object, 
        _mockCityStateRepo.Object, 
        mockSqlService.Object);

    // Act
    int result = repo.GetPickUpLocations(testLoadId);

    // Assert
    Assert.Equal(expectedCount, result);
    mockSqlService.Verify(s => s.GetPickUpLocationCount(testLoadId), Times.Once);
}

总结 #

• 如果你想快速为现有代码写测试，用Moq.EntityFrameworkCore是可行的，但需要处理EF Core的细节。
• 长期来看，把原生SQL操作封装到专门的服务层，能大幅提升代码的可测试性和维护性，也让Repository类更专注于领域逻辑而非数据访问细节。

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