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

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 which 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.

if the Until property is lesser than DateTime.Now, should it be part of the result set? — Matt.G, Nov 01 '19 at 14:50
Yes, they all should be returned, but if the `Until` property lesser than `DateTime.Now`, it is be ordered by it's `Id`. Meaning first all guys that has an `Until` greater than `DateTime.Now` ordered by their `At` property descending, then the rest of the guys ordered by their `Id` descending — Sugafree, Nov 01 '19 at 14:56

Grant Miles · Accepted Answer · 2019-11-01T14:58:52.010

3

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

edited Nov 01 '19 at 14:58

answered Nov 01 '19 at 14:06

Grant Miles

118
1
11

I appreciate your answer, but it is incorrect.If I run this, it will return 1,2,4,3, but `Id 1` should be ordered by it's `Id` property so the correct result would be 2,4,3,1. Also all ordering should be descending. – Sugafree Nov 01 '19 at 14:28
Ah i see the mistake. I have just replicated and tested and get 2,4,3,1. Answer is edited – Grant Miles Nov 01 '19 at 14:56
Grant, I do not see any change in the answer yet:( – Sugafree Nov 01 '19 at 14:58
Refresh now, let me know if it helps – Grant Miles Nov 01 '19 at 14:59
I do not understand why, but it works if you change the `OrderByDescending` to `OrderBy`. Otherwise I get completely wrong answer. Thanks Grant – Sugafree Nov 01 '19 at 15:10
Sorry, I have to take it back, because than the `At` ordering is incorrect. I will try it again to see if the problem is on my end, but if not, then it is still unsolved – Sugafree Nov 01 '19 at 15:12
hmmm.. curious. Sorry to waste your time. Good luck – Grant Miles Nov 01 '19 at 15:14
Grant, I am sorry, you are right. Did a simple console app and it works there. There is another issue on my end which I need to find, but the `LINQ` query is correct! Thanks – Sugafree Nov 01 '19 at 15:30

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

1 Answers1