优化EF Core WHERE子句中重复Linq OrderBy，提升代码可读性

我需要根据子项的排序结果查询Driver对象列表，现有查询能正常运行，但WHERE子句里重复的OrderBy逻辑导致代码极度冗长。

现有查询代码 #

List<Driver> driversToReturn = new List<Driver>();
var drivers = await _rwContext.Drivers
    .Include(d => d.RegionIds)
    .Include(d => d.Carrier).ThenInclude(c => c.DriverHolds)
    .Include(m => m.Manifests).ThenInclude(m => m.ManifestStops)
    .ThenInclude(ms => ms.OffloadJourneys).ThenInclude(o => o.EndLocation).ThenInclude(o => o.LocationAliases.Where(la => la.IsPrimary))
    .Include(m => m.Manifests).ThenInclude(m => m.ManifestStops)
    .ThenInclude(ms => ms.OnloadJourneys).ThenInclude(o => o.StartLocation).ThenInclude(o => o.LocationAliases.Where(la => la.IsPrimary))
    .Where(m =>
    ((startDate == null || endDate == null || m.Manifests == null || m.Manifests.Count ==0

    || (m.Manifests.FirstOrDefault(m => m.ManifestStops != null && m.ManifestStops
    .OrderBy(ms => ms.StopTimestamp != null &&
            ms.StopTimeSet ?
            ms.StopTimestamp :
            (!ms.StopTimeSet && ms.StopTimestamp != null ?
            (ms.IsOffloadStop ?
            ms.StopTimestamp.Value.AddHours((23 - ms.StopTimestamp.Value.Hour)).AddMinutes((59 - ms.StopTimestamp.Value.Minute)) :
            ms.StopTimestamp.Value.AddHours(-1 * (ms.StopTimestamp.Value.Hour)).AddMinutes(-1 * (ms.StopTimestamp.Value.Minute))) :
            (ms.IsOffloadStop ? DateTime.Now.AddYears(10) : DateTime.Now.AddYears(-10)))).ThenBy(ms => ms.IsOffloadStop)
            .LastOrDefault() != null
    && startDate <= m.ManifestStops
    .OrderBy(ms => ms.StopTimestamp != null &&
            ms.StopTimeSet ?
            ms.StopTimestamp :
            (!ms.StopTimeSet && ms.StopTimestamp != null ?
            (ms.IsOffloadStop ?
            ms.StopTimestamp.Value.AddHours((23 - ms.StopTimestamp.Value.Hour)).AddMinutes((59 - ms.StopTimestamp.Value.Minute)) :
            ms.StopTimestamp.Value.AddHours(-1 * (ms.StopTimestamp.Value.Hour)).AddMinutes(-1 * (ms.StopTimestamp.Value.Minute))) :
            (ms.IsOffloadStop ? DateTime.Now.AddYears(10) : DateTime.Now.AddYears(-10)))).ThenBy(ms => ms.IsOffloadStop)
            .LastOrDefault().StopTimestamp
    && endDate.Value.Date.AddDays(1) > m.ManifestStops
    .OrderBy(ms => ms.StopTimestamp != null &&
            ms.StopTimeSet ?
            ms.StopTimestamp :
            (!ms.StopTimeSet && ms.StopTimestamp != null ?
            (ms.IsOffloadStop ?
            ms.StopTimestamp.Value.AddHours((23 - ms.StopTimestamp.Value.Hour)).AddMinutes((59 - ms.StopTimestamp.Value.Minute)) :
            ms.StopTimestamp.Value.AddHours(-1 * (ms.StopTimestamp.Value.Hour)).AddMinutes(-1 * (ms.StopTimestamp.Value.Minute))) :
            (ms.IsOffloadStop ? DateTime.Now.AddYears(10) : DateTime.Now.AddYears(-10)))).ThenBy(ms => ms.IsOffloadStop)
            .LastOrDefault().StopTimestamp) == null) &&

    (m.Manifests.FirstOrDefault(m => m.ManifestStops != null && m.ManifestStops
    .OrderBy(ms => ms.StopTimestamp != null &&
            ms.StopTimeSet ?
            ms.StopTimestamp :
            (!ms.StopTimeSet && ms.StopTimestamp != null ?
            (ms.IsOffloadStop ?
            ms.StopTimestamp.Value.AddHours((23 - ms.StopTimestamp.Value.Hour)).AddMinutes((59 - ms.StopTimestamp.Value.Minute)) :
            ms.StopTimestamp.Value.AddHours(-1 * (ms.StopTimestamp.Value.Hour)).AddMinutes(-1 * (ms.StopTimestamp.Value.Minute))) :
            (ms.IsOffloadStop ? DateTime.Now.AddYears(10) : DateTime.Now.AddYears(-10)))).ThenBy(ms => ms.IsOffloadStop)
            .FirstOrDefault() != null
    && endDate.Value.Date.AddDays(1) > m.ManifestStops
    .OrderBy(ms => ms.StopTimestamp != null &&
            ms.StopTimeSet ?
            ms.StopTimestamp :
            (!ms.StopTimeSet && ms.StopTimestamp != null ?
            (ms.IsOffloadStop ?
            ms.StopTimestamp.Value.AddHours((23 - ms.StopTimestamp.Value.Hour)).AddMinutes((59 - ms.StopTimestamp.Value.Minute)) :
            ms.StopTimestamp.Value.AddHours(-1 * (ms.StopTimestamp.Value.Hour)).AddMinutes(-1 * (ms.StopTimestamp.Value.Minute))) :
            (ms.IsOffloadStop ? DateTime.Now.AddYears(10) : DateTime.Now.AddYears(-10)))).ThenBy(ms => ms.IsOffloadStop)
            .FirstOrDefault().StopTimestamp
    && startDate <= m.ManifestStops
    .OrderBy(ms => ms.StopTimestamp != null &&
            ms.StopTimeSet ?
            ms.StopTimestamp :
            (!ms.StopTimeSet && ms.StopTimestamp != null ?
            (ms.IsOffloadStop ?
            ms.StopTimestamp.Value.AddHours((23 - ms.StopTimestamp.Value.Hour)).AddMinutes((59 - ms.StopTimestamp.Value.Minute)) :
            ms.StopTimestamp.Value.AddHours(-1 * (ms.StopTimestamp.Value.Hour)).AddMinutes(-1 * (ms.StopTimestamp.Value.Minute))) :
            (ms.IsOffloadStop ? DateTime.Now.AddYears(10) : DateTime.Now.AddYears(-10)))).ThenBy(ms => ms.IsOffloadStop)
            .FirstOrDefault().StopTimestamp) == null)
    
    )) &&
    
    (m.Carrier == null || m.CarrierId ==0 || m.Carrier.DriverHolds == null || m.Carrier.DriverHolds.Count == 0 ||
    (m.Carrier.DriverHolds.FirstOrDefault(dr => dr.DriverId == m.DriverId &&
    (startDate == null || endDate == null || (dr.StartDate > startDate.Value && dr.StartDate < endDate.Value.Date.AddDays(1))) &&
    (endDate == null || endDate == null || (dr.EndDate > startDate.Value && dr.EndDate < endDate.Value.Date.AddDays(1)))) == null)
    ) && (RegionId == null || m.RegionIds.FirstOrDefault(r => r.RegionId == RegionId) != null))
    .Select(r => new 
    {
        DriverId = r.DriverId,
        DriverTag = r.DriverTag,

        RegionIds = r.RegionIds.Select(r=>new RegionIdItem() {
            RegionId = r.RegionId,
            DriverId = r.DriverId
        }),
        Carrier = new {
            CarrierId = r.Carrier.CarrierId,
            CarrierTag = r.Carrier.CarrierTag,
            DriverHolds = r.Carrier.DriverHolds.Where(dh=>dh.DriverId == r.DriverId).Select(dh => new DriverHold() {
                StartDate = dh.StartDate,
                EndDate = dh.EndDate,
                DriverId = dh.DriverId,
            })
        },
        Manifests = r.Manifests.Select(m => new  {
            ManifestId = m.ManifestId,
            ManifestStops = m.ManifestStops                       
            .Select(ms => new {
                //manifest stop
                StopTimeSet = ms.StopTimeSet,
                StopTimestamp = ms.StopTimestamp,
                OffloadJourneys = ms.OffloadJourneys.Select(oj => new {
                    EndLocation = new {
                        LocationAliases = oj.EndLocation.LocationAliases.Where(la => la.IsPrimary).Select(la => new LocationAlias()
                        {
                            Alias = la.Alias,
                            IsPrimary = la.IsPrimary
                        })
                    }
                }),
                //manifest stop
                OnloadJourneys = ms.OnloadJourneys.Select(oj => new
                {
                    StartLocation = new
                    {
                        LocationAliases = oj.StartLocation.LocationAliases.Where(la => la.IsPrimary).Select(la => new LocationAlias()
                        {
                            Alias = la.Alias,
                            IsPrimary = la.IsPrimary
                        })
                    }
                })
            })
        })
    })
    .ToListAsync(ct);

尝试过的无效方案 #

• 在ThenInclude选择ManifestStops时添加OrderBy：排序结果不会保留到WHERE子句，查询不生效；
• 在Select子句中添加排序：同样无法将排序逻辑带入WHERE子句；
• 封装IEnumerable类型的扩展方法：触发EF Core的Query could not be translated错误，代码如下：

public static IEnumerable<ManifestStop> OrderManifestStops(this IEnumerable<ManifestStop> source)
{
    return source.OrderBy(ms => ms.StopTimestamp != null &&
        ms.StopTimeSet ?
        ms.StopTimestamp :
        (!ms.StopTimeSet && ms.StopTimestamp != null ?
        (ms.IsOffloadStop ?
        ms.StopTimestamp.Value.AddHours((23 - ms.StopTimestamp.Value.Hour)).AddMinutes((59 - ms.StopTimestamp.Value.Minute)) :
        ms.StopTimestamp.Value.AddHours(-1 * (ms.StopTimestamp.Value.Hour)).AddMinutes(-1 * (ms.StopTimestamp.Value.Minute))) :
        (ms.IsOffloadStop ? DateTime.Now.AddYears(10) : DateTime.Now.AddYears(-10)))).ThenBy(ms => ms.IsOffloadStop);
}

解决方案：封装可被EF Core翻译的排序逻辑 #

