Entity Framework二级过滤：时区日期查询的索引优化与高效计数

现有数据表包含以下字段：

OrderId
CustomerId
TimeZoneOffset   <-- 单位：分钟
DateOrderUTC     <-- 该字段已建立索引
...

表内有约200万条记录，需要根据用户指定的日期过滤订单，且必须考虑订单对应的时区。虽然可以新增存储时区时间的DateOrder字段，但当前数据库结构无法修改。

原查询代码因使用DbFunctions.AddMinutes转换DateOrderUTC时区，无法触发索引查找，性能低下：

return (from rec in dbctx.tblApprovalReferralPromoUsers.AsNoTracking()
        where (DbFunctions.AddMinutes(rec.DateOrderUTC, rec.TimeZoneOffset) >= startDate) && (DbFunctions.AddMinutes(rec.DateOrderUTC, rec.TimeZoneOffset) <= endDate)
        select rec).Count();

计划先通过扩大DateOrderUTC的范围来获取候选数据集（将startDate减1天、endDate加1天），代码如下：

startDate = startDate.AddDays(-1);
endDate = endDate.AddDays(1);
var vRecs = (from rec in dbctx.tblApprovalReferralPromoUsers.AsNoTracking()
             where (rec.DateOrderUTC >= startDate) && (rec.DateOrderUTC <= endDate)
             select rec);
return ???;

由于候选数据集可能多达数十万条，不希望先返回全量列表，询问如何在返回计数前完成时区转换后的日期二次过滤。

直接在候选查询的基础上追加时区转换后的过滤条件，EF会将整个查询合并为单条SQL在数据库端执行，不会拉取全量数据到内存：

// 先保存原始日期范围，避免覆盖后无法复用
var originalStartDate = startDate;
var originalEndDate = endDate;

// 扩大UTC日期范围获取候选集
startDate = startDate.AddDays(-1);
endDate = endDate.AddDays(1);

var count = (from rec in dbctx.tblApprovalReferralPromoUsers.AsNoTracking()
             where rec.DateOrderUTC >= startDate && rec.DateOrderUTC <= endDate
             && DbFunctions.AddMinutes(rec.DateOrderUTC, rec.TimeZoneOffset) >= originalStartDate
             && DbFunctions.AddMinutes(rec.DateOrderUTC, rec.TimeZoneOffset) <= originalEndDate
             select rec).Count();
return count;

关键说明 #

• 必须保留原始的startDate和endDate变量，二次过滤需要用用户指定的原始日期范围，不能直接使用扩大后的日期
• EF会自动优化查询逻辑：先通过DateOrderUTC的索引快速筛选出候选数据，再在数据库端对这些数据做时区转换和二次过滤，最后返回计数，全程不会加载全量数据到内存
• 这种方案既利用了索引提升初始过滤的性能，又保证了最终结果的准确性，同时规避了全量数据加载的性能瓶颈

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