Can you use a CASE statement with OrderBy in an LINQ to Entities query?

Question

I'm wonder if someone can transform the SQL below to a LINQ to Entities query

SELECT Name, IsEmployee, IsQualityNetwork
FROM Person
ORDER BY CASE WHEN IsQualityNetwork = 1 or IsEmployee = 1 THEN 0 ELSE 1 END, Name

I tried using Linq Dynamic but when this code is executed:

var p = ctx.People
    .OrderBy("CASE WHEN IsQualityNetwork = 1 or IsEmployee = 1 THEN 0 ELSE 1 END")
    .OrderBy(e => e.Name);

I get the exception: {"No property or field 'CASE' exists in type 'Person'"}

Answer 1

var p = ctx.People.OrderBy(p => (p.IsQualityNetwork == 1 || p.IsEmployee == 1) ? 0 : 1)
                  .ThenBy(p => p.Name);

Answer 2

Here's a translation of your SQL to LINQ.

var query = from p in ctx.People
            let order = p.IsQualityNetwork || p.IsEmployee ? 0 : 1
            orderby order, p.Name
            select new
            {
                p.Name,
                p.IsEmployee,
                p.IsQualityNetwork,
            }

I've used the fluent query syntax so I could show you the let keyword. let allows you to declare a range variable that can then be reused in your query, this can be very useful if you've got a conditional that gets used in a lot of places, or if you need to chain multiple conditionals.