动态表格查询，JSON查询,WhereDynamicFilter

用途说明

表格查询一般用于动态条件查询，前端只要给JSON后台就可以实现动态查询

1、简单用例

1.1  简单后台构造

var conModels = new List<IConditionalModel>();
conModels.Add(new ConditionalModel{FieldName="id",ConditionalType=ConditionalType.Equal,FieldValue="1"});
var student = db.Queryable<Student>().Where(conModels).ToList();
//select * from Student where id=1
//复杂些看2.1中的用例

//类型如果不兼容需要设置类型
IConditionalModel conditionalModel = new ConditionalModel()
 {
     ConditionalType =0,
     FieldName = "id",
     FieldValue ="1",
     CSharpTypeName="int" //设置类型 和C#名称一样常用的支持
  };
  
//操作符号说明 ConditionalType 看标题4

1.2  简单Json构造

前台传的JSON格式   [{},{},{}]

 [
   {"FieldName":"id","ConditionalType":"0","FieldValue":"1"},
   {"FieldName":"name","ConditionalType":"0","FieldValue":"jack"}
 ]
 
 //像PgSql Oracle类型不匹配就会报错，高版本支持设置 CSharpTypeName  来指定参数类型
  [
    {"FieldName":"id","ConditionalType":"0","FieldValue":"1","CSharpTypeName":"int" } 
  ]
 
 //枚举 ConditionalType 列表看：标题4
 //高版本枚举可以 不用数字

后台代码

 
//5.0.5.1 Json直接序列化
var conModels= db.Utilities.JsonToConditionalModels(json)  

var student = db.Queryable<Student>().Where(conModels).ToList();
//select * from Student where id=1 and name = 'jack'

2、Json二级构造

2.1  二级后台构造

var conModels = new List<IConditionalModel>();

//name='jack'
conModels.Add(new ConditionalModel{FieldName="name",ConditionalType=ConditionalType.Equal,FieldValue="jack"});  

//OR用法: And(id=1 or id=2 and id=3) 
//如果第一个WhereType是OR那么就是 Or(id=1 or id=2 and id=3)
//如果第一个WhereType是And那么就是 And (id=1 or id=2 and id=3)
conModels.Add(new ConditionalCollections()
 {
  ConditionalList = new List<KeyValuePair<WhereType, SqlSugar.ConditionalModel>>() 
  {
   
   new KeyValuePair<WhereType, ConditionalModel>( 
   WhereType.And,
   new ConditionalModel(){FieldName ="id",ConditionalType=ConditionalType.Equal,FieldValue="1"}),

   new KeyValuePair<WhereType, ConditionalModel> (
   WhereType.Or,
   new ConditionalModel() {FieldName ="id",ConditionalType=ConditionalType.Equal,FieldValue="2"}),

   
   new KeyValuePair<WhereType, ConditionalModel> ( 
   WhereType.
   And,new ConditionalModel() {FieldName="id",ConditionalType=ConditionalType.Equal,FieldValue="3"})
  }
 });
 
 //name='jack' And ( id=1 or id=2 and id=3 )
 var student = db.Queryable<Student>().Where(conModels).ToList();

2.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.Utilities.JsonToConditionalModels(json);
var list = db.Queryable<Order>().Where(whereList).ToList();
//如果不用Json怎么构造 标题1里面有介绍

生成的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、Json复杂-树型(请升级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.Utilities.JsonToConditionalModels(json);
var list = db.Queryable<Order>().Where(conditionalModels).ToList();

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

4、操作符解释

ConditionalType是一个枚举

5、列名验证或者转换

需求1：实体和表中字段名称不一样的情况下，我们可以做下面转换

foreach(var r in conModels)
{        
   r.FieldName =db.EntityMaintenance.GetDbColumnName<Order>(r.FieldName );//这样就支持了用实体类中的属性作为参数
}

需求2：我要验证前端传过来的属性名和实体一样，列名虽然防注入，但是还是可以任意传，对于高安全级别项目加个验证更保险 

看文档：

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

6、类型转换

比如PGSQL不支持字符串参数与INT类型相等，我们可以使用类型转换

/*新版本*/
//5.0.5.4
IConditionalModel conditionalModel = new ConditionalModel()
 {
     ConditionalType =0,
     FieldName = "id",
     FieldValue ="1",
     CSharpTypeName="int" //设置类型 和C#名称一样常用的支持
  };

7、多表查询去别名

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// 是一个新类
{      

   Id=o.Id
   CustomName=c.Name   
})
.MergeTable()//通过MergeTable处理下面的查询就不需要加上  (o,i,c) 的别名限制了
.Where(conModels) //因为Select通过Mergetable变成了一个新表，也就是说新表只有 id和CustomName 
.ToList();

8、导航查询+动态查询

//一对一
.Where(List<IConditionalModel>)//主表过滤 (不加MergeTable多表需要别名前缀)
.Where(x=>SqlFunc.Exists(x.SchoolA.Id,List<IConditionalModel>))//导航表 过滤 (不需要别名前缀)


//多对多或者一对多
.Where(List<IConditionalModel>)//主表过滤 (不加MergeTable多表需要别名前缀)
.Where(it=>it.导航对象.Any(List<IConditionalModel>)  //导航表 过滤 (不需要别名前缀)

9、自定义表格查询

9.1 自定扩展方式

当上面的数字不能满足的时候，我们可以使用自定义扩展

https://www.donet5.com/ask/9/16921

9.2 自定义SQL （5.1.4.100+）

需要注意一些安全问题,FieldValue尽量从后端定义

List<IConditionalModel> conModels = new List<IConditionalModel>();
if(FieldName=="前端定义的枚举")
{
  conModels.Add(new ConditionalModel{ 
             FieldName = UtilMethods.FiledNameSql(),//当列名为系统GUID时，Value变成SQL并生效
             ConditionalType = ConditionalType.Equal,
             FieldValue="id>1" //指定Sql最好在后端指定避免前端传入
             });
}

10、Conditionals to sql

需要版本：5.1.4.148+

    var sql= db.Utilities.ConditionalModelsToSql(new List<IConditionalModel>()
    {
        new ConditionalModel()
        { 
            Conditional = ConditionalType.Equal,
            FieldName = "UserName",
            ConditionalValue = "admin",
            CSharpTypeName="int"
        }
    });