原生 SQL 查询Raw SQL Queries

通过 Entity Framework Core 可以在使用关系数据库时下降到原始 SQL 查询。Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. 所需查询不能使用 LINQ 来表示时,可以使用原始 SQL 查询。Raw SQL queries are useful if the query you want can't be expressed using LINQ. 如果使用 LINQ 查询导致 SQL 查询效率低下,也可以使用原始 SQL 查询。Raw SQL queries are also used if using a LINQ query is resulting in an inefficient SQL query. 原始 SQL 查询可返回一般实体类型或者模型中的无键实体类型Raw SQL queries can return regular entity types or keyless entity types that are part of your model.

提示

可在 GitHub 上查看此文章的示例You can view this article's sample on GitHub.

基本原生 SQL 查询Basic raw SQL queries

可使用 FromSqlRaw 扩展方法基于原始 SQL 查询开始 LINQ 查询。You can use the FromSqlRaw extension method to begin a LINQ query based on a raw SQL query. FromSqlRaw 只能在直接位于 DbSet<> 上的查询根上使用。FromSqlRaw can only be used on query roots, that is directly on the DbSet<>.

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

原生 SQL 查询可用于执行存储过程。Raw SQL queries can be used to execute a stored procedure.

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

快速参考Passing parameters

警告

始终对原始 SQL 查询使用参数化Always use parameterization for raw SQL queries

向原始 SQL 查询引入任何用户提供的值时,必须注意防范 SQL 注入攻击。When introducing any user-provided values into a raw SQL query, care must be taken to avoid SQL injection attacks. 除了验证确保此类值不包含无效字符,请始终使用会将值与 SQL 文本分开发送的参数化处理。In addition to validating that such values don't contain invalid characters, always use parameterization which sends the values separate from the SQL text.

具体而言,如果连接和内插的字符串 ($"") 带有用户提供的未经验证的值,则切勿将其传递到 FromSqlRawExecuteSqlRawIn particular, never pass a concatenated or interpolated string ($"") with non-validated user-provided values into FromSqlRaw or ExecuteSqlRaw. 通过 FromSqlInterpolatedExecuteSqlInterpolated 方法,可采用一种能抵御 SQL 注入攻击的方式使用字符串内插语法。The FromSqlInterpolated and ExecuteSqlInterpolated methods allow using string interpolation syntax in a way that protects against SQL injection attacks.

下面的示例通过在 SQL 查询字符串中包含形参占位符并提供额外的实参,将单个形参传递到存储过程。The following example passes a single parameter to a stored procedure by including a parameter placeholder in the SQL query string and providing an additional argument. 虽然此语法可能看上去像 String.Format 语法,但提供的值包装在 DbParameter 中,且生成的参数名称插入到指定 {0} 占位符的位置。While this syntax may look like String.Format syntax, the supplied value is wrapped in a DbParameter and the generated parameter name inserted where the {0} placeholder was specified.

var user = "johndoe";

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
    .ToList();

虽然 FromSqlInterpolated 类似于 FromSqlRaw,但你可以借助它使用字符串内插语法。FromSqlInterpolated is similar to FromSqlRaw but allows you to use string interpolation syntax. FromSqlRaw 一样,FromSqlInterpolated 只能在查询根上使用。Just like FromSqlRaw, FromSqlInterpolated can only be used on query roots. 与上述示例一样,该值会转换为 DbParameter,且不易受到 SQL 注入攻击。As with the previous example, the value is converted to a DbParameter and isn't vulnerable to SQL injection.

备注

在版本 3.0 之前,FromSqlRawFromSqlInterpolated 是名为 FromSql 的两个重载。Prior to version 3.0, FromSqlRaw and FromSqlInterpolated were two overloads named FromSql. 有关详细信息,请参阅历史版本部分For more information, see the previous versions section.

var user = "johndoe";

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

还可以构造 DbParameter 并将其作为参数值提供。You can also construct a DbParameter and supply it as a parameter value. 由于使用了常规 SQL 参数占位符而不是字符串占位符,因此可安全地使用 FromSqlRawSince a regular SQL parameter placeholder is used, rather than a string placeholder, FromSqlRaw can be safely used:

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
    .ToList();

借助 FromSqlRaw,可以在 SQL 查询字符串中使用已命名的参数,这在存储的流程具有可选参数时非常有用:FromSqlRaw allows you to use named parameters in the SQL query string, which is useful when a stored procedure has optional parameters:

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser=@user", user)
    .ToList();

备注

参数排序 Entity Framework Core 根据 SqlParameter[] 数组的顺序传递参数。Parameter Ordering Entity Framework Core passes parameters based on the order of the SqlParameter[] array. 传递多个 SqlParameter 时,SQL 字符串中的顺序必须与存储过程定义中参数的顺序相匹配。When passing multiple SqlParameters, the ordering in the SQL string must match the order of the parameters in the stored procedure's definition. 如果不匹配,在执行此过程时,就可能会导致类型转换异常和/或异常行为。Failure to do this may result in type conversion exceptions and/or unexpected behavior when the procedure is executed.

