LINQ OrderBy from Entity Framework only positive values first

Question

Is there a way to get products from database using Entity Framework ordered by price, but if there are some products have price equal to 0, they should be in the end, not in the beginning

Here is my request:

 result = await _products.OrderBy(p => p.Price)
                .Skip((parameters.PageNumber - 1) * parameters.PageSize) //simple pagination here
                .Take(parameters.PageSize)
                .ToListAsync();

Is it possible to change it for achieve my goal?

Answer 1

var a = new[] { 0, 1, 2, 3, 4 };
a.OrderBy(x => x > 0 ? x : int.MaxValue)

Should be translated to the same order by with case statements in SQL. Not sure about int.MaxValue maybe you will need to assign it to some variable first.

ThenBy can also work but I am not sure if your database can optimize multiple "order by" correctly. Most of RDMS will do it.

Answer 2

Order by <= 0 first. False comes first in booleans.

_products.OrderBy(p => p.Price <= 0).ThenBy(p => p.Price)