How can I achieve SQL CASE statement from LINQ

Question

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 BETWEEN 0 AND 25 THEN 'Low' 
        WHEN Value BETWEEN 25 AND 75 THEN 'Medium' 
        WHEN Value BETWEEN 75 AND 90 THEN 'High' 
        WHEN Value BETWEEN 90 AND 100 THEN 'Very High' 
    END 
FROM Result.Calculation C 
    INNER JOIN Data.SampleSet S ON C.SampleSetID = S.ID  
WHERE  S.SampleDrawn >= DATEADD(MONTH,-3,GETDATE()) AND S.Department = 'LOCATION A'

Currently, I am using FromSql as below to call a stored procedure. I would like to know whether I can do the same without using stored procedures?

var result = context.MyData.FromSql("data.GetMyData @pType = {0}, @pLocation = {1}, @pNoOfDays = {2}, @pStartDate = {3}, @pEndDate = {4}", type, location, noOfDays, startDate, endDate).ToList();

Thanks.

What have you tried already? Try posting a little code - you will get a better answer. Also posting your entity classes would help — ste-fu, Oct 04 '17 at 08:50

score 12 · Answer 1 · answered Oct 04 '17 at 10:22

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"
select new {
    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
}

Amit Kumar Singh · Accepted Answer · 2017-10-04T10:02:23.470

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; }

    }

@Amit : Thank you. That worked. Could you please also let me know how can i extend the linq statement to Group By Range column? — Geshem W, Oct 04 '17 at 09:50
@Amit: No worries i figured that out. Thanks guys for your help. — Geshem W, Oct 04 '17 at 10:03
I literally use the same code. And I'm getting "translation of method failed" error. What's the secret here? — Alexander, Jun 08 '23 at 20:53

score 0 · Answer 3 · answered Oct 04 '17 at 10:41

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 by RangeProvider(a.Value) into groupedData
                         select new { All = groupedData.Count(), Range = groupedData.Key });

score 0 · Answer 4 · answered Apr 06 '23 at 14:57

You can use this custom expression builder method

public static Expression<Func<TEntity, TResult>> SwitchCase<TEntity, TField, TResult>(string fieldName, List<(TField Value, TResult Result)> conditions, TResult defaultValue)
           where TEntity : EntityBase
        {
            // Create parameter expression for TEntity
            var entityParameter = Expression.Parameter(typeof(TEntity), "x");

            // Create expression to extract field property from TEntity
            var fieldExpr = Expression.Convert(Expression.Property(entityParameter, fieldName), typeof(TField));

            // Define default expression for when no conditions match
            var defaultExpr = Expression.Constant(defaultValue);

            // Build conditional expressions by nesting Expression.Condition

            //example x.Status == 0 ? "New" : (x.Status == 1 ? "Active" : "default")
            Expression conditionExpr = defaultExpr;
            for (int i = conditions.Count - 1; i >= 0; i--)
            {
                var mapping = conditions[i];
                var testExpr = Expression.Equal(fieldExpr, Expression.Constant(mapping.Value));
                conditionExpr = Expression.Condition(testExpr, Expression.Constant(mapping.Result), conditionExpr);
            }

            // Create lambda expression for use in LINQ query
            var lambdaExpr = Expression.Lambda<Func<TEntity, TResult>>(conditionExpr, entityParameter);

            return lambdaExpr;
        }

usage:

var mappings = new List<(int Number, string Name)>
{
     (0, "New"),
     (1, "Active"),
     (2, "Closed")
};

var result = await context.Entities.OrderBy(CustomExpressionsBuilder.SwitchCase<Entity, int, string>("Status", mappings, "default")).ToListAsync()

How can I achieve SQL CASE statement from LINQ

4 Answers4

Linked