原生SQL高级用法

虽然 EF Core 的 LINQ 提供了强大的查询能力,但在某些场景下,我们仍需使用原生 SQL 来实现复杂查询、性能优化或数据库特定功能。本章将深入探讨 EF Core 中原生 SQL 的高级用法,包括参数化查询、存储过程、表值函数、批量操作等。

1. 原生SQL基础

1.1 何时使用原生SQL?

适用场景:

✅ 推荐使用原生SQL:

复杂报表查询(多表JOIN、聚合、窗口函数)
数据库特定功能(全文搜索、空间查询、JSON操作)
批量更新/删除(LINQ不支持)
性能关键路径(需要精细控制SQL)
遗留数据库视图/存储过程
递归查询(CTE)

❌ 不推荐使用原生SQL:

简单CRUD操作
需要跨数据库兼容
动态条件查询(可用LINQ)
包含业务逻辑的查询

1.2 原生SQL API概览

csharp

// 1. 查询实体 - FromSqlRaw / FromSqlInterpolated
var products = context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 100)
    .ToList();

// 2. 执行非查询 - ExecuteSqlRaw / ExecuteSqlRawAsync
var rows = await context.Database
    .ExecuteSqlRawAsync("UPDATE Products SET Price = Price * 1.1");

// 3. 标量查询 - SqlQueryRaw (.NET 7+)
var count = await context.Database
    .SqlQueryRaw<int>("SELECT COUNT(*) FROM Products")
    .FirstOrDefaultAsync();

// 4. 无跟踪查询 - AsNoTracking
var products = context.Products
    .FromSqlRaw("SELECT * FROM Products")
    .AsNoTracking()
    .ToList();

2. FromSqlRaw 与 FromSqlInterpolated

2.1 FromSqlRaw - 参数化查询

基本语法:

csharp

public class ProductService
{
    private readonly AppDbContext _context;
    
    public async Task<List<Product>> GetExpensiveProducts(decimal minPrice)
    {
        // ✅ 安全: 使用参数化查询
        var products = await _context.Products
            .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", minPrice)
            .ToListAsync();
        
        return products;
    }
    
    public async Task<List<Product>> SearchProducts(string name, decimal? minPrice, int? categoryId)
    {
        // 多参数示例
        var sql = @"
            SELECT * FROM Products 
            WHERE Name LIKE {0}
            AND ({1} IS NULL OR Price >= {1})
            AND ({2} IS NULL OR CategoryId = {2})";
        
        var searchPattern = $"%{name}%";
        
        return await _context.Products
            .FromSqlRaw(sql, searchPattern, minPrice, categoryId)
            .ToListAsync();
    }
}

⚠️ 重要: 参数占位符规则:

csharp

// ✅ 正确: 使用 {0}, {1}, {2}... 作为占位符
var products = context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0} AND Stock < {1}", 
        minPrice, maxStock)
    .ToList();

// ❌ 错误: 不要使用 @param 或 ?
var products = context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > @minPrice", minPrice)  // 错误!
    .ToList();

// ❌ 错误: 不要字符串拼接
var products = context.Products
    .FromSqlRaw($"SELECT * FROM Products WHERE Price > {minPrice}")  // SQL注入风险!
    .ToList();

2.2 FromSqlInterpolated - 插值字符串

更直观的语法:

csharp

