转换EF Core WHERE子句中重复的Linq OrderBy以提高可读性

w1e3prcc 于 2022-12-15 发布在其他

关注(0)|答案(1)|浏览(132)

我试图根据子项的顺序查询一个对象列表，我的查询是有效的，但是正如你所看到的，因为重复的orderby，where子句过长。

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);

我尝试将OrderBy应用于“ThenInclude”，其中我选择了“ManifestStops”，但这在WHERE子句之前没有生效，并且排序没有保留在WHERE查询中。
我还尝试在select子句中应用排序，但遇到了与在'ThenInclude'中相同的问题。
我尝试的另一种方法是添加一个扩展方法来实现这一点，但是我得到了一个EF“Query could not be translate”错误。

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);
    }

linq

来源：https://stackoverflow.com/questions/73666773/convert-duplicated-linq-orderby-in-ef-core-where-clause-to-be-more-readable

1条答案

按热度按时间

x8diyxa71#

我猜Driver的实现与此接近

public class Driver
{
    public DateTime? StopTimestamp { get; set; }   
    public bool StopTimeSet { get; set; }
    public bool IsOffloadStop { get; set; }
}

您可以创建静态字段OrderBy

public static Func<Driver, DateTime?> 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);

并使用它，例如这样：

var drivers = new List<Driver>();

drivers.OrderBy(OrderBy);

但我怀疑它是否能被EF翻译

赞(0）回复(0）举报 2022-12-15

我来回答

转换EF Core WHERE子句中重复的Linq OrderBy以提高可读性

1条答案

相关问题

热门标签

最新问答