查询条件

1、普通表达式查询

//id=@id
var list=db.Queryable<Student>().Where(it => it.Id == id).ToList();
var list2=db.Queryable<Student>().WhereIF(id>0,it => it.Id == id).ToList();// 如果id>0 添加条件 id=@id 否则查询所有

//id=@id or name like '%'+@name+'%'
var list2 = db.Queryable<Student>().Where(it => it.Id == id||it.Name.Contains("jack")).ToList();



2、根据SQL查询

//id=@id
var list=db.Queryable<Student>().Where("id=@id",new { id=1}).ToList();
//id=@id or name like '%'+@name+'%'
var list2 = db.Queryable<Student>().Where("id=@id or name like '%'+@name+'%' ",new { id=1,name="jack"}).ToList();



3、动态组装查询

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

//复杂用例
List<IConditionalModel> conModels = new List<IConditionalModel>();
conModels.Add(new ConditionalModel{ FieldName = "id", ConditionalType = ConditionalType.Equal,FieldValue="1"}); 
conModels.Add(new ConditionalModel{ FieldName = "Student.id",ConditionalType = ConditionalType.Equal, FieldValue="1"}); 
conModels.Add(new ConditionalModel{ FieldName = "id", ConditionalType = ConditionalType.Like, FieldValue = "1" }); 
conModels.Add(new ConditionalModel{ FieldName = "id", ConditionalType = ConditionalType.IsNullOrEmpty });
//id in ('一','二','三')   FieldValue不能存在单引号,正确代码如下
conModels.Add(new ConditionalModel{ FieldName = "id", ConditionalType = ConditionalType.In,FieldValue = "一,二,三"});  
//not in 
conModels.Add(new ConditionalModel{ FieldName = "id", ConditionalType = ConditionalType.NotIn,FieldValue = "1,2,3"});
conModels.Add(new ConditionalModel{ FieldName = "id", ConditionalType = ConditionalType.NoEqual,FieldValue="1,2,3"});
conModels.Add(new ConditionalModel{ FieldName = "id", ConditionalType = ConditionalType.IsNot,FieldValue=null }); 

conModels.Add(new ConditionalCollections()
{
    ConditionalList = new List<KeyValuePair<WhereType, SqlSugar.ConditionalModel>>()
    //  (id=1 or id=2 and id=1)
{
    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 = "2" })
}
});
var student = db.Queryable<Student>().Where(conModels).ToList();



4、条件拼接查询

var query = db.Queryable<Student>().Where(it=>it.Id==1);

 if(条件)
  query.Where(it => it.Name == "jack");
 if(条件)
   query.Where(it => it.Id==1);
   
 int count=query.Clone().Count();//当query用于2个地方的时候一定要加Clone这点和EF有本质区别
 var list=query.Clone.ToList();



5、动态拼表达式查询

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



6、链式条件查询

var query = db.Queryable<Student>()
                .WhereIF(!string.IsNullOrEmpty(name), it => it.Name.Contains(name))
                .WhereIF(id>0, it => it.Id==id).ToList();