EF Core 3.0 conditional order by in LINQ when the condition is validated in DB

c# entity-framework-core linq

Question

The following is a fairly simple example of the issue that I need to solve. I am using EF Core 3.0 and with LINQ, I need to get all the guys from the database in a specific order. First I need all guys where it has a Feature class and the Until property is bigger than DateTime.Now ordered by the At property descending and then ordered by the Id property from the Guy class descending.

public class Guy
{
    public int Id {get;set;}
    public Feature Feature {get;set;}
}
public class Feature
{
    public int Id {get;set;}
    public DateTime? At {get;set;}
    public DateTime? Until {get;set;}
}

_dbcontext.Guys.Add(new Guy{Id = 1, Feature = new Feature {At = DateTime.Now.AddDays(-1), Until = DateTime.Now.AddDays(7)}})
_dbcontext.Guys.Add(new Guy{Id = 2, Feature = new Feature {At = DateTime.Now.AddDays(3), Until = DateTime.Now.AddDays(7)}})
_dbcontext.Guys.Add(new Guy{Id = 3})
_dbcontext.Guys.Add(new Guy{Id = 4})

Example _dbcontext.Guys.Include(x => x.Feature).OrderByDescending(x => x.Feature.Until > DateTime.Now ? x.Feature.At : DateTime.Now).ThenByDescending(x => x.Id).ToList(); should return Guy with Id 1 first as Feature.At property is greater than DateTime.Now and the rest should be sorted by the Id property descending. Id 4, Id 3 then Id 1.

Unfortunately, this example sort of works, but the issue is, first it will return the ones wich has no Feature class (ordered descending by the Id property), then the ones which has a Feature class where the At is greater than DateTime.Now. Again these are ordered by the Until property correctly, but these should be on top.

Any help will be much appreciated.

1
2
11/1/2019 1:45:08 PM

Accepted Answer

See LINQ order by null column where order is ascending and nulls should be last

Add a condition in to filter by if the value is null or not. See the above post for example.

_dbcontext.Guys
.Include(x => x.Feature)
.OrderByDescending(x => x.Feature != null)
.ThenBy(x => x.Feature.Until > DateTime.Now ? x.Feature.At : DateTime.Now)
.ThenByDescending(x => x.Id).ToList();

Edit:

_dbcontext.Guys
.Include(x => x.Feature)
.OrderByDescending(x => x.Feature!= null && x.Feature.Until > DateTime.Now ? x.Feature.At : DateTime.Now)
.ThenByDescending(x => x.Id).ToList();

I ran this in a console app and it came out 2,4,3,1

1
11/1/2019 2:58:52 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow