首页 > Asp.net开发 > EntityFramework动态组合Lambda表达式作为数据筛选条件,代替拼接SQL语句

EntityFramework动态组合Lambda表达式作为数据筛选条件,代替拼接SQL语句

传统的操作数据库方式,筛选数据需要用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可以很好的完成这步操作。拿个别人现成的例子来用:

 

[csharp] view plaincopy

  1. public class ParameterRebinder : ExpressionVisitor
  2.     {
  3.         private readonly Dictionary<ParameterExpression, ParameterExpression> map;
  4.         public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
  5.         {
  6.             this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
  7.         }
  8.         public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
  9.         {
  10.             return new ParameterRebinder(map).Visit(exp);
  11.         }
  12.         protected override Expression VisitParameter(ParameterExpression p)
  13.         {
  14.             ParameterExpression replacement;
  15.             if (map.TryGetValue(p, out replacement))
  16.             {
  17.                 p = replacement;
  18.             }
  19.             return base.VisitParameter(p);
  20.         }
  21.     }

 

更改后的测试代码:

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

至此表达式顺利拼接完成。当然这样使用还是麻烦,借用别人的扩展类稍微修改一下:

 

[csharp] view plaincopy

  1. public static class PredicateBuilder
  2. {
  3.     public static Expression<Func<T, bool>> True<T>() { return f => true; }
  4.     public static Expression<Func<T, bool>> False<T>() { return f => false; }
  5.     public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
  6.     {
  7.         // build parameter map (from parameters of second to parameters of first)
  8.         var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f);
  9.         // replace parameters in the second lambda expression with parameters from the first
  10.         var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);
  11.         // apply composition of lambda expression bodies to parameters from the first expression
  12.         return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
  13.     }
  14.     public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
  15.     {
  16.         return first.Compose(second, Expression.And);
  17.     }
  18.     public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
  19.     {
  20.         return first.Compose(second, Expression.Or);
  21.     }
  22. }

 

 

 

参考:http://blogs.msdn.com/b/meek/archive/2008/05/02/linq-to-entities-combining-predicates.aspx
完美的动态拼接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();
}

下面是一个我自己使用的例子,仅供参考:

 

[csharp] view plaincopy

  1. using (var db = new SHTrackerDbContext())
  2.             {
  3.                 var predicate = PredicateBuilder.True<Course>();
  4.                 settings = DecorateSettings(settings);
  5.                 Expression<Func<Course, bool>> checkCourse = c => db.Students.Any(s => s.CourseID == c.ID);
  6.                 if (!string.IsNullOrEmpty(settings.Quater_Year))
  7.                 {
  8.                     checkCourse =
  9.                         c => db.Students.Any(s => s.CourseID == c.ID && db.Student2CBOs.Any(
  10.                             s2c => s2c.StudentID == s.ID && s2c.Quater_Year.Equals(settings.Quater_Year)));
  11.                 }
  12.                 if (settings.QuaterYearArray != null)
  13.                 {
  14.                     checkCourse =
  15.                         c => db.Students.Any(s => s.CourseID == c.ID && db.Student2CBOs.Any(
  16.                             s2c =>
  17.                             s2c.StudentID == s.ID && settings.QuaterYearArray.Any(qy => qy.Equals(s2c.Quater_Year))));
  18.                 }
  19.                 if (!string.IsNullOrEmpty(settings.DPU_ID))
  20.                 {
  21.                     checkCourse =
  22.                         checkCourse.And(
  23.                             c => db.Students.Any(s => s.CourseID == c.ID && s.DPU_ID.Equals(settings.DPU_ID)));
  24.                 }
  25.                 predicate = predicate.And(checkCourse);
  26.                 if (settings.IsCheckInstructorName)
  27.                 {
  28.                     predicate = predicate.And(c => c.InstructorName.Equals(settings.InstructorName));
  29.                 }
  30.                 if (!string.IsNullOrEmpty(settings.Term))
  31.                 {
  32.                     predicate = predicate.And(c => c.TermDescription.Equals(settings.Term));
  33.                 }
  34.                 if (settings.TermArray != null)
  35.                 {
  36.                     predicate = predicate.And(c => settings.TermArray.Any(t => t.Equals(c.TermDescription)));
  37.                 }
  38.                 if (settings.CourseType != CourseType.All)
  39.                 {
  40.                     predicate = predicate.And(c => c.Type == (int) settings.CourseType);
  41.                 }
  42.                 var cc =
  43.                     new CourseCollection(
  44.                         db.Courses.AsNoTracking()
  45.                           .Where(predicate)
  46.                           .OrderByDescending(m => m.ID)
  47.                           .Skip((pageIndex - 1)*pageSize)
  48.                           .Take(pageSize)
  49.                           .ToList(),
  50.                         db.Courses.AsNoTracking().Where(predicate).Count())
  51.                         {
  52.                             PageIndex = pageIndex,
  53.                             PageSize = pageSize,
  54.                             Settings = DecorateSettings(settings)
  55.                         };
  56.                 return cc;
  57.             }

本文固定链接: http://www.devba.com/index.php/archives/811.html | 开发吧

报歉!评论已关闭.