【Entity Framework】你必须了解的之自定义SQL查询

一、概述

通过 Entity Framework Core 可以在使用关系数据库时下降到 SQL 查询。 如果所需查询无法使用 LINQ 表示，或者 LINQ 查询导致 EF 生成效率低下的 SQL，则可使用 SQL 查询。 SQL 查询可返回一般实体类型或者模型中的无键实体类型。

二、基本SQL查询

可使用FromSql 基于SQL查询开始LINQ查询：

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.Blogs")
    .ToList();

EF Core 7.0中引入FromSql。使用更旧的版本时，请改用FromSqlInterpolated。

SQL 查询可用于执行返回实体数据的存储过程：

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

FromSql只能直接在DbSet 上使用。不能在任意Linq查询的基础上组合使用它。

三、快速参考

请在使用 SQL 查询时密切关注参数化

向 SQL 查询引入任何用户提供的值时，必须注意防范 SQL 注入攻击。 如果程序将用户提供的字符串值集成到 SQL 查询中，而用户提供的值被创建用来终止字符串并执行另一个恶意 SQL 操作，则表明发生了 SQL 注入。

FromSql 和 FromSqlInterpolated方法可以防止 SQL 注入，始终将参数数据作为单独的 SQL 参数进行集成。 但是，如果不当使用，FromSqlRaw方法可能易受 SQL 注入攻击。

示例通过在 SQL 查询字符串中包含参数占位符并提供额外的自变量，将单个参数传递到存储过程：

var user = "johndoe";
var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

虽然此语法可能看上去像常规 C# 字符串内插，但提供的值包装在 DbParameter 中，且生成的参数名称插入到指定了 {0} 占位符的位置。 这使得 FromSql 可以免受 SQL 注入攻击，可以将值高效且正确地发送到数据库。

执行存储过程时，在 SQL 查询字符串中使用命名参数很有用，尤其是在存储过程具有可选参数的情况下：

var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToList();

如果需要对要发送的数据库参数进行更多控制，还可以构造 DbParameter 并将其作为参数值提供。 这样就可以设置参数的精确数据库类型，或 facet（例如其大小、精度或长度）：

var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

传递的参数必须与存储过程定义完全匹配。 请特别注意参数的排序，注意不要缺失或错放任何参数，也可以考虑使用命名参数表示法。 此外，请确保参数类型对应，并根据需要设置其 facet（大小、精度、规模）。

四、动态SQL和参数

应尽可能使用 FromSql及其参数化。 但在某些情况下，需要将 SQL 动态拼凑在一起，并且无法使用数据库参数。 例如，假设 C# 变量包含要通过其进行筛选的属性的名称。 你可能会迫不及待地想要使用 SQL 查询，例如：

var propertyName = "User";
var propertyValue = "johndoe";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToList();

此代码无效，因为数据库不允许将列名（或架构的任何其他部分）参数化。

首先，请务必考虑通过 SQL 或其他方式动态构造查询的影响。 接受用户提供的列名时，用户可能会选择一个未编制索引的列，使查询运行起来极慢且让数据库过载；用户也可能选择一个包含你不希望公开的数据的列。 除了真正动态的方案外，通常情况下，最好是让两个查询使用两个列名，而不是使用参数化将它们折叠到单个查询中。

如果决定要动态构造 SQL，则必须使用 FromSqlRaw，这样就可以直接将变量数据内插到 SQL 字符串中，而不使用数据库参数：

var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");
var blogs = context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToList();

在上面的代码中，我们使用 C# 字符串内插将列名直接插入到 SQL 中。 你有责任确保此字符串值是安全的，如果它的来源不安全，请对其进行清理；这意味着检测特殊字符（如分号、注释和其他 SQL 构造），并正确地将这些字符进行转义或拒绝此类输入。

另一方面，列值通过 DbParameter 发送，因此在遇到 SQL 注入时是安全的。

使用FromSqlRaw时要非常小心，始终确保值来自安全的源，或者经过正确清理。SQL注入攻击可能会为应用程序带来灾难后果。

五、使用LINQ编写

