用 .SqlSugar ORM 來實現報表功能 .NET CORE /.NET

2021 年 7 月 5 日
筆記
orm

架框介紹

SqlSugar是一款.NET老牌ORM 並且也是新手基數比較多的ORM（因為上手容易），SqlSugar之所以能一直更新到現在，還是要感謝SqlSugar的忠實用戶，隨著我的技術越來越好，加上用戶量越來越多，我也越來越勤快，功能也越來越注重細節

查詢體系

用開源框架最怕遇到的就是想用一個功能發現沒有，這個有時候就會非常麻煩，或者有這個功能發現不好用，SqlSugar是經過7年來真實的需求量身訂製的，可以說在功能方面都比

EF CORE更加實用也不為過，一圖看懂 SqlSugar查詢功能

基礎查詢
基礎查詢	查詢單條、主鍵查詢、查所有、模糊查詢、排序、TOP 、Count、查單條、IN等等操作
分頁查詢	分頁查詢
分組查詢	分組查詢和去重複 Group by Distinct
排序	Order by 、隨機排序、動態排序
多表查詢
聯表查詢	使用Left Join Inner Join進行查詢
配置查詢	簡化聯表操作，解決字典聯表和簡單Name聯表問題
子查詢 2個Queryable Join	查詢中套查詢，2個結果集進行JOIN等操作
導航查詢	一對多、一對一、多對多操作，有層級的查詢
並集查詢	Union all
樹型查詢	查詢出一個樹形結構，比如菜單
業務查詢
無實體查詢	沒有實體查詢
表格查詢	前端組裝好查詢條件，後台直接使用
全局過濾器	比如很多地方用到假刪除，那麼我們可以配置加上IsDeleted
多庫查詢	如果表結構一樣，那我們可以用一個實體操作不同表
高級功能
二級快取	支援Redis等快取，讓你不需要維護 CacheKey輕鬆使用快取來提高伺服器性能
非同步查詢	使用非同步進行查詢
Sqlfun函數	使用SqlSugar自帶的資料庫函數查詢
擴展Sql函數	當有些ORM不能解析的功能，可以自已封裝SQL函數
Queryable
Select用法	一列、多列、匿名對象、多表映射等
Where用法	表達式、拼表達式、Sql、動態條件等
生命周期	原理、引用類型、拷貝機制
執行查詢	ToList First ToDateTable ToJson ToTree ToParentList ToSql ToPivotList ToPivotTable ToClassString ToDictionary ToDictionaryList Count Any Sum Max Min
報表
報表查詢	讓ORM可以方便的實現報表統計
Sql查詢	Sql語句查詢和分頁

ORM實現報表

用ORM實現報表有2個短板

1. Sql可以方便的創建臨時表並且可以將這些臨時表進行 JOIN 操作

2.Sql可以方便的實現行列互轉操作

實踐一、按月統計沒有記錄為0

表結構如下

我們用Gropby實現查詢，發現結果就只有3條記錄，其中2月份就沒有數據，那麼這個Sql就不符合要求

這個時候我們就需要生成一個月份臨時表

然後上面的表在和臨時表進行JOIN 在分組，會不會認為Sql也挺麻煩的呢？

用SqlSugar輕鬆實現

var queryableLeft = db.Reportable(ReportableDateType.MonthsInLast1years).ToQueryable<DateTime>(); //生成月份
//ReportableDateType.MonthsInLast1yea 表式近一年月份 並且queryable之後還能在where過濾
var queryableRight = db.Queryable<operateinfo>(); //聲名表

//月份和表JOIN
var list= db.Queryable(queryableLeft, queryableRight, JoinType.Left, (x1, x2) => x2.operate_time.ToString("yyyy-MM")== x1.ColumnName .ToString("yyyy-MM")) 
.GroupBy((x1,x2)=>x1.ColumnName) 
.Select((x1, x2) => new { 
                     count=SqlFunc.AggregateSum(SqlFunc.IIF(x2.id>0,1,0)) , //null的數據要為0所以不能用count
                     date=x1.ColumnName.ToString("yyyy-MM") }
).ToList();

實踐二、指定月統計

還是這張表和數據，下面數據只有2條還是不符合要求，應該是31條記錄

用SqlSugar實現

            var days = (time.AddMonths(1) - time).Days;//獲取2021年1月的總天數
            var dayArray = Enumerable.Range(1, days).ToList();//轉成int數組
            var queryableLeft = db.Reportable(dayArray).ToQueryable<int>();
            var queryableRight = db.Queryable<operateinfo>();
            var list= db.Queryable(queryableLeft, queryableRight, JoinType.Left,
                (x1, x2) => x2.operate_time.Day==x1.ColumnName)
                .GroupBy((x1,x2)=>x1.ColumnName)
                .Select((x1, x2) => new
                {
                    count=SqlFunc.AggregateSum(SqlFunc.IIF(x2.id>0,1,0)) ,
                    day=x1.ColumnName
                    
                }).ToList();

結果出來了，我們還能做的更好點在進行一下行轉列

     var days = (time.AddMonths(1) - time).Days;//獲取1月天數
            var dayArray = Enumerable.Range(1, days).ToList();//轉成int數組
            var queryableLeft = db.Reportable(dayArray).ToQueryable<int>();
            var queryableRight = db.Queryable<operateinfo>();
            var list= db.Queryable(queryableLeft, queryableRight, JoinType.Left,
                (x1, x2) => x2.operate_time.Day==x1.ColumnName)
                .GroupBy((x1,x2)=>x1.ColumnName)
                .Select((x1, x2) => new  
                {
                    count=SqlFunc.AggregateSum(SqlFunc.IIF(x2.id>0,1,0)) ,
                    day=x1.ColumnName
                    
                }).ToPivotTable(  it=>it.day,it=> it.count, it=>it.Sum(x=>x.count));

實踐三、對象和表隨意JOIN

比如我們創建一個List對象，我們這個List對象就能和表進行Join了

        var list = new List<OrderItem>() { 
              new OrderItem(){ CreateTime=DateTime.Now, OrderId=1, Price=100 },
              new OrderItem(){ CreateTime=DateTime.Now, OrderId=2, Price=120 }
            };
            var query1 = db.Queryable<Order>();
            var queryable2 = db.Reportable(list).ToQueryable();
            var x = db.Queryable(query1, queryable2, (x1, x2) => x1.Id.Equals(x2.OrderId))
                .Select((x1, x2) => new { name = x1.Name,id=x1.Id, orderid = x2.OrderId }).ToList();

源碼下載：

//github.com/donet5/SqlSugar 記得點贊和推薦 SqlSugar將會一直更新，你的贊和推薦就是我最好的動力

Tags: orm