EF Core Raw SQL

简介

虽然 EF Core 的 LINQ 能覆盖大多数 CRUD 和常规查询，但在复杂报表、存储过程调用、窗口函数、CTE、批量更新或性能敏感查询场景里，Raw SQL 仍然非常有价值。关键不在于"是否写原生 SQL"，而在于如何在安全性、可维护性和性能之间取得平衡。

Raw SQL 在 EF Core 中不是一个"退路"，而是一个经过精心设计的扩展点。EF Core 提供了多种 API 来执行原生 SQL，每种 API 都有不同的安全模型和使用场景。理解这些 API 的差异，是安全高效使用 Raw SQL 的前提。

特点

• 1.表达能力强 — 能直接使用 CTE、窗口函数、Hint、存储过程等特性
• 2.适合性能调优 — 可精确控制 SQL 结构与执行路径
• 3.支持参数化 — 避免 SQL 注入并保留执行计划复用能力
• 4.可与 EF 结合 — 查询结果仍可映射到实体或 DTO
• 5.适合特殊场景 — 报表、统计、批量操作、复杂联查常用
• 6.事务兼容 — Raw SQL 操作参与同一个 DbContext 事务
• 7.日志集成 — 原生 SQL 同样享受 EF Core 的日志和拦截器

API 速览

EF Core Raw SQL API 分类

查询类 API（返回数据）：
- FromSqlRaw            — 原始 SQL 字符串 + SqlParameter 参数
- FromSqlInterpolated   — 插值字符串，自动参数化（推荐）
- SqlQueryRaw           — EF Core 7+，直接映射到任意类型
- SqlQueryInterpolated  — EF Core 7+，插值版

执行类 API（不返回数据）：
- ExecuteSqlRaw         — 执行原始 SQL，返回影响行数
- ExecuteSqlInterpolated — 插值版，自动参数化
- ExecuteSqlRawAsync    — 异步版
- ExecuteSqlInterpolatedAsync — 异步版

ADO.NET 底层 API：
- db.Database.GetDbConnection()   — 获取底层 ADO.NET 连接
- db.Database.GetDbCommand()      — 获取底层命令对象
- db.Database.BeginTransaction()  — 开启事务

实现

FromSqlInterpolated / FromSqlRaw 查询

基础配置

// 订单实体
public class Order
{
    public int Id { get; set; }
    public long CustomerId { get; set; }
    public decimal Amount { get; set; }
    public string Status { get; set; } = string.Empty;
    public string? Remark { get; set; }
    public DateTime CreatedAt { get; set; }
}

// 报表 DTO — 无主键，用于接收聚合查询结果
public class OrderSummaryDto
{
    public long CustomerId { get; set; }
    public int OrderCount { get; set; }
    public decimal TotalAmount { get; set; }
}

// 上下文配置
public class AppDbContext : DbContext
{
    public DbSet<Order> Orders => Set<Order>();

    // 注册无键实体以支持 FromSql 查询
    public DbSet<OrderSummaryDto> OrderSummaries => Set<OrderSummaryDto>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 标记为无键实体
        modelBuilder.Entity<OrderSummaryDto>().HasNoKey();
    }
}

FromSqlInterpolated 参数化查询

