带有COUNT和GROUP的LINQ案例声明 [英] LINQ Case statement with COUNT and GROUP

问题描述

您能帮我把Mysql转换成Linq吗?

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

谢谢

推荐答案

好，您需要查看生成的SQL，但是过滤和分组部分很简单-仅计数位特别棘手.您可能可以在小组的预测范围内做到这一点:

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

这篇关于带有COUNT和GROUP的LINQ案例声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！