.NET 4.7.6与Entity Framework 3.1.16中批量执行多数据库查询的可行性方案咨询

针对你遇到的循环产生大量SQL请求的问题，确实存在类似Elasticsearch批量查询的机制，在EF Core和ADO.NET层面都能实现，而且不需要重构现有数据库架构。下面分几种实用方案说明：

1. 预加载关联数据，消除N+1查询 #

你的代码里多次调用GetAllMemberScores，还在循环里重复查询部门相关数据，可以通过EF的预加载把关联数据一次性拉取，后续操作全部在内存中完成：

// 一次性加载所有成员分数，包含关联的CompanyValueScores
var allMemberScores = _context.MemberScores
    .Include(s => s.CompanyValueScores)
    .Where(s => s.CompanyId == companyId)
    .ToList();

// 内存中筛选当前/上期数据，无需再查库
var currentPeriodMemberScores = allMemberScores
    .Where(s => s.TimeSpan == timespan)
    .ToList();
var previousPeriodMemberScores = allMemberScores
    .Where(s => s.TimeSpan == previousTimespan)
    .ToList();

// 一次性加载所有公司指标
var companyValues = _context.CompanyValues
    .Where(v => v.CompanyId == companyId)
    .ToList();

这样原来的4个独立查询就压缩成了2个，后续的部门筛选、分数计算都在内存中完成，避免了重复请求。

2. 多结果集批量查询（完全匹配你要的"多查询+ID区分"需求） #

EF Core 3.1支持通过ADO.NET的DbDataReader读取多个结果集，你可以把多个独立的SELECT语句打包成一个SQL请求发送到数据库，然后依次读取每个结果集并映射到对应的实体，完美对应Elasticsearch的多查询ID机制：

var sql = @"
-- 查询1：公司指标
SELECT * FROM CompanyValues WHERE CompanyId = @CompanyId;
-- 查询2：所有成员分数
SELECT * FROM MemberScores WHERE CompanyId = @CompanyId;
-- 查询3：当期成员分数
SELECT * FROM MemberScores WHERE CompanyId = @CompanyId AND TimeSpan = @CurrentTimespan;
-- 查询4：上期成员分数
SELECT * FROM MemberScores WHERE CompanyId = @CompanyId AND TimeSpan = @PreviousTimespan;
";

var parameters = new[]
{
    new SqlParameter("@CompanyId", companyId),
    new SqlParameter("@CurrentTimespan", timespan),
    new SqlParameter("@PreviousTimespan", previousTimespan)
};

using var connection = _context.Database.GetDbConnection();
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = sql;
command.Parameters.AddRange(parameters);
using var reader = command.ExecuteReader();

// 读取第一个结果集：公司指标
var companyValues = _context.CompanyValues.FromSqlReader(reader).ToList();
reader.NextResult();

// 读取第二个结果集：所有成员分数
var allMemberScores = _context.MemberScores.FromSqlReader(reader).ToList();
reader.NextResult();

// 读取第三个结果集：当期成员分数
var currentPeriodMemberScores = _context.MemberScores.FromSqlReader(reader).ToList();
reader.NextResult();

// 读取第四个结果集：上期成员分数
var previousPeriodMemberScores = _context.MemberScores.FromSqlReader(reader).ToList();

这种方式只需要一次数据库请求，就能获取所有基础数据，结果集的顺序和你编写SQL的顺序一致，完全可以对应"按ID提取响应"的需求。

3. 合并循环内的小查询为批量查询 #

针对循环中每个部门、每个公司指标的查询，可以把多个小查询合并成一个批量查询，彻底消除循环查库的问题：

示例：批量获取所有部门的响应率 #

原来每个部门查一次响应率，现在一次性查询所有部门的结果：

var departmentIds = currentDepartments.Select(d => d.Id).ToList();

// 一次性查询所有部门的响应率（SQL根据你的实际表结构调整）
var responseRateDict = _context.Database.SqlQuery<DepartmentResponseRate>(@"
SELECT 
    d.Id AS DepartmentId,
    CAST(COUNT(s.Id) AS float)/COUNT(*) AS Rate
FROM Departments d
LEFT JOIN Surveys s ON d.Id = s.DepartmentId AND s.TimeSpan = @CurrentTimespan
WHERE d.Id IN ({0})
GROUP BY d.Id
", string.Join(",", departmentIds))
.ToDictionary(r => r.DepartmentId, r => (int)Math.Round(r.Rate * 100));

// 后续循环直接从字典取值，无需再查库
foreach (var department in currentDepartments)
{
    int responseRate = responseRateDict.TryGetValue(department.Id, out var rate) ? rate : 0;
    // ...其他业务逻辑
}

需要定义一个DTO类来接收查询结果：

public class DepartmentResponseRate
{
    public int DepartmentId { get; set; }
    public float Rate { get; set; }
}

示例：批量计算所有公司指标的差值 #

把循环中每个指标的差值计算合并成一个SQL：

var departmentIds = currentDepartments.Select(d => d.Id).ToList();
var valueIds = companyValues.Select(v => v.Id).ToList();

// 批量计算所有部门+指标的差值
var valueDiffList = _context.Database.SqlQuery<ValueDifference>(@"
SELECT 
    d.Id AS DepartmentId,
    cv.Id AS CompanyValueId,
    AVG(CASE WHEN ms.TimeSpan = @CurrentTimespan THEN cvs.Score ELSE NULL END) -
    AVG(CASE WHEN ms.TimeSpan = @PreviousTimespan THEN cvs.Score ELSE NULL END) AS Difference
FROM Departments d
JOIN MemberScores ms ON d.Id = ms.DepartmentId
JOIN CompanyValueScores cvs ON ms.Id = cvs.MemberScoreId
JOIN CompanyValues cv ON cvs.CompanyValueId = cv.Id
WHERE d.Id IN ({0}) AND cv.Id IN ({1})
GROUP BY d.Id, cv.Id
", string.Join(",", departmentIds), string.Join(",", valueIds))
.ToList();

// 循环中直接从集合查找结果
foreach (var department in currentDepartments)
{
    foreach (var companyValue in companyValues)
    {
        var diff = valueDiffList.FirstOrDefault(vd => 
            vd.DepartmentId == department.Id && vd.CompanyValueId == companyValue.Id)?.Difference;
        // ...处理差值逻辑
    }
}

对应的DTO类：

public class ValueDifference
{
    public int DepartmentId { get; set; }
    public int CompanyValueId { get; set; }
    public decimal? Difference { get; set; }
}

4. 第三方库辅助批量操作（可选） #

如果不想写原生SQL，可以考虑使用支持EF Core 3.1的第三方库，比如Z.EntityFramework.Extensions.EFCore，它提供了开箱即用的批量查询、批量更新功能，能自动把多个查询打包成一个数据库请求。不过这是付费库，适合有预算的场景：

var batch = _context.BulkQuery();
var companyValues = batch.Query<CompanyValue>(x => x.CompanyId == companyId).ToList();
var allMemberScores = batch.Query<MemberScore>(x => x.CompanyId == companyId).ToList();
// ...其他批量查询

总结下来，最推荐预加载+多结果集查询+批量合并循环内查询的组合，既能大幅减少数据库请求数，又完全适配你的遗留项目架构，不需要做任何数据库改动。

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