问题出在之前的扩展方法基于IEnumerable，会强制客户端求值，而WHERE子句需要服务器端翻译。改为基于IQueryable和表达式树的扩展方法，让EF Core能识别并复用排序逻辑。

步骤1：封装排序表达式和Queryable扩展方法 #

// 封装排序键的表达式，确保EF Core能翻译
private static Expression<Func<ManifestStop, object>> GetManifestStopSortKey()
{
    return ms => ms.StopTimestamp != null && ms.StopTimeSet 
        ? (object)ms.StopTimestamp 
        : (!ms.StopTimeSet && ms.StopTimestamp != null 
            ? (object)(ms.IsOffloadStop 
                ? ms.StopTimestamp.Value.AddHours(23 - ms.StopTimestamp.Value.Hour).AddMinutes(59 - ms.StopTimestamp.Value.Minute) 
                : ms.StopTimestamp.Value.AddHours(-ms.StopTimestamp.Value.Hour).AddMinutes(-ms.StopTimestamp.Value.Minute)) 
            : (object)(ms.IsOffloadStop ? DateTime.Now.AddYears(10) : DateTime.Now.AddYears(-10)));
}

// 基于IQueryable的扩展方法，EF Core可翻译
public static IOrderedQueryable<ManifestStop> OrderManifestStops(this IQueryable<ManifestStop> source)
{
    var sortKeyExpr = GetManifestStopSortKey();
    return source.OrderBy(sortKeyExpr).ThenBy(ms => ms.IsOffloadStop);
}

步骤2：在查询中复用扩展方法 #

修改WHERE子句中所有重复的m.ManifestStops.OrderBy(...)部分，替换为：
m.ManifestStops.AsQueryable().OrderManifestStops()

比如原WHERE子句中的这段：

m.ManifestStops.OrderBy(ms => ...).ThenBy(ms => ms.IsOffloadStop).LastOrDefault()

替换为：

m.ManifestStops.AsQueryable().OrderManifestStops().LastOrDefault()

这样整个WHERE子句的重复代码会被大幅简化，同时EF Core能正确将排序逻辑翻译为SQL，不会出现翻译错误。

额外优化：提取重复的日期判断逻辑 #

还可以把WHERE子句中关于日期范围的判断也封装成表达式或方法，进一步精简代码，比如：

private static Expression<Func<ManifestStop, bool>> IsStopInDateRange(DateTime? startDate, DateTime? endDate)
{
    return ms => startDate == null || endDate == null 
        || (startDate <= ms.StopTimestamp && endDate.Value.Date.AddDays(1) > ms.StopTimestamp);
}

然后在查询中复用这个表达式，进一步减少重复代码。

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