15798

LINQ - Complex Sorting

Question:

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 addition, I can't add any fields to my database. Can anyone tell me how I can solve this problem in LINQ?

Thank you!

Answer1:

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.

Answer2:

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.

Answer3:

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(); }

Recommend

  • Change background of Div from select
  • iOS/Swift: in which function between viewDidLoad and viewWillAppear am I supposed to query a databas
  • How to refactor a function that throws exceptions?
  • Selecting and check if user exist in table without foreach in laravel blade
  • Date of max id: sql/oracle optimization
  • Why java statement evaluation is happening like these ?
  • Ruby Mixins and Instance variables
  • Delete object in core data, failed to match the Swift Array Element type
  • Ordering a Union Query in MS Access SQL
  • Implement a writable serializer for multilevel nested relationships in django rest framework
  • SUM and GROUP BY in xquery with 1 xml file
  • ObjectMaterialize in EF not firing on first level query
  • In explicit LINQ-to-SQL (C#) does order matter?
  • How does Skobbler prioritize which POIs to show?
  • How to sort things out in ListView?
  • Why is it still possible to insert a foreign key that doesn't exist?
  • How to bind comma separated list of values to List
  • Crafting a LINQ based solution to determine if a set of predicates are satisfied for a pair of colle
  • Log4j cannot find properties file in a simple application
  • Sum data table columns using linq
  • Stitching 2 images (OpenCV)
  • Count from each distinct date, fill in missing dates with zero
  • NHibernate manually control fetching
  • Elasticsearch script query involving root and nested values
  • Why use database factory in asp.net mvc?
  • How do I configure context broker accept post requests from my remote sensor?
  • Sort List of Strings By Version
  • Breeze - Deleted Items nav properties bug
  • javaw.exe and eclipse startup problems
  • Javascript Callbacks with Object constructor
  • Join two tables and save into third-sql
  • How to model a transition system with SPIN
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • How do you join a server to an Active Directory (domain)?
  • need help with bizarre java.net.HttpURLConnection behavior
  • How does Linux kernel interrupt the application?
  • Converting MP3 duration time