为何添加CreatedTimestamp过滤条件后我的EF Core LINQ查询无法执行

问题原因 #

EF Core的查询翻译器无法将LINQ查询内部的DateTime.Now.AddMonths(-11)方法调用转换为对应SQL语句，在多层左连接的复杂查询结构下，翻译器的推导能力进一步受限，最终抛出无法翻译的异常。

解决方案 #

1. 提前计算日期阈值（最优方案）

将动态日期计算逻辑放到查询外部完成，把计算结果作为常量传入查询，EF Core可正常翻译该过滤条件：

// 本地预先计算日期阈值，不参与SQL翻译
var dateThreshold = DateTime.Now.AddMonths(-11);

var q = (
    from trckhead in DbContext.TrackingBatchHeader
    from userdets in DbContext.UserDetails
        .Where(u => u.UserId == trckhead.ClosedByUserId).DefaultIfEmpty()
    from trckkeys in DbContext.TrackingBatchesItemKey
        .Where(t => t.TrackingNo == trckhead.TrackingNo).DefaultIfEmpty()
    from trcklink1 in DbContext.TrackingBatchesLink
        .Where(x => x.TrackingNo == trckhead.TrackingNo && x.TrackingType == "I").DefaultIfEmpty()
    from trcklink2 in DbContext.TrackingBatchesLink
        .Where(x => x.TrackingNo == trckhead.TrackingNo && x.TrackingType == "T").DefaultIfEmpty()
    join trckref in DbContext.TrackingBatchesReference on trckhead.TrackingBatchType equals trckref.TrackingBatchType
    join mthsite in DbContext.SiteDetail on trckhead.SiteCode equals mthsite.SiteCode
    join userdets2 in DbContext.UserDetails on trckhead.CreatedByUserId equals userdets2.UserId
    where
    (string.IsNullOrEmpty(searchRequest.Status) || trckhead.Status == searchRequest.Status) &&      
    trckhead.CreatedTimestamp >= dateThreshold
    select new TrackingBatchSearchResultDto
    {
        TrackingBatchId = trckhead.TrackingNo,
        SiteCode = trckhead.SiteCode,
        TrackingBatchType = trckhead.TrackingBatchType,
        Status = trckhead.Status,
        Created = trckhead.CreatedTimestamp,
        CreatedById = trckhead.CreatedByUserId,
        ClosedById = trckhead.ClosedByUserId,
        UserDescription = trckhead.UsersDescription,
        TrackingBatchDescription = trckref.TrackingBatchTypeDescription,
        SiteName = mthsite.Title,
        CreatedByName = userdets2.FullUserName ?? string.Empty,
        ClosedByName = userdets.FullUserName ?? string.Empty,
        Link1 = trcklink1.TrackingData ?? string.Empty,
        Link2 = trcklink2.TrackingData ?? string.Empty
    }).Distinct();

return await q.ToListAsync();

2. 过滤条件前置（可选优化）

将主表的过滤条件提前到主表查询阶段，减少后续连接操作的数据集大小，同时降低翻译器的推导压力，进一步提升查询性能：

var dateThreshold = DateTime.Now.AddMonths(-11);
var filteredHeaders = DbContext.TrackingBatchHeader
    .Where(t => t.CreatedTimestamp >= dateThreshold 
        && (string.IsNullOrEmpty(searchRequest.Status) || t.Status == searchRequest.Status));

var q = (
    from trckhead in filteredHeaders
    // 后续连接、Select逻辑保持不变
    // ...
)

修改后所有过滤逻辑均可在数据库端执行，无需拉取全量数据到本地过滤，完全满足性能要求。

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