如何将含COUNT统计的SQL查询转为EF Core Linq查询并返回模型外的Leads计数字段

你可以通过EF Core的投影功能实现需求，不需要修改现有实体类，以下是两种常用方案：

方案1：使用匿名类型接收结果（适合本地临时使用） #

直接通过Linq关联两个表分组统计，将结果投影到匿名类中，不需要额外定义类型：

// 等价于原SQL的查询逻辑，若你的Leads表CampaignID为非空外键，该写法结果与原SQL完全一致
var queryResult = context.Campaigns
    .GroupJoin(
        context.Leads,
        campaign => campaign.CampaignID,
        lead => lead.CampaignID,
        (campaign, leads) => new 
        {
            campaign.AssignedTo,
            campaign.CampaignID,
            campaign.Name,
            campaign.Venue,
            campaign.StartedOn,
            campaign.CompletedOn,
            campaign.IsOpen,
            Leads = leads.Count()
        }
    )
    .OrderBy(res => res.AssignedTo)
    .ToList();

如果需要严格匹配原SQL的RIGHT JOIN逻辑（包含没有对应Campaign的Leads记录），可以调整为从Leads表开始查询：

var queryResult = context.Leads
    .GroupBy(lead => lead.CampaignID)
    .Select(g => new 
    {
        CampaignInfo = context.Campaigns.FirstOrDefault(c => c.CampaignID == g.Key),
        LeadsCount = g.Count()
    })
    .Select(res => new 
    {
        res.CampaignInfo?.AssignedTo,
        res.CampaignInfo?.CampaignID,
        res.CampaignInfo?.Name,
        res.CampaignInfo?.Venue,
        res.CampaignInfo?.StartedOn,
        res.CampaignInfo?.CompletedOn,
        res.CampaignInfo?.IsOpen,
        Leads = res.LeadsCount
    })
    .OrderBy(res => res.AssignedTo)
    .ToList();

方案2：使用强类型DTO接收结果（适合跨层传递数据） #

如果需要将查询结果传递到其他业务层，建议定义专用的DTO类来接收，类型更安全：

1. 先定义DTO类

public class CampaignWithLeadsDto
{
    public int? AssignedTo { get; set; }
    public int? CampaignID { get; set; }
    public string Name { get; set; }
    public string Venue { get; set; }
    public DateTime? StartedOn { get; set; }
    public DateTime? CompletedOn { get; set; }
    public bool? IsOpen { get; set; }
    public int Leads { get; set; }
}

2. 将Linq查询的投影目标改为DTO即可：

var queryResult = context.Campaigns
    .GroupJoin(
        context.Leads,
        campaign => campaign.CampaignID,
        lead => lead.CampaignID,
        (campaign, leads) => new CampaignWithLeadsDto
        {
            AssignedTo = campaign.AssignedTo,
            CampaignID = campaign.CampaignID,
            Name = campaign.Name,
            Venue = campaign.Venue,
            StartedOn = campaign.StartedOn,
            CompletedOn = campaign.CompletedOn,
            IsOpen = campaign.IsOpen,
            Leads = leads.Count()
        }
    )
    .OrderBy(res => res.AssignedTo)
    .ToList();

补充：仍想使用FromSqlRaw的处理方式 #

如果你偏好直接执行原生SQL，只需要在DbContext中将DTO配置为无键实体即可：

1. 在DbContext的OnModelCreating方法中添加配置：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 其他配置...
    modelBuilder.Entity<CampaignWithLeadsDto>().HasNoKey().ToView(null);
}

2. 执行原生SQL查询：

var queryResult = context.Set<CampaignWithLeadsDto>()
    .FromSqlRaw(@"SELECT 
        c.AssignedTo ,c.CampaignID, c.Name, c.Venue,c.StartedOn, 
        c.CompletedOn, c.IsOpen, COUNT(C.Name) AS Leads 
    FROM 
        Campaign AS c 
    RIGHT JOIN 
        Leads AS l ON l.CampaignID = c.CampaignID 
    GROUP BY
        c.AssignedTo, c.CampaignID, c.Name,c.Venue,c.StartedOn, c.CompletedOn, c.IsOpen 
    ORDER BY 
        c.AssignedTo")
    .ToList();

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