SQL及LinQ实现：查询Finca并统计关联子表数据量

假设三张表关联规则为：TableA与TableB通过外键关联（例：TableA.id = TableB.a_id），TableB与TableC通过外键关联（例：TableB.id = TableC.b_id）。要查询TableA全量数据，同时统计每条记录关联的TableB数量、间接关联的TableC数量，可通过LEFT JOIN结合COUNT(DISTINCT)避免重复计数：

SELECT 
    a.*,
    COUNT(DISTINCT b.id) AS TableBCount,
    COUNT(DISTINCT c.id) AS TableCCount
FROM TableA a
LEFT JOIN TableB b ON a.id = b.a_id
LEFT JOIN TableC c ON b.id = c.b_id
GROUP BY a.id, a.col1, a.col2, -- 需列出TableA所有非聚合字段
         a.colN; -- 按主键+全部非聚合字段分组（不同数据库语法略有差异）

• LEFT JOIN保证无关联数据时TableA记录仍保留，统计数为0
• COUNT(DISTINCT)避免多表关联产生笛卡尔积导致的计数膨胀
• 分组需包含TableA所有非聚合字段，符合标准SQL分组规则

假设核心实体结构如下：

public class Finca
{
    public int ConcesionId { get; set; }
    public int PlanificacionId { get; set; }
    public int AcometidaId { get; set; }
    // 其他字段
}

public class Puntosuministroview
{
    public int ConcesionId { get; set; }
    public int PlanificacionId { get; set; }
    public int AcometidaId { get; set; }
    public int PsId { get; set; }
    // 其他字段
}

public class Contrato
{
    public int PsId { get; set; }
    // 其他字段
}

方式1：分组关联查询 #

var result = from f in dbContext.Fincas
             join psv in dbContext.Puntosuministroviews
                 on new { f.ConcesionId, f.PlanificacionId, f.AcometidaId } 
                 equals new { psv.ConcesionId, psv.PlanificacionId, psv.AcometidaId }
                 into psvGroup
             from psv in psvGroup.DefaultIfEmpty()
             join c in dbContext.Contratos
                 on psv.PsId equals c.PsId into cGroup
             from c in cGroup.DefaultIfEmpty()
             group new { psv, c } by f into grouped
             select new 
             {
                 Finca = grouped.Key,
                 PuntosuministroviewCount = grouped.Select(x => x.psv).Distinct().Count(),
                 ContratoCount = grouped.Select(x => x.c).Distinct().Count()
             };

方式2：子查询（逻辑更直观） #

var result = from f in dbContext.Fincas
             select new
             {
                 Finca = f,
                 PuntosuministroviewCount = dbContext.Puntosuministroviews
                     .Count(psv => psv.ConcesionId == f.ConcesionId 
                                 && psv.PlanificacionId == f.PlanificacionId 
                                 && psv.AcometidaId == f.AcometidaId),
                 ContratoCount = dbContext.Contratos
                     .Count(c => dbContext.Puntosuministroviews
                         .Any(psv => psv.ConcesionId == f.ConcesionId 
                                    && psv.PlanificacionId == f.PlanificacionId 
                                    && psv.AcometidaId == f.AcometidaId 
                                    && psv.PsId == c.PsId))
             };

• 方式2通过子查询直接统计关联数据，避免内存中处理笛卡尔积，数据量较大时更高效
• EF Core会自动优化子查询为SQL关联语句，性能表现稳定

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