传统的操作方式,筛选数据需要用StringBuilder拼接一大堆的WHERE子句。
在Entity Framework中,代码稍有不慎就会造成巨大性能消耗,如:
using(var db=new MyDbContext())
{
var s= db.Students.ToList().First(s=>s.ID=1200);
}
嘣!进行了全表数据读取!当然一般人也不会犯这种低级的错误,言归正传。
可以简单的这样筛选数据:
using(var db=new MyDbContext())
{
var list =db.Students.AsQueryable();
if(********){list=list.Where(s=>s.ID=1200);}
if(******){list=list.Where(...)}
}
但是有时这种方法不能完成特定需求,如:
using(var db=new MyDbContext())
{
var list =db.Students.AsQueryable();
if(条件1){list=list.Where(s=>s.ID>1200);}
if(条件2){list=list.Where(s=>s.ID<1000);}
}
现在条件1和条件2同时成立,得到的是空结果集而不是ID>1200和ID<1000的结果集。
这只是两个并列简单条件的组合,如果是条件嵌套呢?
下面是假想:
using (var db = new MyDbContext())
{ Expression<Func<Student, bool>> checkStudent1 = s1 => s1.ID > 1200; Expression<Func<Student, bool>> checkStudent2 = s2 => s2.ID < 1000; var e = Expression.Lambda<Func<Student, bool>>( Expression.Or(checkStudent1.Body, checkStudent2.Body), checkStudent1.Parameters); var result = db.Students.Where(e).ToList(); }叫它假想的原因是执行会产生异常”The parameter 's2' was not bound in the specified LINQ to Entities query expression“。
e的内容是{s1 => ((s1.ID > 1200) Or (s2.ID < 1000))},很明显s2这个参数是没有被定义的。
实际上我们一直操作一个Student表,最终我们想要的也是多Lambda表达式合在一起对该Student表的操作。换句话说,s2应该用s1代替。
有人说了,这样:
Expression<Func<Student, bool>> checkStudent1 = s => s.ID > 1200;
Expression<Func<Student, bool>> checkStudent2 = s => s.ID < 1000; var e = Expression.Lambda<Func<Student, bool>>( Expression.Or(checkStudent1.Body, checkStudent2.Body), checkStudent1.Parameters); var result = db.Students.Where(e).ToList();异常:”The parameter 's' was not bound in the specified LINQ to Entities query expression“。
e的内容是{s => ((s.ID > 1200) Or (s.ID < 1000))},现在参数都一样是s了,但其实它们的GUID不同,也就是说它们还是两个不同的参数。
我们需要做的是手工把checkStudent2.Body里面的参数s换成checkStudent1.Body里面的参数s。
ExpressionVisitor可以很好的完成这步操作。拿个别人现成的例子来用:
public class ParameterRebinder : ExpressionVisitor { private readonly Dictionarymap; public ParameterRebinder(Dictionary map) { this.map = map ?? new Dictionary (); } public static Expression ReplaceParameters(Dictionary map, Expression exp) { return new ParameterRebinder(map).Visit(exp); } protected override Expression VisitParameter(ParameterExpression p) { ParameterExpression replacement; if (map.TryGetValue(p, out replacement)) { p = replacement; } return base.VisitParameter(p); } }
更改后的代码:
Expression<Func<Student, bool>> checkStudent1 = s => s.ID > 1200;
Expression<Func<Student, bool>> checkStudent2 = s => s.ID < 1000; var body2 = ParameterRebinder.ReplaceParameters( checkStudent2.Parameters.Select((s,i)=>new{s,f=checkStudent1.Parameters[i]}).ToDictionary(p=>p.s,p=>p.f), checkStudent2.Body); var e = Expression.Lambda<Func<Student, bool>>( Expression.Or(checkStudent1.Body, body2), checkStudent1.Parameters); var result = db.Students.Where(e).ToList();至此表达式顺利拼接完成。当然这样使用还是麻烦,借用别人的扩展类稍微修改一下:
public static class PredicateBuilder { public static Expression> True () { return f => true; } public static Expression > False () { return f => false; } public static Expression Compose (this Expression first, Expression second, Func merge) { // build parameter map (from parameters of second to parameters of first) var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f); // replace parameters in the second lambda expression with parameters from the first var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body); // apply composition of lambda expression bodies to parameters from the first expression return Expression.Lambda (merge(first.Body, secondBody), first.Parameters); } public static Expression > And (this Expression > first, Expression > second) { return first.Compose(second, Expression.And); } public static Expression > Or (this Expression > first, Expression > second) { return first.Compose(second, Expression.Or); } }
参考:
完美的动态拼接Lambda表达式如下: using (var db = new MyDbContext()) { var predicate = PredicateBuilder.True<Student>(); predicate=predicate.And(s => s.ID > 1200); predicate=predicate.Or(s => s.ID < 1000); var result = db.Students.Where(predicate).ToList(); }下面是一个我自己使用的例子,仅供参考:
using (var db = new SHTrackerDbContext()) { var predicate = PredicateBuilder.True(); settings = DecorateSettings(settings); Expression > checkCourse = c => db.Students.Any(s => s.CourseID == c.ID); if (!string.IsNullOrEmpty(settings.Quater_Year)) { checkCourse = c => db.Students.Any(s => s.CourseID == c.ID && db.Student2CBOs.Any( s2c => s2c.StudentID == s.ID && s2c.Quater_Year.Equals(settings.Quater_Year))); } if (settings.QuaterYearArray != null) { checkCourse = c => db.Students.Any(s => s.CourseID == c.ID && db.Student2CBOs.Any( s2c => s2c.StudentID == s.ID && settings.QuaterYearArray.Any(qy => qy.Equals(s2c.Quater_Year)))); } if (!string.IsNullOrEmpty(settings.DPU_ID)) { checkCourse = checkCourse.And( c => db.Students.Any(s => s.CourseID == c.ID && s.DPU_ID.Equals(settings.DPU_ID))); } predicate = predicate.And(checkCourse); if (settings.IsCheckInstructorName) { predicate = predicate.And(c => c.InstructorName.Equals(settings.InstructorName)); } if (!string.IsNullOrEmpty(settings.Term)) { predicate = predicate.And(c => c.TermDescription.Equals(settings.Term)); } if (settings.TermArray != null) { predicate = predicate.And(c => settings.TermArray.Any(t => t.Equals(c.TermDescription))); } if (settings.CourseType != CourseType.All) { predicate = predicate.And(c => c.Type == (int) settings.CourseType); } var cc = new CourseCollection( db.Courses.AsNoTracking() .Where(predicate) .OrderByDescending(m => m.ID) .Skip((pageIndex - 1)*pageSize) .Take(pageSize) .ToList(), db.Courses.AsNoTracking().Where(predicate).Count()) { PageIndex = pageIndex, PageSize = pageSize, Settings = DecorateSettings(settings) }; return cc; }
参考:http://blog.csdn.net/leewhoee/article/details/8968023