SqlSugar ORM 入門 ：查詢 讓我們實現零SQL

來自：孫凱旋

連結：http://www.cnblogs.com/sunkaixuan/p/9787566.html

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 StudentDb { get { return new SimpleClient(Db); } }//用來處理Student表的常用操作

        public SimpleClient SchoolDb { get { return new SimpleClient(Db); } }//用來處理School表的常用操作

}

單表的簡單查詢

我們使用的SimpleClient實現了簡單的單表查詢，如何擴充套件SimpleClient可以看我的上一篇文章

//調式程式碼 用來列印SQL 

Db.Aop.OnLogExecuting = (sql, pars) =>

{

    Console.WriteLine(sql + ”
” + 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 conModels = new List();

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()

.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().Single(it => it.Id == 1);

多表查詢

var list = Db.Queryable((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((st, sc) => st.SchoolId == sc.Id)

                        .Select().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((st, sc) => new object[] {

              JoinType.Left,st.SchoolId==sc.Id

            }).Select()

            .ToPageList(pageIndex,pageSize)

子查詢

var getAll = Db.Queryable((st, sc) => new object[] {

JoinType.Left,st.Id==sc.Id})

.Where(st => st.Id == SqlFunc.Subqueryable().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((st, sc) => new object[] {

JoinType.Left,st.Id==sc.Id})

.Select(st =>

       new{

              name = st.Name,

              id = SqlFunc.Subqueryable().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((st, sc) => st.SchoolId == sc.Id).Select()

.Mapper(it =>

{

    it.Name = Md5(it.Name);

    //有多少列要處理寫多少列，能用Mapper的就少用Select相容性更好些

}).ToList();

高效能的一對多查詢

我們也可以用Mapper來實現一對多，彌補.Select()不足

var s12 = db.Queryable((st, sc) => st.SchoolId == sc.Id).Select()

.Mapper((it, cache) =>

{

    var allSchools = cache.GetListByPrimaryKeys(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(), db.Queryable()).ToList();//union all

兩個Queryable聯表查詢（有人說我只支援12表JOIN，那這樣就可以支援24張表了）

var q1 = db.Queryable((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();

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((s1, s2) => s1.Id == s2.Id).Select(s1 => s1).WithCache().ToList();//可以設定過期時間WithCache(60)

刪除快取

我們需要刪除快取也相當方便，只需要在對該表操作的時候加 RemoveDataCache 就能把查詢中取用該表的快取全部清除

db.Deleteable().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一直在默默進步，得到了一些大型企業的認可，但這隻是開始，希望你喜歡。

SqlSugar ORM 入門到精通入門篇 （https://www.cnblogs.com/sunkaixuan/p/8454844.html）

---

●編號163，輸入編號直達本文

●輸入m獲取文章目錄

推薦↓↓↓

Web開發

更多推薦《18個技術類公眾微信》

涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。

孫凱旋

贊賞

長按二維碼向我轉賬

受蘋果公司新規定影響，微信 iOS 版的贊賞功能被關閉，可透過二維碼轉賬支援公眾號。

閱讀原文


微信掃一掃
使用小程式
即將開啟”“小程式
取消
開啟