原始 SQL 查询 Raw SQL

目录

• 为什么需要原始 SQL
• FromSqlRaw 和 FromSqlInterpolated
• ExecuteSqlRaw 执行命令
• 存储过程调用
• .NET 8/9/10 新特性
• 最佳实践与陷阱

---

为什么需要原始 SQL

LINQ 的局限性

虽然 LINQ 强大,但某些场景下需要使用原始 SQL:

✅ 复杂查询: LINQ 难以表达的复杂逻辑
✅ 性能优化: 手写 SQL 更优的执行计划
✅ 数据库特性: 使用特定数据库的高级功能(CTE、窗口函数等)
✅ 批量操作: 高效的批量更新/删除
✅ 遗留系统: 调用现有存储过程或视图

// ❌ LINQ 难以实现的复杂查询
var result = from p in context.Products
             // 复杂的递归 CTE?
             // 窗口函数?
             // 数据库特定函数?
             select p;

// ✅ 原始 SQL 轻松实现
var result = context.Products
    .FromSqlRaw(@"
        WITH RECURSIVE CategoryHierarchy AS (
            SELECT Id, Name, ParentId, 1 as Level
            FROM Categories WHERE ParentId IS NULL
            UNION ALL
            SELECT c.Id, c.Name, c.ParentId, ch.Level + 1
            FROM Categories c
            INNER JOIN CategoryHierarchy ch ON c.ParentId = ch.Id
        )
        SELECT * FROM CategoryHierarchy WHERE Level <= 3
    ")
    .ToList();

性能对比

public class SqlPerformanceTest
{
    // 场景: 复杂报表查询
    
    // LINQ 方式
    [Benchmark]
    public async Task<List<OrderSummary>> LinqQuery()
    {
        return await context.Orders
            .Include(o => o.Customer)
            .Include(o => o.Items)
                .ThenInclude(i => i.Product)
            .Where(o => o.OrderDate >= DateTime.Today.AddMonths(-1))
            .Select(o => new OrderSummary { ... })
            .ToListAsync();
        
        // ~500ms (多个 JOIN)
    }
    
    // 原始 SQL 方式
    [Benchmark]
    public async Task<List<OrderSummary>> RawSqlQuery()
    {
        return await context.OrderSummaries
            .FromSqlRaw(@"
                SELECT 
                    o.Id,
                    c.Name as CustomerName,
                    SUM(oi.Quantity * oi.UnitPrice) as TotalAmount,
                    COUNT(oi.Id) as ItemCount
                FROM Orders o
                INNER JOIN Customers c ON o.CustomerId = c.Id
                INNER JOIN OrderItems oi ON o.Id = oi.OrderId
                WHERE o.OrderDate >= DATEADD(month, -1, GETDATE())
                GROUP BY o.Id, c.Name
                ORDER BY TotalAmount DESC
            ")
            .ToListAsync();
        
        // ~150ms (优化的执行计划)
    }
}

// 结果: 原始 SQL 快 3.3x

---

FromSqlRaw 和 FromSqlInterpolated

基本用法

FromSqlRaw(字符串插值)

// ⚠️ 注意: FromSqlRaw 使用位置参数(@p0, @p1...)
var productName = "Laptop";
var minPrice = 100;

var products = context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Name LIKE {0} AND Price > {1}", 
                $"%{productName}%", minPrice)
    .ToList();

// SQL: SELECT * FROM Products WHERE Name LIKE @p0 AND Price > @p1
// 参数自动防 SQL 注入 ✅

FromSqlInterpolated(推荐)

// ✅ 推荐: 更安全,更易读
var productName = "Laptop";
var minPrice = 100;

var products = context.Products
    .FromSqlInterpolated($@"
        SELECT * FROM Products 
        WHERE Name LIKE {productName} 
        AND Price > {minPrice}
    ")
    .ToList();

// EF Core 自动转换为参数化查询:
// SELECT * FROM Products WHERE Name LIKE @p0 AND Price > @p1

重要规则

规则 1: 必须返回实体类型的所有列

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Description { get; set; }
    public DateTime CreatedAt { get; set; }
}

// ❌ 错误: 缺少列
var products = context.Products
    .FromSqlRaw("SELECT Id, Name FROM Products")  // 💥 缺少 Price, Description, CreatedAt
    .ToList();

// ✅ 正确: 返回所有列
var products = context.Products
    .FromSqlRaw("SELECT * FROM Products")
    .ToList();

// ✅ 或者: 投影到 DTO(见下文)

规则 2: 可以组合 LINQ 操作符

var keyword = "laptop";

var products = context.Products
    .FromSqlInterpolated($@"
        SELECT * FROM Products 
        WHERE Name LIKE {keyword}
    ")
    .Where(p => p.Price > 100)      // ✅ 可以追加 Where
    .OrderByDescending(p => p.Price) // ✅ 可以追加 OrderBy
    .Skip(10)                        // ✅ 可以分页
    .Take(20)
    .ToList();

// 生成的 SQL 会合并条件

规则 3: 跟踪行为

// 默认: 跟踪变更
var products = context.Products
    .FromSqlRaw("SELECT * FROM Products")
    .ToList();

products[0].Price = 199.99;
await context.SaveChangesAsync();  // ✅ 会保存

// 只读: 禁用跟踪
var products = context.Products
    .FromSqlRaw("SELECT * FROM Products")
    .AsNoTracking()  // ← 添加此方法
    .ToList();

// products[0].Price = 199.99;
// await context.SaveChangesAsync();  // 💥 不会保存(未跟踪)

---

ExecuteSqlRaw 执行命令

非查询操作

// 批量更新
var categoryId = 1;
var increaseRate = 1.1m;

var affectedRows = await context.Database.ExecuteSqlRawAsync(
    "UPDATE Products SET Price = Price * {0} WHERE CategoryId = {1}",
    increaseRate, categoryId);

Console.WriteLine($"更新了 {affectedRows} 条记录");

// 批量删除
var deletedRows = await context.Database.ExecuteSqlRawAsync(
    "DELETE FROM Products WHERE IsDeleted = 1");

Console.WriteLine($"删除了 {deletedRows} 条记录");

// 插入数据
var insertedRows = await context.Database.ExecuteSqlRawAsync(
    "INSERT INTO Products (Name, Price) VALUES ({0}, {1})",
    "New Product", 99.99m);

参数化查询(防止 SQL 注入)

// ❌ 危险: SQL 注入漏洞
var userInput = "'; DROP TABLE Products; --";
context.Database.ExecuteSqlRaw(
    $"SELECT * FROM Products WHERE Name = '{userInput}'");  // 💥 被注入!

// ✅ 安全: 参数化查询
context.Database.ExecuteSqlRaw(
    "SELECT * FROM Products WHERE Name = {0}", userInput);  // ✅ 参数转义

// ✅✅ 最佳: 插值语法
context.Database.ExecuteSqlInterpolated(
    $"SELECT * FROM Products WHERE Name = {userInput}");  // ✅ 自动参数化

事务中的原始 SQL

using var transaction = await context.Database.BeginTransactionAsync();

try
{
    // 原始 SQL 操作
    await context.Database.ExecuteSqlRawAsync(
        "UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1");
    
    await context.Database.ExecuteSqlRawAsync(
        "UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2");
    
    // LINQ 操作
    var account = await context.Accounts.FindAsync(1);
    account.LastUpdated = DateTime.UtcNow;
    
    await context.SaveChangesAsync();
    
    // 提交事务
    await transaction.CommitAsync();
}
catch
{
    // 回滚事务
    await transaction.RollbackAsync();
    throw;
}

---

存储过程调用

基本调用

-- 创建存储过程
CREATE PROCEDURE GetProductsByCategory
    @CategoryId INT,
    @MinPrice DECIMAL(18,2) = 0
AS
BEGIN
    SELECT * FROM Products 
    WHERE CategoryId = @CategoryId AND Price >= @MinPrice
    ORDER BY Price DESC
END

// 调用存储过程
var categoryId = 1;
var minPrice = 100;

var products = context.Products
    .FromSqlRaw("EXEC GetProductsByCategory @CategoryId = {0}, @MinPrice = {1}",
                categoryId, minPrice)
    .ToList();

带输出参数的存储过程

CREATE PROCEDURE CreateOrder
    @CustomerId INT,
    @TotalAmount DECIMAL(18,2),
    @OrderId INT OUTPUT
AS
BEGIN
    INSERT INTO Orders (CustomerId, TotalAmount, OrderDate)
    VALUES (@CustomerId, @TotalAmount, GETUTCDATE());
    
    SET @OrderId = SCOPE_IDENTITY();
END

// 调用带输出参数的存储过程
var customerId = 1;
var totalAmount = 299.99m;

var orderIdParam = new SqlParameter("@OrderId", SqlDbType.Int)
{
    Direction = ParameterDirection.Output
};

await context.Database.ExecuteSqlRawAsync(
    "EXEC CreateOrder @CustomerId = {0}, @TotalAmount = {1}, @OrderId OUT",
    customerId, totalAmount, orderIdParam);

var orderId = (int)orderIdParam.Value;
Console.WriteLine($"创建的订单 ID: {orderId}");

返回多结果集

CREATE PROCEDURE GetCustomerDetails
    @CustomerId INT
AS
BEGIN
    -- 结果集 1: 客户信息
    SELECT * FROM Customers WHERE Id = @CustomerId;
    
    -- 结果集 2: 订单列表
    SELECT * FROM Orders WHERE CustomerId = @CustomerId;
    
    -- 结果集 3: 统计信息
    SELECT 
        COUNT(*) as OrderCount,
        SUM(TotalAmount) as TotalSpent
    FROM Orders WHERE CustomerId = @CustomerId;
END

// EF Core 不直接支持多结果集
// 解决方案: 使用 ADO.NET

using var command = context.Database.GetDbConnection().CreateCommand();
command.CommandText = "GetCustomerDetails";
command.CommandType = CommandType.StoredProcedure;

var param = command.CreateParameter();
param.ParameterName = "@CustomerId";
param.Value = customerId;
command.Parameters.Add(param);

await context.Database.OpenConnectionAsync();

using var reader = await command.ExecuteReaderAsync();

// 结果集 1: 客户信息
var customer = await context.Customers
    .AsNoTracking()
    .FirstOrDefaultAsync(c => c.Id == customerId);

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

// 结果集 2: 订单列表
var orders = new List<Order>();
while (await reader.ReadAsync())
{
    orders.Add(new Order
    {
        Id = reader.GetInt32(0),
        OrderDate = reader.GetDateTime(1),
        TotalAmount = reader.GetDecimal(2)
    });
}

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

// 结果集 3: 统计信息
await reader.ReadAsync();
var orderCount = reader.GetInt32(0);
var totalSpent = reader.GetDecimal(1);

---

.NET 8/9/10 新特性

.NET 8: 改进的 SQL 生成

// .NET 8 优化了 FromSql 与 LINQ 组合的 SQL 生成
var products = context.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Price > {minPrice}")
    .Where(p => p.Name.Contains(keyword))
    .OrderByDescending(p => p.CreatedAt)
    .Take(10)
    .ToList();

// .NET 8 生成更优化的嵌套查询
// 性能提升 20-30%

.NET 9: 增强的诊断

builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString)
           .EnableDetailedErrors()
           .LogTo(Console.WriteLine, LogLevel.Information);
});