public async Task<List<Product>> SearchWithInterpolation(string name, decimal minPrice)
{
    // ✅ 安全: 自动参数化
    var products = await _context.Products
        .FromSqlInterpolated($@"
            SELECT * FROM Products 
            WHERE Name LIKE {%{name}%}
            AND Price > {minPrice}
            ORDER BY Price DESC
        ")
        .ToListAsync();
    
    return products;
}

// 编译器转换为:
// FromSqlRaw("SELECT * FROM Products WHERE Name LIKE {0} AND Price > {1}", "%name%", minPrice)

对比:

csharp

// 方式1: FromSqlRaw - 适合动态构建SQL
var sql = "SELECT * FROM Products WHERE 1=1";
var parameters = new List<object>();

if (name != null)
{
    sql += " AND Name LIKE {" + parameters.Count + "}";
    parameters.Add($"%{name}%");
}

if (minPrice.HasValue)
{
    sql += " AND Price >= {" + parameters.Count + "}";
    parameters.Add(minPrice.Value);
}

var products = context.Products
    .FromSqlRaw(sql, parameters.ToArray())
    .ToList();

// 方式2: FromSqlInterpolated - 适合静态SQL
var products = context.Products
    .FromSqlInterpolated($@"
        SELECT * FROM Products 
        WHERE Name LIKE {%{name}%}
        AND Price >= {minPrice}
    ")
    .ToList();

2.3 组合LINQ查询

在原生SQL基础上使用LINQ:

csharp

// 原生SQL + LINQ过滤
var products = await context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE IsAvailable = 1")
    .Where(p => p.Price > 100)
    .OrderByDescending(p => p.CreatedAt)
    .Take(10)
    .ToListAsync();

// 生成的SQL:
// SELECT TOP(10) [p].[Id], [p].[Name], [p].[Price], ...
// FROM (
//     SELECT * FROM Products WHERE IsAvailable = 1
// ) AS [p]
// WHERE [p].[Price] > 100
// ORDER BY [p].[CreatedAt] DESC

// 原生SQL + Include(预加载)
var orders = await context.Orders
    .FromSqlRaw("SELECT * FROM Orders WHERE OrderDate >= {0}", startDate)
    .Include(o => o.Items)
    .ThenInclude(i => i.Product)
    .Include(o => o.Customer)
    .ToListAsync();

// 原生SQL + 投影
var productDtos = await context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE CategoryId = {0}", categoryId)
    .Select(p => new ProductDto
    {
        Id = p.Id,
        Name = p.Name,
        Price = p.Price,
        CategoryName = p.Category.Name
    })
    .ToListAsync();

2.4 列名映射

当数据库列名与实体属性不匹配时:

csharp

public class Product
{
    public int Id { get; set; }
    
    [Column("product_name")]  // 数据库列名
    public string Name { get; set; }
    
    [Column("unit_price")]
    public decimal Price { get; set; }
}

// 查询时必须返回正确的列名
var products = context.Products
    .FromSqlRaw(@"
        SELECT 
            Id,
            product_name AS Name,     -- 或使用别名
            unit_price AS Price
        FROM Products 
        WHERE unit_price > {0}", minPrice)
    .ToList();

// 或者使用 Fluent API 配置
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>(entity =>
    {
        entity.ToTable("Products");
        entity.Property(p => p.Name).HasColumnName("product_name");
        entity.Property(p => p.Price).HasColumnName("unit_price");
    });
}

3. ExecuteSqlRaw 执行非查询

3.1 批量更新

csharp

public class BatchUpdateService
{
    private readonly AppDbContext _context;
    
    /// <summary>
    /// 批量更新价格
    /// </summary>
    public async Task<int> UpdatePricesAsync(decimal multiplier, CancellationToken ct = default)
    {
        var rows = await _context.Database
            .ExecuteSqlRawAsync(
                "UPDATE Products SET Price = Price * {0} WHERE IsAvailable = 1", 
                multiplier, ct);
        
        return rows;
    }
    
    /// <summary>
    /// 批量标记为已删除
    /// </summary>
    public async Task<int> SoftDeleteRangeAsync(DateTime beforeDate, string deletedBy)
    {
        return await _context.Database.ExecuteSqlRawAsync(@"
            UPDATE Products 
            SET IsDeleted = 1, 
                DeletedAt = GETUTCDATE(), 
                DeletedBy = {0}
            WHERE CreatedAt < {1} AND IsDeleted = 0",
            deletedBy, beforeDate);
    }
    
    /// <summary>
    /// 批量设置库存
    /// </summary>
    public async Task UpdateStockAsync(Dictionary<int, int> productStocks)
    {
        using var transaction = await _context.Database.BeginTransactionAsync();
        
        try
        {
            foreach (var kvp in productStocks)
            {
                await _context.Database.ExecuteSqlRawAsync(
                    "UPDATE Products SET Stock = {0} WHERE Id = {1}",
                    kvp.Value, kvp.Key);
            }
            
            await transaction.CommitAsync();
        }
        catch
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
}

3.2 批量删除

csharp

/// <summary>
/// 物理删除已软删除的记录(清理)
/// </summary>
public async Task<int> PurgeSoftDeletedAsync(DateTime beforeDate)
{
    return await _context.Database.ExecuteSqlRawAsync(@"
        DELETE FROM Products 
        WHERE IsDeleted = 1 AND DeletedAt < {0}",
        beforeDate);
}

/// <summary>
/// 删除过期订单
/// </summary>
public async Task<int> DeleteExpiredOrdersAsync(int daysToKeep)
{
    return await _context.Database.ExecuteSqlRawAsync(@"
        DELETE FROM Orders 
        WHERE Status = 'Expired' 
        AND OrderDate < DATEADD(DAY, -{0}, GETUTCDATE())",
        daysToKeep);
}

3.3 插入数据

csharp

/// <summary>
/// 批量插入(高效)
/// </summary>
public async Task BulkInsertAsync(List<Product> products)
{
    using var connection = _context.Database.GetDbConnection();
    await connection.OpenAsync();
    
    using var command = connection.CreateCommand();
    command.CommandText = @"
        INSERT INTO Products (Name, Price, Stock, CreatedAt)
        VALUES (@Name, @Price, @Stock, @CreatedAt)";
    
    var nameParam = command.CreateParameter();
    nameParam.ParameterName = "@Name";
    command.Parameters.Add(nameParam);
    
    var priceParam = command.CreateParameter();
    priceParam.ParameterName = "@Price";
    command.Parameters.Add(priceParam);
    
    var stockParam = command.CreateParameter();
    stockParam.ParameterName = "@Stock";
    command.Parameters.Add(stockParam);
    
    var createdAtParam = command.CreateParameter();
    createdAtParam.ParameterName = "@CreatedAt";
    createdAtParam.Value = DateTime.UtcNow;
    command.Parameters.Add(createdAtParam);
    
    foreach (var product in products)
    {
        nameParam.Value = (object)product.Name ?? DBNull.Value;
        priceParam.Value = product.Price;
        stockParam.Value = product.Stock;
        
        await command.ExecuteNonQueryAsync();
    }
}

/// <summary>
/// 从另一张表复制数据
/// </summary>
public async Task<int> CopyProductsAsync(int sourceCategoryId, int targetCategoryId)
{
    return await _context.Database.ExecuteSqlRawAsync(@"
        INSERT INTO Products (Name, Price, Stock, CategoryId, CreatedAt)
        SELECT Name, Price, Stock, {0}, GETUTCDATE()
        FROM Products
        WHERE CategoryId = {1}",
        targetCategoryId, sourceCategoryId);
}

3.4 返回值处理

csharp

// ExecuteSqlRaw 返回受影响的行数
var updatedRows = await _context.Database
    .ExecuteSqlRawAsync("UPDATE Products SET Price = Price * 1.1");

Console.WriteLine($"Updated {updatedRows} rows");

// 注意: DELETE 也返回行数
var deletedRows = await _context.Database
    .ExecuteSqlRawAsync("DELETE FROM Products WHERE IsDeleted = 1");

Console.WriteLine($"Deleted {deletedRows} rows");

// INSERT 返回插入的行数
var insertedRows = await _context.Database
    .ExecuteSqlRawAsync("INSERT INTO Products (Name, Price) VALUES ('Test', 99.99)");

Console.WriteLine($"Inserted {insertedRows} rows");  // 输出: 1

4. 存储过程调用

4.1 创建存储过程迁移

csharp

public partial class CreateProductSearchProcedure : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            CREATE PROCEDURE [dbo].[sp_SearchProducts]
                @SearchTerm NVARCHAR(200),
                @MinPrice DECIMAL(18,2) = NULL,
                @MaxPrice DECIMAL(18,2) = NULL,
                @CategoryId INT = NULL,
                @PageNumber INT = 1,
                @PageSize INT = 20,
                @TotalCount INT OUTPUT
            AS
            BEGIN
                SET NOCOUNT ON;
                
                -- 计算总数
                SELECT @TotalCount = COUNT(*)
                FROM Products p
                WHERE (@SearchTerm IS NULL OR p.Name LIKE '%' + @SearchTerm + '%')
                AND (@MinPrice IS NULL OR p.Price >= @MinPrice)
                AND (@MaxPrice IS NULL OR p.Price <= @MaxPrice)
                AND (@CategoryId IS NULL OR p.CategoryId = @CategoryId)
                AND p.IsDeleted = 0;
                
                -- 分页查询
                SELECT p.Id, p.Name, p.Price, p.Stock, p.CreatedAt
                FROM Products p
                WHERE (@SearchTerm IS NULL OR p.Name LIKE '%' + @SearchTerm + '%')
                AND (@MinPrice IS NULL OR p.Price >= @MinPrice)
                AND (@MaxPrice IS NULL OR p.Price <= @MaxPrice)
                AND (@CategoryId IS NULL OR p.CategoryId = @CategoryId)
                AND p.IsDeleted = 0
                ORDER BY p.CreatedAt DESC
                OFFSET (@PageNumber - 1) * @PageSize ROWS
                FETCH NEXT @PageSize ROWS ONLY;
            END
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP PROCEDURE IF EXISTS [dbo].[sp_SearchProducts]");
    }
}

4.2 调用存储过程

csharp

public class ProductRepository
{
    private readonly AppDbContext _context;
    
    public async Task<(List<Product> Items, int TotalCount)> SearchProductsAsync(
        string searchTerm,
        decimal? minPrice,
        decimal? maxPrice,
        int? categoryId,
        int pageNumber,
        int pageSize)
    {
        // 准备参数
        var searchTermParam = new SqlParameter("@SearchTerm", searchTerm ?? (object)DBNull.Value);
        var minPriceParam = new SqlParameter("@MinPrice", minPrice ?? (object)DBNull.Value);
        var maxPriceParam = new SqlParameter("@MaxPrice", maxPrice ?? (object)DBNull.Value);
        var categoryIdParam = new SqlParameter("@CategoryId", categoryId ?? (object)DBNull.Value);
        var pageNumberParam = new SqlParameter("@PageNumber", pageNumber);
        var pageSizeParam = new SqlParameter("@PageSize", pageSize);
        
        // OUTPUT 参数
        var totalCountParam = new SqlParameter("@TotalCount", SqlDbType.Int)
        {
            Direction = ParameterDirection.Output
        };
        
        // 执行存储过程
        var products = await _context.Products
            .FromSqlRaw(@"
                EXEC sp_SearchProducts 
                    @SearchTerm, @MinPrice, @MaxPrice, @CategoryId, 
                    @PageNumber, @PageSize, @TotalCount OUT",
                searchTermParam, minPriceParam, maxPriceParam, 
                categoryIdParam, pageNumberParam, pageSizeParam, totalCountParam)
            .ToListAsync();
        
        var totalCount = (int)totalCountParam.Value;
        
        return (products, totalCount);
    }
}

4.3 返回结果集的存储过程

sql

-- 创建返回多个结果集的存储过程
CREATE PROCEDURE [dbo].[sp_GetOrderStatistics]
    @Year INT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 结果集1: 月度统计
    SELECT 
        MONTH(OrderDate) AS Month,
        COUNT(*) AS TotalOrders,
        SUM(TotalAmount) AS TotalRevenue
    FROM Orders
    WHERE YEAR(OrderDate) = @Year
    GROUP BY MONTH(OrderDate)
    ORDER BY Month;
    
    -- 结果集2: 产品分类统计
    SELECT 
        c.Name AS CategoryName,
        COUNT(DISTINCT o.Id) AS OrderCount,
        SUM(oi.Quantity) AS TotalQuantity
    FROM Orders o
    INNER JOIN OrderItems oi ON o.Id = oi.OrderId
    INNER JOIN Products p ON oi.ProductId = p.Id
    INNER JOIN Categories c ON p.CategoryId = c.Id
    WHERE YEAR(o.OrderDate) = @Year
    GROUP BY c.Name
    ORDER BY TotalQuantity DESC;
END

读取多个结果集:

csharp

public async Task<(List<MonthlyStats> Monthly, List<CategoryStats> Categories)> 
    GetOrderStatisticsAsync(int year)
{
    var connection = _context.Database.GetDbConnection();
    await connection.OpenAsync();
    
    using var command = connection.CreateCommand();
    command.CommandText = "sp_GetOrderStatistics";
    command.CommandType = CommandType.StoredProcedure;
    
    var yearParam = command.CreateParameter();
    yearParam.ParameterName = "@Year";
    yearParam.Value = year;
    command.Parameters.Add(yearParam);
    
    using var reader = await command.ExecuteReaderAsync();
    
    // 读取第一个结果集
    var monthly = new List<MonthlyStats>();
    while (await reader.ReadAsync())
    {
        monthly.Add(new MonthlyStats
        {
            Month = reader.GetInt32(0),
            TotalOrders = reader.GetInt32(1),
            TotalRevenue = reader.GetDecimal(2)
        });
    }
    
    // 移动到下一个结果集
    await reader.NextResultAsync();
    
    // 读取第二个结果集
    var categories = new List<CategoryStats>();
    while (await reader.ReadAsync())
    {
        categories.Add(new CategoryStats
        {
            CategoryName = reader.GetString(0),
            OrderCount = reader.GetInt32(1),
            TotalQuantity = reader.GetInt32(2)
        });
    }
    
    return (monthly, categories);
}

5. 表值函数(TVF)

5.1 创建表值函数

csharp

public partial class CreateProductSearchFunction : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            CREATE FUNCTION [dbo].[fn_GetProductsInRange]
            (
                @MinPrice DECIMAL(18,2),
                @MaxPrice DECIMAL(18,2)
            )
            RETURNS TABLE
            AS
            RETURN
            (
                SELECT Id, Name, Price, Stock, CategoryId, CreatedAt
                FROM Products
                WHERE Price BETWEEN @MinPrice AND @MaxPrice
                AND IsDeleted = 0
            )
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP FUNCTION IF EXISTS [dbo].[fn_GetProductsInRange]");
    }
}

