第二十五节：再探IQueryable和IEnumerable、EFCore调用SQL语句、增删改翻译后的SQL剖析(是否批处理)
一. 再探IQueryable 和 IEnumerable
1. 二者生成SQL的区别
普通集合的版本(IEnumerable)是在内存中过滤（客户端评估），而IQueryable版本则是把查询操作翻译成SQL语句,在DB中操作。
代码分享：
{
using var db = new EFCore6xDBContext();
//在DB中操作
IQueryable<UserInfo> userInfo = db.UserInfo.Where(u => u.userName.Contains("ypf"));
foreach (var item in userInfo)
{
Console.WriteLine($"{item.userName},{item.userGender},{item.addTime}");
}
//在内存中操作
IEnumerable<UserInfo> userInfo2 = db.UserInfo.Where(u => u.userName.Contains("ypf"));
foreach (var item2 in userInfo2)
{
Console.WriteLine($"{item2.userName},{item2.userGender},{item2.addTime}");
}
}2. IQueryable的延迟执行
(1). IQueryable只是代表一个“可以放到数据库服务器去执行的查询”，它没有立即执行，只是“可以被执行”而已。
(2). 对于IQueryable接口调用非终结方法的时候不会执行查询，而调用终结方法的时候则会立即执行查询。
(3). 终结方法：遍历、ToArray()、ToList()、Min()、Max()、Count()等；
非终结方法：GroupBy()、OrderBy()、Include()、Skip()、Take()等。
(4). 简单判断：一个方法的返回值类型如果是IQueryable类型，那么这个方法一般就是非终结方法，否则就是终结方法。
(5). 作用：用于拼接动态查询
代码分享：
{
using var db = new EFCore6xDBContext();
//在DB中操作
IQueryable<UserInfo> u1 = db.UserInfo.Where(u => u.userName.Contains("ypf"));
IQueryable<UserInfo> u2 = u1.Where(u => u.addTime < DateTime.Now);
//最终执行sql语句
var data = u2.ToList();
}3. 分页
这里使用LongCount，输出总页数和总条数
代码分享：
{
static void PrintMsg(int pageIndex, int pageSize)
{
using var db = new EFCore6xDBContext();
var data = db.UserInfo.Skip((pageIndex - 1) * pageSize).Take(pageSize);
foreach (var item in data)
{
Console.WriteLine(item.userName, item.userPwd);
}
long count = data.LongCount();
long pageCount = (long)Math.Ceiling(count * 1.0 / pageSize);
Console.WriteLine($"总条数:{count},总页数{pageCount}");
}
//调用
PrintMsg(1, 2);
}4. 使用场景
(1).DataReader 和 DataTable
DataReader：分批从数据库服务器读取数据。内存占用小、 DB连接占用时间长；
DataTable：把所有数据都一次性从数据库服务器都加载到客户端内存中。内存占用大，节省DB连接。
(2).IQueryable的使用方式
IQueryable内部就是在调用DataReader。
优点：节省客户端内存。缺点：如果处理的慢，会长时间占用连接
(3). 如何一次性加载数据到内存?
一次性加载数据到内存：用IQueryable的ToArray()、ToArrayAsync()、ToList()、ToListAsync()等方法。
(4). 什么情况下需要一次性加载到内存中?
场景1：遍历IQueryable并且进行数据处理的过程很耗时。
场景2：如果方法需要返回查询结果，并且在方法里销毁DbContext的话，是不能返回IQueryable的。必须一次性加载返回。
场景3：多个IQueryable的遍历嵌套。很多数据库的ADO.NET Core Provider是不支持多个DataReader同时执行的。把连接字符串中的MultipleActiveResultSets=true删掉，其他数据库不支持这个。
二. EFCore调用SQL语句
1. 非查询类语句(增删改)
(1).用法：推荐使用ExecuteSqlInterpolated()方法来执行原生的非查询SQL语句
(2).剖析: 使用内插法传入参数，ExecuteSqlInterpolated()方法会自动进行参数化处理，防止SQL注入，详见截图
(3).补充：除此之外，还有ExecuteSqlRaw()方法可以使用，但是需要手动SqlParameter 处理（详见 https://www.cnblogs.com/yaopengfei/p/11459170.html）
代码分享：
{
using var db = new EFCore6xDBContext();
int count = db.Database.ExecuteSqlInterpolated(@$"insert into UserInfo (id,userName,userPwd,userGender,userAge,addTime,delflag)
values({Guid.NewGuid().ToString("N")},'ypf','12345','男',20,{DateTime.Now},0)");
FormattableString sql1 = @$"insert into UserInfo (id,userName,userPwd,userGender,userAge,addTime,delflag)
values({Guid.NewGuid().ToString("N")},'ypf','12345','男',20,{DateTime.Now},0)";
int count2 = db.Database.ExecuteSqlInterpolated(sql1);
Console.WriteLine($"新增成功,{count}");
}SQL截图：
2. 实体查询
(1).用法：推荐使用FromSqlInterpolated()方法来执行一个查询SQL语句，同样使用字符串内插来传递参数防止SQL注入
(2).补充： 除此之外，还可以使用FromSqlRaw，但需要手动SqlParameter 处理 （详见 https://www.cnblogs.com/yaopengfei/p/11459170.html）
代码分享
{
using var db = new EFCore6xDBContext();
var data = db.Set<UserInfo>().FromSqlInterpolated(@$"select * from UserInfo where addTime < {DateTime.Now}").ToList();
Console.WriteLine($"查询成功,{data}");
}SQL截图
3. 任意查询
使用EFCore可以声明个实体来接收，建议用ADO.Net 或 Dapper
三. 增删改查翻译后的SQL剖析(是否批处理)
1. 新增
结论：使用Add或AddRange方法,当数量<=3,生成的多条insert，当数量>3, 则合并生成1条insert，详见截图
代码分享：
{
using var db = new EFCore6xDBContext();
for (int i = 1; i <= 12; i++)
{
UserInfo user = new UserInfo()
{
id = Guid.NewGuid().ToString("N"),
userName = "ypf1_" + i,
userPwd = "123456",
userAge = 19,
userGender = "男",
addTime = DateTime.Now,
delflag = 0
};
await db.Set<UserInfo>().AddAsync(user);
}
await db.SaveChangesAsync();
Console.WriteLine("新增成功");
//using var db = new EFCore6xDBContext();
//List<UserInfo> userList = new List<UserInfo>();
//for (int i = 1; i <= 4; i++)
//{
// UserInfo user = new UserInfo()
// {
// id = Guid.NewGuid().ToString("N"),
// userName = "ypf1_" + i,
// userPwd = "123456",
// userAge = 19,
// userGender = "男",
// addTime = DateTime.Now,
// delflag = 0
// };
// userList.Add(user);
//}
//await db.Set<UserInfo>().AddRangeAsync(userList);
//await db.SaveChangesAsync();
//Console.WriteLine("新增成功");
}SQL截图-小于3
SQL截图-大于3 
2. 修改
在EFCore中必须先查询出来，然后执行修改操作，并不能直接 update xxxx where xxx
结论：当数量<=3,生成的多条update，当数量>3, 则合并生成1次请求了,但仍然是多个update，详见截图
代码分享：
{
using var db = new EFCore6xDBContext();
var userList = db.Set<UserInfo>().Take(4).ToList();
foreach (var item in userList)
{
item.addTime = DateTime.Now;
item.delflag++;
}
await db.SaveChangesAsync();
Console.WriteLine("修改成功");
}SQL截图-小于3
SQL截图-大于3
3. 删除
在EFCore中必须先查询出来，然后执行修改操作，并不能直接 delete xxxx where xxx
结论：当数量<=3,生成的多条delete，当数量>3, 则合并生成1次请求了,但仍然是多个delete，详见截图
代码分享：
{
using var db = new EFCore6xDBContext();
var userList = db.Set<UserInfo>().Take(4).ToList();
foreach (var item in userList)
{
db.Remove(item);
}
await db.SaveChangesAsync();
Console.WriteLine("删除成功");
}SQL截图-小于3
SQL截图-大于3 
4. 如何修改批处理条数呢？
默认的批处理条数非常大,但我们可以自己修改批处理条数,比如：下图将批处理条数设置为10
optionsBuilder.UseSqlServer("Server=localhost;Database=EFCore6xDB;User ID=sa;Password=123456;", b => b.MaxBatchSize(10));
经测试：
(1). <=3条的时候,任何是生成多条insert语句
(2). >3条 且 小于等于 10条, 则生成1条insert语句
(3). 比如12条吧,则生成两条insert语句，1个插入10个数据，1个插入2个数据，详见截图
SQL截图
!
- 作 者 : Yaopengfei(姚鹏飞)
- 博客地址 : http://www.cnblogs.com/yaopengfei/
- 声 明1 : 如有错误，欢迎讨论，请勿谩骂^_^。
- 声 明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址，否则保留追究法律责任的权利。
