5.7 原始 SQL、存储过程与批量操作

EF Core 覆盖了大多数日常查询和更新，但不是所有数据访问都适合用 LINQ 表达。复杂报表、数据库已有存储过程、批量更新和批量删除，都可能需要原始 SQL 或 EF Core 的批量 API。关键不是“绕开 ORM”，而是在保持安全、可维护和可测试的前提下选择合适工具。

学习目标

能判断什么时候继续使用 LINQ，什么时候考虑原始 SQL。
能用参数化方式编写 FromSql 和 ExecuteSql，避免 SQL 注入。
能调用返回结果集或只执行命令的存储过程。
能使用 ExecuteUpdateAsync 和 ExecuteDeleteAsync 处理批量更新和删除。

应用场景

复杂统计查询难以用 LINQ 清晰表达。
老系统已经存在存储过程，需要新 API 复用。
批量把过期任务标记为归档。
按条件删除大量临时数据或过期日志。

核心概念

概念	说明
`FromSql`	从原始 SQL 查询实体或映射类型
`ExecuteSql`	执行不返回实体结果集的 SQL 命令
参数化查询	把用户输入作为参数传入，而不是拼进 SQL 字符串
存储过程	保存在数据库中的可执行逻辑，常用于遗留系统或复杂数据操作
`ExecuteUpdate`	不加载实体，直接在数据库侧批量更新
`ExecuteDelete`	不加载实体，直接在数据库侧批量删除

案例：参数化原始 SQL 查询

如果查询需要利用数据库特定能力，或 LINQ 版本可读性很差，可以使用 FromSql。用户输入必须参数化。

public async Task<IReadOnlyList<TodoItem>> SearchTodosAsync(
    string keyword,
    string userId,
    CancellationToken cancellationToken)
{
    return await dbContext.Todos
        .FromSql($"""
            SELECT *
            FROM Todos
            WHERE UserId = {userId}
              AND IsDeleted = 0
              AND Title LIKE {'%' + keyword + '%'}
            """)
        .AsNoTracking()
        .OrderByDescending(todo => todo.CreatedAt)
        .ToListAsync(cancellationToken);
}

插值形式会把表达式转成数据库参数，而不是直接拼接文本。不要把完整 SQL 片段、列名或排序方向来自用户输入后直接插入；这类动态结构必须使用白名单。

示例：安全处理动态排序

列名不能像普通值一样参数化。如果前端传入排序字段，要映射到固定白名单，而不是把字符串直接拼进 SQL。

var orderBy = request.SortBy switch
{
    "createdAt" => "CreatedAt",
    "dueDate" => "DueDate",
    "title" => "Title",
    _ => "CreatedAt"
};

var direction = request.Descending ? "DESC" : "ASC";

var sql = $"""
    SELECT Id, Title, IsCompleted, CreatedAt
    FROM Todos
    WHERE UserId = @userId AND IsDeleted = 0
    ORDER BY {orderBy} {direction}
    OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY
    """;

这种场景里只有 orderBy 和 direction 来自代码白名单，@userId、@skip、@take 仍然是参数。不要让请求直接决定任意 SQL 片段。

示例：执行命令 SQL

对不需要加载实体的命令，可以使用 ExecuteSql。例如把一批过期任务标记为已归档：

var affectedRows = await dbContext.Database.ExecuteSqlAsync($"""
    UPDATE Todos
    SET IsArchived = 1,
        UpdatedAt = {DateTimeOffset.UtcNow},
        UpdatedBy = {userId}
    WHERE TenantId = {tenantId}
      AND IsCompleted = 1
      AND CompletedAt < {cutoff}
    """, cancellationToken);

ExecuteSql 不会自动更新当前 DbContext 已跟踪实体的内存状态。如果同一个上下文之前已经查询过这些实体，后续读取可能看到旧值，应避免混用或清理跟踪状态。

示例：调用存储过程

