C# Sql to Linq checking multiple cases

Question

How to check multiple cases in Linq ( to classify "over paid","under paid","medium pay")

Sql

select id,name,salary,
  case  when salary <=1500 then 'under paid'
        when salary >=3500 then 'over paid'
        else 'medium pay' end as status 
from Person

Linq

   var q =
          context.Persons.
          Select(c => 
          new { 
               EmployeeID = c.Id,
               EmployeeName = c.name,
               EmployeeSalary = c.salary,
               Status = c.salary > 1000 ? "under paid" : "overpaid"
              }
            );

Using ternary operator i can check either or cases.otherwise i have to use if..else if.

Answer 1

You can nest your ternary operator statements and LINQ will convert it into a CASE statement: http://lancefisher.net/blog/archive/2008/05/07/linq-to-sql---case-statements.aspx

Answer 2

Status = c.salary < 1000 ? "under paid" : 
         c.salary < 2000 ? "medium paid" :
         c.salary < 3000 ? "not bad paid" :
         "overpaid";

the above essentially means:

If salary is under 1k then return "under paid" else if salary is under 2k then return "medium paid" else if salary is under 3k etc etc

You can stack ternary operators like this. The first one that evaluates to true will be returned and the rest ignored.

Or, just call a friggen method.

new { Status = GetPaymentStatus(c.salary) }

You can call methods from within a linq query.

Answer 3

I would just write a method and call that from within the selection area.

var q = context.Persons.Select(c => GetStatus(c.salary));