像以前没有使用EF时,对于从界面输入的多条件查询时,都是使用SQL语句的拼接,因为使用了哪几种条件是未知的。
类似下面这样
var sql = "select * from xxx where xxx"; if(!string.IsNullOrEmpty(xxid) { sql += “ And xxid = ” + xxid; }
这种方法虽然笨拙,但是能很好的解决问题。
使用EF后,也遇到同样的问题。在linq中是可以利用表达式树来实现组合条件查询的功能,但是需要一定的学习门槛。
如果你已经了解了基本的linq语法,但是又暂时没有时间学习表达式树,可以试试以下的简单方法。
原理很简单,执行多次where函数即可,这种方法虽然会增加循环次数,但是也算很好的解决了问题。
测试代码如下:
模型
1 public class Student 2 { 3 public string Name { get; set; } 4 public string StudentID { get; set; } 5 }
功能实现
1 static void Main(string[] args) 2 { 3 List<Student> students = new List<Student>(); 4 5 students.Add(new Student() { Name = "雍心慈", StudentID = "1" }); 6 students.Add(new Student() { Name = "雍心慈2", StudentID = "2" }); 7 students.Add(new Student() { Name = "乔宁夏", StudentID = "3" }); 8 students.Add(new Student() { Name = "乔宁夏2", StudentID = "4" }); 9 students.Add(new Student() { Name = "宓白夜", StudentID = "5" }); 10 students.Add(new Student() { Name = "仰灵萱", StudentID = "6" }); 11 students.Add(new Student() { Name = "冷杏儿", StudentID = "7" }); 12 13 var query = students.AsEnumerable(); 14 15 foreach (var rule in GetExpression("心","1")) 16 { //循环执行过滤条件 17 query = query.Where(x => rule(x)); 18 } 19 20 foreach (var item in query) 21 { 22 Console.WriteLine(item.StudentID); 23 Console.WriteLine(item.Name); 24 } 25 } 26 27 private static List<Func<Student, bool>> GetExpression(string name, string studentId) 28 { 29 List<Func<Student, bool>> list = new List<Func<Student, bool>>(); 30 31 if (!string.IsNullOrEmpty(studentId)) 32 { 33 list.Add(x => x.StudentID.Contains(studentId)); 34 } 35 36 if (!string.IsNullOrEmpty(name)) 37 { 38 list.Add(x => x.Name.Contains(name)); 39 } 40 41 return list; 42 }
如果想要减少循环次数,代码复杂一点,可以参考如下方法:
1 internal class Program 2 { 3 static void Main(string[] args) 4 { 5 //构建 一个测试列表 6 List<Student> students = new List<Student>(); 7 8 students.Add(new Student() { Name = "雍心慈", StudentID = "1" }); 9 students.Add(new Student() { Name = "雍心慈2", StudentID = "2" }); 10 students.Add(new Student() { Name = "乔宁夏", StudentID = "3" }); 11 students.Add(new Student() { Name = "乔宁夏2", StudentID = "4" }); 12 students.Add(new Student() { Name = "宓白夜", StudentID = "5" }); 13 students.Add(new Student() { Name = "仰灵萱", StudentID = "6" }); 14 students.Add(new Student() { Name = "冷杏儿", StudentID = "7" }); 15 //通过名字和Id进行过滤 16 var result = students.AsQueryable().Where(GetExpressionTree("心", "1")); 17 18 foreach (var item in result) 19 { 20 Console.WriteLine(item.StudentID); 21 Console.WriteLine(item.Name); 22 } 23 }40 //通过条件构建表达式树 41 private static Expression<Func<Student, bool>> GetExpressionTree(string name, string studentId) 42 { 43 Expression<Func<Student, bool>> expression = x => true; 44 45 var paramExpression = Expression.Parameter(typeof(Student)); 46 47 if (!string.IsNullOrEmpty(name)) 48 { 49 //名字条件 50 Expression<Func<Student, bool>> nameExpression = x => x.Name.Contains(name); 51 expression = ConcatExpression(expression, nameExpression, paramExpression); 52 } 53 54 if (!string.IsNullOrEmpty(studentId)) 55 { 56 //Id条件 57 Expression<Func<Student, bool>> patientIdExpression = x => x.StudentID.Contains(studentId); 58 expression = ConcatExpression(expression, patientIdExpression, paramExpression); 59 } 60 61 return expression; 62 } 63 64 /// <summary> 65 /// 将表达式组合在一起 66 /// </summary> 67 /// <param name="left"></param> 68 /// <param name="right"></param> 69 /// <param name="parameterExpression"></param> 70 /// <returns></returns> 71 private static Expression<Func<Student, bool>> ConcatExpression(Expression<Func<Student, bool>> left, Expression<Func<Student, bool>> right, ParameterExpression parameterExpression) 72 { 73 var expressionBody = Expression.AndAlso(left.Body, right.Body); 74 expressionBody = (BinaryExpression)new ParameterReplacer(parameterExpression).Visit(expressionBody); 75 return Expression.Lambda<Func<Student, bool>>(expressionBody, parameterExpression); 76 } 77 } 78 79 /// <summary> 80 /// 重写表达式树。 81 /// </summary> 82 internal class ParameterReplacer : ExpressionVisitor 83 { 84 private readonly ParameterExpression _parameter; 85 86 protected override Expression VisitParameter(ParameterExpression node) 87 { 88 return base.VisitParameter(_parameter); 89 } 90 91 internal ParameterReplacer(ParameterExpression parameter) 92 { 93 _parameter = parameter; 94 } 95 }
运行结果:
参考:
c# - How to Combine two lambdas - Stack Overflow
c# - Combining two expressions (Expression<Func<T, bool>>) - Stack Overflow
How do I combine LINQ expressions into one? - Stack Overflow