使用Join改写优化C# LINQ to SQL代码以提升运行速度

原有代码性能瓶颈 #

• 双重循环每次迭代都触发独立数据库查询，总查询次数 = 时间跨度天数 * 路径编码总数，属于典型的N+1查询问题，数据库IO开销极高
• 每次循环都重复读取全量PathCodes表，资源浪费严重
• 单次查询仅处理单条数据，无法利用数据库索引和批量查询优化

优化思路 #

采用批量预加载数据+内存关联匹配的方案替换嵌套循环，将原有的数百/数千次数据库查询压缩为3次批量查询：

1. 一次性预加载时间范围内所有需要的表数据到内存
2. 生成日期集合与路径编码的笛卡尔积，得到所有需要计算的组合
3. 对每个组合优先匹配PathReps记录，无匹配则取Paths表最近生效记录
4. 最后按日期分组生成目标字典结构

优化后代码 #

// 1. 生成目标时间段所有日期集合
var dateRange = Enumerable.Range(0, 1 + endDate.Subtract(startDate).Days)
    .Select(offset => startDate.AddDays(offset))
    .ToList();

// 2. 批量预加载所有需要的数据（仅3次数据库查询）
// 2.1 预加载全量路径编码
var allPathCodes = db.PathCodes.ToList();
// 2.2 预加载时间段内所有PathReps记录
var allPathReps = db.PathReps
    .Where(p => dateRange.Contains(p.rep_start))
    .ToList();
// 2.3 预加载所有生效日期<=结束日期的Paths记录（避免重复查库）
var allPaths = db.Paths
    .Where(p => p.start_date <= endDate)
    .OrderByDescending(p => p.start_date)
    .ToList();

// 3. 生成所有日期+路径编码的组合，统一处理
var result = dateRange
    .Select(date => new
    {
        Date = date,
        PathDict = allPathCodes.ToDictionary(
            code => code.path_code,
            code => 
            {
                // 优先匹配PathReps
                var rep = allPathReps.FirstOrDefault(r => 
                    r.path_code == code.path_code && r.rep_start == date);
                if (rep != null)
                {
                    return new CPath
                    {
                        PathId = rep.ID,
                        PathCode = rep.path_code,
                        PathGroupName = rep.path_group_name,
                        // 其余字段按需赋值
                    };
                }
                // 无匹配则取Paths最近生效记录
                var path = allPaths.First(p => 
                    p.path_code_id == code.Id && p.start_date <= date);
                return new CPath
                {
                    PathId = path.ID,
                    PathCode = path.path_code,
                    PathGroupName = path.path_group_name,
                    // 其余字段按需赋值
                };
            }
        )
    })
    // 转换为你需要的最终字典结构，注意shamsi_date_int是原代码的波斯历日期转换，按需替换
    .ToDictionary(item => ConvertShamsiToInt(item.Date), item => item.PathDict);

// 最终result就是原代码的dPaths_dic

注：如果Paths表数据量过大，可加过滤条件p.start_date >= 最早需要的生效日期进一步缩小预加载范围，或者对allPaths按path_code_id分组预处理，匹配时直接从分组中取第一条符合日期要求的记录，内存匹配速度会更快。

性能提升说明 #

• 以时间跨度30天、路径编码100条的场景为例，原代码需要发起30*100+30=3030次数据库查询，优化后仅需3次批量查询，IO开销降低99%以上
• 内存匹配采用预加载+线性匹配，CPU开销远低于频繁的数据库连接建立、查询解析、网络传输开销

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