.NET 通用多條件動態引數查詢方法 - SqlSugar ORM

果糖大資料科技發表於2021-12-26
SqlSugarORM

通用查詢用途

一般我們Grid控制元件，會有很多條件傳給後臺，如果每個條件都寫一個邏輯的話，那麼工作量將非常大，所以通用查詢功能是每個軟體必備的，

SqlSugar將通用查詢封裝到支援了樹型條件，並且支援所有常用的操作，用SqlSugar或者不用SqlSugar的都可參參考一下

 

1、簡單多條件多動引數 

建立資料庫物件

 //建立資料庫物件 SqlSugarClient   
 SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
 {
            ConnectionString = "Server=.xxxxx",//連線符字串
            DbType = DbType.SqlServer, //資料庫型別
            IsAutoCloseConnection = true //不設成true要手動close
 });

 

前臺傳的JSON格式   [{},{},{}]

 [
   {"FieldName":"id","ConditionalType":"0","FieldValue":"1"},
   {"FieldName":"name","ConditionalType":"0","FieldValue":"jack"}
 ]

後臺程式碼　

//手動構造
var conModels = new List<IConditionalModel>();
conModels.Add(new ConditionalModel{ FieldName = "id",ConditionalType=ConditionalType.Equal,FieldValue="1"});
conModels.Add(new ConditionalModel{ FieldName = "name",ConditionalType=ConditionalType.Equal,FieldValue="jack"});  
 
//5.0.5.1 Json直接序列化
var conModels= db.Context.Utilities.JsonToConditionalModels(json)  
 
var student = db.Queryable<Student>().Where(conModels).ToList();
//select * from Student where id=1 and name = 'jack'

這種比較簡單 多一條記錄就多一個AND

 

2、二級多條件動態引數

這種模式對於表格查詢已經夠用了，支援到2級查詢，並且AND OR都比較靈活了

[ {"FieldName": "id","FieldValue": "1","ConditionalType": 10}, 
  {"FieldName": "id","FieldValue": null,"ConditionalType": 12}, 
  {
    "ConditionalList": [{
        "Key": 1,
        "Value": {
            "FieldName": "id",
            "FieldValue": "2",
            "ConditionalType": 0
        }
    }, {
        "Key": 0,
        "Value": {
            "FieldName": "id",
            "FieldValue": "2",
            "ConditionalType": 0
        }
    }]
}]
//5.0.5.1 Json直接序列化
var whereList= db.Context.Utilities.JsonToConditionalModels(json);
var list = db.Queryable<Order>().Where(whereList).ToList();

　生成的Sql:　

 WHERE   [id] <> @Conditid0   
    AND    [id]  IS NOT  NULL   
    OR     (   [id] = @Conditid10000   AND   [id] = @Conditid20000   )

說明：ConditionalList 第一個Key為1 那麼就會生成  Or( 條件 )

 

          ConditionalList 第一個Key為0 那麼就會生成  And( 條件 )

          Key表式運算子:   And =0，  Or=1 

 

例子1：ConditionalList  集合中 有3條記錄 key =1 , key =0 ,key=1

生成的Sql     OR(條件 AND 條件 OR條件)

 

例子2：ConditionalList  集合中 有1條記錄 key =1 

生成的Sql     OR(條件)

 

例子3：ConditionalList  集合中 有4條記錄 key =0,key=1,key=1,key=1 

生成的Sql     AND (條件 OR 條件 OR條件 OR 條件)

 

這種模式只支援2級操作，需要更多層級就實現不了了

 

3、樹型動態條件 (請升級5.0.5.1)

這種就比較強大了，一般用於表的公開API等操作，可以構造任何層級的條件 ，可以支援樹型條件

Key表式運算子:   And =0，  Or=1,  null=-1

 

[{
    "ConditionalList": [{
        "Key": -1,
        "Value": {
            "FieldName": "id",
            "FieldValue": "2",
            "ConditionalType": 0
        }
    }, {
        "Key": 0,
        "Value": {
            "FieldName": "name",
            "FieldValue": "2",
            "ConditionalType": 0
        }
    }, {
        "Key": 0,
        "Value": {
            "ConditionalList": [{
                "Key": -1,
                "Value": {
                    "FieldName": "price",
                    "FieldValue": "1",
                    "ConditionalType": 0
                }
            }, {
                "Key": 0,
                "Value": {
                    "FieldName": "CustomId",
                    "FieldValue": "1",
                    "ConditionalType": 0
                }
            }]
        }
    }]
}]

