Question:

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

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.DateServ >= start && labCase.DateServ <= 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) };

Here is the Lambda Query

var query = db.labcase .Where(p => p.SEQ != null && p.date_serv >= new Datetime(2016,01,01) && p.date_serv <= new Datetime(2016,01,10)) .GroupBy(f => new {distcode} , (key , elem) => new { key.distcode ,count_id1 = elem.Count(x => x.LabId == 1) ,count_id2 = elem.Count(x => x.LabId == 2) ,count_id3 = elem.Count(x => x.LabId == 3) })