Net/NetCore/.NET5 ORM 六大查詢體系 - SqlSugar 高階篇

果糖大資料科技發表於2021-01-03
NetCoreORMSqlSugar

框架介紹

SqlSugar ORM是一款老牌國產ORM框架，生命力也比較頑強，從早期ORM不成熟階段，一直存活到現在，我為什麼要一直堅持，那是因為還有很多使用者在使用，本來我能夠較早推出新開源框架 ，可是使用者還在不停的提新的需求和高的要求，所以我也盡我最大努力將SqlSugar更加完善

1、有人說不支援國產資料庫 我支援了

2、有人說 PgSql MySql Oracle相容性不好，經過努力我也讓他成熟了

3、有人說SqlSugar非同步是Task.RUN，我也將SqlSugar升級到NET 4.5支援了原生非同步

4、有人說導航支援不給力 ，我也讓他開始給力

5、有人說不支援多級插入，我也支援了

我不能保證6年來每個使用者的需求都能夠支援，但是我能保證多個使用者提出了一樣的需求，那我就需要好好設計並且滿足他們

 

本文重點

SqlSugar查詢非常的豐富多彩，且這些功能也有非常高的實用性，我將SqlSugar的查詢做了一個整理和分類：

1、基礎查詢  

2、聯表查詢

3、子查詢

4、導航查詢

5、查詢結果

6、高階查詢

 

一、基礎查詢

基礎查詢從字面意思 就是最常用最簡單的查詢

var getAll = db.Queryable<Student>().ToList();//查詢所有
var top10= db.Queryable<Student>().Take(10).ToList();//查詢前10
var getFirst = db.Queryable<Student>().First(it=>it.Id==1);//查詢單條
var getAllNoLock = db.Queryable<Student>().With(SqlWith.NoLock).ToList();//SqlServer裡面的withnolock
var getByPrimaryKey = db.Queryable<Student>().InSingle(2);//根據主鍵查詢
var sum = db.Queryable<Student>().Sum(it=>it.Id);//查詢總和
var isAny = db.Queryable<Student>().Where(it=>it.Id==-1).Any();//是否存在
var isAny2 = db.Queryable<Student>().Any(it => it.Id == -1);
var getListByRename = db.Queryable<School>().AS("Student").ToList();
var getByWhere = db.Queryable<Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var list= db.Queryable<Student>().AS("student2019").ToList();//select * from student2019
var list2 = db.Queryable<Order>().Where(it =>it.Name.Contains("jack")).ToList();//模糊查詢 name like '%'+@name+'%'

分組查詢

var list = db.Queryable<Student>()
             .GroupBy(it => new { it.Id, it.Name })
             .Having(it => SqlFunc.AggregateAvg(it.Id) > 0)//不是聚合函式用Where就可以了
             .Select(it => new { idAvg = SqlFunc.AggregateAvg(it.Id), name = it.Name })
             .ToList();
//SELECT AVG([Id]) AS[idAvg], [Name] AS[name]  FROM[Student] GROUP BY[Name],[Id] HAVING(AVG([Id]) > 0 )

分頁查詢

//同步分頁 
 int pageIndex = 1; 
 int pageSize = 20;
 int totalCount=0;
 var page = db.Queryable<Student>().ToPageList(pageIndex, pageSize, ref totalCount);

並集合查詢

 var q1 = db.Queryable<Student>().Select(it=>new Model{ name=it.Name });
 var q2 = db.Queryable<School>().Select(it => new Model { name = it.Name });
 var list = db.UnionAll(q1, q2).ToList();
SELECT * FROM  (SELECT  [Name] AS [name]  FROM [STudent]  UNION ALL
SELECT  [Name] AS [name]  FROM [School] ) unionTable

 

二、聯表查詢

1.兩表查詢將結果返回匿名物件

var list = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(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])

 

2、聯表查詢將結果返回到新類,實現  Select a.*,  b.name as bname

var oneClass = db.Queryable<Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
    JoinType.Left, o.Id == i.OrderId,
    JoinType.Left, o.CustomId == c.Id
))
.Select((o,i,c)=> new ViewOrder// 是一個新類
{
   Id=SqlFunc.GetSelfAndAutoFill(o.Id),// 等於 o.*
   CustomName=c.Name   // 等於 [c].[Name] AS [CustomName]
}).ToList()

 