5.2 映射TVF到DbContext

csharp

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    
    // TVF 方法
    public IQueryable<Product> GetProductsInRange(decimal minPrice, decimal maxPrice)
    {
        return Set<Product>()
            .FromSqlInterpolated($"SELECT * FROM fn_GetProductsInRange({minPrice}, {maxPrice})");
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        
        modelBuilder.Entity<Product>()
            .HasNoKey()  // TVF 返回的结果没有主键
            .ToFunction(null);  // 不映射到表
    }
}

// 使用
var products = await context.GetProductsInRange(100, 500)
    .Where(p => p.Stock > 0)
    .OrderByDescending(p => p.CreatedAt)
    .ToListAsync();

6. 复杂结果映射

6.1 映射到DTO

csharp

// 定义 DTO
public class ProductSummaryDto
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; }
    public int ProductCount { get; set; }
    public decimal AvgPrice { get; set; }
    public decimal MinPrice { get; set; }
    public decimal MaxPrice { get; set; }
    public int TotalStock { get; set; }
}

// 使用原生SQL查询并映射到DTO
public async Task<List<ProductSummaryDto>> GetCategorySummaryAsync()
{
    return await _context.Database
        .SqlQueryRaw<ProductSummaryDto>(@"
            SELECT 
                c.Id AS CategoryId,
                c.Name AS CategoryName,
                COUNT(p.Id) AS ProductCount,
                AVG(p.Price) AS AvgPrice,
                MIN(p.Price) AS MinPrice,
                MAX(p.Price) AS MaxPrice,
                SUM(p.Stock) AS TotalStock
            FROM Categories c
            LEFT JOIN Products p ON c.Id = p.CategoryId AND p.IsDeleted = 0
            GROUP BY c.Id, c.Name
            ORDER BY ProductCount DESC
        ")
        .ToListAsync();
}

6.2 映射到复杂类型

csharp

public class OrderDetailDto
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public string CustomerName { get; set; }
    public List<OrderItemDto> Items { get; set; }
    public decimal TotalAmount { get; set; }
}

