Custom orderby in linq to sql

Question

Hi

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.daniel-skinner.co.uk/custom-sorting-order-in-linq-order-by-weighting/18/03/2009) 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

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