I ran into a situation recently that took me some time to work out and thought I’d document here. I have an older ASP.NET Web Forms application I help maintain. Some upgrades and changes to the workflow used by the customer had meant a few assumptions I’d made no longer applied.
The biggest of these was that a list of values no longer returned from the database the way I’d assumed before. Here is the Linq to Sql that pulled the values to that point.
1: var plans = from m in dc.MenuPlans
2: where m.client == CurrentClient && m.year == year &&
3: m.month == month && m.day == day
4: select m;
As you might guess from this code, it pulls a set of menu plans from a database for a client. Each menu plan is specific to a day. What you can’t see here is that each meal has a meal name that is simply “Breakfast”, “Lunch”, “Dinner”, or “Snack”. Before the meals had been entered in that order and the creation method ensured they showed up in the order being entered.
Now they were being entered in a different order by multiple people and the order of creation no longer worked. The desired order was still breakfast, lunch, dinner, and then snack at the end. Sorting simply by the column wouldn’t work as that would produce an alphabetical order resulting in breakfast, dinner, lunch, and then snack. Close, but not quite.
What I needed was a custom ordering sequence. I could pull the items over in four groups and then append them to a final list, but that seemed messy and slow. I wanted a solution to do so at the database and not have to bring the elements in and sort in memory. I finally worked out a nice solution with this code.
1: var plans = from m in dc.MenuPlans
2: where m.client == CurrentClient && m.year == year &&
3: m.month == month && m.day == day
4: orderby m.mealname == "Breakfast" ? 1 :
5: m.mealname == "Lunch" ? 2 :
6: m.mealname == "Dinner" ? 3 : 4
7: select m;
The new code lies on lines 4-6. What I do is compare the element that I want to sort by to the values in the order I wish things to show. I’m using the binary operator here. If you’re not familiar with it, this works like a compact if/then statement. The binary operator:
1: return x > 0 ? 0 : 1
Is equivalent to the following if/then statement.
1: if(x > 0)
2: return 0;
3: else
4: return 1
So the code lets me map the string values to numeric values arbitrarily. Breakfast maps to 1, Lunch maps to 2, Dinner maps to 3, and any other value to 4. Since the result of this is a set of integers, the ordering works the way I want.
While the code looks a bit messy, it translates nicely to SQL that runs on the database server through a CASE statement and I get the order I want without any extra processing in the web application.