Convert Age Range SQL to Linq Query

Question

Can anyone help me to convert this SQL Query to Linq Query. I tried with different ways. but still I'm not able to to it.

SELECT  ISNULL(SUM(CASE WHEN Age < 10 THEN 1 ELSE 0 END), 0) AS Between0to10,
        ISNULL(SUM(CASE WHEN Age BETWEEN 11 AND 20 THEN 1 ELSE 0 END), 0) AS Between11to20,
        ISNULL(SUM(CASE WHEN Age BETWEEN 21 AND 30 THEN 1 ELSE 0 END), 0) AS Between21to30,
        ISNULL(SUM(CASE WHEN Age BETWEEN 31 AND 40 THEN 1 ELSE 0 END), 0) AS Between31to40,
        ISNULL(SUM(CASE WHEN Age BETWEEN 41 AND 50 THEN 1 ELSE 0 END), 0) AS Between41to50,
        ISNULL(SUM(CASE WHEN Age BETWEEN 51 AND 60 THEN 1 ELSE 0 END), 0) AS Between51to60,
        ISNULL(SUM(CASE WHEN Age BETWEEN 61 AND 70 THEN 1 ELSE 0 END), 0) AS Between61to70,
        ISNULL(SUM(CASE WHEN Age BETWEEN 71 AND 80 THEN 1 ELSE 0 END), 0) AS Between71to80,
        ISNULL(SUM(CASE WHEN Age BETWEEN 81 AND 90 THEN 1 ELSE 0 END), 0) AS Between81to90,
        ISNULL(SUM(CASE WHEN Age BETWEEN 91 AND 100 THEN 1 ELSE 0 END), 0) AS Between91to100
FROM    CovidMaster

See: [C# Sql to Linq checking multiple cases](https://stackoverflow.com/questions/1888340/c-sharp-sql-to-linq-checking-multiple-cases) — Craig Gers, Nov 23 '20 at 08:59
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you, especially step 12. Also, you really need to specify if you are using LINQ to SQL / EF 6.x / EF Core 2.0 / 2.1 / 3.x — NetMage, Nov 23 '20 at 23:16

score 1 · Accepted Answer · answered Nov 24 '20 at 09:56

try this query

     var result=(from item in data
                group item by true into groupData
                select new 
                {
                    Between0to10=groupData.Where(x=>x.Age >= 0 &&  x.Age <= 10).Count(),
                    Between11to20=groupData.Where(x=>x.Age >= 11 &&  x.Age <= 20).Count(),
                    Between21to30=groupData.Where(x=>x.Age >= 21 &&  x.Age <= 30).Count(),
                    Between31to40=groupData.Where(x=>x.Age >= 31 &&  x.Age <= 40).Count(),
                    Between41to50=groupData.Where(x=>x.Age >= 41 &&  x.Age <= 50).Count(),
                    Between51to60=groupData.Where(x=>x.Age >= 51 &&  x.Age <= 60).Count(),
                    Between61to70=groupData.Where(x=>x.Age >= 61 &&  x.Age <= 70).Count(),
                    Between71to80=groupData.Where(x=>x.Age >= 71 &&  x.Age <= 80).Count(),
                    Between81to90=groupData.Where(x=>x.Age >= 81 &&  x.Age <= 90).Count(),
                    Between91to100=groupData.Where(x=>x.Age >= 91 &&  x.Age <= 100).Count(),
                    
                }).ToList();

for more detail refer to this link https://dotnetfiddle.net/u0uUyH

Convert Age Range SQL to Linq Query

1 Answers1