Convert duplicated Linq OrderBy in EF Core WHERE clause to be more readable

Question

I am trying to query a list of objects based on the ordering of child items. I have this query that works, but as you can see the where clause is excessively long because of the duplicated 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);

I tried to apply the OrderBy to the 'ThenInclude' where I am selecting 'ManifestStops' but that didn't take effect before the WHERE clause and the ordering was not preserved in the Where query.

I also tried to apply the ordering in the select clause but had the same problem as doing it in the 'ThenInclude'.

Another approach I attempted was to add an extension method to do this but I got an EF 'Query could not be translated' error.

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

OrderManifestStops needs to receive and return an `IQueryable` rather than an `IEnumerable` — Eric J., Sep 09 '22 at 19:44
Perhaps this answer will be helpful https://stackoverflow.com/a/233505/7949123 — Timothy, Sep 09 '22 at 19:44
@EricJ. I tried that and that also could be translated even when prefixed with AsQueryable(). — Justin Morrison, Sep 09 '22 at 20:07
@Timothy I'm not quite sure that does what I am trying to do since I am potentially sorting on and even modifying the properties within the orderby which most of the things that I have looked at that are similar don't seem to allow. Not saying you are wrong, I am just struggling to apply the information. — Justin Morrison, Sep 09 '22 at 20:12
Why do you need such OrderBy? Each next `OrederBy` discards previous. Maybe you ned `ThenOrderBy`? — Svyatoslav Danyliv, Sep 09 '22 at 20:14
@SvyatoslavDanyliv I need multiples because I want to apply a filter param to multiple attributes of my data. If I could sort it once and get it stick I would prefer that, but I haven't been able to find a way to do that. — Justin Morrison, Sep 18 '22 at 00:58

score 0 · Answer 1 · answered Sep 10 '22 at 11:27

I guess Driver has an implementation close to that

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

You can create static field 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);

And use it, for example like this:

var drivers = new List<Driver>();


drivers.OrderBy(OrderBy);

But I doubt that it can be translated by EF

Thanks for the answer but sadly your assessment that EF can translate it is correct so it doesn't quite do what I am looking for. — Justin Morrison, Sep 18 '22 at 01:06

Convert duplicated Linq OrderBy in EF Core WHERE clause to be more readable

1 Answers1