Custom orderby in linq to sql

Question

I've already searched the archives but can't find a solution that works for linq to sql.

How do you create a custom orderby in linq to sql so that it generates SQL code like this

ORDER BY
CASE SEASON
  WHEN 'WINTER' THEN 1
  WHEN 'SPRING' THEN 2
  WHEN 'SUMMER' THEN 3
  WHEN 'AUTUMN' THEN 4
END

Note that custom comparators dont seem to compile and OrderByWeight as seen in this tutorial (http://www.skindog.co.uk/2009/03/18/custom-sorting-order-in-linq-order-by-weighting/) doesn't seem to exist

Note

I want the ordering to happen on the sql server and not in c# as this will give me different results since I am partitioning my results.

Answer 1

Here is an approach using a lambda expression

MyTable
   .OrderBy (t => (t.Season == "Winter") ? 1 : (t.Season == "Spring") ? 2 : [...])
   .Select (
      t => new

        { 
           MyColumn = t.MyColumn 
           ...
        }
   )