如何在.NET Core的EF Core中用原始SQL查询并映射关联实体

首先明确：ExecuteSqlRaw仅用于执行无返回结果的SQL语句（如增删改），返回值是受影响的行数，完全不适合用来查询并映射实体。要实现你的需求，需要使用EF Core提供的FromSqlRaw或FromSqlInterpolated方法，同时处理一对多关系的映射。

方案一：利用EF Core的导航属性加载（简单直接，适合大多数场景） #

这种方式先通过原始SQL过滤出符合条件的Project，再让EF自动加载关联的Tasks，同时解决分页和SQL注入问题：

步骤1：确保实体与数据库字段映射正确 #

如果数据库中Project表的主键是ProjectId，Task表的主键是TaskId，需要在实体属性上添加Column特性做映射（或者修改SQL字段别名）：

public class Project : IId
{
    [Column("ProjectId")]
    public int Id { get; set; }
    // 其他属性保持不变
}

public class Task : IId
{
    [Column("TaskId")]
    public int Id { get; set; }
    // 其他属性保持不变
}

步骤2：编写查询代码 #

public Project GetProject(string surname, string phone, string email, int page, int limit)
{
    // 使用FromSqlInterpolated自动参数化，避免SQL注入
    var projectQuery = DbContext.Projects
        .FromSqlInterpolated($@"
            SELECT P.*
            FROM Project P
            INNER JOIN Task T ON P.ProjectId = T.ProjectId
            INNER JOIN CS26 C26 ON LEFT(C26.Bran,1) = LEFT(P.Branch,1) AND C26.Client = P.Client AND C26.Ref = P.Ref
            INNER JOIN A_C AC ON LEFT(AC.Bran,1) = LEFT(C26.Bran,1) AND AC.Client = C26.Client
            WHERE AC.Surname = {surname} 
              AND AC.Phone = {phone} 
              AND C26.WEBADDR = {email}
            GROUP BY P.ProjectId, P.Reference, P.CreatedOn, P.ACode, P.ICode, P.Scheme, P.Ticket, P.Inception
            ORDER BY P.CreatedOn DESC
            OFFSET {(page - 1) * limit} ROWS FETCH NEXT {limit} ROWS ONLY")
        .Include(p => p.Tasks); // 显式加载关联的Tasks导航属性

    return projectQuery.FirstOrDefault();
}

说明：

• 用GROUP BY避免因为关联Task而返回重复的Project记录
• Include(p => p.Tasks)会让EF在获取Project后，自动查询对应的Task数据并填充到Tasks列表
• FromSqlInterpolated会自动将参数包装为SQL参数，彻底避免注入风险
• 添加了分页逻辑OFFSET ... FETCH NEXT ...，适配你的page和limit参数

方案二：手动映射平级查询结果（适合需要一次性拉取所有数据，避免N+1查询） #

如果想避免EF自动加载Tasks时的N+1问题，可以直接查询Project和Task的联合数据，再手动分组映射到实体：

步骤1：创建DTO接收联合查询结果 #

public class ProjectTaskFlatDto
{
    // Project字段
    public int ProjectId { get; set; }
    public string Reference { get; set; }
    public DateTime CreatedOn { get; set; }
    public string ACode { get; set; }
    public string ICode { get; set; }
    public string Scheme { get; set; }
    public int? Ticket { get; set; }
    public DateTime Inception { get; set; }

    // Task字段（用前缀避免重名）
    public int TaskId { get; set; }
    public int TaskProjectId { get; set; }
    public Type Type { get; set; }
    public Status Status { get; set; }
    public string CompletedBy { get; set; }
    public DateTime? CompletedOn { get; set; }
    public DateTime? Deadline { get; set; }
    public string CreatedBy { get; set; }
    public DateTime? TaskCreatedOn { get; set; }
    public int? TaskTicket { get; set; }
    public string Comment { get; set; }
}

步骤2：查询并手动映射 #

public Project GetProject(string surname, string phone, string email, int page, int limit)
{
    var flatData = DbContext.Set<ProjectTaskFlatDto>()
        .FromSqlInterpolated($@"
            SELECT 
                P.ProjectId, P.Reference, P.CreatedOn, P.ACode, P.ICode, P.Scheme, P.Ticket, P.Inception,
                T.TaskId, T.ProjectId AS TaskProjectId, T.Type, T.Status, T.CompletedBy, T.CompletedOn, T.Deadline,
                T.CreatedBy, T.CreatedOn AS TaskCreatedOn, T.Ticket AS TaskTicket, T.Comment
            FROM Task T
            INNER JOIN Project P ON P.ProjectId = T.ProjectId
            INNER JOIN CS26 C26 ON LEFT(C26.Bran,1) = LEFT(P.Branch,1) AND C26.Client = P.Client AND C26.Ref = P.Ref
            INNER JOIN A_C AC ON LEFT(AC.Bran,1) = LEFT(C26.Bran,1) AND AC.Client = C26.Client
            WHERE AC.Surname = {surname} 
              AND AC.Phone = {phone} 
              AND C26.WEBADDR = {email}
            ORDER BY P.CreatedOn DESC
            OFFSET {(page - 1) * limit} ROWS FETCH NEXT {limit} ROWS ONLY")
        .ToList();

    if (!flatData.Any())
        return null;

    // 分组映射到Project实体
    var project = new Project
    {
        Id = flatData.First().ProjectId,
        Reference = flatData.First().Reference,
        CreatedOn = flatData.First().CreatedOn,
        ACode = flatData.First().ACode,
        ICode = flatData.First().ICode,
        Scheme = flatData.First().Scheme,
        Ticket = flatData.First().Ticket,
        Inception = flatData.First().Inception,
        Tasks = flatData.Select(dto => new Task
        {
            Id = dto.TaskId,
            ProjectId = dto.TaskProjectId,
            Type = dto.Type,
            Status = dto.Status,
            CompletedBy = dto.CompletedBy,
            CompletedOn = dto.CompletedOn,
            Deadline = dto.Deadline,
            CreatedBy = dto.CreatedBy,
            CreatedOn = dto.TaskCreatedOn,
            Ticket = dto.TaskTicket,
            Comment = dto.Comment
        }).ToList()
    };

    return project;
}

关键注意事项 #

• 绝对不要用string.Format拼接SQL参数：这会导致严重的SQL注入漏洞，必须使用FromSqlInterpolated或FromSqlRaw的参数化方式
• 导航属性加载的限制：如果使用FromSqlRaw/FromSqlInterpolated查询实体后调用Include，EF会先执行你的原始SQL获取主实体，再单独查询每个主实体的关联数据（可能产生N+1查询），如果数据量较大，建议用方案二
• 数据库分页语法：示例中用的是SQL Server的OFFSET/FETCH语法，如果你用的是MySQL，需要替换为LIMIT {limit} OFFSET {(page-1)*limit}

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