1. Simple multi condition multi action parameter

Create database object

 //Create database object SqlSugarClient   
 SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
 {
            ConnectionString = "Server=.xxxxx",//Connector string
            DbType = DbType.SqlServer, //Database type
            IsAutoCloseConnection = true //Not set as true To manually close
 });

 

JSON format transferred from the foreground [{}, {}, {}]

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

Background code

//Manual construction
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 direct serialization
var conModels= db.Context.Utilities.JsonToConditionalModels(json)  
 
var student = db.Queryable<Student>().Where(conModels).ToList();
//select * from Student where id=1 and name = 'jack'

This is relatively simple. One more record is one more AND

 

2. Two level multi condition dynamic parameters

This mode is sufficient for table queries, supports Level 2 queries, and is more flexible in 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 direct serialization
var whereList= db.Context.Utilities.JsonToConditionalModels(json);
var list = db.Queryable<Order>().Where(whereList).ToList();

Generated Sql:

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

Note: if the first Key of the ConditionalList is 1, a {or (condition) will be generated

 

If the first Key of the ConditionalList is 0, a {and (condition) will be generated

Key tabular operator: And =0, Or=1

 

Example 1: there are 3 records in the ConditionalList {set, key = 1, key = 0, key = 1

Generated Sql # OR (condition AND # OR condition)

 

Example 2: there is a record key =1 in the ConditionalList # set

Generated Sql} or (condition)

 

Example 3: there are 4 records in the ConditionalList {set, key =0,key=1,key=1,key=1

Generated Sql # AND (condition OR # condition OR condition)

 

This mode only supports two-level operation, which cannot be realized if more levels are required

 

3. Tree dynamic condition (please upgrade 5.0.5.1)

This is more powerful. It is generally used for table public API and other operations. It can construct conditions at any level and support tree conditions

Key tabular operator: 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
                }
            }]
        }
    }]
}]

Generated SQL:

 

WHERE  (   [id] = @Conditid10001   

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

　　

C# code

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

More use cases: https://www.donet5.com/Ask/9/14378

 

3. Operator interpretation

ConditionalTypek is an enumeration

enumeration	enum	describe
Equal	0	be equal to
Like	1	Fuzzy query
GreaterThan	2	greater than
GreaterThanOrEqual	3	Greater than or equal to
LessThan	4	less than
LessThanOrEqual	5	Less than or equal to
In	6	In operation Correct format: X,Y,Z Bad format 'X','Y','z'
NotIn	7	The Not in operation parameter is the same as in
LikeLeft	8	Left blur
LikeRight	9	Right blur
NoEqual	10	Not equal to
IsNullOrEmpty	11	Is null or ''
IsNot	12	Case 1: value is not equal to null Field < > x Case 2: value equals null Field is not null
NoLike	13	Fuzzy query negation
EqualNull	14	Case 1: value is not equal to null Field = x Case 2: value equals null Field is null
InLike	15	Correct format: X,Y,Z Bad format 'X','Y','z' Sql generated in:  ( id like '%X%' or id like '%Y%' or id like '%Z%')

       

4. Column name validation or conversion

Requirement 1: when the entity and the field names in the table are different, we can do the following conversion

foreach(var r in conModels)
{        
   r.FieldName =db.EntityMaintenance.GetDbColumnName<Order>(r.FieldName );//This supports the use of attributes in entity classes as parameters
}

See the document: requirement 2: I want to verify that the attribute name transmitted from the front end is the same as the entity. Although the column name is anti injection, it can still be transmitted arbitrarily. It is safer to add verification for high-level security projects

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

 

 

5. Type conversion

For example, PGSQL does not support string parameters equal to INT type. We can use type conversion

//SqlSugar's own type conversion
new ConditionalModel() { 
                FieldName = "id", 
                ConditionalType = ConditionalType.Equal, 
                FieldValue = "1",
                FieldValueConvertFunc=it=>SqlSugar.UtilMethods.ChangeType2(it,typeof(int)) 
              }
//Type conversion from one implementation                       
new ConditionalModel()  { 
                FieldName = "id", 
                ConditionalType = ConditionalType.Equal, 
                FieldValue = "1",
                FieldValueConvertFunc=it=>Convert.ToInt32(it)) 
               }

　　

6. Multi table query de alias

List<IConditionalModel> conModels = new List<IConditionalModel>();
conModels.Add(new ConditionalModel{ FieldName = "id", ConditionalType = ConditionalType.Equal,FieldValue="1"});
 
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// Is a new class
{      
 
   Id=o.Id
   CustomName=c.Name   
})
.MergeTable()//If the following query is processed through MergeTable, there is no need to add the alias limit of (o,i,c)
.Where(conModels) //Because Select becomes a new table through Mergetable, that is, the new table only has id and CustomName 
.ToList();

　　

Installation: Nuget SqlSugarCore

Source code: https://github.com/donet5/SqlSugar