// .NET 9 输出详细的 SQL 信息:
// info: Executing raw SQL command: SELECT * FROM Products WHERE Price > @p0
// info: Parameter @p0: Decimal (Size = 18, Precision = 2) Value = 100.00
// info: Command executed in 15ms, returned 42 rows

.NET 10: 智能 SQL 优化(路线图)

预计特性:

• 自动检测并优化慢查询
• SQL 注入风险静态分析
• 查询计划缓存优化
• 跨数据库 SQL 转换助手

---

最佳实践与陷阱

最佳实践

1. 优先使用 LINQ,必要时用原始 SQL

// ✅ 推荐: 简单查询用 LINQ
var products = await context.Products
    .Where(p => p.Price > 100)
    .OrderByDescending(p => p.CreatedAt)
    .ToListAsync();

// ✅ 推荐: 复杂查询用原始 SQL
var report = await context.ReportData
    .FromSqlRaw(@"
        WITH MonthlySales AS (...)
        SELECT * FROM MonthlySales
    ")
    .ToListAsync();

2. 始终使用参数化查询

// ✅ 推荐: 插值语法(自动参数化)
var name = "Laptop";
var products = context.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Name LIKE %{name}%")
    .ToList();

// ❌ 避免: 字符串拼接(SQL 注入风险)
var products = context.Products
    .FromSqlRaw($"SELECT * FROM Products WHERE Name LIKE '%{name}%'")  // 💥 危险!
    .ToList();

3. 为原始 SQL 创建专用 DTO

// ✅ 推荐: 专用 DTO
public class SalesReportDto
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public decimal TotalSales { get; set; }
    public int UnitsSold { get; set; }
}