public class OrderItemDto
{
    public string ProductName { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public decimal Subtotal { get; set; }
}

// 手动映射复杂结构
public async Task<List<OrderDetailDto>> GetOrderDetailsAsync(DateTime fromDate)
{
    var connection = _context.Database.GetDbConnection();
    await connection.OpenAsync();
    
    using var command = connection.CreateCommand();
    command.CommandText = @"
        SELECT 
            o.Id AS OrderId,
            o.OrderDate,
            c.Name AS CustomerName,
            p.Name AS ProductName,
            oi.Quantity,
            oi.UnitPrice,
            oi.Quantity * oi.UnitPrice AS Subtotal
        FROM Orders o
        INNER JOIN Customers c ON o.CustomerId = c.Id
        INNER JOIN OrderItems oi ON o.Id = oi.OrderId
        INNER JOIN Products p ON oi.ProductId = p.Id
        WHERE o.OrderDate >= @FromDate
        ORDER BY o.OrderDate DESC";
    
    var fromParam = command.CreateParameter();
    fromParam.ParameterName = "@FromDate";
    fromParam.Value = fromDate;
    command.Parameters.Add(fromParam);
    
    using var reader = await command.ExecuteReaderAsync();
    
    var orders = new Dictionary<int, OrderDetailDto>();
    
    while (await reader.ReadAsync())
    {
        var orderId = reader.GetInt32(0);
        
        if (!orders.ContainsKey(orderId))
        {
            orders[orderId] = new OrderDetailDto
            {
                OrderId = orderId,
                OrderDate = reader.GetDateTime(1),
                CustomerName = reader.GetString(2),
                Items = new List<OrderItemDto>(),
                TotalAmount = 0
            };
        }
        
        var order = orders[orderId];
        var item = new OrderItemDto
        {
            ProductName = reader.GetString(3),
            Quantity = reader.GetInt32(4),
            UnitPrice = reader.GetDecimal(5),
            Subtotal = reader.GetDecimal(6)
        };
        
        order.Items.Add(item);
        order.TotalAmount += item.Subtotal;
    }
    
    return orders.Values.ToList();
}

7. SQL注入防护

7.1 常见注入攻击

csharp

// ❌ 极度危险: 字符串拼接
var userInput = "'; DROP TABLE Products; --";
var products = context.Products
    .FromSqlRaw($"SELECT * FROM Products WHERE Name = '{userInput}'")  // SQL注入!
    .ToList();

// ❌ 仍然危险: 格式化字符串
var products = context.Products
    .FromSqlRaw(string.Format("SELECT * FROM Products WHERE Name = '{0}'", userInput))
    .ToList();

// ✅ 安全: 参数化查询
var products = context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Name = {0}", userInput)
    .ToList();

// ✅ 安全: 插值字符串(自动参数化)
var products = context.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Name = {userInput}")
    .ToList();

7.2 动态SQL防护

csharp

public async Task<List<Product>> DynamicSearchAsync(
    string sortBy,      // 用户输入的排序字段
    string sortOrder,   // ASC 或 DESC
    string searchTerm)
{
    // ⚠️ 排序字段不能参数化,必须白名单验证
    var allowedSortFields = new[] { "Name", "Price", "CreatedAt", "Stock" };
    
    if (!allowedSortFields.Contains(sortBy, StringComparer.OrdinalIgnoreCase))
    {
        sortBy = "CreatedAt";  // 默认值
    }
    
    sortOrder = sortOrder.ToUpper() == "DESC" ? "DESC" : "ASC";
    
    // ✅ 排序字段白名单 + 搜索词参数化
    var sql = $@"
        SELECT * FROM Products 
        WHERE Name LIKE {{0}}
        ORDER BY [{sortBy}] {sortOrder}";
    
    return await context.Products
        .FromSqlRaw(sql, $"%{searchTerm}%")
        .ToListAsync();
}

7.3 存储过程中的防护

sql

-- ❌ 危险: 动态SQL拼接
CREATE PROCEDURE sp_DangerousSearch
    @SearchTerm NVARCHAR(200)
AS
BEGIN
    EXEC('SELECT * FROM Products WHERE Name LIKE ''' + @SearchTerm + '''');
END

-- ✅ 安全: 使用 sp_executesql 参数化
CREATE PROCEDURE sp_SafeSearch
    @SearchTerm NVARCHAR(200)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT * FROM Products 
        WHERE Name LIKE @Term';
    
    EXEC sp_executesql @SQL, N'@Term NVARCHAR(200)', @Term = @SearchTerm;
END

8. 性能优化与最佳实践

8.1 性能对比

csharp

// 场景: 查询10000条记录

// 方式1: LINQ查询
var sw1 = Stopwatch.StartNew();
var products1 = await context.Products
    .Where(p => p.Price > 100)
    .OrderByDescending(p => p.CreatedAt)
    .Take(1000)
    .ToListAsync();
sw1.Stop();
Console.WriteLine($"LINQ: {sw1.ElapsedMilliseconds}ms");

// 方式2: 原生SQL
var sw2 = Stopwatch.StartNew();
var products2 = await context.Products
    .FromSqlRaw(@"
        SELECT * FROM Products 
        WHERE Price > {0}
        ORDER BY CreatedAt DESC
        OFFSET 0 ROWS FETCH NEXT {1} ROWS ONLY",
        100, 1000)
    .ToListAsync();
sw2.Stop();
Console.WriteLine($"Raw SQL: {sw2.ElapsedMilliseconds}ms");

// 通常原生SQL快10-30%(减少翻译开销)

8.2 最佳实践清单

✅ 应该做的:

始终使用参数化查询

csharp

FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", price)

添加索引提示

csharp

FromSqlRaw("SELECT * FROM Products WITH(INDEX(IX_Products_Price)) WHERE Price > {0}", price)

只选择需要的列

csharp

FromSqlRaw("SELECT Id, Name, Price FROM Products WHERE ...")

使用异步方法

csharp

await FromSqlRawAsync(...)
await ExecuteSqlRawAsync(...)

监控慢查询

csharp

optionsBuilder.LogTo(Console.WriteLine, LogLevel.Warning);

❌ 不应该做的:

不要字符串拼接
不要在循环中执行SQL
不要忘记事务
不要忽略异常
不要硬编码表名/列名

总结

原生SQL是EF Core的强大补充:

核心优势

✅ 完全控制 - 精确控制SQL执行
✅ 高性能 - 减少ORM翻译开销
✅ 数据库特性 - 使用特定数据库功能

关键原则

始终参数化 - 防止SQL注入
白名单验证 - 动态SQL必须验证
异步执行 - 避免阻塞
事务管理 - 保证数据一致性
性能监控 - 识别慢查询

掌握原生SQL,你可以在需要时突破ORM限制,实现最优性能!

原生SQL高级用法 ​

目录 ​

1. 原生SQL基础 ​

1.1 何时使用原生SQL? ​

1.2 原生SQL API概览 ​

2. FromSqlRaw 与 FromSqlInterpolated ​

2.1 FromSqlRaw - 参数化查询 ​

2.2 FromSqlInterpolated - 插值字符串 ​

2.3 组合LINQ查询 ​

2.4 列名映射 ​

3. ExecuteSqlRaw 执行非查询 ​

3.1 批量更新 ​

3.2 批量删除 ​

3.3 插入数据 ​

3.4 返回值处理 ​

4. 存储过程调用 ​

4.1 创建存储过程迁移 ​

4.2 调用存储过程 ​

4.3 返回结果集的存储过程 ​

5. 表值函数(TVF) ​

5.1 创建表值函数 ​

5.2 映射TVF到DbContext ​

6. 复杂结果映射 ​

6.1 映射到DTO ​

6.2 映射到复杂类型 ​

7. SQL注入防护 ​

7.1 常见注入攻击 ​

7.2 动态SQL防护 ​

7.3 存储过程中的防护 ​

8. 性能优化与最佳实践 ​

8.1 性能对比 ​

8.2 最佳实践清单 ​

总结 ​

核心优势 ​

关键原则 ​

原生SQL高级用法

目录

1. 原生SQL基础

1.1 何时使用原生SQL?

1.2 原生SQL API概览

2. FromSqlRaw 与 FromSqlInterpolated

2.1 FromSqlRaw - 参数化查询

2.2 FromSqlInterpolated - 插值字符串

2.3 组合LINQ查询

2.4 列名映射

3. ExecuteSqlRaw 执行非查询

3.1 批量更新

3.2 批量删除

3.3 插入数据

3.4 返回值处理

4. 存储过程调用

4.1 创建存储过程迁移

4.2 调用存储过程

4.3 返回结果集的存储过程

5. 表值函数(TVF)

5.1 创建表值函数

5.2 映射TVF到DbContext

6. 复杂结果映射

6.1 映射到DTO

6.2 映射到复杂类型

7. SQL注入防护

7.1 常见注入攻击

7.2 动态SQL防护

7.3 存储过程中的防护

8. 性能优化与最佳实践

8.1 性能对比

8.2 最佳实践清单

总结

核心优势

关键原则