存储过程可以返回实体结果，也可以只执行业务命令。返回实体时，结果列需要能匹配实体映射；如果只需要报表 DTO，更推荐配置无键类型或使用专门查询模型。

public async Task<IReadOnlyList<TodoItem>> GetOverdueTodosAsync(
    string tenantId,
    CancellationToken cancellationToken)
{
    return await dbContext.Todos
        .FromSql($"EXEC dbo.GetOverdueTodos @TenantId = {tenantId}")
        .AsNoTracking()
        .ToListAsync(cancellationToken);
}

只执行命令的存储过程：

var affectedRows = await dbContext.Database.ExecuteSqlAsync(
    $"EXEC dbo.ArchiveCompletedTodos @TenantId = {tenantId}, @Cutoff = {cutoff}",
    cancellationToken);

存储过程会把部分业务逻辑放进数据库。团队需要明确版本管理、测试策略和迁移发布流程，避免应用代码和数据库逻辑脱节。

示例：批量更新和删除

当只是按条件批量修改字段，不需要加载实体、触发实体方法或逐条校验时，可以使用 ExecuteUpdateAsync。

var affectedRows = await dbContext.Todos
    .Where(todo => todo.TenantId == tenantId)
    .Where(todo => todo.IsCompleted)
    .Where(todo => todo.CompletedAt < cutoff)
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(todo => todo.IsArchived, true)
        .SetProperty(todo => todo.UpdatedAt, DateTimeOffset.UtcNow)
        .SetProperty(todo => todo.UpdatedBy, userId),
        cancellationToken);

批量删除同理：

var deletedRows = await dbContext.TodoImports
    .Where(import => import.TenantId == tenantId)
    .Where(import => import.CreatedAt < cutoff)
    .ExecuteDeleteAsync(cancellationToken);

批量 API 直接在数据库执行，不会逐个加载实体，也不会触发你写在实体实例方法里的业务逻辑。用于软删除时通常更适合 ExecuteUpdate，而不是 ExecuteDelete。

选择建议

场景	推荐方式
常规 CRUD、筛选、分页	LINQ + EF Core 查询
简单批量更新或删除	`ExecuteUpdate` / `ExecuteDelete`
数据库特定函数或复杂查询	参数化 `FromSql`
遗留数据库已有过程	存储过程
超大规模导入导出	专门批量库、数据库原生命令或离线任务

重点难点

原始 SQL 的第一原则是参数化，任何用户输入都不能直接拼进 SQL。
动态列名、表名和排序方向不能参数化，只能通过白名单映射。
FromSql 查询实体时仍受实体映射影响，结果列不匹配会报错。
批量 API 会绕过变更跟踪，不适合依赖实体状态、领域事件或逐条校验的流程。
存储过程要纳入迁移、评审、测试和回滚流程，否则很容易变成隐藏业务逻辑。

常见误区

误区	推荐做法
LINQ 写不出来就拼字符串 SQL	先确认是否能用投影、分组或批量 API 解决
把请求里的排序字段直接拼进 SQL	使用白名单映射列名和方向
批量更新后继续相信已跟踪实体	避免混用，必要时重新查询或清理跟踪
用物理批量删除实现软删除	使用批量更新设置软删除字段
存储过程不进版本库	用迁移脚本或数据库项目管理变更

练习

把一个复杂统计 LINQ 改写为参数化 FromSql 查询。
给任务列表排序参数增加白名单，禁止任意 SQL 片段进入查询。
使用 ExecuteUpdateAsync 批量归档 90 天前已完成任务。
设计一个存储过程发布流程，说明如何测试和回滚。

学习目标​

应用场景​

核心概念​

案例：参数化原始 SQL 查询​

示例：安全处理动态排序​

示例：执行命令 SQL​

示例：调用存储过程​

示例：批量更新和删除​

选择建议​

重点难点​

常见误区​

练习​

延伸阅读​