使用Entity Framework Core中的原始SQL查询与存储过程调用指南-源码库

使用Entity Framework Core中的原始SQL查询与存储过程调用指南

你好，我是源码库的技术博主。在多年的.NET开发中，Entity Framework Core (EF Core) 的LINQ查询无疑极大地提升了开发效率。但就像任何优秀的工具都有其适用边界一样，我们总会遇到一些场景：一个极其复杂的多表关联报表、一个必须使用特定数据库优化提示的查询，或者一个遗留系统中已经存在并经过充分优化的存储过程。在这些时候，绕过LINQ，直接使用原始SQL或调用存储过程就成了更明智的选择。今天，我就结合自己的实战经验，和你详细聊聊在EF Core中如何安全、高效地使用这些“高级”功能，并分享一些我踩过的坑。

一、为什么以及何时需要使用原始SQL？

首先，我们必须明确一点：优先使用LINQ。LINQ是类型安全的，能利用编译时检查，并且与EF Core的变更跟踪无缝集成。但在以下情况，原始SQL会成为你的得力助手：

性能瓶颈：对于极其复杂的查询，你手动编写的SQL可能比EF Core生成的SQL更高效。
数据库特定功能：需要使用某个数据库（如SQL Server的FOR JSON PATH、PostgreSQL的窗口函数）特有的、EF Core尚未在LINQ中支持的高级语法。
存储过程调用：与现有数据库架构集成，调用已编写好的业务逻辑存储过程。
批量操作：执行大规模的更新或删除，而无需先将数据加载到内存中（EF Core 7+ 的批量更新更好，但早期版本依赖此方式）。

记住，能力越大，责任越大。使用原始SQL意味着你需要自行防范SQL注入攻击，并处理结果集与实体模型的映射。

二、基础操作：从数据库查询实体

这是最常见的使用场景。EF Core提供了FromSqlRaw和FromSqlInterpolated（或FromSql）方法。这里有一个至关重要的安全提示：永远使用参数化查询来避免SQL注入。

错误示范（危险！）：

// 绝对不要这样写！直接拼接字符串是SQL注入的温床。
var dangerous = context.Blogs.FromSqlRaw($"SELECT * FROM Blogs WHERE Name = '{userInput}'");

正确示范（使用 FromSqlInterpolated）：

string blogName = "源码库技术博客";
var blogs = await context.Blogs
    .FromSqlInterpolated($"SELECT * FROM Blogs WHERE Name = {blogName}")
    .ToListAsync();

EF Core会将{blogName}转换为一个SQL参数（如@p0），从而确保安全。查询返回的仍然是Blog实体集合，你可以继续使用LINQ进行组合：

var filteredBlogs = await context.Blogs
    .FromSqlInterpolated($"SELECT * FROM Blogs WHERE CreatedYear = {2023}")
    .Where(b => b.IsActive) // 可以继续链式调用LINQ操作！
    .OrderBy(b => b.Name)
    .ToListAsync();

实战踩坑提示：使用FromSql*查询实体时，SELECT语句必须返回该实体的所有字段，并且列名必须与模型属性映射的列名匹配。否则，EF Core在后续跟踪或更新时可能会出错。

三、进阶：查询非实体类型与标量值

有时我们只想查询几个字段，或者执行一个返回标量值的聚合函数。这时，我们可以使用DbContext.Database API。

1. 查询自定义类型（非DbSet）：

public class BlogStats
{
    public string BlogName { get; set; }
    public int PostCount { get; set; }
    public DateTime LastPostDate { get; set; }
}

using (var command = context.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = @"
        SELECT b.Name AS BlogName, 
               COUNT(p.Id) AS PostCount,
               MAX(p.PublishedDate) AS LastPostDate
        FROM Blogs b
        LEFT JOIN Posts p ON b.Id = p.BlogId
        GROUP BY b.Id, b.Name";
    context.Database.OpenConnection();
    using (var reader = await command.ExecuteReaderAsync())
    {
        var stats = new List();
        while (await reader.ReadAsync())
        {
            stats.Add(new BlogStats
            {
                BlogName = reader.GetString(0),
                PostCount = reader.GetInt32(1),
                LastPostDate = reader.GetDateTime(2)
            });
        }
        return stats;
    }
}

