SqlSugar ORM 入门篇2 【查询】 让我们实现零SQL「建议收藏」
SqlSugar在查询的功能是非常强大的,多表查询、分页查询 、 一对一查询、二级缓存、一对多查、WhenCase等复杂函数、Mapper功能、和拉姆达自定义扩展等,用好了是可以做到真正零SQL的一款ORM。
首先将SqlSugar更新到4.8版本,下面我就来一一讲解每种查询的写法
创建DbContext
public class DbContext
{
public DbContext()
{
Db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = "server=.;uid=sa;pwd=sasa;database=SqlSugar4XTest",
DbType = DbType.SqlServer,
IsAutoCloseConnection = true,//开启自动释放模式和EF原理一样我就不多解释了
//InitKey默认SystemTable
});
}
public SqlSugarClient Db;//用来处理事务多表查询和复杂的操作
public SimpleClient<Student> StudentDb { get { return new SimpleClient<Student>(Db); } }//用来处理Student表的常用操作
public SimpleClient<School> SchoolDb { get { return new SimpleClient<School>(Db); } }//用来处理School表的常用操作
}
单表的简单查询
我们使用的SimpleClient实现了简单的单表查询,如何扩展SimpleClient可以看我的上一篇文章
//调式代码 用来打印SQL Db.Aop.OnLogExecuting = (sql, pars) => { Console.WriteLine(sql + "\r\n" + Db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value))); Console.WriteLine(); }; var data1 = StudentDb.GetById(1);//根据ID查询 var data2 = StudentDb.GetList();//查询所有 var data3 = StudentDb.GetList(it => it.Id == 1); //根据条件查询 var p = new PageModel() { PageIndex=1,PageSize=2};// 分页查询 var data4 = StudentDb.GetPageList(it => it.Name == "xx", p); Console.Write(p.PageCount);//返回总数 // 分页查询加排序 var data5 = StudentDb.GetPageList(it => it.Name == "xx", p,it=>it.Name,OrderByType.Asc); Console.Write(p.PageCount);//返回总数
对于Grid控件来说我一般用这个表单封装好了全部通用
List<IConditionalModel> conModels = new List<IConditionalModel>(); conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" });//id=1 conModels.Add(new ConditionalModel() { FieldName = "Student.id", ConditionalType = ConditionalType.Equal, FieldValue = "1" });//id=1 conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Like, FieldValue = "1" });// id like '%1%' conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.IsNullOrEmpty }); conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.In, FieldValue = "1,2,3" }); conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.NotIn, FieldValue = "1,2,3" }); conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.NoEqual, FieldValue = "1,2,3" }); conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.IsNot, FieldValue = null });// id is not null var data6 = StudentDb.GetPageList(conModels,p,it=>it.Name,OrderByType.Asc); //组装条件当查询条件的 分页查询加排序
简单查询中拉姆达的使用技巧
基本上和EF差不太多
var data3 = StudentDb.GetList(it => it.Name.Contains("a")); // like %a% 模糊查询 var p2 = new int[] { 1, 2, 3 }; var data31 = StudentDb.GetList(it => p2.Contains(it.Id)); // id in (1,2,3)
我们还支持了SqlFunc.xxx一串方法来给我们使用,如下用法
var data311 = StudentDb.GetList(it => SqlFunc.Between(it.Id,1,2)); // id between 1 and 2
动态拼表达式查询
var exp = Expressionable.Create<Student>() .OrIF(1 == 1, it => it.Id == 11) .And(it => it.Id == 1) .AndIF(2 == 2, it => it.Id == 1) .Or(it => it.Name == "a1").ToExpression();//拼接表达式 var data311 = StudentDb.GetList(exp); // 动态表达式查询
扩展拉姆达方法
例如我们有自定义的SQL函数或者SqlSugar不支持的我们可以自定扩展
具体看这个连接 http://www.codeisbug.com/Doc/8/1162
复杂查询
我们上面看到的简单查询底层都是用复杂查询实现的
var data1 = StudentDb.GetById(1); //等同于 var data2 = Db.Queryable<Student>().Single(it => it.Id == 1);
多表查询
var list = Db.Queryable<Student, School>((st, sc) => new object[] { JoinType.Left,st.SchoolId==sc.Id}) .Select((st,sc)=>new{Id=st.Id,Name=st.Name,SchoolName=sc.Name}).ToList();
生成的SQL如下
SELECT [st].[ID] AS [id] , [st].[Name] AS [name] , [sc].[Name] AS [schoolName] FROM [STudent] st Left JOIN School sc ON ( [st].[SchoolId] =[sc].[Id])
多表查询自支持自动填充到ViewModel
var s11 = Db.Queryable<Student, School>((st, sc) => st.SchoolId == sc.Id) .Select<ViewModelStudent3>().ToList();
public class ViewModelStudent3: Student { public string SchoolName { get; set; } }
生成的Sql如下
SELECT sc.[Name] AS [SchoolName],--这一列神奇的自动出现了 sc.[Id] AS [scId], st.[ID] AS [Id], st.[SchoolId] AS [SchoolId], st.[Name] AS [Name], st.[CreateTime] AS [CreateTime] FROM [STudent] st ,[School] sc WHERE ( [st].[SchoolId] = [sc].[Id])
多表分页查询
var list3 = Db.Queryable<Student, School>((st, sc) => new object[] { JoinType.Left,st.SchoolId==sc.Id }).Select<ViewModel>() .ToPageList(pageIndex,pageSize)
子查询
var getAll = Db.Queryable<Student, School>((st, sc) => new object[] { JoinType.Left,st.Id==sc.Id}) .Where(st => st.Id == SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id)) .ToList(); //生成的MYSQL语句,如果是SqlServer就是TOP 1 SELECT `st`.`ID`,`st`.`SchoolId`,`st`.`Name`,`st`.`CreateTime` FROM `STudent` st Left JOIN `School` sc ON ( `st`.`ID` = `sc`.`Id` ) WHERE ( `st`.`ID` =(SELECT `Id` FROM `School` WHERE ( `Id` = `st`.`ID` ) limit 0,1))
一对一的查询
var getAll = Db.Queryable<Student, School>((st, sc) => new object[] { JoinType.Left,st.Id==sc.Id}) .Select(st => new{ name = st.Name, id = SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id) }).ToList();
本文只讲重点,更多多表查询请看 API
http://www.codeisbug.com/Doc/8/1124
Mapper功能
如果说 .Select() 也可以实现一对一的查询或者一些SQL函数但是毕竟是用来生成SQL的所以有很多局限性,Mapper是在查询出结果后进行处理所以任何C#方法都支持
也更强大
var s12 = db.Queryable<Student, School>((st, sc) => st.SchoolId == sc.Id).Select<ViewModelStudent3>() .Mapper(it => { it.Name = Md5(it.Name); //有多少列要处理写多少列,能用Mapper的就少用Select兼容性更好些 }).ToList();
高性能的一对多查询
我们也可以用Mapper来实现一对多,弥补.Select()不足
var s12 = db.Queryable<Student, School>((st, sc) => st.SchoolId == sc.Id).Select<ViewModelStudent3>() .Mapper((it, cache) => { var allSchools = cache.GetListByPrimaryKeys<School>(vmodel => vmodel.SchoolId); //in(ViewModelStudent3[0].SchoolId , ViewModelStudent3[1].SchoolId...) /*one to many*/ it.Schools = allSchools.Where(i => i.Id == it.SchoolId).ToList(); /*C# syntax conversion*/ it.Name = it.Name == null ? "null" : it.Name; }).ToList();
一对多查询的性能可以秒杀其它ORM ,因为生成的SQL只有2条,并且这2条不会多查询一条没用的记录,有幸趣的可以研究一下,其它的都内存处理
多Queryable查询
Union all查询将结果集合并
var getUnionAllList2 = db.UnionAll(db.Queryable<Student>(), db.Queryable<Student>()).ToList();//union all
两个Queryable联表查询(有人说我只支持12表JOIN,那这样就可以支持24张表了)
var q1 = db.Queryable<Student, School>((st,sc)=>new object[] { JoinType.Left,st.SchoolId==sc.Id }).Select((st, sc) => new ViewModelStudent4() { Id=st.Id, Name=st.Name,SchoolName=sc.Name }); var q2 = db.Queryable<School>(); var innerJoinList = db.Queryable(q1, q2, (j1, j2) => j1.Id == j2.Id).Select((j1, j2) => j1).ToList();//inner join var leftJoinList = db.Queryable(q1, q2,JoinType.Left, (j1, j2) => j1.Id == j2.Id).Select((j1, j2) => j1).ToList();/
二级缓存支持
二级缓存功能是对查询出来的数据进行缓存,在缓存不失效的情况下,下次同样的查询操作都会从缓存内读取
使用缓存查询
var list=db.Queryable<Student, School>((s1, s2) => s1.Id == s2.Id).Select(s1 => s1).WithCache().ToList();//可以设置过期时间WithCache(60)
删除缓存
我们需要删除缓存也相当方便,只需要在对该表操作的时候加 RemoveDataCache 就能把查询中引用该表的缓存全部清除
db.Deleteable<Student>().Where(it => it.Id == 1).RemoveDataCache().ExecuteCommand();
//Updateable和Insertable一样用法
自动删除缓存
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = Config.ConnectionString, MoreSettings =new ConnMoreSettings(){ IsAutoRemoveDataCache=true }
创建db对象
我们需要创建一个MyCache类,你可以用我写好的也可以用你自已写的实现缓存
ICacheService myCache = new RedisCache("10.1.249.196");//ICacheService SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = Config.ConnectionString, DbType = DbType.SqlServer, IsAutoCloseConnection = true, ConfigureExternalServices = new ConfigureExternalServices() { DataInfoCacheService = new RedisCache() //RedisCache是继承ICacheService自已实现的一个类 } });
我写好的Cache类可以作为参考
Redis:
https://github.com/sunkaixuan/SqlSugar/blob/dev/Src/Asp.Net/SqlSugar.Extensions.DataCache/RedisCache.cs
.Net自带Cache:
https://github.com/sunkaixuan/SqlSugar/blob/dev/Src/Asp.Net/SqlSugar.Extensions.DataCache/HttpRuntimeCache.cs
永久开源,源码下:
https://github.com/sunkaixuan/SqlSugar
SqlSugar一直在默默进步,得到了一些大型企业的认可,但这只是开始,希望你喜欢。
上一篇
https://www.cnblogs.com/sunkaixuan/p/8454844.html
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/168072.html原文链接:https://javaforall.cn
相关文章
- SQL Server 获取服务器时间的sql语句
- PL/SQL Developer实现双击table表名查询详解数据库
- 一键实现:SQL数据转换到Oracle的神器(sql转oracle工具)
- MySQL监控:追踪SQL执行性能(mysql监控sql)
- 使用 Oracle SQL 实现多表拼接(oracle拼接sql)
- MySQL学习笔记:创建表的SQL语句(mysql创建表的sql)
- Oracle事务SQL:处理复杂数据问题(oracle事务sql)
- 数据加载SQL Server数据:轻松实现智能化管理(加载sqlserver)
- SQL Server实现增量查询的技巧(sqlserver查增量)
- 接Sql Server 左联接技术:实现有效的数据查询(sqlserver 左联)
- SQL Server工具书:让你学会实现数据库功能(sqlserver工具书)
- SQL Server实现取整的新姿势(sqlserver 取整)
- SQL Server中的分页技术:实现高效的分页效果(sqlserver分页类)
- 如何使用 MySQL 还原 SQL 数据库备份文件?(mysql还原sql)
- MSSQL如何通过美化格式简化SQL查询(mssql美化格式sql)
- 将SQL查询结果存入Redis缓存(sql结果存入redis)
- Oracle中SQl实现相减运算(oracle中sql相减)
- 化分析利用Oracle SQL进行量化分析(oracle sql?量)
- Oracle SQL实现数据集求差(oracle sql求差)