Linq Case Statement

linq case statement

If its just the CASE statement in LINQ your after (read your comment) then an example of this is...

Int32[] numbers = new Int32[] { 1, 2, 1, 3, 1, 5, 3, 1 };

var numberText =
(
    from n in numbers
    where n > 0
    select new
    {
        Number = n,
        Text = 
        (
            n == 1 ? "One" :
            n == 2 ? "Two" :
            n == 3 ? "Three" : "Unknown"
        )
    }
);

How can I achieve SQL CASE statement from LINQ

You can use this if it suits you.
I would just explain the LINQ query part.
You can use this with EF. I created dummy data for these.
For EF, use IQueryable instead.

// from a row in first table
// join a row in second table
// on a.Criteria equal to b.Criteria
// where additional conditions
// select the records into these two fields called All and Range
// Convert the result set to list.
var query = (from a in lstCalc
join b in lstSampleSet
on a.SampleSetID equals b.ID where b.SampleDrawn >= DateTime.Now.AddMonths(-8)
&& b.Department == "Location A"
select new { All = 0, Range = Utilities.RangeProvider(a.Value) }).ToList();

EDIT : LINQ Query for grouped result.. Make sure you are using IQueryable.

 var query = (from a in lstCalc
  join b in lstSampleSet
  on a.SampleSetID equals b.ID where b.SampleDrawn >= DateTime.Now.AddMonths(-8) 
   && b.Department == "Location A"
    group a by Utilities.RangeProvider(a.Value) into groupedData
     select new Result { All = groupedData.Sum(y => y.Value), Range = 
   groupedData.Key }).ToList();

Here is the code for the same.

 public class Program
    {
        public static void Main(string[] args) {
            List<Calculation> lstCalc = new List<Calculation>();
            lstCalc.Add(new Calculation() {SampleSetID=1, Value=10 });
            lstCalc.Add(new Calculation() { SampleSetID = 1, Value = 10 });
            lstCalc.Add(new Calculation() { SampleSetID = 2, Value = 20 });
            lstCalc.Add(new Calculation() { SampleSetID = 3, Value = 30 });
            lstCalc.Add(new Calculation() { SampleSetID = 4, Value = 40 });
            lstCalc.Add(new Calculation() { SampleSetID = 5, Value = 50 });
            lstCalc.Add(new Calculation() { SampleSetID = 6, Value = 60 });
            lstCalc.Add(new Calculation() { SampleSetID = 7, Value = 70 });
            lstCalc.Add(new Calculation() { SampleSetID = 8, Value = 80 });
            lstCalc.Add(new Calculation() { SampleSetID = 9, Value = 90 });

            List<SampleSet> lstSampleSet = new List<SampleSet>();
            lstSampleSet.Add(new SampleSet() {Department = "Location A", ID=1, SampleDrawn=DateTime.Now.AddMonths(-5)});
            lstSampleSet.Add(new SampleSet() { Department = "Location A", ID = 2, SampleDrawn = DateTime.Now.AddMonths(-4) });
            lstSampleSet.Add(new SampleSet() { Department = "Location A", ID = 3, SampleDrawn = DateTime.Now.AddMonths(-3) });
            lstSampleSet.Add(new SampleSet() { Department = "Location A", ID = 4, SampleDrawn = DateTime.Now.AddMonths(-2) });
            lstSampleSet.Add(new SampleSet() { Department = "Location A", ID = 5, SampleDrawn = DateTime.Now.AddMonths(-2) });
            lstSampleSet.Add(new SampleSet() { Department = "Location A", ID = 6, SampleDrawn = DateTime.Now.AddMonths(-2) });
            lstSampleSet.Add(new SampleSet() { Department = "Location A", ID = 7, SampleDrawn = DateTime.Now.AddMonths(-1) });

            var query = (from a in lstCalc
                        join b in lstSampleSet
                        on a.SampleSetID equals b.ID where b.SampleDrawn >= DateTime.Now.AddMonths(-8)
                         && b.Department == "Location A"
                        select new { All = 0, Range = Utilities.RangeProvider(a.Value) }).ToList();

            Console.WriteLine(query.Count);
            Console.ReadLine();

        }


    }

        public class Utilities
        {
            public static string RangeProvider(int value)
            {
                if (value > 0 && value <= 25)
                { return "Low"; }
                if (value > 25 && value <= 75)
                { return "Medium"; }
                if (value > 75 && value <= 90)
                { return "High"; }
                else
                { return "Very High"; }
            }

        }

    public class Result {
      public int All { get; set; }
      public string Range { get; set; }
   }

    public class Calculation
    {
        public int SampleSetID { get; set; }
        public int Value { get; set; }

    }

    public class SampleSet
    {
        public int ID { get; set; }
        public DateTime SampleDrawn { get; set; }

        public string Department { get; set; }

    }

SQL to LINQ - Case Statement

Try using ternary operator for all cases in your ThenBy clause:

 .ThenBy(x => x.column3 == "X" 
      ? 1 
      : x.column3 == "Y" 
          ? 2 
          : x.column3 == "Z" 
                ? 3 
                : defaultOrder)

Linq Expression in Case Statement

If you query against a database, you can commit the query first and then apply the compiled e1:

  var e1Compiled = Expression.Lambda<Func<MyTbl,bool>>(e1, peTbl).Compile();
  var query = myTbl
                .Where(whereCondition).ToList()
                .Select(s => new { mytbl = s, mycase = (e1Compiled(s) ? 1 : 0) });

if there is no database, just use the compiled e1:

  var query = myTbl
                .Where(whereCondition)
                .Select(s => new { mytbl = s, mycase = (e1Compiled(s) ? 1 : 0) });