Custom orderby in linq to sql

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.

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 
           ...
        }
   )

You must select your data first in an inner loop like you did, then select data from that result and sort normally on your custom column:

(off my head)

from s in
  (from x in y select new { Sort = x.z == 'WINTER' ? 1 : x.z == 'SPRING' ? 2 : [...], Data = x })
orderby s.Sort
select s.Data

Something like that

Hope this helps