EF Core原生 SQL 查询

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第24天，点击查看活动详情

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

基本 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 文本分开发送的参数化处理。
具体而言，如果连接和内插的字符串 ($"") 带有用户提供的未经验证的值，则切勿将其传递到 FromSqlRaw 或 ExecuteSqlRaw。 通过 FromSqlInterpolated 和 ExecuteSqlInterpolated 方法，可采用一种能抵御 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 注入攻击。在 3.0 之前，FromSqlRaw 和 FromSqlInterpolated 是名为 FromSql 的两个重载。
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();
使用 LINQ 编写
可使用 LINQ 运算符在初始的原始 SQL 查询基础上进行组合。 EF Core 将其视为子查询，并在数据库中对其进行组合。 下面使用原始 SQL 查询，查询从表值函数 (TVF) 中进行选择。 然后使用 LINQ 进行筛选和排序对其进行组合。
var searchTerm = ".NET";
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 = ".NET";
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 0 或 TOP 100 PERCENT 配合使用的 ORDER BY 子句
SQL Server 不允许对存储过程调用进行组合，因此任何尝试向此类调用应用其他查询运算符的操作都将导致无效的 SQL。 请在 FromSqlRaw 或 FromSqlInterpolated 方法之后立即使用 AsEnumerable 或 AsAsyncEnumerable 方法，确保 EF Core 不会尝试对存储过程进行组合。
使用 FromSqlRaw 或 FromSqlInterpolated 方法的查询遵循与 EF Core 中所有其他 LINQ 查询完全相同的更改跟踪规则。 例如，如果该查询投影实体类型，默认情况下会跟踪结果。
下面的示例使用原始 SQL 查询，该查询从表值函数 (TVF) 中进行选择，然后禁用通过对 AsNoTracking 的调用来更改跟踪：
var searchTerm = ".NET";
var blogs = context.Blogs
    .FromSqlInterpolated($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToList();
使用原生 SQL 查询时需注意几个限制：
SQL 查询必须返回实体类型的所有属性的数据。
结果集中的列名必须与属性映射到的列名匹配。 此行为不同于 EF6。 EF6 中忽略了原始 SQL 查询的属性/列映射关系，结果集列名必须与属性名相匹配。
SQL 查询不能包含关联数据。 但是，在许多情况下你可以在查询后面紧跟着使用 Include 方法以返回关联数据