3.按規則自動填充

需要注意的是 Select用的是自動填充這樣使用方便，高併發的地方還是寫成上面那種方式

 public class ViewModelStudent : Student
 {
        public string SchoolName{get;set;}// 類名 + 屬性名
 }
 var list = db.Queryable<Student, School, DataTestInfo>((st, sc, di) => new JoinQueryInfos(
              JoinType.Left,st.SchoolId==sc.Id,//可以用&&實現 on 條件 and
              JoinType.Left,st.Name==di.String
            ))
           //.Where((st,sc)=>sc.id>0) 多表條件用法
           .Select<ViewModelStudent>().ToList();
  
 //SELECT 
    //sc.[Name] AS [SchoolName],--自動生成 SchoolName
      //st.[ID] AS [Id],st.[SchoolId] AS [SchoolId],
      //st.[Name] AS [Name],st.[CreateTime] AS [CreateTime]
      //FROM [STudent] st
      //Left JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] )  
      //Left JOIN [DataTestInfo] di ON ( [st].[Name] = [di].[String] )

 

4、簡單聯表

var list = db.Queryable<Order, OrderItem, Custom>((o, i, c) => o.Id == i.OrderId&&c.Id == o.CustomId)
                .Select<ViewOrder>()
                .ToList();

 生成的Sql:

SELECT  c.[Name] AS [CustomName],
        o.[Id] AS [Id],o.[Name] AS [Name],
        o.[Price] AS [Price],
        o.[CreateTime] AS [CreateTime],
        o.[CustomId] AS [CustomId]         FROM [Order] o  ,[OrderDetail]  i ,[Custom]  c  
         WHERE (( [o].[Id] = [i].[OrderId] ) AND ( [c].[Id] = [o].[CustomId] ))

 

三、子查詢

SqlSugar的子查詢也是非常的強大，很多人都問

1. 子查詢查一列

var getAll = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.Id==sc.Id))
.Where(st => st.Id == SqlFunc.Subqueryable<School>().Where(s => s.Id == st.Id).Select(s => s.Id))
.ToList();

 生成的Sql如下

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

在select中也可以使用

var getAll = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(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();

同時Subquery也支援了Join

.LeftJoin<OrderItem>((cus,item)=>cus.Id==item.CustomId /* 用 && 追加條件 */)

 

2.IN和NOT IN的操作

var getAll7 = db.Queryable<Student>().Where(it => 
SqlFunc.Subqueryable<School>().Where(s => s.Id == it.Id).Any()).ToList();

/*生成的SQL(等於同於it.id in(select id from school)只是寫法不一樣
SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it 
WHERE (EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) )) 
*/


var getAll8 = db.Queryable<Student>().Where(it => 
SqlFunc.Subqueryable<School>().Where(s => s.Id == it.Id).NotAny()).ToList();

/*生成的SQL
SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it 
WHERE (NOT EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) ))
*/

 

3、聯表子查詢

當你要用一個表和一個聯進行聯查詢的時候，或者2個聯表在進行聯表查詢的時候都可以用這種方式實現

var query1 = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))
.Where(st => st.Name == "jack");

var query2 = db.Queryable<DataTestInfo>();

db.Queryable(query1, query2, (p1, p2) => p1.Id == p2.Int1).Select<ViewModelStudent>().ToList();


//SELECT * FROM 
// (SELECT [st].[ID],[st].[SchoolId],[st].[Name],[st].[CreateTime] 
// FROM [STudent] st 
// Left JOIN [School] sc ON ([st].[SchoolId]=[sc].[Id] ) 
// WHERE ( [st].[Name] = @Name0Join0 )) p1 
//
// Inner JOIN

// (SELECT [Int1],[Int2],[String],[Decimal1],[Decimal2],
// [Datetime1],[Datetime2],[Image1],[Image2], 
// [Guid1],[Guid2],[Money1],[Money2],[Varbinary1],
// [Varbinary2],[Float1],[Float2] FROM [DataTestInfo] )p2 

