LINQ - Complex Sorting

I am querying a table of Order elements with LINQ (C#). Each Order has the following fields:

- ID
- OpenDate
- PriorityID
- StatusID
- Description

The StatusID field maps to a Status table. The Status table is structured as:

- ID
- Name

I need to get all of the Order objects sorted by their Priority and Status. I can successfully get the Order objects sorted by Priority. I'm doing this via the following:

List<Order> orders = new List<Order>();
using (DBDataContext context = new DBDataContext())
{
  orders = (from o in context.Orders
            orderby (o.PriorityID.HasValue ? o.PriorityID : Int32.MaxValue)  ascending
            select o).ToList();
}

But my problem is factoring in the Status.

Once the order objects have been sorted by priority, I need to sort the Order objects in the following order of Status: Cancelled, Open, In-Route, and Delivered. Significantly, The IDs of these Status values are firmly set in a random, non-helpful order. I cannot alter them. As you can tell, I can't sort the status by alphbetical name either. In a开发者_StackOverflowddition, I can't add any fields to my database. Can anyone tell me how I can solve this problem in LINQ?

Thank you!

I think you have solution to this by implement IComparer and use Linq to order it. Since your Status is not in numeric nor alphabet order.

public class CustomComparer : IComparer<Status>
{
     public int Compare(Status statusA, Status statusB)
     {
       if (statusA.StatusName == "Cancelled" && statusB.StatusName == "Cancelled")
       {
          return 0; // equals
       } 
       else if (statusA.StatusName == "Cancelled" && statusB.StatusName != "Cancelled")
       {
          return 1; // A > B
       }
       ....
     }
}

then

orders.OrderBy(x => x.Status, new CustomComparer())

Hope this helps.

As long as the list of statuses are going to be consistent, you could create a string such as "COID", and compare two statuses based on "COID".indexOf(firstletterofstatus). Maybe not the best software practice, but it would work.

If it's not going to a long list (i.e. it will reasonably fit in memory), you could sort client side with a mapping between statuses and relative orders:

int StatusOrder(Status status) 
{
    switch(status.Id)
    {
       case 1: return 5;
       case 2: return 1;
       case 4: return 3;
       //etc
    }
}

List<Order> orders; //no need to create a list here
using (DBDataContext context = new DBDataContext())
{
    orders = (from o in context.Orders
        orderby (o.PriorityID.HasValue ? o.PriorityID : Int32.MaxValue)  ascending,
                SorderOrder(o.Status)
        select o).ToList();
}