R. tumnu R. tumnu - 21 days ago 5
C# Question

LINQ Case statement with COUNT and GROUP

Can you help me translate this Mysql into Linq please :

SELECT distcode, COUNT(cid) as count_all_case
,count(case when labid = 1 then cid end) as count_id1
,count(case when labid = 2 then cid end) as count_id2
,count(case when labid = 3 then cid end) as count_id3
FROM labcase
WHERE SEQ is not NULL AND date_serv BETWEEN 20160101 AND 20161001
GROUP BY distcode


Thanks

Answer

Well, you'd need to look at the generated SQL, but the filtering and grouping parts are simple - it's only the counting bit that's particularly tricky. You can probably do that within the projection of the group:

var start = new DateTime(2016, 1, 1);
var end = new DateTime(2016, 10, 1);
var query = from labCase in db.LabCase
            where labCase.Seq != null &&
                labCase.Date >= start &&
                labCase.Date <= end
            group labCase by labCase.DistCode into g
            select new
            {
                DistCode = g.Key,
                CountId1 = g.Count(x => x.LabId == 1),
                CountId2 = g.Count(x => x.LabId == 2),
                CountId3 = g.Count(x => x.LabId == 3)
            };
Comments