// ON ( [p1].[ID] = [p2].[Int1] )

 

4、多合一子查詢

一般多表查詢後 動態使用搜尋條件和排序 需要使用 st sc等字首，多表合成一後變成了單表查詢 所以都不需要加別名了

var pageJoin = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))
.Select((st,sc) => new{ 
                        id = st.Id,
                        name = sc.Name})
.MergeTable()
.Where(it=>it.id==1).OrderBy("name asc").ToList();

生成的Sql如下：

SELECT * FROM

(SELECT 
[st].[Id] AS [id] , [sc].[Name] AS [name] 
FROM [Student] st Left 
JOIN [School] sc ON ( [st].[CustomId] = [sc].[Id] ) ) MergeTable--將結果變成一個表 

WHERE ( [id] = @id0 )ORDER BY name asc

 

5、一合一的子查詢

我只是想單表外面在包一層 你可以這樣實現，和MergetTable區別在於前者需要加上Select後者不需要

var listx=db.Queryable(db.Queryable<Order>()).ToList();

 Sql程式碼如下：

SELECT t.* FROM (SELECT [Id],[Name],[Price],[CreateTime],[CustomId] FROM [Order] ) t

 

四、導航查詢

1、無配置開箱就用

對於其它ORM導航查詢來說一般都需要實體配置實體的關聯關係，而SqlSugar則開箱就用，除了給非資料庫欄位加上Ignore外沒有任何配置

2、高效能

自動對映模式不會產生迴圈Sql讀取的情況，比如一對多查詢 查詢1條記錄 那麼生成2條SQL，那麼查詢200條記錄還是生成2條SQL，

有人說為什麼是2條而不是1條呢，1條複雜的sql效能未必好過2條簡單無聚合分組的sql，可以通在sqlserver介面把3條sql扔進去然後

看執行計劃的開銷比例，如果說1條Sql大於等於50%那麼他的效能就低於2條SQL效能

 

1、一對一查詢

使用Mapper一對一模式需要設定2-3個引數， Mapper( 1需要填充的子物件 ,2主表關聯欄位，3從表關聯欄位（預設為從表主鍵）)

第一個引數：it.Order是填充的子物件

第二個引數 it.OrderId是主表中關聯欄位

第三個引數 不填等於從表主鍵，也可以指定從表某個欄位

其實就是 主表中的 it.OrderId 和從表中的主鍵進行關聯 ，就能自動填充到 OrderItem.Order裡面

 

var list= db.Queryable<OrderItem>().Mapper(it => it.Order, it => it.OrderId).ToList(); 
foreach(var item in list)
{
   Console.WriteLine(item.Order.Name);//輸出子物件中的值
}
 
//新功能： 1對1模式中支援了條件過濾
var list= db.Queryable<OrderItem>()
.Mapper(it => it.Order, it => it.OrderId)
.Where(it=>it.Order.Name=="哈哈").ToList(); //我們可以使用2級物件屬性進行篩選

public class OrderItem {
 [SugarColumn(IsPrimaryKey =true ,IsIdentity =true)]
 public int ItemId { get; set; }
 public string ItemCode { get; set; }
 public decimal Pirce { get; set; }
 public int OrderId { get; set; }
 [SugarColumn(IsIgnore =true)]
 public Order Order{ get; set; }
}
public class Order {
 [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
 public int Id { get; set; }
 public string Name { get; set; }
}

　　

2、一對多查詢

var list= db.Queryable<Order>().Mapper(it => it.Items, it => it.Items.First().OrderId).ToList();
public class Order
{
 [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
 public int Id { get; set; }
 public string Name { get; set; }
 [SugarColumn(IsIgnore = true)]
 public List<OrderItem> Items { get; set; }
}

 

3、 多對多查詢

public class ABMapping
{
  [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
  public int AId { get; set; }
  public int BId { get; set; }
  [SugarColumn(IsIgnore = true)]
  public A A { get; set; }
  [SugarColumn(IsIgnore = true)]
  public B B { get; set; }
}
public class A {
 [SugarColumn(IsPrimaryKey =true ,IsIdentity =true)]
   public int Id { get; set; }
  public string Name { get; set; }
}
public class B
{
  [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
  public int Id { get; set; }
  public string Name { get; set; }
}
var list= db.Queryable<ABMapping>()
.Mapper(it => it.A,it=>it.AId)
.Mapper(it => it.B, it => it.BId).ToList();


4、 相同欄位名對映

子表物件=子表物件.where(it=>it.OrgId==主表物件中的.OrgId)

var list= db.Queryable<A>().Mapper(it => it.B,it=>it.OrgId).ToList();

　　

