Select case in LINQ

Question

how can I translate this into LINQ?

select t.age as AgeRange, count(*) as Users
from (
  select case  
    when age between 0 and 9 then ' 0-25'
    when age between 10 and 14 then '26-40'
    when age between 20 and 49 then '60-100'
    else '50+' end as age
  from user) t
group by t.age

Thank you!

possible duplicates - http://stackoverflow.com/questions/936028/linq-case-statement , http://stackoverflow.com/questions/209924/switch-statement-in-linq , http://stackoverflow.com/questions/436028/linq-to-sql-case-query , http://stackoverflow.com/questions/936028/linq-case-statement — pavanred, Nov 22 '10 at 09:53
In case anyone came across this and wondered "what is the difference between them" Only one is, maybe, duplicate was: stackoverflow.com/questions/436028/linq-to-sql-case-query and it doesn't specify ranges in the title, however it is the answer. The others are limited to case statements, but within specific scenarios. The answer marked in the actual question has nothing to do with ranges as the question specifies... so.... — user1040975, Aug 01 '17 at 19:20

score 39 · Accepted Answer · answered Nov 22 '10 at 09:57

39

Maybe this works:

from u in users
let range = (u.Age >= 0  && u.Age < 10 ? "0-25" :
             u.Age >= 10 && u.Age < 15 ? "26-40" :
             u.Age >= 15 && u.Age < 50 ? "60-100" :
            "50+")
group u by range into g
select new { g.Key, Count=g.Count() };

answered Nov 22 '10 at 09:57

Botz3000

39,020
8
103
127

2

+1 Wow this helped me in so many ways! – A1rPun Apr 15 '14 at 08:18
It helps, but I don't think the labels, such as "0-25", correspond to the conditions. – Michael Harmon Jul 20 '21 at 18:07

Pranay Rana · Answer 2 · 2010-11-22T10:02:13.207

15

check this may help you

var query = from grade in sc.StudentGrade
                        join student in sc.Person on grade.Person.PersonID
                                      equals student.PersonID
                        select new
                        {
                            FirstName = student.FirstName,
                            LastName = student.LastName,
                            Grade = grade.Grade.Value >= 4 ? "A" :
                                        grade.Grade.Value >= 3 ? "B" :
                                        grade.Grade.Value >= 2 ? "C" :
                                        grade.Grade.Value != null ? "D" : "-"
                        };

edited Nov 22 '10 at 10:02

answered Nov 22 '10 at 09:51

Pranay Rana

175,020
35
237
263

1

Copy/paste from another [answer](http://stackoverflow.com/questions/936028/linq-case-statement/936136#936136) – abatishchev Nov 22 '10 at 10:00
@abatishchev- changed I thought its good example but no probs answer is updated by another example – Pranay Rana Nov 22 '10 at 10:03

abatishchev · Answer 3 · 2010-11-22T10:16:45.717

Use something like that:

class AgeHelper
{
    private static Dictionary<IEnumerable<int>, string> dic = new Dictionary<IEnumerable<int>, string>
    {
        { Enumerable.Range(0, 10), "0-25" },
        { Enumerable.Range(10, 5), "26-40" },
        { Enumerable.Range(15, 35), "60-100" }
    };

    public string this[int age]
    {
        get
        {
            return dic.FirstOrDefault(p => p.Key.Contains(age)).Value ?? "50+";
        }
    }
}

The rest of @Botz3000's answer:

from u in users
let range = new AgeHelper()[u.Age]
...

score 4 · Answer 4 · answered Nov 22 '10 at 09:55

Something like this?

var users = (from u in Users
             select new
             {
                User = u,
                AgeRange =
                    u.Age >= 0 && u.Age <= 9 ? "0-25"  :
                    u.Age <= 14              ? "26-50" :
                    u.Age <= 49              ? "60-100":
                                               "50+"
              }).GroupBy(e => e.AgeRange);

score 0 · Answer 5 · answered Nov 22 '10 at 09:48

I don't know of any way how to create efficient SQL like this, using a LINQ statement. But you can use:

Use a stored procedure (or function), and call the stored procedure from LINQ.
Use Direct SQL

Sure you can use a lot of inline conditional statements (? :), but I don't think the result will be efficient.

Select case in LINQ

5 Answers5

Linked