可以使用 LINQ 运算符在初始 SQL 查询的基础上进行组合；EF Core 会将 SQL 视为子查询，在数据库中以它为基础进行组合。 下面的示例使用 SQL 查询，该查询从表值函数 (TVF) 中进行选择。 然后，使用 LINQ 进行筛选和排序，从而对其进行组合。

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToList();

上面的查询生成以下 SQL：

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM (
    SELECT * FROM dbo.SearchBlogs(@p0)
) AS [b]
WHERE [b].[Rating] > 3
ORDER BY [b].[Rating] DESC

包含关联数据

Include 运算符可用于加载相关数据，就像对其他 LINQ 查询那样：

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

使用 LINQ 进行组合要求 SQL 查询是可组合的，因为 EF Core 会将提供的 SQL 视为子查询。 可组合 SQL 查询通常以 SELECT 关键字开头，不能包含子查询中无效的 SQL 功能，例如：

• 结尾分号
• 在 SQL Server 上，结尾处的查询级提示（例如，OPTION (HASH JOIN)）
• 在 SQL Server 上，SELECT 子句中不与 OFFSET 0 或 TOP 100 PERCENT 配合使用的 ORDER BY 子句

SQL Server 不允许对存储过程调用进行组合，因此任何尝试向此类调用应用其他查询运算符的操作都将导致无效的 SQL。

请在FromSql或FromSqlRaw之后立即使用AsEnumerable或AsAsyncEnumerable，确保EF Core不会尝试对存储过程进行组合。

六、更改跟踪

使用 FromSql 或 FromSqlRaw 的查询遵循与 EF Core 中所有其他 LINQ 查询完全相同的更改跟踪规则。 例如，如果该查询投影实体类型，默认情况下会跟踪结果。

下面的示例使用 SQL 查询，该查询从表值函数 (TVF) 中进行选择，然后禁用通过调用AsNoTracking进行的更改跟踪：

var searchTerm = "Lorem ipsum";
var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToList();

查询标量（非实体）类型

虽然可以使用 FromSql来查询模型中定义的实体，但如果使用 SqlQuery，你就可以通过 SQL 轻松查询非实体标量类型，无需下降到较低级别的数据访问 API。 例如，以下查询从 Blogs 表中提取所有 ID：

var ids = context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToList();

还可以在 SQL 查询的基础上组合使用 LINQ 运算符。 但是，由于 SQL 成为子查询，其输出列需要由 SQL EF 添加项来引用，因此必须为输出列 Value 命名。 例如，以下查询返回的 ID 高于 ID 平均值：

var overAverageIds = context.Database
    .SqlQuery<int>($"SELECT [BlogId] AS [Value] FROM [Blogs]")
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

FromSql可与数据库提供程序支持的任何标量类型配合使用。如果想要使用数据库提供程序不支持的类型，可以使用约定前配置为其定义值转换。

SqlQueryRaw允许动态构造SQL查询，就像FromSqlRaw 对实体类型所做的那样。

七、执行非查询 SQL

在某些情况下，可能需要执行不返回任何数据的 SQL，通常用于修改数据库中的数据或调用不返回任何结果集的存储过程。 可以通过ExcuteSql完成此操作：

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
}

它会执行提供的 SQL 并返回修改的行的数目。ExcuteSql使用安全的参数化来防止SQL注入，就像FromSql一样，而ExcuteSqlRaw允许动态构造SQL查询，就像FromSqlRaw对查询所做的那样。

在 EF Core 7.0 之前，有时必须使用 ExecuteSql API 对数据库执行“批量更新”，如上所示；这比在查询所有匹配行后使用 SaveChanges 来修改它们要高效得多。 EF Core 7.0 引入了ExceuteUpdate和ExecuteDelete，因此可以通过LINQ表达高效的批量更新操作。建议尽可能使用这些API而非ExecuteSql。

八、总结

从 SQL 查询返回实体类型时，需注意以下几个限制：

• SQL 查询必须返回实体类型的所有属性的数据。
• 结果集中的列名必须与属性映射到的列名称匹配。 请注意，此行为与 EF6 不同；
• EF6 忽略了 SQL 查询的属性-列映射，只需结果集列名与这些属性名相匹配即可。
• SQL 查询不能包含关联数据。 但是，在许多情况下你可以在查询后面紧跟着使用 Include 方法以返回关联数据