var report = context.SalesReports
    .FromSqlRaw(@"
        SELECT 
            p.Id as ProductId,
            p.Name as ProductName,
            SUM(oi.Quantity * oi.UnitPrice) as TotalSales,
            SUM(oi.Quantity) as UnitsSold
        FROM OrderItems oi
        INNER JOIN Products p ON oi.ProductId = p.Id
        GROUP BY p.Id, p.Name
    ")
    .AsNoTracking()
    .ToList();

4. 封装复用

// ✅ 推荐: 扩展方法封装
public static class DbContextExtensions
{
    public static async Task<List<T>> ExecuteStoredProcedureAsync<T>(
        this DbContext context, 
        string procedureName, 
        params object[] parameters) where T : class
    {
        var sqlParams = parameters.Select((p, i) => new SqlParameter($"@p{i}", p)).ToArray();
        var paramNames = string.Join(", ", sqlParams.Select(p => p.ParameterName));
        
        return await context.Set<T>()
            .FromSqlRaw($"EXEC {procedureName} {paramNames}", sqlParams)
            .ToListAsync();
    }
}

// 使用
var products = await context.ExecuteStoredProcedureAsync<Product>(
    "GetProductsByCategory", categoryId, minPrice);

常见陷阱

陷阱 1: SQL 注入

// ❌ 错误: 直接拼接用户输入
var userInput = request.Query["search"];
var products = context.Products
    .FromSqlRaw($"SELECT * FROM Products WHERE Name LIKE '%{userInput}%'")  // 💥 注入!
    .ToList();

// ✅ 正确: 参数化
var products = context.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Name LIKE %{userInput}%")  // ✅ 安全
    .ToList();

陷阱 2: 忘记 AsNoTracking

// ⚠️ 注意: 原始 SQL 默认跟踪变更
var products = context.Products
    .FromSqlRaw("SELECT * FROM Products")
    .ToList();  // 跟踪所有实体,占用内存

// ✅ 推荐: 只读查询禁用跟踪
var products = context.Products
    .FromSqlRaw("SELECT * FROM Products")
    .AsNoTracking()  // ← 添加此方法
    .ToList();

陷阱 3: 列名不匹配

// ❌ 错误: 列名与属性不匹配
var products = context.Products
    .FromSqlRaw("SELECT product_id, product_name FROM Products")  // 💥 列名不匹配
    .ToList();

// ✅ 正确: 使用别名
var products = context.Products
    .FromSqlRaw("SELECT product_id as Id, product_name as Name FROM Products")
    .ToList();

陷阱 4: 事务外执行修改操作

// ❌ 错误: 未在事务中执行修改
await context.Database.ExecuteSqlRawAsync("UPDATE Products SET Price = 100");
await context.Database.ExecuteSqlRawAsync("UPDATE Products SET Stock = 50");
// 💥 如果第二条失败,第一条无法回滚

// ✅ 正确: 使用事务
using var transaction = await context.Database.BeginTransactionAsync();
try
{
    await context.Database.ExecuteSqlRawAsync("UPDATE Products SET Price = 100");
    await context.Database.ExecuteSqlRawAsync("UPDATE Products SET Stock = 50");
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

---

总结

核心要点

1. 使用场景: 复杂查询、性能优化、数据库特性、存储过程
2. API: FromSqlRaw/FromSqlInterpolated 查询,ExecuteSqlRaw 执行命令
3. 安全性: 始终使用参数化查询,防止 SQL 注入
4. 性能: 原始 SQL 可能比 LINQ 快 2-3x(复杂查询)
5. 限制: 必须返回实体所有列,或投影到 DTO

决策流程

需要查询数据?
├─ 简单查询 → LINQ
├─ 复杂查询 → 原始 SQL
│   ├─ 需要实体跟踪 → FromSql + 返回所有列
│   └─ 只读查询 → FromSql + AsNoTracking + DTO
└─ 执行命令 → ExecuteSqlRaw
    ├─ 批量更新/删除 → ExecuteSqlRawAsync
    └─ 存储过程 → FromSqlRaw("EXEC ...")

代码模板

// 模板 1: 基本查询
var entities = context.Entities
    .FromSqlInterpolated($"SELECT * FROM Entities WHERE Column = {value}")
    .AsNoTracking()
    .ToList();

// 模板 2: 组合 LINQ
var entities = context.Entities
    .FromSqlInterpolated($"SELECT * FROM Entities WHERE Date >= {startDate}")
    .Where(e => e.Status == "Active")
    .OrderByDescending(e => e.CreatedAt)
    .Take(10)
    .ToList();

// 模板 3: 执行命令
var affectedRows = await context.Database.ExecuteSqlRawAsync(
    "UPDATE Entities SET Status = {0} WHERE Date < {1}",
    "Archived", cutoffDate);

// 模板 4: 存储过程
var results = context.Results
    .FromSqlRaw("EXEC StoredProcedureName @Param1 = {0}, @Param2 = {1}", 
                param1, param2)
    .ToList();

下一步

• 📖 阅读 客户端vs服务端评估
• 🔧 学习 拦截器技术
• 🚀 了解 图形更新