How Can I Achieve Sql Case Statement From Linq

I am new to LINQ and I would like to know if I can achieve the below SQL query from LINQ? I am using Entity Framework Core. SELECT 0 [All], [Range] = CASE WHEN Value

Solution 1:

You can use this.

from C in Calculations
join S in SampleSets on C.SampleSetID equals S.ID 
where S.SampleDrawn >= DateTime.Now.AddMonths(-3)
      && S.Department == "LOCATION A"selectnew {
    All = 1 
    , Range = 
        (C.Value >= 0 && C.Value < 25) ? "Low" :
        (C.Value >= 25 && C.Value < 75) ? "Medium" :
        (C.Value >= 75 && C.Value < 90) ? "High" :
        (C.Value >= 90 && C.Value <= 100) ? "Very High" : null
}

Solution 2:

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"selectnew { 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
     selectnew Result { All = groupedData.Sum(y => y.Value), Range = 
   groupedData.Key }).ToList();

Here is the code for the same.

publicclassProgram
    {
        publicstaticvoidMain(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"selectnew { All = 0, Range = Utilities.RangeProvider(a.Value) }).ToList();

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

        }


    }

        publicclassUtilities
        {
            publicstaticstringRangeProvider(intvalue)
            {
                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"; }
            }

        }

    publicclassResult {
      publicint All { get; set; }
      publicstring Range { get; set; }
   }

    publicclassCalculation
    {
        publicint SampleSetID { get; set; }
        publicint Value { get; set; }

    }

    publicclassSampleSet
    {
        publicint ID { get; set; }
        public DateTime SampleDrawn { get; set; }

        publicstring Department { get; set; }

    }

Solution 3:

Below is the final LINQ statement which worked for me. As Amit explain in his answer RangeProvider method will be used to replace the SQL CASE statement.

var test2 = (from a in context.Calculations
                         join b in context.SampleSets on a.SampleSetID equals b.ID
                         where b.SampleDrawn >= DateTime.Now.AddDays(-10) && b.Department == "Location A"group a byRangeProvider(a.Value) into groupedData
                         selectnew { All = groupedData.Count(), Range = groupedData.Key });