Bill Morefield My thoughts, discoveries, and occasional rambiings.

January 15, 2013

Arbitrary Sorting Order in Linq To SQL

Filed under: c#,development — Tags: , , — Bill Morefield @ 3:22 pm

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 && == 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 && == 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.

December 30, 2011

Moving Web Servers

Filed under: aspnet,web — Tags: , , , , — Bill Morefield @ 9:08 am

A few notes from the recent move of about ten web sites from one server to another.


Plesk is a nice tool for managing web sites, but I’ve found the migration tool to be so quirky as to be useless.  Last time I migrated servers I spent more time migrating than manually moving them would have taken.  This time, I spent about two hours fighting the migration tool before doing a simple backup/restore through Plesk.  Due to space limitations of old server I couldn’t migrate content through backup/restore, but a simple FTP took care of that.  Worked much more smoothly and I had everything done in about four hours total.

Migrating Databases

There needs to be an easier way to move a database from one site to another.  That took much of the time of the move and in the end I again found just moving the actual files to be the easiest method.

Logins also cause problems when you backup/restore or move the database file.  The login is server level, but the details reside in the database.  When you restore or attach, the two aren’t automatically connected.  You can either delete the user in the database and recreate (not always possible or desirable) or use the sp_change_users_login stored procedure to link the two.  I know that stored procedure is deprecated, but old habits die hard.  The replacement is the ALTER USER command, but the stored procedure still works in Microsoft SQL 2008.

Showing ASP Errors under Windows 2008

ASP still lives.  Two of the sites run, and work quite well, as ASP pages.  Out of the box Windows 2008 doesn’t provide much useful information for errors.  You can fix that using two steps of the web site:

  1. Set Site –> ASP –> Debugging –> Send Errors to Browser to True and click Apply
  2. Set Site –> Error Pages –> 500 –> Edit Feature Settings to Detailed Errors if you need to be able to troubleshoot from a remote connection.  The default only sends the detailed error to local connections.
  3. Change back if desired after the problems are resolved.

ASP Parent Paths under Windows 2008

A lot of ASP pages use parent paths and these are not turned on by default in Windows 2008.  This can be changed under the Virtual Application Settings –> Allow Parent Paths.

ASP.NET Migration

Always check the version of .NET the application is running under.  Most problems come back to this.  Next check any database connection string point to the correct server.  About 90% of errors after a move are one of those two items.

Update – SQL 2008 Issue

Found an issue I’d not encountered before when installing SQL 2008 Express R2.  It installed with dynamic ports by default.  Likely related to the fact I installed SQL using the web platform and not installing directly.  For info and changes to fix see

Powered by WordPress