How to sort List using custom column values in linq?

Question

Net core, Ef core and linq. I have table with column status. It holds the values New, In-Progress and closed. When I will query all the rows with New should come first followed by in-progress and closed. Below is my code.

var Requests = await this.RequestRepository.GetAsync(x => x.IsActive == true && x.CreatedBy == LoggedInUser, null, x => x.Country).ConfigureAwait(false);

Below is my GetAsync method

public async Task<IEnumerable<T>> GetAsync(Expression<Func<T, bool>> filter = null, Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null, params Expression<Func<T, object>>[] includes)
        {
            IQueryable<T> query = this.dbSet;
            foreach (Expression<Func<T, object>> include in includes)
            {
                query = query.Include(include);
            }

            if (filter != null)
            {
                query = query.Where(filter);
            }

            if (orderBy != null)
            {
                query = orderBy(query);
            }

            return await query.ToListAsync().ConfigureAwait(false);
        } 

So I am expecting all the rows with status New should come first then In-Progress and closed. I am not finding anyway to figure it out. Can someone help me to write this. Any help would be appreciated. Thank you

Answer 1

Your life would probably be a lot easier if you throw this "helper" method away and just use LINQ straight. Look how much more obviously readable it is:

var apps = db.FundingApplications
  .Include(x => x.Applicant)
  .Where(x => x.ApplicationDate.Year == DateTime.Now.Year)
  .OrderBy(x => (int)x.Status)

(I skipped the await/async for brevity, not because I'm advocating sync)

Status of course being an enum like:

enum Status{
  New, Inprogress, Closed
}

If your enum is awkwardly NOT in numeric order like this but is instead eg

enum Status {
  New = 20,
  InProgress = 1,
  Closed = 99
}

Then you could order it by descending after you ToString it (you'll notice the names are in reverse alphabet order) or create a mapping using a dictionary that maps eg d[New] = 0, d[InProgress] = 1 ..., or do an (in-line) if set in the OrderBy.

OrderByDescending(x => x.Status.ToString("g"));

var d = new Dictionary<status, int>() {
  {New, 0}, {InProg,1}, {Closed,2}
};
OrderBy(x => d[x.Status]);

OrderBy(x => x.Status == Status.New ? 0 : (x.Status == Status.InProg ? 1 : 2))

Only the last of these has any real chance of being executed on the server though, and ordering by its name will only work If you don't rename them or add new names that break the ordering.

I do also appreciate the OrderBy/ThenBy given in the other answer and think it's good but I think care should be given to testing whether it executed on the server or the client because it carries out a boolean compare and a lot of db don't use booleans as a data type or if they do they might sort falsy (0) ahead of truey (1)

OrderBy(x => x.Status == New)

Might just be then that it sorts all closed and InProg ahead of New because the result of the boolean if evaluated by the server is 0 for InProg/closed and 1 for new. MySql and Postgres would be my top picks for worrying that this would be server evaluated because they do allow boolean type values in contexts other dbs don't.

So, be careful that a) this kind of ordering is evaluating where you expect and want (client/server) and b) if it really is evaluating on the server that the server orders it as you want

---

Edit, just saw your comment that indicates that Status isn't an enum but some set of string constants??

If your DB values are literally string and status was never an enum then you can just order them by descending

OrderByDescending(x => x.Status)

Answer 2

I have made a sample code according to your requirement. It's working fine on mine. Please refer to this. Hope this will work on your code too.

using System;
using System.Collections.Generic;
using System.Linq;

namespace Test
{
    public class Program
    {
        public static void Main(string[] args)
        {
            List<Name> list = new List<Name>
            {
                new Name { FirstName = "Nishan1", LastName = "Dhungana1", Status = Status.Closed},
                new Name { FirstName = "Nishan2", LastName = "Dhungana2", Status = Status.New},
                new Name { FirstName = "Nishan3", LastName = "Dhungana3", Status = Status.New},
                new Name { FirstName = "Nishan4", LastName = "Dhungana4", Status = Status.Closed},
                new Name { FirstName = "Nishan5", LastName = "Dhungana5", Status = Status.Closed},
                new Name { FirstName = "Nishan6", LastName = "Dhungana6", Status = Status.InProgress},
                new Name { FirstName = "Nishan7", LastName = "Dhungana7", Status = Status.Closed},
                new Name { FirstName = "Nishan8", LastName = "Dhungana8", Status = Status.InProgress},
                new Name { FirstName = "Nishan9", LastName = "Dhungana9", Status = Status.InProgress},
                new Name { FirstName = "Nishan10", LastName = "Dhungana10", Status = Status.New},
                new Name { FirstName = "Nishan11", LastName = "Dhungana11", Status = Status.New}
            };

            list = list.OrderByDescending(x => x.Status == Status.New)
                .ThenByDescending(x => x.Status == Status.InProgress)
                .ThenByDescending(x => x.Status == Status.Closed)
                .ToList();

            Console.WriteLine("hey");
            Console.ReadLine();
        }
    }

    public class Name
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public Status Status { get; set; }
    }

    public enum Status
    {
        New = 1,
        InProgress = 2,
        Closed = 3
    }
}