Entity Framework Core生成SQL比原生SQL慢40倍性能排查

1. 是否可以通过EF Core标准语法将WHERE中的过滤条件移动到JOIN的ON子句中？ #

可以。
EF Core默认的LINQ join 语法只对等值关联条件做直接翻译，非等值判断、计算类过滤条件默认会被放到外层WHERE子句。要把这部分逻辑移到ON子句，用等值关联追加恒真标记的写法即可，EF Core 3.0+版本都能正确翻译：

var positions = from x in baseTable
                join y in baseTable
                on new 
                {
                    x.EventKey, 
                    x.CustomParticipant, 
                    x.BetCategory, 
                    x.CustomLine,
                    Match = true
                } 
                equals new 
                {
                    y.EventKey, 
                    y.CustomParticipant, 
                    y.BetCategory, 
                    y.CustomLine,
                    Match = x.BetPosition != y.BetPosition && (1/x.OddsDecimal + 1/y.OddsDecimal) < 0.985
                }
                select new
                {
                    Id1 = x.Id,
                    PositionId1 = x.PositionId,
                    OddsDecimal1 = x.OddsDecimal,
                    Id2 = y.Id,
                    PositionId2 = y.PositionId,
                    OddsDecimal2 = y.OddsDecimal
                };

上述写法会把BetPosition不等值判断、赔率计算逻辑全部生成到JOIN的ON子句中，不会落到外层WHERE。
注意：不建议用SelectMany做交叉关联实现非等值连接，这种写法翻译出来的SQL会生成交叉连接再加过滤，性能更差。

2. 是否可以强制EF Core生成带WITH（CTE）的SQL语句，复用UNION ALL结果避免重复全表扫描？ #

分版本决定方案：

• EF Core 8.0以下版本没有原生支持。EF Core的查询翻译器默认不会自动识别重复引用的子查询并提升为CTE，你在代码里两次引用baseTable变量，翻译器会重复生成两次Concat（即UNION ALL）逻辑，这就是两张表被各扫描两次的直接原因。低版本下要么直接用FromSqlRaw手写CTE SQL映射结果，要么依赖第三方类库（比如EntityFrameworkCore.Projectables）实现CTE生成，稳定性不如原生方案。
• EF Core 8.0及以上版本原生支持CTE标记，直接调用内置的ToCte()方法即可，后续多次引用同一个查询变量时，EF Core会自动把它提升为WITH子句中的CTE，只生成一次UNION ALL逻辑：

  // 标记为CTE，后续多次引用不会重复生成子查询
  var baseTable = fd.Concat(dk).ToCte();
  

3. 两处SQL写法差异为何会带来40倍的性能差距，如何通过执行计划定位瓶颈？ #

性能差距核心原因 #

两个写法差异的影响权重不同：

• 重复UNION ALL扫描是核心瓶颈：EF生成的SQL会把FanduelPositions、DraftkingsPositions两张表各扫描两次，做两次UNION ALL生成两个独立的派生结果集，基础IO成本、临时结果集物化成本直接翻倍。如果表数据量较大、LastUpdated字段没有合适的索引，仅这部分的耗时就会达到秒级。你手写的CTE只会对两张表各做一次范围扫描，UNION ALL结果只物化一次，直接砍掉了70%以上的基础开销。
• 过滤条件位置是次要瓶颈：很多人误以为INNER JOIN的ON和WHERE条件逻辑等价、性能无差，这个结论只在单表直接关联、无嵌套子查询的场景下成立。EF生成的SQL是两个独立嵌套派生表做关联，优化器很容易受查询结构、统计信息精度限制，无法穿透派生表把过滤条件下推到关联阶段，导致先按4个等值字段做全量关联，生成体量极大的中间结果集（比如几十万、上百万行），再在外层WHERE做过滤，哈希匹配、排序操作的CPU、内存开销会指数级上涨，甚至出现中间结果太大内存放不下、溢出到TempDB做磁盘IO的情况。而手写SQL把所有过滤条件都放在ON子句，优化器可以在关联匹配阶段直接丢弃不符合条件的行，中间结果集体量会缩小几个数量级。
  补充：脱离.NET环境直接跑EF生成的SQL依然慢，已经完全排除了EF Core上下文跟踪、对象映射的开销，问题100%出在数据库执行层面。

执行计划定位方法 #

看三个核心节点即可快速定位瓶颈：

• 看表扫描节点的数量和行数：EF生成的SQL执行计划里会出现4次表扫描（两次FanduelPositions、两次DraftkingsPositions），如果每个扫描返回的行数和你手写SQL的单次扫描行数一致，说明重复扫描是主要开销；
• 看JOIN关联节点（Hash Match/Nested Loop）的实际行数：如果关联节点输出的行数远大于最终查询返回的行数（比如中间结果有几百万行，最终只返回几百行），说明过滤条件没有下推，中间结果膨胀拖慢了查询；
• 看是否有溢出节点：如果执行计划里出现Hash Spill、Sort Spill类的警告，说明中间结果集太大超过了数据库分配的内存阈值，需要读写磁盘TempDB，这部分磁盘IO是耗时暴涨的常见原因。

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