如何在EF Core中实现指定的SQL多表关联统计查询？

EF Core 对应查询实现方案 #

前置准备 #

首先定义需要用到的商户ID筛选集合，同时确保你的DbContext中已注册两张表对应的DbSet：

// 筛选目标商户ID列表
var targetMerchantIds = new[] {"00020", "00025"};

// DbContext中需要提前注册对应实体
public DbSet<CaptureMethodTerminal> CaptureMethodTerminals { get; set; }
public DbSet<MerchantCaptureMethod> MerchantCaptureMethods { get; set; }

方案1：手动Join写法（和原生SQL逻辑完全对齐） #

如果你的实体没有配置导航属性，用这种写法可以1:1匹配你给出的SQL逻辑：

var statistics = await _dbContext.CaptureMethodTerminals
    // 关联MerchantCaptureMethod表，对应SQL里的JOIN逻辑
    .Join(
        _dbContext.MerchantCaptureMethods,
        cmt => cmt.CaptureMethodId,
        mcm => mcm.CaptureMethodId,
        (cmt, mcm) => new { cmt, mcm }
    )
    // 筛选商户ID，对应SQL里的IN条件
    .Where(x => targetMerchantIds.Contains(x.mcm.ClientMerchantId))
    // 按常量分组实现全局聚合，不需要额外分组维度
    .GroupBy(_ => 1)
    // 计算三个统计指标，对应SQL里的count和sum(case...)逻辑
    .Select(g => new 
    {
        Total = g.Count(),
        Active = g.Sum(x => x.cmt.DeactivateDate == null ? 1 : 0),
        Inactive = g.Sum(x => x.cmt.DeactivateDate != null ? 1 : 0)
    })
    .FirstOrDefaultAsync();

方案2：导航属性写法（更符合EF Core最佳实践） #

如果你在MerchantCaptureMethod实体中配置了关联CaptureMethodTerminals的集合导航属性，写法会更简洁：

var statistics = await _dbContext.MerchantCaptureMethods
    .Where(mcm => targetMerchantIds.Contains(mcm.ClientMerchantId))
    // 展开关联的所有CaptureMethodTerminals记录
    .SelectMany(mcm => mcm.CaptureMethodTerminals)
    .GroupBy(_ => 1)
    .Select(g => new 
    {
        Total = g.Count(),
        Active = g.Sum(cmt => cmt.DeactivateDate == null ? 1 : 0),
        Inactive = g.Sum(cmt => cmt.DeactivateDate != null ? 1 : 0)
    })
    .FirstOrDefaultAsync();

说明 #

两种写法生成的SQL和你提供的原生语句基本一致，EF Core 3.0及以上版本都可以正确翻译，不会触发客户端计算。如果需要强类型接收结果，替换匿名类为自定义的统计DTO类即可。

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