生成的SQL:　　

 

WHERE  (   [id] = @Conditid10001   

      AND   [name] = @Conditname20001  
      AND(   [price] = @Conditprice10000   AND   [CustomId] = @ConditCustomId20000   )  )

　　

C#程式碼

var conditionalModels = db.Context.Utilities.JsonToConditionalModels(json);
var list = db.Queryable<Order>().Where(conditionalModels).ToList();　　

 更多用例：https://www.donet5.com/Ask/9/14378

 

3、操作符解釋

ConditionalTypek是一個列舉

列舉列舉值描述
Equal  0 等於
Like  1 模糊查詢
GreaterThan  2 大於
GreaterThanOrEqual  3 大於等於
LessThan  4 小於
LessThanOrEqual  5 小於等於
In  6

In操作

正確格式   X,Y,Z 

錯誤格式   'X','Y','z'
NotIn  7 Not in操作 引數和in一樣
LikeLeft  8 左模糊
LikeRight  9 右模糊
NoEqual  10 不等於
IsNullOrEmpty  11 是null或者''
IsNot  12

情況1   value不等於null

欄位<> x

情況2   value等於null 

欄位 is not null
NoLike  13 模糊查詢取反
EqualNull  14

情況1   value不等於null

欄位= x

情況2   value等於null 

欄位 is null

 
InLike 15

正確格式   X,Y,Z 

錯誤格式   'X','Y','z'

生在的Sql ：  

 ( id like '%X%' or id like '%Y%' or id like '%Z%')

       

4、列名驗證或者轉換

需求1：實體和表中欄位名稱不一樣的情況下，我們可以做下面轉換

foreach(var r in conModels)
{        
   r.FieldName =db.EntityMaintenance.GetDbColumnName<Order>(r.FieldName );//這樣就支援了用實體類中的屬性作為引數
}

看文件：需求2：我要驗證前端傳過來的屬性名和實體一樣，列名雖然防注入，但是還是可以任意傳，對於高安全級別專案加個驗證更保險 

https://www.donet5.com/Home/Doc?typeId=1202 

 

 

5、型別轉換

比如PGSQL不支援字串引數與INT型別相等，我們可以使用型別轉換

//SqlSugar自帶的型別轉換
new ConditionalModel() { 
                FieldName = "id", 
                ConditionalType = ConditionalType.Equal, 
                FieldValue = "1",
                FieldValueConvertFunc=it=>SqlSugar.UtilMethods.ChangeType2(it,typeof(int)) 
              }
//自個實現型別轉換                       
new ConditionalModel()  { 
                FieldName = "id", 
                ConditionalType = ConditionalType.Equal, 
                FieldValue = "1",
                FieldValueConvertFunc=it=>Convert.ToInt32(it)) 
               }

　　

6、多表查詢去別名 

　　

List<IConditionalModel> conModels = new List<IConditionalModel>();
conModels.Add(new ConditionalModel{ FieldName = "id", ConditionalType = ConditionalType.Equal,FieldValue="1"});

 //查詢
 var list=db.Queryable<Order>()
          .LeftJoin<Custom>   ((o,i) => o.CustomId == cus.Id)
          .LeftJoin<OrderDetail> ((o,i,c) => o.Id == oritem.OrderId)
          .Select((o,i,c)=> new ViewOrder{ Id=o.Id CustomName=c.Name }) // 是一個新類 
          .MergeTable()//通過MergeTable處理下面的查詢就不需要加上 (o,i,c) 的別名限制了  
          .Where(conModels)//因為Select通過Mergetable變成了一個新表，也就是說新表只有 id和CustomName 
          .ToList();　

 

7、未來計劃

未來會打算支援 Sql函式，真正做到所有的查詢條件都能用　　

　　

 安裝： Nuget SqlSugarCore

 原始碼： https://github.com/donet5/SqlSugar

相關文章