使用Entity Framework Core关联表时关联筛选被忽略的问题

问题描述 #

将项目从Entity Framework升级到Entity Framework Core后，某查询出现超时。通过SQL Profiler分析发现，原本的关联查询被拆分为多个独立查询，其中Customers、Payments、PaymentData三张表被直接全表查询，没有应用任何筛选条件，因数据量过大导致查询耗时过长。

原查询代码 #

var orders = context.Orders.AsQueryable();
var orderItems = context.OrderItems.AsQueryable();
var customers = context.Customers.AsQueryable();
var statusCodes = statuses.Cast<short>();
var paymentData = context.PaymentData.AsQueryable();

query = 
    from order in orders
    where order.CustomerId == customerId && statusCodes.Contains(order.StatusCode)
    join item in orderItems on order.OrderId equals item.OrderId into itemList
    from customer in customers
    where order.CustomerId == customer.CustomerId &&
          order.Currency == customer.Currency &&
          order.ProductType == customer.ProductType &&
          order.PaymentMethodId == customer.PaymentMethodId
    join payment in context.Payments.AsQueryable() on new
    { OrderId = order.OrderId, ProductType = order.ProductType.Value } equals
    new { OrderId = (int)payment.OrderId, payment.ProductType } into payments
    from paymentResult in payments.DefaultIfEmpty()
    join paymentDetail in paymentData
    on order.OrderId equals paymentDetail.OrderId into paymentDetails
    select new OrderResponse
    {
        OrderId = order.OrderId,
        CustomerId = order.CustomerId,
        CustomerName = order.CustomerName,
        OrderDate = order.OrderDate,
        //... 其他属性
    };

表关系 #

• Orders与OrderItems：一对多
• Orders与Customers：多对一
• Orders与Payments：一对多（左关联）
• Orders与PaymentData：一对多

Profiler捕获的SQL #

exec sp_executesql N'SELECT ...
FROM [Sales].[Orders] AS [order]
LEFT JOIN [Sales].[OrderItems] AS [item] ON [order].[OrderId] = [item].[OrderId]
WHERE ([order].[CustomerId] = @__customerId_0) AND [order].[StatusCode] IN (CAST(1 AS smallint), CAST(2 AS smallint), CAST(3 AS smallint), CAST(4 AS smallint), CAST(5 AS smallint), CAST(6 AS smallint), CAST(7 AS smallint), CAST(8 AS smallint), CAST(9 AS smallint), CAST(81 AS smallint), CAST(82 AS smallint), CAST(89 AS smallint), CAST(90 AS smallint), CAST(91 AS smallint), CAST(99 AS smallint))
ORDER BY [OrderId0]',N'@__customerId_0 varchar(50)',@__customerId_0='62325234'

SELECT [customer].[CustomerId], [customer].[Currency], [customer].[ProductType]
FROM [Sales].[Customers] AS [customer]

SELECT [payment].[OrderId], [payment].[ProductType], [payment].[CollectionDueDate]
FROM [Sales].[Payments] AS [payment]

SELECT [paymentDetail].[OrderId], [paymentDetail].[AccountHolderName], [paymentDetail].[BankAccountNumber], [paymentDetail].[BankName], [paymentDetail].[IachId], [paymentDetail].[Routing]
FROM [Sales].[PaymentData] AS [paymentDetail]

解决方案 #

问题根源 #

原查询中from customer in customers的写法属于内存过滤逻辑：EF Core无法将后续的where条件转化为SQL层级的关联筛选，只能先全表拉取所有Customers数据，再在内存中匹配Orders记录。同理，Payments和PaymentData的join写法虽然看似正确，但查询结构的歧义导致EF Core选择拆分查询。

修改步骤 #

1. 修正Customers的关联方式

将笛卡尔积式的from customer in customers改为显式join，让EF Core生成SQL层级的关联筛选：

// 替换原查询中的from customer in customers部分
join customer in customers 
on new 
{ 
    order.CustomerId, 
    order.Currency, 
    order.ProductType, 
    order.PaymentMethodId 
} equals new 
{ 
    customer.CustomerId, 
    customer.Currency, 
    customer.ProductType, 
    customer.PaymentMethodId 
}

2. 简化查询结构，去掉不必要的AsQueryable()

EF Core中DbSet本身就是IQueryable类型，无需提前调用AsQueryable()：

// 简化变量定义
var orders = context.Orders;
var orderItems = context.OrderItems;
var customers = context.Customers;
var paymentData = context.PaymentData;

3. （可选）使用导航属性替代手动Join

如果实体类中已定义导航属性（比如Order.Customer、Order.Payments、Order.PaymentData），直接使用导航属性能让EF Core自动生成更优的关联SQL：

query = 
    from order in orders
    where order.CustomerId == customerId && statusCodes.Contains(order.StatusCode)
    let customer = order.Customer
    where order.Currency == customer.Currency &&
          order.ProductType == customer.ProductType &&
          order.PaymentMethodId == customer.PaymentMethodId
    select new OrderResponse
    {
        OrderId = order.OrderId,
        CustomerId = order.CustomerId,
        CustomerName = order.CustomerName,
        OrderDate = order.OrderDate,
        // 通过导航属性直接获取关联数据
        PaymentDetails = order.PaymentData.Select(pd => new PaymentDetailResponse { ... }),
        //... 其他属性
    };

4. 强制禁用拆分查询（EF Core 5+支持）

如果上述修改后仍存在拆分查询，可在查询末尾添加AsSplitQuery(false)强制生成单条关联SQL：

query = query.AsSplitQuery(false);

修改后的完整查询示例 #

var orders = context.Orders;
var orderItems = context.OrderItems;
var customers = context.Customers;
var statusCodes = statuses.Cast<short>();
var paymentData = context.PaymentData;

query = 
    from order in orders
    where order.CustomerId == customerId && statusCodes.Contains(order.StatusCode)
    join item in orderItems on order.OrderId equals item.OrderId into itemList
    join customer in customers 
    on new 
    { 
        order.CustomerId, 
        order.Currency, 
        order.ProductType, 
        order.PaymentMethodId 
    } equals new 
    { 
        customer.CustomerId, 
        customer.Currency, 
        customer.ProductType, 
        customer.PaymentMethodId 
    }
    join payment in context.Payments on new
    { OrderId = order.OrderId, ProductType = order.ProductType.Value } equals
    new { OrderId = (int)payment.OrderId, payment.ProductType } into payments
    from paymentResult in payments.DefaultIfEmpty()
    join paymentDetail in paymentData
    on order.OrderId equals paymentDetail.OrderId into paymentDetails
    select new OrderResponse
    {
        OrderId = order.OrderId,
        CustomerId = order.CustomerId,
        CustomerName = order.CustomerName,
        OrderDate = order.OrderDate,
        //... 其他属性
    };

验证方式 #

修改后重新执行查询，通过SQL Profiler检查生成的SQL：

• 不再出现单独的全表查询语句
• 所有关联都合并为单条（或少数几条）包含JOIN的SQL语句
• 查询耗时显著降低

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