原生 SQL 查询

通过 Entity Framework Core 可以在使用关系数据库时下降到原始 SQL 查询。 所需查询不能使用 LINQ 来表示时,可以使用原始 SQL 查询。 如果使用 LINQ 查询导致 SQL 查询效率低下,也可以使用原始 SQL 查询。 原始 SQL 查询可返回一般实体类型或者模型中的无键实体类型

提示

可在 GitHub 上查看此文章的示例

基本原生 SQL 查询

可使用 FromSqlRaw 扩展方法基于原始 SQL 查询开始 LINQ 查询。 FromSqlRaw 只能在直接位于 DbSet<> 上的查询根上使用。

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

原生 SQL 查询可用于执行存储过程。

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

快速参考

警告

始终对原始 SQL 查询使用参数化

向原始 SQL 查询引入任何用户提供的值时,必须注意防范 SQL 注入攻击。 除了验证确保此类值不包含无效字符,请始终使用会将值与 SQL 文本分开发送的参数化处理。

具体而言,如果连接和内插的字符串 ($"") 带有用户提供的未经验证的值,则切勿将其传递到 FromSqlRawExecuteSqlRaw。 通过 FromSqlInterpolatedExecuteSqlInterpolated 方法,可采用一种能抵御 SQL 注入攻击的方式使用字符串内插语法。

下面的示例通过在 SQL 查询字符串中包含形参占位符并提供额外的实参,将单个形参传递到存储过程。 虽然此语法可能看上去像 String.Format 语法,但提供的值包装在 DbParameter 中,且生成的参数名称插入到指定 {0} 占位符的位置。

var user = "johndoe";

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

虽然 FromSqlInterpolated 类似于 FromSqlRaw,但你可以借助它使用字符串内插语法。 与 FromSqlRaw 一样,FromSqlInterpolated 只能在查询根上使用。 与上述示例一样,该值会转换为 DbParameter,且不易受到 SQL 注入攻击。

var user = "johndoe";

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

还可以构造 DbParameter 并将其作为参数值提供。 由于使用了常规 SQL 参数占位符而不是字符串占位符,因此可安全地使用 FromSqlRaw

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

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

借助 FromSqlRaw,可以在 SQL 查询字符串中使用已命名的参数,这在存储的流程具有可选参数时非常有用:

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

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

注意

参数排序 Entity Framework Core 根据 数组的顺序传递参数。 传递多个 SqlParameter 时,SQL 字符串中的顺序必须与存储过程定义中参数的顺序相匹配。 如果不匹配,在执行此过程时,就可能会导致类型转换异常和/或异常行为。

使用 LINQ 编写

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

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSqlInterpolated($"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
    .FromSqlInterpolated($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

使用 LINQ 进行组合要求原始 SQL 查询是可组合的,因为 EF Core 会将提供的 SQL 视为子查询。 以 SELECT 关键字开始的 SQL 查询一般是可组合的。 此外,传递的 SQL 不应包含子查询上无效的任何字符或选项,如:

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

SQL Server 不允许对存储过程调用进行组合,因此任何尝试向此类调用应用其他查询运算符的操作都将导致无效的 SQL。 请在 FromSqlRawFromSqlInterpolated 方法之后立即使用 AsEnumerableAsAsyncEnumerable 方法,确保 EF Core 不会尝试对存储过程进行组合。

更改跟踪

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

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

var searchTerm = "Lorem ipsum";

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

限制

使用原生 SQL 查询时需注意以下几个限制:

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