2. 执行标量查询：

var totalPosts = await context.Database
    .SqlQueryRaw("SELECT COUNT(*) FROM Posts WHERE BlogId = {0}", blogId)
    .SingleAsync();
Console.WriteLine($"总文章数：{totalPosts}");

这里使用了EF Core 7+引入的SqlQueryRaw泛型方法，非常方便。对于更早的版本，你可能需要像上面查询自定义类型那样手动执行命令。

四、执行非查询操作与存储过程调用

对于UPDATE、DELETE或调用不返回结果集的存储过程，我们使用ExecuteSqlRawAsync。

1. 高效的批量更新（无需先查询）：

string category = "旧分类";
string newCategory = "新分类";
int affectedRows = await context.Database.ExecuteSqlRawAsync(
    "UPDATE Posts SET Category = {0} WHERE Category = {1}",
    newCategory, category);
Console.WriteLine($"更新了 {affectedRows} 行数据。");

2. 调用存储过程（无返回值）：

// 假设有一个清理过期日志的存储过程 sp_CleanupOldLogs
int daysToKeep = 30;
await context.Database.ExecuteSqlRawAsync("EXEC sp_CleanupOldLogs @DaysToKeep = {0}", daysToKeep);

3. 调用返回结果集的存储过程：

调用返回实体结果集的存储过程，可以像普通查询一样使用FromSqlRaw。

var year = 2023;
var topBlogs = await context.Blogs
    .FromSqlRaw("EXEC sp_GetTopBlogsForYear @Year = {0}", year)
    .AsNoTracking() // 存储过程结果通常只读，建议不跟踪
    .ToListAsync();

如果存储过程返回的结果集与现有实体不完全匹配，你需要像“查询非实体类型”一节那样，使用原始的ADO.NET方式或定义合适的类型来接收。

五、关键安全实践与性能考量

这是本文的精华部分，请务必牢记：

参数化，参数化，参数化！ 再次强调，使用FromSqlInterpolated或ExecuteSqlRawAsync时传递参数，永远不要用字符串拼接。
慎用动态SQL：如果SQL语句本身（不仅仅是条件值）需要动态构建，务必进行严格的白名单校验。例如，只允许排序字段在几个预定义的列名中选择。
管理数据库连接：FromSql*和ExecuteSql*方法会自动管理连接。但当你使用DbContext.Database.GetDbConnection()进行更低级操作时，必须确保在结束时正确关闭连接，最好使用using语句。
性能与兼容性：原始SQL绕过了EF Core的查询编译和规划阶段，可能会带来轻微的性能提升（或下降，如果你的SQL写得不好）。但代价是失去了数据库提供程序之间的可移植性。你的SQL可能只适用于特定的数据库（如SQL Server）。

六、总结与最佳实践建议

经过上面的探讨，我们可以总结出在EF Core中使用原始SQL和存储过程的清晰路径：

默认使用LINQ：让它处理90%的日常数据访问。
有节制地使用原始SQL：将其用于解决特定的性能问题或访问数据库特有功能。
安全第一：无条件使用参数化查询。
明确边界：对于返回实体的查询，确保SELECT语句完整且列名匹配；对于复杂结果，定义专用的DTO或视图模型。
考虑封装：将复杂的原始SQL或存储过程调用封装在Repository模式或特定的服务类中，避免业务代码中四处散落SQL字符串。

希望这篇指南能帮助你在享受EF Core便利的同时，也能在关键时刻握有原始SQL这把“手术刀”，精准地解决复杂问题。如果你有更多心得或疑问，欢迎在源码库社区继续交流。编码愉快！

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。

使用Entity Framework Core中的原始SQL查询与存储过程调用指南