SqlSugar ORM 入门篇2 【查询】让我们实现零SQL

2023-01-21 07:23:13

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();//根据ID查询

            var data2 = StudentDb.GetList();//查询所有

            var data3 = StudentDb.GetList(it => it.Id == );  //根据条件查询      

            var p = new PageModel() { PageIndex=,PageSize=};// 分页查询

            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 = "" });//id=1

            conModels.Add(new ConditionalModel() { FieldName = "Student.id", ConditionalType = ConditionalType.Equal, FieldValue = "" });//id=1

            conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Like, FieldValue = "" });// 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[] { , ,  };

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,,));  // id between 1 and 2

动态拼表达式查询

            var exp = Expressionable.Create<Student>()

                          .OrIF( == , it => it.Id == )

                          .And(it => it.Id == )

                          .AndIF( == , it => it.Id == )

                          .Or(it => it.Name == "a1").ToExpression();//拼接表达式

            var data311 = StudentDb.GetList(exp);  // 动态表达式查询

扩展拉姆达方法

例如我们有自定义的SQL函数或者SqlSugar不支持的我们可以自定扩展

具体看这个连接 http://www.codeisbug.com/Doc/8/1162

复杂查询

我们上面看到的简单查询底层都是用复杂查询实现的

var data1 = StudentDb.GetById();

//等同于

var data2 = Db.Queryable<Student>().Single(it => it.Id == );

多表查询

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[].SchoolId , ViewModelStudent3[].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

码农公寓