 五、查詢結果

支援常用的 類、匿名類、陣列、字典、字典集合、JSON、DataTable、樹

//返回List
List<Student> list = db.Queryable<Student>().ToList();
//返回DataTable
DataTable dataTable = db.Queryable<Student>().Select(it => it.Id).ToDataTable();
//返回Json
var json = db.Queryable<Student>().ToJson();
//返回陣列
List<int> listInt=db.Queryable<Student>().Select(it => it.Id).ToList();　　
//返回匿名物件
var dynamic = db.Queryable<Student>().Select<dynamic>().ToList();　　
//表示式返回字典
Dictionary<string,object> dc = db.Queryable<Order>().ToDictionary(it=>it.Id,it=>it.Name);
//返回字典集合
List<Dictionary<string, object>> dcList = db.Queryable<Order>().ToDictionaryList();;

Tree樹結果

var tree = db.Queryable<Tree>().ToTree(it=>it.Child,it=>it.ParentId,0); //第三個引數為0代表 parent為0是第一級
//實體
public class Tree
{
 [SqlSugar.SugarColumn(IsPrimaryKey =true)]
 public int Id { get; set; }
 public string Name { get; set; }
 public int ParentId { get; set; }
 [SqlSugar.SugarColumn(IsIgnore = true)]
 public List<Tree> Child { get; set; }
}

 

六、高階查詢

二緩快取是將結果集進行快取，當SQL和引數沒發生變化的時候從快取裡面讀取資料，減少資料庫的讀取操作

ICacheService myCache = new HttpRuntimeCache();
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
  ConnectionString = Config.ConnectionString,
  DbType = DbType.SqlServer,
  IsAutoCloseConnection = true,
  ConfigureExternalServices = new ConfigureExternalServices()
  {
    DataInfoCacheService = myCache //配置我們建立的快取類
  }
});
db.Queryable<Student>().Where(it => it.Id > 0).WithCache().ToList();//設定快取預設一天
db.Queryable<Student>().WithCache(1000).ToList();//設定具體過期時間

非同步查詢

var task1=db.Queryable<Order>().FirstAsync();
var task2 = db.Queryable<Order>().Where(it=>it.Id==1).ToListAsync();
 
//分頁需要特別注意用法
RefAsync<int> total = 0;
Db.Queryable<Order>().ToPageListAsync(1, 2, total);

Json型別查詢

public class UnitJsonTest
{
   [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
   public int Id { get; set; }
    [SqlSugar.SugarColumn(ColumnDataType ="varchar(2000)", IsJson = true)]
   public Order Order { get; set; }
   public string Name{get;set;}
}
Db.Insertable(new UnitJsonTest() { Name="json1",Order = new Order { Id = 1, Name = "order1" } }).ExecuteCommand();
var list = Db.Queryable<UnitJsonTest>().ToList();

動態表示式查詢

var exp= Expressionable.Create<Student>()
                .And(it=>it.Id==1)
                .Or(it =>it.Name.Contains("jack")).ToExpression();
var list=db.Queryable<Student>().Where(exp).ToList();

動態條件查詢

var conModels = new List<IConditionalModel>();
conModels.Add(new ConditionalModel{ FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1"}); 
var student = db.Queryable<Student>().Where(conModels).ToList();

　　

 SqlSugar原始碼下載：

https://github.com/donet5/SqlSugar

沒點關注的點一波關注，我不能保證SqlSugar現在就是最好，便是未來會越來越好 ，謝謝 

 新的開源框架WebFirst也在開發中，預計春節前釋出,將支援 web和exe 

 

 

　　

　　

相關文章