Skip to content

Latest commit

 

History

History
101 lines (70 loc) · 3.98 KB

File metadata and controls

101 lines (70 loc) · 3.98 KB

原生SQL查询

Entity Framework Core 允许你在使用关系数据库时下降为原生SQL查询。当你想要执行一些 LINQ 无法表达的查询,或者使用 LINQ 会导致发送到数据库的 SQL 效率低下时,这会很有用。

提示

你可以在 GitHub 上查阅当前文章涉及的代码样例

局限性

使用原生 SQL 查询有一些限制。

  • SQL 查询只能用于返回模型中的实体类型实例。在我们的待办事项列表中有一个 允许从 SQL 查询返回特定类型实例 的优化工作。
  • SQL 查询必须为实体类型的所有属性返回数据。
  • 结果集中的列名必须与每个属性所映射到的列名相匹配。注意,这与 EF6 不同。在 EF6 中使用原生 SQL 查询时,属性/列映射会被忽略,结果集的列名必须与每个属性的名称匹配。
  • SQL 查询无法包含关联数据。然而,大部分情况下你可以在查询的顶端组合使用 Include 操作来返回关联数据(详见 包含关联数据)。

原生 SQL 基础查询

可以使用 FromSql 扩展方法来开始基于原生 SQL 查询的 LINQ 查询。

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

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

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

传递参数

与任何接受 SQL 的 API 一样,参数化任何用户输入以防止 SQL 注入攻击是很重要的。你可以在 SQL 查询字符串中包含参数占位符,然后将参数值作为附加参数来提供。你提供的任何参数都会被自动转换为 DbParameter

以下样例传递了一个参数给存储过程。这看上去与 String.Format 语法相似。提供的值会被包装为参数,而所生成的参数名称会被插到 {0} 占位符指定的位置上。

var user = "johndoe";

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

以下查询与上面的查询是等效的,只是这里使用了 EF Core 2.0 或以上版本所支持的字符串插值语法。

var user = "johndoe";

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

还可以构造 DbParameter 并将其作为参数值来提供。这允许你在 SQL 查询字符串中使用命名参数。

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

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

与 LINQ 组合

如果你的 SQL 查询允许在数据库中被组合,那么你也就可以在初始原生 SQL 查询的顶端使用 LINQ 运算进行组合。可被组合的 SQL 查询都是以 SELECT 关键字开头的。

以下样例使用了一个原生 SQL 查询来从表值函数(Table-Valued Function,TVF)查询数据,然后使用 LINQ 来在其上组合执行筛选和排序。

var searchTerm = ".NET";

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

包含关联数据

与 LINQ 操作组合可用于在查询中包含关联数据。

var searchTerm = ".NET";

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

警告

在原生 SQL 查询中务必要使用参数化: 接受 SQL 字符串的 API(比如 FromSqlExecuteSqlCommand)能够轻松地将值作为参数来传递。除了验证用户输入以外,务必要对原生 SQL 查询/命令中用到的值进行参数化。如果你正在使用字符串串联来动态构建查询字符串的任何片段,那么你就要负责验证任何输入以防止 SQL 注入攻击。