C# 组合查询条件
在我们开发过程中经常会遇见一些通过条件获取数据的功能,比如说获取我们的用户信息,查询输入框有用户姓名,部门,入职年份等等,但查询时可能只输入一个条件或多个条件,像这种不确定的查询时,我们应该如何处理。
1.通过sql语句来实现
select * fron User where Name = decode('{0}','全部',Name,'{0}') and Year= decode('{1}','全部',Year,'{1}')
实现原理是通过decode来实现的,当没有输入用户条件则对应的语句就是 Name=Name,当输入了用户条件则是 Name=‘张三’
这种方法很明显效率很低,但是写法方便不用添加代码判断逻辑,都是通过sql语句来判断的。
2.通过字符串拼接sql语句
public string GetSql() { string sql="select * from User where 1=1"; if(txt_user.Text!="") { sql=sql+"and Name="+txt_user.Text; } if(Year.Text!="") { sql=sql+"and Year="+Year.Text; } return sql; }
这种方法是最常见的,但是效率也不是很好,如果拼接的语句比较多建议用StringBuilder代替string。
3.EF+Expression
用表达式根目录树来实现。
3.1新建一个类
public static class LinqExtensions { public static Expression Property(this Expression expression, string propertyName) { return Expression.Property(expression, propertyName); } public static Expression AndAlso(this Expression left, Expression right) { return Expression.AndAlso(left, right); } public static Expression Call(this Expression instance, string methodName, params Expression[] arguments) { return Expression.Call(instance, instance.Type.GetMethod(methodName), arguments); } public static Expression GreaterThan(this Expression left, Expression right) { return Expression.GreaterThan(left, right); } public static Expression<T> ToLambda<T>(this Expression body, params ParameterExpression[] parameters) { return Expression.Lambda<T>(body, parameters); } public static Expression<Func<T, bool>> True<T>() { return param => true; } public static Expression<Func<T, bool>> False<T>() { return param => false; } /// <summary> /// 组合And /// </summary> /// <returns></returns> public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) { return first.Compose(second, Expression.AndAlso); } /// <summary> /// 组合Or /// </summary> /// <returns></returns> public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second) { return first.Compose(second, Expression.OrElse); } /// <summary> /// Combines the first expression with the second using the specified merge function. /// </summary> static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge) { var map = first.Parameters .Select((f, i) => new { f, s = second.Parameters[i] }) .ToDictionary(p => p.s, p => p.f); var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body); return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters); } /// <summary> /// ParameterRebinder /// </summary> private class ParameterRebinder : ExpressionVisitor { /// <summary> /// The ParameterExpression map /// </summary> readonly Dictionary<ParameterExpression, ParameterExpression> map; /// <summary> /// Initializes a new instance of the <see cref="ParameterRebinder"/> class. /// </summary> /// <param name="map">The map.</param> ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map) { this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>(); } /// <summary> /// Replaces the parameters. /// </summary> /// <param name="map">The map.</param> /// <param name="exp">The exp.</param> /// <returns>Expression</returns> public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp) { return new ParameterRebinder(map).Visit(exp); } /// <summary> /// Visits the parameter. /// </summary> /// <param name="p">The p.</param> /// <returns>Expression</returns> protected override Expression VisitParameter(ParameterExpression p) { ParameterExpression replacement; if (map.TryGetValue(p, out replacement)) { p = replacement; } return base.VisitParameter(p); } } }
3.2 根据查询条件生成Expression,需要建立一个查询条件的类
private Expression<Func<AlarmEntity, bool>> ListFilter(AlarmListParam param) { //AlarmEntity数据源 //AlarmListParam 是一个查询条件的类 var expression = LinqExtensions.True<AlarmEntity>(); if (param != null) { if (!string.IsNullOrEmpty(param.Module_code)) { expression = expression.And(t => t.module_code.Contains(param.Module_code)); } if (!string.IsNullOrEmpty(param.Machine)) { expression = expression.And(t => t.machine.Contains(param.Machine)); } } return expression; }
3.3 通过Expression生成数据
#region 获取数据 public async Task<List<AlarmEntity>> GetList(AlarmListParam param) { var expression = ListFilter(param); var list = await this.BaseRepository().FindList(expression); return list.ToList(); } #endregion