如何用LinQ实现多表JOIN、GROUP与DISTINCT COUNT操作？

数据库表结构 #

create table project_model(
  id int auto_increment primary key,
  name varchar(100) not null
);

create table project_model_phase(
  id int auto_increment primary key,
  mid int not null,
  name varchar(100) not null,
  foreign key(mid) references project_model(id)
);

create table project_model_procedure(
  id int auto_increment primary key,
  pid int not null,
  foreign key(pid) references project_model_phase(id)
);

测试数据 #

insert into project_model value (null, 'model-1');
insert into project_model_phase values (null, 1, 'phase-1'), (null, 1, 'phase-2');
insert into project_model_procedure values
  (null, 1, 'procedure-1'),
  (null, 1, 'procedure-2'),
  (null, 2, 'procedure-3'),
  (null, 2, 'procedure-4');

期望查询结果 #

+----+--------------------+-------------+-----------------+
| id | name               | phase-count | procedure-count |
+----+--------------------+-------------+-----------------+
|  1 | model-1            |           2 |               4 |
+----+--------------------+-------------+-----------------+

已实现的SQL查询 #

以下SQL可直接得到期望结果：

select m.id, m.name, count(distinct p.name) as `phase-count`, count(pp.id) as `procedure-count` 
from project_model m 
join project_model_phase p on m.id = p.mid 
join project_model_procedure pp on pp.pid = p.id 
group by m.id, m.name;

当前低效的LinQ实现 #

目前采用多次查询拼接的方式，效率较低：

var query =
    from n in context.ProjectModels
    join p in context.ProjectModelPhases
    on n.Id equals p.Mid
    group n by n.Id into g
    select new { id = g.Key, c = g.Count() };

var query2 =
    from n in context.ProjectModels
    join p in context.ProjectModelPhases
    on n.Id equals p.Mid
    join pp in context.ProjectModelProcedures
    on p.Id equals pp.Pid
    group n by n.Id into g
    select new { id = g.Key, c = g.Count() };

var query3 =
    (from n in context.ProjectModels
     join p in query on n.Id equals p.id
     join pp in query2 on n.Id equals pp.id
     select new {n.Id, n.Name, cp = p.c, cpp = pp.c })
     .ToList();

高效LinQ实现 #

对应的高效LinQ语句可直接关联三张表，通过一次分组完成统计，逻辑与SQL完全一致，仅需一次数据库请求：

var efficientQuery =
    from m in context.ProjectModels
    join p in context.ProjectModelPhases on m.Id equals p.Mid
    join pp in context.ProjectModelProcedures on p.Id equals pp.Pid
    group new { p, pp } by new { m.Id, m.Name } into g
    select new
    {
        Id = g.Key.Id,
        Name = g.Key.Name,
        PhaseCount = g.Select(x => x.p.Name).Distinct().Count(),
        ProcedureCount = g.Count()
    };

var result = efficientQuery.ToList();

说明 #

• 该LinQ会被EF Core翻译成与你提供的SQL逻辑一致的查询语句，仅执行一次数据库请求
• Distinct().Count()对应SQL中的count(distinct p.name)，用于统计不重复的阶段数量
• 直接使用g.Count()对应SQL中的count(pp.id)，因为每个流程记录都会关联到阶段和模型，统计结果即为流程总数

内容的提问来源于stack exchange，提问作者user2182284