02020504 EF Core高级04-EF Core查询原生SQL语句、好用的IQueryable、EF Core中的ADO.NET示例、Dapper示例
02020503 EF Core高级04-EF Core查询原生SQL语句、好用的IQueryable、EF Core中的ADO.NET示例、Dapper示例
1. EF Core方法执行非查询原生SQL语句（视频3-30）
1.1 为何要写原生的SQL语句
1、尽管EF Core已经非常强大，但是仍然存在着无法被写成标准EF Core调用方法的SQL语句，少数情况下仍然需要写原生SQL。
2、可能无法跨数据库。
3、三种情况：非查询语句、实体查询、任意SQL查询。
1.2 非查询语句
- 在02020409章5.2节基础上继续
使用dbCtx.Database. ExecuteSqlInterpolated () dbCtx.Database. ExecuteSqlInterpolatedAsync()方法来执行原生的非查询SQL语句。
ctx.Database.ExecuteSqlInterpolatedAsync(@$"insert into T_Books(Title,PubTime,Price,AuthorName)
select Title, PubTime, Price,{aName} from T_Books
where Price > {price}");
—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—
// 数据库中信息
Id Title Message Price
1 杨中科入选中科院 大新闻 30
2 微软发布.NET 10.0 中新闻 10
3 微软发射微星 小新闻 20
4 中国发射小行星探测器 劲爆新闻 0
—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—
// 原生SQL语句示例
using Microsoft.EntityFrameworkCore;
using System.Threading.Tasks;
namespace OneToMany
{
class Program
{
static async Task Main(string[] args)
{
int age = 18;
string name = "YZK";
using (MyDbContext ctx = new MyDbContext())
{
// C#中多行内插值用@$完成字符串拼接。
await ctx.Database.ExecuteSqlInterpolatedAsync(@$"
insert into T_Articles(Title,Message,Price)
select Title, {name}, Price
from T_Articles
where Price >= {age}");
}
}
}
}
控制台输出：
数据库更新成功！
// 数据库中信息
Id Title Message Price
1 杨中科入选中科院 大新闻 30
2 微软发布.NET 10.0 中新闻 10
3 微软发射微星 小新闻 20
4 中国发射小行星探测器 劲爆新闻 0
547 杨中科入选中科院 YZK 30 //大于18的插入
548 微软发射微星 YZK 20 // 大于18的插入
说明：这里是执行SQL的原生插入，因此不需要用SaveChanges方法。
1.3 SQL注入漏洞
1、字符串内插的方式会不会有SQL注入攻击漏洞吗？查看一下执行的SQL语句吧。
2、字符串内插如果赋值给string变量，就是字符串拼接；字符串内插如果赋值给FormattableString变量，编译器就会构造FormattableString 对象。打印FormattableString的成员试试看。
3、ExecuteSqlInterpolatedAsync()的参数是FormattableString类型。因此ExecuteSqlInterpolatedAsync会进行参数化SQL的处理。
—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—
using Microsoft.EntityFrameworkCore;
using System;
using System.Threading.Tasks;
namespace OneToMany
{
class Program
{
static async Task Main(string[] args)
{
int age = 18;
string name = "; delete from T_Articles"; // SQL注入删库
string s = @$"
insert into T_Articles(Title,Message,Price)
select Title, {name}, Price
from T_Articles
where Price >= {age}"; // @1
Console.WriteLine(s);
using (MyDbContext ctx = new MyDbContext())
{
// 多行内插值用@
await ctx.Database.ExecuteSqlInterpolatedAsync(@$"
insert into T_Articles(Title,Message,Price)
select Title, {name}, Price
from T_Articles
where Price >= {age}"); // @2
}
Console.WriteLine("数据库更新成功！");
}
}
}
控制台输出：
insert into T_Articles(Title,Message,Price)
select Title, ; delete from T_Articles, Price
from T_Articles
where Price >= 18
********************
数据库更新成功！
// 查看SQL语句
insert into T_Articles(Title,Message,Price)
select Title, @p0, Price
from T_Articles
where Price >= @p1
说明：
1. 在@1处，字符串拼接的语句和在@2处数据库中生成的SQL语句不同。
2. 在@1处是字符串拼接，在@2处的SQL是查询参数@p0，@p1
—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—
// 将string类型改为FormattableString查看输出结果
using Microsoft.EntityFrameworkCore;
using System;
using System.Threading.Tasks;
namespace OneToMany
{
class Program
{
static async Task Main(string[] args)
{
int age = 18;
string name = "; delete from T_Articles"; // SQL注入删库
FormattableString s = @$"
insert into T_Articles(Title,Message,Price)
select Title, {name}, Price
from T_Articles
where Price >= {age}"; // @1
Console.WriteLine("Format：" + s.Format);
Console.WriteLine("参数：" + string.Join("，", s.GetArguments()));
return; // 下面不再执行
using (MyDbContext ctx = new MyDbContext())
{
// 多行内插值用@
await ctx.Database.ExecuteSqlInterpolatedAsync(@$"
insert into T_Articles(Title,Message,Price)
select Title, {name}, Price
from T_Articles
where Price >= {age}"); // @2
}
Console.WriteLine("数据库更新成功！");
}
}
}
控制台输出：
Format：
insert into T_Articles(Title,Message,Price)
select Title, {0}, Price
from T_Articles
where Price >= {1}
参数：; delete from T_Articles，18
—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—
总结：使用ExecuteSqlInterpolatedAsync这种内插值的方法，不会引起SQL注入攻击。
1.4 ExecuteSqlRaw方法
除了ExecuteSqlInterpolated ()、ExecuteSqlInterpolatedAsync() ，还有ExecuteSqlRaw()、ExecuteSqlRawAsync() 也可以执行原生SQL语句，但需要开发人员自己处理查询参数等了，因此不推荐使用。
2. 实体相关SQL（视频3-31）
如果要执行的原生SQL是一个查询语句，并且查询的结果也能对应一个实体，就可以调用对应实体的DbSet的FromSqlInterpolated()方法来执行一个查询SQL语句，同样使用字符串内插来传递参数。
IQueryable<Book> books = ctx.Books.FromSqlInterpolated(@$"select * from T_Books
where DatePart(year,PubTime)>{year}
order by newid()");
—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—
using Microsoft.EntityFrameworkCore;
using System;
using System.Threading.Tasks;
namespace OneToMany
{
class Program
{
static async Task Main(string[] args)
{
using (MyDbContext ctx = new MyDbContext())
{
string titlePatten = "%中%";
// 多行内插值用@
var queryable = ctx.Articles.FromSqlInterpolated($@"
select * from T_Articles
where Title like {titlePatten}
order by newid()
");
foreach (var item in queryable)
{
Console.WriteLine(item.Id + "，" + item.Title);
}
}
Console.WriteLine();
}
}
}
控制台输出：
4，中国发射小行星探测器
1，杨中科入选中科院
// SQL语句
select * from T_Articles
where Title like @p0
order by newid()
3. 好用的IQueryable
3.1 原生SQL与EF Core协作
- 在本课1.2中继续
1、FromSqlInterpolated()方法的返回值是IQueryable类型的，因此我们可以在实际执行IQueryable之前，对IQueryable进行进一步的处理。
IQueryable<Book> books = ctx.Books.FromSqlInterpolated(@$"select * from T_Books where DatePart(year,PubTime)>{year}");
foreach(Book b in books.Skip(3).Take(6))
2、把只能用原生SQL语句写的逻辑用FromSqlInterpolated()去执行，然后把分页、分组、二次过滤、排序、Include等其他逻辑尽可能仍然使用EF Core的标准操作去实现。
—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using System.Threading.Tasks;
namespace OneToMany
{
class Program
{
static async Task Main(string[] args)
{
using (MyDbContext ctx = new MyDbContext())
{
string titlePatten = "%中%";
// 多行内插值用@
var queryable = ctx.Articles.FromSqlInterpolated($@"
select * from T_Articles
where Title like {titlePatten}
order by newid()
");
foreach (var item in queryable.Skip(1).Take(2))
{
Console.WriteLine(item.Id + "，" + item.Title);
}
}
Console.WriteLine();
}
}
}
抛出异常：Microsoft.Data.SqlClient.SqlException:“除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
“OFFSET”附近有语法错误。
在 FETCH 语句中选项 NEXT 的用法无效。”
// 查看SQL
SELECT [a].[Id], [a].[Message], [a].[Price], [a].[Title]
FROM (
select * from T_Articles
where Title like @p0
order by newid()
) AS [a]
ORDER BY (SELECT 1)
OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
说明：
1. 因为SQL Server的限制，在子查询里面不能用Order By.
2. 这里原生的select * from T_Articles where Title like @p0 order by newid()成了子查询。外层用分页查询。
—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using System.Threading.Tasks;
namespace OneToMany
{
class Program
{
static async Task Main(string[] args)
{
using (MyDbContext ctx = new MyDbContext())
{
string titlePatten = "%中%";
// 多行内插值用@
var queryable = ctx.Articles.FromSqlInterpolated($@"
select * from T_Articles
where Title like {titlePatten}
"); // 去掉order by newid()
foreach (var item in queryable.Skip(1).Take(2))
{
Console.WriteLine(item.Id + "，" + item.Title);
}
}
Console.WriteLine();
}
}
}
控制台输出：
4，中国发射小行星探测器
547，杨中科入选中科院
// 查看SQL
SELECT [a].[Id], [a].[Message], [a].[Price], [a].[Title]
FROM (
select * from T_Articles
where Title like @p0
) AS [a]
ORDER BY (SELECT 1)
—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using System.Threading.Tasks;
namespace OneToMany
{
class Program
{
static async Task Main(string[] args)
{
using (MyDbContext ctx = new MyDbContext())
{
string titlePatten = "%中%";
// 多行内插值用@
var queryable = ctx.Articles.FromSqlInterpolated($@"
select * from T_Articles
where Title like {titlePatten}
"); // 去掉order by newid()
foreach (var item in queryable.OrderBy(a => Guid.NewGuid()).Skip(1).Take(2)) // 效果如随机排序
{
Console.WriteLine(item.Id + "，" + item.Title);
}
}
Console.WriteLine();
}
}
}
控制台输出：
549，杨中科入选中科院
547，杨中科入选中科院
3.2 局限性
- SQL 查询必须返回实体类型对应数据库表的所有列；
- 结果集中的列名必须与属性映射到的列名称匹配。
- 只能单表查询，不能使用Join语句进行关联查询。但是可以在查询后面使用Include()来进行关联数据的获取。
4. EF Core执行任意原生SQL查询语句（视频3-32）
4.1 什么时候要用ADO.NET
1、FromSqlInterpolated()只能单表查询，但是在实现报表查询等的时候，SQL语句通常是非常复杂的，不仅要多表Join，而且返回的查询结果一般也都不会和一个实体类完整对应。因此需要一种执行任意SQL查询语句的机制。
2、EF Core中允许把视图或存储过程映射为实体，因此可以把复杂的查询语句写成视图或存储过程，然后再声明对应的实体类，并且在DbContext中配置对应的DbSet。
3、不推荐写存储过程；项目复杂查询很多，导致：视图太多；非实体的DbSet；DbSet膨胀。
4.2 可以在EF Core中声明实体类
// SQL语句
select Author, Count(*) ACount from T_Books
group by Author
—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—
// 声明映射的实体类
class BookAuthor
{
public string Author {get; set;}
public int ACount {get; set;}
}
说明：声明映射的实体类，然后用上面的方法来处理也是可以做到的。但是不推荐这种用法，不要这么用，项目开发会越来越坑。
4.3 执行任意SQL
- dbCxt.Database.GetDbConnection()获得ADO.NET Core的数据库连接对象。这里不讲解ADO.NET基础知识。
DbConnection conn = ctx.Database.GetDbConnection(); // ADO.NET连接对象
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"xxx";
var p1 = cmd.CreateParameter();
p1.ParameterName = "@year";
p1.Value = year;
cmd.Parameters.Add(p1);
using (var reader = cmd.ExecuteReader())
}
推荐用Dapper等框架执行原生复杂查询SQL。
4.4 执行任意SQL示例
- 在本课1.2中继续
using Microsoft.EntityFrameworkCore;
using System;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
namespace OneToMany
{
class Program
{
static async Task Main(string[] args)
{
using (MyDbContext ctx = new MyDbContext())
{
DbConnection conn = ctx.Database.GetDbConnection(); // @1 拿到Context对应的底层的Connection对象。
if(conn.State != System.Data.ConnectionState.Open) // @2
{
await conn.OpenAsync();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select Price, Count(*) from T_Articles group by Price";
using(var reader = await cmd.ExecuteReaderAsync())
{
while(await reader.ReadAsync())
{
double price = reader.GetInt32(0);
int count = reader.GetInt32(1);
Console.WriteLine($"{price} : {count}");
}
}
}
}
Console.WriteLine();
}
}
}
控制台输出：
0 : 1
10 : 1
20 : 16
30 : 16
说明：
1. 在@1处的conn对象不需要手动释放，因为这是EF Core中底层的ADO.NET连接对象，只要EF Core中的MyDbContext对象ctx释放，那么conn对象会自动释放。
2. 在@2处的写法，直接用的是conn对象，此时跳过EF Core，直接用的是SQL语句。因此EF Core日志系统不会自动生成SQL语句。
3. 上例演示的是用原生的ADO.NET来操作数据库的示例，此时想干啥就干啥。关于纯ADO.NET，课外自己查询相关数据。
4.5 使用Dapper示例
- 在本课1.2中继续
// step1 → 安装Dapper
PM> install-package dapper -version 2.0.90
—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—
// step2 → 声明实体类
namespace OneToMany
{
class GroupArticleByPrice
{
public int Price { get; set; }
public int PCount { get; set; }
}
}
说明：
1. 这个类不是实体类，也不会放到DbContext里面，因此不会有DbSet膨胀。
2. 这个类可以理解为DTO，或者视图。（这个表述可能不准确，理解即可）
—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—
// step3 → 使用Dapper
using Dapper;
using Microsoft.EntityFrameworkCore;
using System;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
namespace OneToMany
{
class Program
{
static async Task Main(string[] args)
{
using (MyDbContext ctx = new MyDbContext())
{
var items = ctx.Database.GetDbConnection().Query<GroupArticleByPrice>(
"select Price, Count(*) PCount from T_Articles group by Price" // PCount与别名对应
);
foreach (var item in items)
{
Console.WriteLine(item.Price + " ：" + item.PCount);
}
}
Console.WriteLine();
}
}
}
控制台输出：
0 ：1
10 ：1
20 ：16
30 ：16
说明：
1. 此处与4.4中执行结果一致。Dapper做的工作就是将4.4中一大堆东西，直接映射到IEnumerable中来。
2. Dapper就是将ADO.NET进行了简单的封装，不需要自己建立conn或者cmd对象。
—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—·—
总结：
1. 一个项目中可能Dapper和EF Core同时存在，各取所需，它们两者并不是对立关系，用好了是相互协作的关系。
2. 大部分情况下EF Core足够使用，这样简单方便。如果存在复杂的查询，可以考虑Dapper或者其它的技术栈。
4.6 总结
一般Linq操作就够了，尽量不用写原生SQL；
1、非查询SQL用ExecuteSqlInterpolated () ；
2、针对实体的SQL查询用FromSqlInterpolated()。
3、复杂SQL查询用ADO.NET的方式或者Dapper等。
结尾
书籍：ASP.NET Core技术内幕与项目实战
视频：https://www.bilibili.com/video/BV1pK41137He
著：杨中科
ISBN：978-7-115-58657-5
版次：第1版
发行：人民邮电出版社
※敬请购买正版书籍，侵删请联系85863947@qq.com※
※本文章为看书或查阅资料而总结的笔记，仅供参考，如有错误请留言指正，谢谢！※
浙公网安备 33010602011771号