SqlSugar ORM 入門篇2 【查詢】 讓我們實現零SQL

孫凱旋發表於2018-10-14
SqlSugarORM

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

相關文章