c# - 使用 Entity Framework 的 sql IN 子句的动态 linq 查询表达式树

Question

我想使用代码优先方法在 EF 6.0 中为 sql IN 子句创建动态 linq 表达式。请注意，我是表达式的新手。我想要实现的是

select * from Courses where CourseId in (1, 2, 3, 4)
//CourseId is integer

正常的 linq 查询看起来像这样。但是我想动态查询它

string[] ids = new string[]{"1", "2", "3", "4"};
var courselist = DBEntities.Courses.Where(c => ids.Contains(SqlFunctions.StringConvert((decimal?)c.CourseId)))

动态表达有两种方式。
1) 一种方法是遍历 id 并制作表达式
下面的代码将在调试 View 中创建以下表达式

{f => ((StringConvert(Convert(f.CourseId)).Equals("23") Or StringConvert(Convert(f.CourseId)).Equals("2")) Or StringConvert(Convert(f.CourseId)).Equals("1"))}

动态表达式是

var param = Expression.Parameters(typeof(Course), "f")
MemberExpression property = Expression.PropertyOrField(param, "CourseId");                   
MethodInfo mi = null;
MethodCallExpression mce = null;
if (property.Type == typeof(int))
{
   var castProperty = Expression.Convert(property, typeof(double?));
   var t = Expression.Parameter(typeof(SqlFunctions), "SqlFunctions");
   mi = typeof(SqlFunctions).GetMethod("StringConvert", new Type[] { typeof(double?) });   
   mce = Expression.Call(null,mi, castProperty);
}
mi = typeof(string).GetMethod("Equals", new Type[]{ typeof(string)});            
BinaryExpression bex = null;
if (values.Length <= 1)
{
   return Expression.Lambda<Func<T, bool>>(Expression.Call(mce, mi,     Expression.Constant(values[0]), param));
}
//var exp1 = Expression.Call(mce, mi, Expression.Constant(values[0]));
for (int i = 0; i < values.Length; i++)
{               
   if (bex == null)
   {
      bex = Expression.Or(Expression.Call(mce, mi, Expression.Constant(values[i])), Expression.Call(mce, mi, Expression.Constant(values[i + 1])));
      i++;
   }
   else             
      bex = Expression.Or(bex, Expression.Call(mce, mi, Expression.Constant(values[i])));

}//End of for loop
return Expression.Lambda<Func<T, bool>>(bex, param);

2) 我尝试的第二种方式(调试 View )

{f => val.Contains("23")}//val 是上面值的参数
我试过的上面的动态表达式是

var param = Expression.Parameters(typeof(Course), "f")
MemberExpression property = Expression.PropertyOrField(param, "CourseId"); 
var micontain = typeof(Enumerable).GetMethods().Where(m => m.Name == "Contains" && m.GetParameters().Length == 2).Single().MakeGenericMethod(typeof(string));
var mc = Expression.Call(micontain, Expression.Parameter(values.GetType(), "val"), Expression.Constant("2"));//NOTE: I haven't use CourseId for now as i am getting conversion error
return Expression.Lambda<Func<T, bool>>(mc, param);

我得到以下错误

• LINQ to Entities 无法识别“System.String StringConvert(System.Nullable`1[System.Double])”方法，而此当我使用第一种方法。我知道我不能将 ToString 与 EF 一起使用，这就是我使用 SqlFunctions 的原因，但它对我不起作用。
• 使用第二种方法，参数“val”未绑定(bind)到指定的 LINQ to Entities 查询表达式

我从过去 4 天开始尝试这个。我用谷歌搜索但没有找到任何合适的解决方案。请帮助我。

Answer 1

经过一番努力，我找到了问题的解决方案。我想实现这个sql查询

select * from Courses where CourseId in (1, 2, 3, 4)

使用 Linq to Entities，但我想将 (1,2,3,4) 列表动态传递给 Linq 查询。为此，我创建了一个扩展类。

public static class LinqExtensions
{
   public static Expression<Func<T, bool>> False<T>() { return f => false; } 
   public static Expression<Func<T, bool>> In<T, TValue>(this Expression<Func<T, bool>> predicate,string propertyName, List<TValue> values)
   {            
           var param = predicate.Parameters.Single();
           MemberExpression property = Expression.PropertyOrField(param, propertyName);            
           var micontain = typeof(List<TValue>).GetMethod("Contains");            
           var mc = Expression.Call(Expression.Constant(values), micontain, property);
           return Expression.Lambda<Func<T, bool>>(mc, param);
   }
}

LinqExtensions 的使用

var pred = LinqExtensions.False<Course>(); //You can chain In function like  LinqExtensions.False<Course>().In<Course, int>("CourseId", inList);
var inList= new List<int>(){1, 2, 3}; //Keep in mind the list must be of same type of the Property that will be compared with. In my case CourseId is integer so the in List have integer values
pred =pred.In<Course, int>("CourseId", inList); //TValue is int. As CourseId is of type int.
var data = MyEntities.Courses.Where(pred);

希望对大家有用