// FromSqlInterpolated 会自动将插值参数转换为 SqlParameter
// 这是 EF Core 推荐的参数化查询方式
public async Task<List<Order>> GetOrdersByCustomerAsync(long customerId)
{
    return await db.Orders
        .FromSqlInterpolated($@"
            SELECT *
            FROM Orders
            WHERE CustomerId = {customerId}
              AND Status = {'paid'}")
        .AsNoTracking()
        .ToListAsync();
}

// 生成的 SQL：
// SELECT * FROM Orders WHERE CustomerId = @p0 AND Status = @p1
// 参数：@p0 = 1001, @p1 = 'paid'

FromSqlRaw 显式参数查询

// FromSqlRaw 需要手动创建 SqlParameter
public async Task<List<OrderSummaryDto>> GetOrderSummaryAsync(
    DateTime start, DateTime end)
{
    var startParam = new SqlParameter("@start", SqlDbType.DateTime2)
    {
        Value = start
    };
    var endParam = new SqlParameter("@end", SqlDbType.DateTime2)
    {
        Value = end
    };

    return await db.OrderSummaries
        .FromSqlRaw(@"
            SELECT CustomerId,
                   COUNT(*) AS OrderCount,
                   SUM(Amount) AS TotalAmount
            FROM Orders
            WHERE CreatedAt >= @start AND CreatedAt < @end
            GROUP BY CustomerId", startParam, endParam)
        .ToListAsync();
}

EF Core 7+ SqlQuery 映射到任意类型

// EF Core 7 引入 SqlQuery，无需注册无键实体
// 可以直接映射到任意 POCO 类型
public class SalesReport
{
    public string Category { get; set; } = string.Empty;
    public int TotalOrders { get; set; }
    public decimal Revenue { get; set; }
    public decimal AvgOrderValue { get; set; }
}

public async Task<List<SalesReport>> GetSalesReportAsync(int year)
{
    return await db.Database
        .SqlQueryRaw<SalesReport>(@"
            SELECT
                CASE
                    WHEN Amount < 100 THEN '小额'
                    WHEN Amount < 1000 THEN '中额'
                    ELSE '大额'
                END AS Category,
                COUNT(*) AS TotalOrders,
                SUM(Amount) AS Revenue,
                AVG(Amount) AS AvgOrderValue
            FROM Orders
            WHERE YEAR(CreatedAt) = {0}
              AND Status = 'paid'
            GROUP BY CASE
                WHEN Amount < 100 THEN '小额'
                WHEN Amount < 1000 THEN '中额'
                ELSE '大额'
            END", year)
        .ToListAsync();
}

// 插值版本
public async Task<List<SalesReport>> GetSalesReportInterpolatedAsync(int year)
{
    return await db.Database
        .SqlQueryInterpolated<SalesReport>($@"
            SELECT
                CASE
                    WHEN Amount < 100 THEN N'小额'
                    WHEN Amount < 1000 THEN N'中额'
                    ELSE N'大额'
                END AS Category,
                COUNT(*) AS TotalOrders,
                SUM(Amount) AS Revenue,
                AVG(Amount) AS AvgOrderValue
            FROM Orders
            WHERE YEAR(CreatedAt) = {year}
              AND Status = {N'paid'}
            GROUP BY CASE
                WHEN Amount < 100 THEN N'小额'
                WHEN Amount < 1000 THEN N'中额'
                ELSE N'大额'
            END")
        .ToListAsync();
}

FromSql 与 LINQ 组合

// FromSql 的结果可以继续用 LINQ 追加过滤条件
// EF Core 会在原始 SQL 外面包一层子查询
var results = await db.Orders
    .FromSqlInterpolated($@"
        SELECT * FROM Orders
        WHERE CustomerId = {customerId}")
    .Where(x => x.Status == "paid" && x.Amount > 100)
    .OrderByDescending(x => x.CreatedAt)
    .Skip(10).Take(20)
    .ToListAsync();

// 生成的 SQL：
// SELECT * FROM (
//     SELECT * FROM Orders WHERE CustomerId = @p0
// ) AS [o]
// WHERE [o].[Status] = 'paid' AND [o].[Amount] > 100
// ORDER BY [o].[CreatedAt] DESC
// OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY

ExecuteSqlRaw / 存储过程 / 批量更新

批量更新

// 批量更新过期订单状态
public async Task<int> ExpirePendingOrdersAsync()
{
    var threshold = DateTime.UtcNow.AddHours(-2);
    var count = await db.Database.ExecuteSqlInterpolatedAsync($@"
        UPDATE Orders
        SET Status = {'expired'}
        WHERE Status = {'pending'}
          AND CreatedAt < {threshold}");

    Console.WriteLine($"过期订单更新完成，影响行数: {count}");
    return count;
}

// 批量删除历史日志
public async Task<int> CleanOldLogsAsync(DateTime before)
{
    var beforeParam = new SqlParameter("@before", SqlDbType.DateTime2) { Value = before };
    return await db.Database.ExecuteSqlRawAsync(@"
        DELETE FROM SystemLogs
        WHERE CreatedAt < @before
          AND Level = 'debug'", beforeParam);
}

存储过程调用

// 调用存储过程返回数据
public async Task<List<OrderSummaryDto>> GetTopOrdersByCustomerAsync(
    long customerId, int topN)
{
    var customerParam = new SqlParameter("@CustomerId", SqlDbType.BigInt)
    {
        Value = customerId
    };
    var topNParam = new SqlParameter("@TopN", SqlDbType.Int)
    {
        Value = topN
    };

    return await db.OrderSummaries
        .FromSqlRaw(
            "EXEC dbo.usp_GetTopOrdersByCustomer @CustomerId, @TopN",
            customerParam, topNParam)
        .ToListAsync();
}

// 调用带输出参数的存储过程
public async Task<(int TotalCount, List<Order> Orders)> SearchOrdersAsync(
    string keyword, int pageIndex, int pageSize)
{
    var keywordParam = new SqlParameter("@Keyword", SqlDbType.NVarChar, 200)
    {
        Value = keyword ?? (object)DBNull.Value
    };
    var offsetParam = new SqlParameter("@Offset", SqlDbType.Int)
    {
        Value = (pageIndex - 1) * pageSize
    };
    var sizeParam = new SqlParameter("@PageSize", SqlDbType.Int)
    {
        Value = pageSize
    };
    var totalParam = new SqlParameter("@TotalCount", SqlDbType.Int)
    {
        Direction = ParameterDirection.Output
    };

    var orders = await db.Orders
        .FromSqlRaw(@"
            EXEC dbo.usp_SearchOrders
                @Keyword, @Offset, @PageSize, @TotalCount OUTPUT",
            keywordParam, offsetParam, sizeParam, totalParam)
        .ToListAsync();

    var totalCount = (int)totalParam.Value;
    return (totalCount, orders);
}

存储过程定义

-- 带输出参数的搜索存储过程
CREATE PROCEDURE dbo.usp_SearchOrders
    @Keyword     NVARCHAR(200) = NULL,
    @Offset      INT = 0,
    @PageSize    INT = 20,
    @TotalCount  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- 先计算总数
    SELECT @TotalCount = COUNT(*)
    FROM Orders
    WHERE @Keyword IS NULL
       OR Remark LIKE '%' + @Keyword + '%'
       OR Status LIKE '%' + @Keyword + '%';

    -- 再返回分页数据
    SELECT *
    FROM Orders
    WHERE @Keyword IS NULL
       OR Remark LIKE '%' + @Keyword + '%'
       OR Status LIKE '%' + @Keyword + '%'
    ORDER BY CreatedAt DESC
    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
END;

-- 返回多个结果集的存储过程
CREATE PROCEDURE dbo.usp_GetOrderDashboard
    @Date DATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 第一个结果集：当日订单汇总
    SELECT
        Status,
        COUNT(*) AS OrderCount,
        SUM(Amount) AS TotalAmount
    FROM Orders
    WHERE CAST(CreatedAt AS DATE) = @Date
    GROUP BY Status;

    -- 第二个结果集：Top 10 客户
    SELECT TOP 10
        CustomerId,
        COUNT(*) AS OrderCount,
        SUM(Amount) AS TotalSpent
    FROM Orders
    WHERE CAST(CreatedAt AS DATE) = @Date
    GROUP BY CustomerId
    ORDER BY TotalSpent DESC;
END;

处理多结果集

// 使用 ADO.NET 处理多结果集
public async Task<(List<StatusSummary> Summaries, List<TopCustomer> Customers)>
    GetOrderDashboardAsync(DateTime date)
{
    var summaries = new List<StatusSummary>();
    var customers = new List<TopCustomer>();

    await using var connection = db.Database.GetDbConnection();
    await connection.OpenAsync();

    await using var command = connection.CreateCommand();
    command.CommandText = "EXEC dbo.usp_GetOrderDashboard @Date";
    command.Parameters.Add(new SqlParameter("@Date", date));

    await using var reader = await command.ExecuteReaderAsync();

    // 读取第一个结果集
    while (await reader.ReadAsync())
    {
        summaries.Add(new StatusSummary
        {
            Status = reader.GetString(0),
            OrderCount = reader.GetInt32(1),
            TotalAmount = reader.GetDecimal(2)
        });
    }

    // 移动到下一个结果集
    await reader.NextResultAsync();

    // 读取第二个结果集
    while (await reader.ReadAsync())
    {
        customers.Add(new TopCustomer
        {
            CustomerId = reader.GetInt64(0),
            OrderCount = reader.GetInt32(1),
            TotalSpent = reader.GetDecimal(2)
        });
    }

    return (summaries, customers);
}

public class StatusSummary
{
    public string Status { get; set; } = string.Empty;
    public int OrderCount { get; set; }
    public decimal TotalAmount { get; set; }
}

public class TopCustomer
{
    public long CustomerId { get; set; }
    public int OrderCount { get; set; }
    public decimal TotalSpent { get; set; }
}

CTE / 窗口函数 / 安全边界

CTE 查询示例

// 使用 CTE 查询每个客户金额最大的前 N 笔订单
public async Task<List<OrderSummaryDto>> GetTopOrdersPerCustomerAsync(int topN)
{
    return await db.OrderSummaries
        .FromSqlInterpolated($@"
            WITH CustomerOrders AS (
                SELECT
                    CustomerId,
                    Id,
                    Amount,
                    ROW_NUMBER() OVER(
                        PARTITION BY CustomerId
                        ORDER BY Amount DESC
                    ) AS rn
                FROM Orders
                WHERE Status = {'paid'}
            )
            SELECT
                CustomerId,
                1 AS OrderCount,
                Amount AS TotalAmount
            FROM CustomerOrders
            WHERE rn <= {topN}")
        .AsNoTracking()
        .ToListAsync();
}

窗口函数示例

// 使用窗口函数计算累计销售额和环比增长
public class SalesTrendDto
{
    public int Month { get; set; }
    public decimal MonthlyRevenue { get; set; }
    public decimal CumulativeRevenue { get; set; }
    public decimal MonthOverMonthGrowth { get; set; }
}

public async Task<List<SalesTrendDto>> GetSalesTrendAsync(int year)
{
    return await db.Database
        .SqlQueryRaw<SalesTrendDto>(@"
            WITH MonthlySales AS (
                SELECT
                    MONTH(CreatedAt) AS Month,
                    SUM(Amount) AS MonthlyRevenue
                FROM Orders
                WHERE YEAR(CreatedAt) = {0}
                  AND Status = 'paid'
                GROUP BY MONTH(CreatedAt)
            )
            SELECT
                Month,
                MonthlyRevenue,
                SUM(MonthlyRevenue) OVER(
                    ORDER BY Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                ) AS CumulativeRevenue,
                CASE
                    WHEN LAG(MonthlyRevenue) OVER (ORDER BY Month) IS NULL THEN 0
                    ELSE (MonthlyRevenue - LAG(MonthlyRevenue) OVER (ORDER BY Month))
                         / LAG(MonthlyRevenue) OVER (ORDER BY Month) * 100
                END AS MonthOverMonthGrowth
            FROM MonthlySales
            ORDER BY Month", year)
        .ToListAsync();
}

递归 CTE 示例

// 使用递归 CTE 查询组织架构树
public class OrgTreeNode
{
    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; } = string.Empty;
    public int ManagerId { get; set; }
    public string Role { get; set; } = string.Empty;
    public int Level { get; set; }
}

public async Task<List<OrgTreeNode>> GetOrgTreeAsync(int rootManagerId)
{
    return await db.Database
        .SqlQueryRaw<OrgTreeNode>(@"
            WITH OrgTree AS (
                -- 锚点：从指定管理者开始
                SELECT
                    EmployeeId, EmployeeName, ManagerId, Role, 0 AS Level
                FROM Employees
                WHERE EmployeeId = {0}

                UNION ALL

                -- 递归：查找所有下属
                SELECT
                    e.EmployeeId, e.EmployeeName, e.ManagerId, e.Role, t.Level + 1
                FROM Employees e
                INNER JOIN OrgTree t ON e.ManagerId = t.EmployeeId
            )
            SELECT * FROM OrgTree
            ORDER BY Level, EmployeeName", rootManagerId)
        .ToListAsync();
}

Query Hint 示例

// 使用查询提示优化特定查询
public async Task<List<Order>> GetLargeOrderReportWithHintAsync()
{
    return await db.Orders
        .FromSqlRaw(@"
            SELECT *
            FROM Orders WITH (INDEX(IX_Orders_CustomerId_Status))
            WHERE Status = 'paid'
              AND Amount > 10000
            OPTION (MAXDOP 4, OPTIMIZE FOR UNKNOWN)")
        .AsNoTracking()
        .ToListAsync();
}

SQL 注入防护

危险示例 vs 安全示例

// ===== 危险：字符串拼接（绝对禁止）=====
public async Task<List<Order>> SearchOrdersDangerousAsync(string keyword)
{
    // 直接拼接用户输入到 SQL 字符串中
    // 攻击者可以输入: "'; DROP TABLE Orders; --"
    var sql = $"SELECT * FROM Orders WHERE Remark LIKE '%{keyword}%'";
    return await db.Orders.FromSqlRaw(sql).ToListAsync();
    // 这行代码可能导致整张表被删除！
}

// ===== 安全：参数化查询（推荐）=====
public async Task<List<Order>> SearchOrdersSafeAsync(string keyword)
{
    return await db.Orders
        .FromSqlInterpolated($@"
            SELECT * FROM Orders
            WHERE Remark LIKE {'%' + keyword + '%'}")
        .ToListAsync();
    // EF Core 自动将 keyword 转为参数 @p0
    // 即使 keyword 包含恶意 SQL 也不会被执行
}

动态 SQL 的安全处理

// 场景：动态排序字段
// 排序字段不能直接参数化，必须使用白名单
public async Task<List<Order>> GetOrdersSortedAsync(
    string sortField, bool descending)
{
    // 白名单校验
    var allowedSortFields = new Dictionary<string, string>
    {
        ["CreatedAt"] = "CreatedAt",
        ["Amount"] = "Amount",
        ["Status"] = "Status",
        ["CustomerId"] = "CustomerId"
    };

    if (!allowedSortFields.TryGetValue(sortField, out var safeColumn))
    {
        throw new ArgumentException($"不支持的排序字段: {sortField}");
    }

    var direction = descending ? "DESC" : "ASC";

    // 使用 FromSqlRaw + 白名单验证过的字段名
    return await db.Orders
        .FromSqlRaw($"SELECT * FROM Orders ORDER BY [{safeColumn}] {direction}")
        .AsNoTracking()
        .ToListAsync();
}

// 场景：动态表名
// 表名不能参数化，需要严格白名单
public async Task<List<Dictionary<string, object>>> QueryDynamicTableAsync(
    string tableName, int topN)
{
    var allowedTables = new HashSet<string>
    {
        "Orders", "Products", "Customers", "Inventory"
    };

    if (!allowedTables.Contains(tableName))
    {
        throw new ArgumentException($"不允许查询的表: {tableName}");
    }

    // 验证表名格式（只允许字母和下划线）
    if (!Regex.IsMatch(tableName, @"^[a-zA-Z_]+$"))
    {
        throw new ArgumentException("表名格式不合法");
    }

    // 使用方括号转义，防止 SQL 注入
    return await db.Database
        .SqlQueryRaw<DynamicRowDto>(
            $"SELECT TOP ({topN}) * FROM [{tableName}]")
        .Select(row => new Dictionary<string, object>
        {
            // 动态列映射...
        })
        .ToListAsync();
}

输出参数和返回值

// 调用带返回值的存储过程
public async Task<int> CreateOrderWithSpAsync(Order order)
{
    var orderIdParam = new SqlParameter("@OrderId", SqlDbType.Int)
    {
        Direction = ParameterDirection.Output
    };
    var customerIdParam = new SqlParameter("@CustomerId", SqlDbType.BigInt)
    {
        Value = order.CustomerId
    };
    var amountParam = new SqlParameter("@Amount", SqlDbType.Decimal)
    {
        Value = order.Amount
    };

    await db.Database.ExecuteSqlRawAsync(@"
        EXEC dbo.usp_CreateOrder
            @CustomerId, @Amount, @OrderId OUTPUT",
        customerIdParam, amountParam, orderIdParam);

    return (int)orderIdParam.Value!;
}

实战模式

模式一：报表查询封装

/// <summary>
/// 统一报表查询服务 — 将所有 Raw SQL 报表集中管理
/// </summary>
public class ReportQueryService
{
    private readonly AppDbContext _db;

    public ReportQueryService(AppDbContext db)
    {
        _db = db;
    }

    /// <summary>
    /// 销售日报
    /// </summary>
    public async Task<DailySalesReport> GetDailySalesAsync(DateTime date)
    {
        var dateParam = new SqlParameter("@Date", SqlDbType.Date) { Value = date };

        return await _db.Database
            .SqlQueryRaw<DailySalesReport>(@"
                SELECT
                    @Date AS ReportDate,
                    COUNT(*) AS TotalOrders,
                    SUM(CASE WHEN Status = 'paid' THEN 1 ELSE 0 END) AS PaidOrders,
                    SUM(CASE WHEN Status = 'pending' THEN 1 ELSE 0 END) AS PendingOrders,
                    SUM(Amount) AS GrossRevenue,
                    AVG(Amount) AS AvgOrderValue,
                    MAX(Amount) AS MaxOrderValue,
                    MIN(Amount) AS MinOrderValue
                FROM Orders
                WHERE CAST(CreatedAt AS DATE) = @Date", dateParam)
            .FirstOrDefaultAsync()
            ?? new DailySalesReport { ReportDate = date };
    }

    /// <summary>
    /// 客户消费排名
    /// </summary>
    public async Task<List<CustomerRanking>> GetCustomerRankingAsync(
        DateTime start, DateTime end, int topN)
    {
        return await _db.Database
            .SqlQueryRaw<CustomerRanking>(@"
                SELECT TOP ({0})
                    CustomerId,
                    COUNT(*) AS OrderCount,
                    SUM(Amount) AS TotalSpent,
                    AVG(Amount) AS AvgOrderValue,
                    MAX(CreatedAt) AS LastOrderDate,
                    RANK() OVER (ORDER BY SUM(Amount) DESC) AS Rank
                FROM Orders
                WHERE CreatedAt >= {1} AND CreatedAt < {2}
                  AND Status = 'paid'
                GROUP BY CustomerId
                ORDER BY TotalSpent DESC", topN, start, end)
            .ToListAsync();
    }
}

public class DailySalesReport
{
    public DateTime ReportDate { get; set; }
    public int TotalOrders { get; set; }
    public int PaidOrders { get; set; }
    public int PendingOrders { get; set; }
    public decimal GrossRevenue { get; set; }
    public decimal AvgOrderValue { get; set; }
    public decimal MaxOrderValue { get; set; }
    public decimal MinOrderValue { get; set; }
}

public class CustomerRanking
{
    public long CustomerId { get; set; }
    public int OrderCount { get; set; }
    public decimal TotalSpent { get; set; }
    public decimal AvgOrderValue { get; set; }
    public DateTime LastOrderDate { get; set; }
    public int Rank { get; set; }
}

模式二：批量操作服务

/// <summary>
/// 批量操作服务 — 使用 Raw SQL 处理大批量数据操作
/// </summary>
public class BulkOperationService
{
    private readonly AppDbContext _db;
    private readonly ILogger<BulkOperationService> _logger;

    public BulkOperationService(AppDbContext db, ILogger<BulkOperationService> logger)
    {
        _db = db;
        _logger = logger;
    }

    /// <summary>
    /// 批量更新订单状态（使用分批处理避免锁表）
    /// </summary>
    public async Task<int> BatchUpdateStatusAsync(
        string fromStatus, string toStatus, int batchSize)
    {
        var totalUpdated = 0;
        var updated = 0;

        do
        {
            // 每次更新一批，避免长时间锁表
            updated = await _db.Database.ExecuteSqlInterpolatedAsync($@"
                UPDATE TOP ({batchSize}) Orders
                SET Status = {toStatus},
                    UpdatedAt = {DateTime.UtcNow}
                WHERE Status = {fromStatus}");

            totalUpdated += updated;
            _logger.LogInformation(
                "批量更新进度: 本批 {BatchSize}, 累计 {Total}",
                updated, totalUpdated);

            // 批次间短暂等待，减少锁竞争
            if (updated > 0)
            {
                await Task.Delay(100);
            }

        } while (updated > 0);

        return totalUpdated;
    }

    /// <summary>
    /// 分区归档历史数据
    /// </summary>
    public async Task ArchiveOrdersAsync(DateTime cutoffDate, string archiveTable)
    {
        // 验证目标表名
        if (!Regex.IsMatch(archiveTable, @"^OrdersArchive_\d{4}$"))
        {
            throw new ArgumentException("归档表名格式不合法");
        }

        // 开启事务确保原子性
        await using var transaction = await _db.Database.BeginTransactionAsync();

        try
        {
            // 1. 创建归档表（如果不存在）
            await _db.Database.ExecuteSqlRawAsync($@"
                IF NOT EXISTS (
                    SELECT * FROM INFORMATION_SCHEMA.TABLES
                    WHERE TABLE_NAME = '{archiveTable}'
                )
                BEGIN
                    SELECT * INTO [{archiveTable}] FROM Orders WHERE 1 = 0;
                END");

            // 2. 迁移数据
            var moved = await _db.Database.ExecuteSqlRawAsync($@"
                INSERT INTO [{archiveTable}]
                SELECT * FROM Orders
                WHERE CreatedAt < @cutoffDate", new SqlParameter("@cutoffDate", cutoffDate));

            _logger.LogInformation("归档数据: {Count} 条", moved);

            // 3. 删除已归档数据
            await _db.Database.ExecuteSqlRawAsync($@"
                DELETE FROM Orders
                WHERE CreatedAt < @cutoffDate", new SqlParameter("@cutoffDate", cutoffDate));

            await transaction.CommitAsync();
        }
        catch (Exception ex)
        {
            await transaction.RollbackAsync();
            _logger.LogError(ex, "归档失败");
            throw;
        }
    }
}

模式三：SQL 文件管理

/// <summary>
/// SQL 文件管理器 — 将复杂 SQL 保存为嵌入资源
/// </summary>
public class SqlFileManager
{
    private readonly Assembly _assembly;

    public SqlFileManager()
    {
        _assembly = Assembly.GetExecutingAssembly();
    }

    /// <summary>
    /// 从嵌入资源读取 SQL 文件
    /// </summary>
    public string GetSql(string resourceName)
    {
        var fullName = $"MyApp.Sql.{resourceName}.sql";
        using var stream = _assembly.GetManifestResourceStream(fullName)
            ?? throw new FileNotFoundException($"SQL 资源不存在: {fullName}");

        using var reader = new StreamReader(stream);
        return reader.ReadToEnd();
    }

    /// <summary>
    /// 读取 SQL 并替换命名参数
    /// </summary>
    public string GetSqlWithParams(
        string resourceName, Dictionary<string, string> parameters)
    {
        var sql = GetSql(resourceName);
        foreach (var (key, value) in parameters)
        {
            sql = sql.Replace($"{{{key}}}", value);
        }
        return sql;
    }
}

// 使用示例
public class OrderReportService
{
    private readonly AppDbContext _db;
    private readonly SqlFileManager _sqlFiles;

    public async Task<List<CustomerRanking>> GetCustomerRankingAsync(
        DateTime start, DateTime end)
    {
        var sql = _sqlFiles.GetSql("CustomerRanking");

        return await _db.Database
            .SqlQueryRaw<CustomerRanking>(
                sql,
                new SqlParameter("@Start", start),
                new SqlParameter("@End", end))
            .ToListAsync();
    }
}

性能考量

LINQ vs Raw SQL 性能对比

// 场景：复杂聚合查询

// LINQ 写法 — EF Core 生成的 SQL 可能不够优化
var linqResult = await db.Orders
    .Where(o => o.Status == "paid")
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        TotalAmount = g.Sum(o => o.Amount),
        OrderCount = g.Count()
    })
    .OrderByDescending(x => x.TotalAmount)
    .Take(10)
    .ToListAsync();

// Raw SQL 写法 — 可以精确控制 SQL 结构和 Hint
var rawResult = await db.Database
    .SqlQueryRaw<CustomerRanking>(@"
        SELECT TOP 10
            CustomerId,
            SUM(Amount) AS TotalSpent,
            COUNT(*) AS OrderCount
        FROM Orders WITH (NOLOCK)
        WHERE Status = 'paid'
        GROUP BY CustomerId
        ORDER BY TotalSpent DESC
        OPTION (OPTIMIZE FOR UNKNOWN)")
    .ToListAsync();

// 对比：
// - LINQ 写法可维护性更好，但生成的 SQL 可能缺少 Hint
// - Raw SQL 可以精确控制执行计划，但维护成本更高
// - 建议：先用 LINQ 实现，发现性能瓶颈再改用 Raw SQL

批量操作性能对比

// 场景：更新 10000 条记录的状态

// 方式 1：EF Core SaveChanges（最慢）
var orders = await db.Orders
    .Where(o => o.Status == "pending")
    .ToListAsync();

foreach (var order in orders)
{
    order.Status = "expired";
}

await db.SaveChangesAsync();
// 生成 10000 条 UPDATE 语句，性能极差

// 方式 2：ExecuteUpdate（EF Core 7+）
var count = await db.Orders
    .Where(o => o.Status == "pending")
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(o => o.Status, "expired")
        .SetProperty(o => o.UpdatedAt, DateTime.UtcNow));
// 生成单条 UPDATE 语句，性能好

// 方式 3：ExecuteSqlRaw（最快，可添加 Hint）
var count2 = await db.Database.ExecuteSqlRawAsync(@"
    UPDATE Orders WITH (TABLOCK)
    SET Status = 'expired', UpdatedAt = GETUTCDATE()
    WHERE Status = 'pending'");
// 单条 SQL + 表锁 Hint，批量操作性能最优

事务管理

// Raw SQL 操作可以与 EF Core 操作共享事务
public async Task TransferOrderAsync(int orderId, long newCustomerId)
{
    await using var transaction = await db.Database.BeginTransactionAsync();

    try
    {
        // EF Core 操作：更新订单
        var order = await db.Orders.FindAsync(orderId);
        order!.CustomerId = newCustomerId;
        await db.SaveChangesAsync();

        // Raw SQL 操作：记录变更日志
        await db.Database.ExecuteSqlRawAsync(@"
            INSERT INTO OrderTransferLog (OrderId, OldCustomerId, NewCustomerId, TransferredAt)
            VALUES (@OrderId, @OldCustomerId, @NewCustomerId, GETUTCDATE())",
            new SqlParameter("@OrderId", orderId),
            new SqlParameter("@OldCustomerId", order.CustomerId),
            new SqlParameter("@NewCustomerId", newCustomerId));

        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

日志与监控

// 监控 Raw SQL 执行
public class RawSqlLoggingInterceptor : DbCommandInterceptor
{
    private readonly ILogger<RawSqlLoggingInterceptor> _logger;

    public RawSqlLoggingInterceptor(ILogger<RawSqlLoggingInterceptor> logger)
    {
        _logger = logger;
    }

    public override ValueTask<DbDataReader> ReaderExecutedAsync(
        DbCommand command, CommandExecutedEventData eventData, DbDataReader result,
        CancellationToken cancellationToken = default)
    {
        // 记录慢查询
        if (eventData.Duration.TotalMilliseconds > 1000)
        {
            _logger.LogWarning(
                "慢 Raw SQL 查询: {Duration}ms\nSQL: {Sql}\n参数: {Parameters}",
                eventData.Duration.TotalMilliseconds,
                command.CommandText,
                string.Join(", ", command.Parameters
                    .Cast<DbParameter>()
                    .Select(p => $"{p.ParameterName}={p.Value}")));
        }

        return base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
    }

    public override ValueTask<int> NonQueryExecutedAsync(
        DbCommand command, CommandExecutedEventData eventData, int result,
        CancellationToken cancellationToken = default)
    {
        if (eventData.Duration.TotalMilliseconds > 500)
        {
            _logger.LogWarning(
                "慢 Raw SQL 执行: {Duration}ms, 影响行数: {Rows}\nSQL: {Sql}",
                eventData.Duration.TotalMilliseconds,
                result,
                command.CommandText);
        }

        return base.NonQueryExecutedAsync(command, eventData, result, cancellationToken);
    }
}

优点

• 1.灵活度高 — 可直接使用数据库高级特性
• 2.性能可控 — 更容易围绕执行计划做针对性优化
• 3.适合复杂查询 — 报表、统计、批量处理更直接
• 4.仍可复用 EF 基础设施 — 事务、连接、上下文管理仍可沿用
• 5.数据库特性全覆盖 — CTE、窗口函数、Hint、存储过程全部可用
• 6.可组合 LINQ — FromSql 结果可继续追加 LINQ 过滤

缺点

• 1.可维护性下降 — 复杂 SQL 增多后与代码耦合会变重
• 2.数据库耦合增强 — 不同数据库方言迁移成本增加
• 3.安全风险更高 — 拼接字符串不当就可能引入 SQL 注入
• 4.映射要更小心 — DTO、无键实体、字段别名都需要对齐
• 5.重构困难 — 代码重命名工具无法更新 SQL 字符串中的列名
• 6.测试复杂 — 需要 SQL 环境才能运行，Mock 困难

总结

EF Core Raw SQL 不是对 LINQ 的否定，而是对复杂场景的补充。实践中建议优先用 LINQ 保持可维护性，只有在表达能力受限、性能明确不足或必须使用数据库特性时，再引入 Raw SQL，并始终坚持参数化和白名单控制。

关键知识点

• 查询优先用 FromSqlInterpolated / 参数化，避免拼接字符串。
• 无键 DTO 适合接收报表或存储过程结果。
• EF Core 7+ 的 SqlQuery<T> 可以映射到任意类型，无需注册 DbSet。
• Raw SQL 强大，但也更需要关注迁移成本和安全边界。
• 动态列名、排序字段等不能直接参数化，必须白名单处理。
• ExecuteSqlRaw 适合批量更新和删除，性能远优于逐条 SaveChanges。
• 多结果集需要使用 ADO.NET 底层 API（DbDataReader）处理。
• Raw SQL 与 EF Core 操作可以共享同一事务。

项目落地视角

• 报表统计和窗口函数常常直接用 Raw SQL 更清晰。
• 批量更新过期订单、清理任务可用 ExecuteSql* 提高效率。
• 存储过程仍适合一些稳定且复杂的数据库内逻辑。
• 对高频慢查询，可先用 SQL 验证最优写法，再决定是否保留 Raw SQL。
• 复杂 SQL 建议保存为嵌入资源文件，便于管理和版本控制。
• 为 Raw SQL 查询编写集成测试，验证结果正确性。

常见误区

• 为了"性能更高"把所有 LINQ 都改成 SQL 字符串。
• 把用户输入直接拼进 SQL，埋下注入风险。
• 返回字段与 DTO 不对齐，导致运行时映射错误。
• 不看执行计划，只觉得原生 SQL 一定比 LINQ 快。
• 忘记在 FromSqlRaw 中使用 SqlParameter，导致参数无法传递。
• 在高并发场景下使用 TABLOCK Hint 导致锁争用。
• Raw SQL 中使用了数据库特定语法（如 SQL Server 的 TOP），导致无法切换数据库。

进阶路线

• 学习 SQL Server / PostgreSQL 的执行计划与窗口函数优化。
• 研究 EF Core 的 compiled query 与 Raw SQL 搭配场景。
• 为复杂 Raw SQL 建立统一仓储层和测试样例。
• 将高复杂度报表迁移到专门分析库或只读副本上执行。
• 学习使用 ADO.NET 底层 API 处理多结果集和批量操作。
• 探索 Dapper 与 EF Core 混合使用的模式。

适用场景

• 复杂统计报表。
• 存储过程调用。
• 窗口函数、CTE、Hint 等 LINQ 表达困难场景。
• 性能敏感的批量更新、批量清理、离线任务。
• 需要精确控制执行计划的查询优化。
• 多结果集查询。
• 递归查询（组织架构、分类树等）。

落地建议

• 先明确为什么必须用 Raw SQL，而不是默认使用它。
• 所有用户输入都必须参数化或白名单过滤。
• 把复杂 SQL 封装在明确的方法中，并补充测试。
• 为高频 SQL 保留执行计划、索引和优化说明，方便后续维护。
• 使用 SQL 嵌入资源文件管理复杂查询，避免 SQL 散落在代码中。
• 配置慢查询日志，监控 Raw SQL 的执行性能。
• 为批量操作设计分批处理策略，避免长时间锁表。

排错清单

• 检查 SQL 是否参数化，是否存在拼接风险。
• 检查返回列名与 DTO/实体映射是否一致。
• 检查执行计划是否真的优于对应 LINQ 方案。
• 检查数据库方言差异是否影响迁移或测试环境运行。
• 检查 FromSqlRaw 的参数顺序是否与 SQL 中占位符一致。
• 检查存储过程的输出参数是否正确配置 Direction。
• 检查多结果集查询是否正确调用了 NextResultAsync。
• 检查 Raw SQL 事务是否与 EF Core 事务正确关联。

复盘问题

• 这个查询必须用 Raw SQL，还是只是因为写得更顺手？
• 你能否解释这段 SQL 的性能优势来自哪里？
• 这段 SQL 的数据库耦合是否在团队可接受范围内？
• 如果数据库换版本或换引擎，这段 SQL 的风险有多大？
• 你的 Raw SQL 是否有对应的单元测试或集成测试？
• 复杂 SQL 是否有注释说明优化思路和适用条件？
• 团队中是否有人能看懂并维护这段 SQL？

延伸阅读

• SQL 查询优化实战
• EF Core 继承映射
• EF Core 高级查询
• T-SQL 高级查询