EF Core中QueryFilter引发的SQL查询性能问题求助

我之前也碰到过一模一样的问题——EF Core的全局查询过滤器会把过滤条件塞进LEFT JOIN的子查询里，导致关联查询性能拉胯，尤其是在你这种千万级大表的场景下，性能差距特别明显。

问题根源 #

EF Core的全局查询过滤器是针对单个实体生效的，当你用Include关联带过滤器的实体时，它会自动把过滤器条件放到子查询中，而不是外层WHERE或者JOIN的ON子句里。这种生成逻辑在大表上会导致子查询扫描大量数据，拖慢整个查询的执行速度。

几个可行的解决方案 #

1. 在Include中直接添加过滤条件（EF Core 5+支持）

如果你用的是EF Core 5或更高版本，可以去掉PurchaseOffer上的全局查询过滤器，然后在Include时手动指定过滤条件。这种方式生成的SQL会把过滤条件放到JOIN的ON子句里，避免子查询，性能会好很多：

// 先移除modelBuilder中PurchaseOffer的HasQueryFilter配置
var existingPurchaseOfferMetadatasById = await db.PurchaseOfferMetadatas
    .Where(pom => purchaseOfferIds.Contains(pom.Id))
    .Include(pom => pom.PurchaseOffer.Where(po => po.MandatorId == 1)) // 直接在Include里加过滤
    .ToDictionaryAsync(pom => pom.Id, cancellationToken);

生成的SQL大概会是这样：

SELECT [pom].[Id], [pom].[DeleteDate], [pom].[UpdateDate], [pom].[Version], [po].[Id], [po].[MandatorId], [po].[NetPriceAmount], [po].[NetPriceCurrencyIso4217Code]
FROM [externaldata].[PurchaseOfferMetadata] AS [pom]
LEFT JOIN [externaldata].[PurchaseOffer] AS [po] ON [pom].[Id] = [po].[Id] AND [po].[MandatorId] = 1
WHERE [pom].[Id] IN (CAST(3094411 AS bigint), CAST(4757070 AS bigint), CAST(4757112 AS bigint), CAST(5571232 AS bigint))

这种方式既保留了原来的LEFT JOIN逻辑（即使没有匹配的PurchaseOffer也会返回Metadata），又避免了子查询的性能问题。

2. 手动构造关联查询，替代Include

如果你的EF Core版本较低，或者需要更灵活的查询逻辑，可以手动用LINQ的Join语法来构造查询，把过滤条件放到合适的位置：

var query = from pom in db.PurchaseOfferMetadatas
            where purchaseOfferIds.Contains(pom.Id)
            join po in db.PurchaseOffers on pom.Id equals po.Id into poGroup
            from po in poGroup.Where(po => po.MandatorId == 1).DefaultIfEmpty()
            select new { Metadata = pom, Offer = po };

var resultDict = await query.ToDictionaryAsync(x => x.Metadata.Id, x => x.Metadata, cancellationToken);

// 如果需要把PurchaseOffer关联到Metadata实体上，可以手动设置
foreach (var item in query)
{
    db.Entry(item.Metadata).Reference(pom => pom.PurchaseOffer).CurrentValue = item.Offer;
}

这种方式完全由你控制SQL的生成逻辑，避免EF Core自动生成低效的子查询。

3. 添加复合索引优化子查询性能

如果你不想修改代码，也可以通过添加索引来优化现有子查询的性能。针对PurchaseOffer表，创建一个包含MandatorId和Id的复合索引，同时包含查询需要的列，这样数据库可以快速定位到符合条件的行，避免全表扫描：

CREATE NONCLUSTERED INDEX IX_PurchaseOffer_MandatorId_Id
ON [externaldata].[PurchaseOffer] (MandatorId, Id)
INCLUDE (NetPriceAmount, NetPriceCurrencyIso4217Code);

这个索引可以让子查询快速筛选出MandatorId=1的行，并且直接从索引中获取需要的列，不需要回表查询，大幅提升子查询的执行速度。

总结 #

如果你的EF Core版本够新，优先用方案1，代码简洁且性能最优；如果版本受限，用方案2手动构造查询；如果不想改动业务代码，方案3的索引优化是最省事的选择。

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