使用 LINQ 编写Composing with LINQ

可使用 LINQ 运算符在初始的原始 SQL 查询基础上进行组合。You can compose on top of the initial raw SQL query using LINQ operators. EF Core 将其视为子查询,并在数据库中对其进行组合。EF Core will treat it as subquery and compose over it in the database. 下面的示例使用原始 SQL 查询,该查询从表值函数 (TVF) 中进行选择。The following example uses a raw SQL query that selects from a Table-Valued Function (TVF). 然后,使用 LINQ 进行筛选和排序,从而对其进行组合。And then composes on it using LINQ to do filtering and sorting.

var searchTerm = ".NET";

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

上面的查询生成以下 SQL:Above query generates following 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 查询那样:The Include method can be used to include related data, just like with any other LINQ query:

var searchTerm = ".NET";

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

使用 LINQ 进行组合要求原始 SQL 查询是可组合的,因为 EF Core 会将提供的 SQL 视为子查询。Composing with LINQ requires your raw SQL query to be composable since EF Core will treat the supplied SQL as a subquery. SELECT 关键字开始的 SQL 查询一般是可组合的。SQL queries that can be composed on begin with the SELECT keyword. 此外,传递的 SQL 不应包含子查询上无效的任何字符或选项,如:Further, SQL passed shouldn't contain any characters or options that aren't valid on a subquery, such as:

  • 结尾分号A trailing semicolon
  • 在 SQL Server 上,结尾处的查询级提示(例如,OPTION (HASH JOIN)On SQL Server, a trailing query-level hint (for example, OPTION (HASH JOIN))
  • 在 SQL Server 上,SELECT 子句中不与 OFFSET 0TOP 100 PERCENT 配合使用的 ORDER BY 子句On SQL Server, an ORDER BY clause that isn't used with OFFSET 0 OR TOP 100 PERCENT in the SELECT clause

SQL Server 不允许对存储过程调用进行组合,因此任何尝试向此类调用应用其他查询运算符的操作都将导致无效的 SQL。SQL Server doesn't allow composing over stored procedure calls, so any attempt to apply additional query operators to such a call will result in invalid SQL. 请在 FromSqlRawFromSqlInterpolated 方法之后立即使用 AsEnumerableAsAsyncEnumerable 方法,确保 EF Core 不会尝试对存储过程进行组合。Use AsEnumerable or AsAsyncEnumerable method right after FromSqlRaw or FromSqlInterpolated methods to make sure that EF Core doesn't try to compose over a stored procedure.

更改跟踪Change Tracking

使用 FromSqlRawFromSqlInterpolated 方法的查询遵循与 EF Core 中所有其他 LINQ 查询完全相同的更改跟踪规则。Queries that use the FromSqlRaw or FromSqlInterpolated methods follow the exact same change tracking rules as any other LINQ query in EF Core. 例如,如果该查询投影实体类型,默认情况下会跟踪结果。For example, if the query projects entity types, the results will be tracked by default.

下面的示例使用原始 SQL 查询,该查询从表值函数 (TVF) 中进行选择,然后禁用通过对 AsNoTracking 的调用来更改跟踪:The following example uses a raw SQL query that selects from a Table-Valued Function (TVF), then disables change tracking with the call to AsNoTracking:

var searchTerm = ".NET";

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

限制Limitations

使用原生 SQL 查询时需注意以下几个限制:There are a few limitations to be aware of when using raw SQL queries:

  • SQL 查询必须返回实体类型的所有属性的数据。The SQL query must return data for all properties of the entity type.
  • 结果集中的列名必须与属性映射到的列名称匹配。The column names in the result set must match the column names that properties are mapped to. 请注意,此行为不同于 EF6。Note this behavior is different from EF6. EF6 中忽略了原始 SQL 查询的属性/列映射关系,结果集列名必须与属性名相匹配。EF6 ignored property to column mapping for raw SQL queries and result set column names had to match the property names.
  • SQL 查询不能包含关联数据。The SQL query can't contain related data. 但是,在许多情况下你可以在查询后面紧跟着使用 Include 方法以返回关联数据(请参阅包含关联数据)。However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

旧版Previous versions

EF Core 2.2 及更低版本具有两个名为 FromSql 的方法重载,它们的行为方式与较新的 FromSqlRawFromSqlInterpolated 的相同。EF Core version 2.2 and earlier had two overloads of method named FromSql, which behaved in the same way as the newer FromSqlRaw and FromSqlInterpolated. 因此,在意图调用内插字符串方法时很容易意外调用原始字符串方法,反之亦然。It was easy to accidentally call the raw string method when the intent was to call the interpolated string method, and the other way around. 意外调用错误的重载会导致本该参数化的查询没有参数化。Calling wrong overload accidentally could result in queries not being parameterized when they should have been.