Dynamic linq query expression tree for sql IN clause using Entity framework

Question

I want to create a dynamic linq expression for sql IN clause in EF 6.0 with the first code of the code. Please note that I am new to expressions. What I want to achieve

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

A typical linq query looks like this. But I want to dynamically query it

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

There are two ways to make a dynamic expression.
1) one way is to go through identifiers and expressions.
The code below will generate the following expression in debug form

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

Dynamic expression

 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) The second method I tried (viewing debugging)

{f => val.Contains("23")} //val is parameter of values above
The dynamic expression above that I tried is

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

I get the following errors:

• LINQ to Entities does not recognize the 'System.String StringConvert (System.Nullable`1 [System.Double])' method, and this method cannot be translated into the repository expression when I use the first methodology. I know that I can not use ToString with EF, so I used SqlFunctions, but it does not work for me.
• The 'val' parameter was not bound in the specified LINQ to Entities query expression using methodology 2

I have been trying this for the last 4 days. I searched for it, but did not find a suitable solution. Please help me.

Answer 1

After a great struggle, I found a solution to my question. I want to execute this sql query

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

Using Linq to Entities, but I want to dynamically list the (1,2,3,4) list in a linq query. For this purpose, I created an extension class.

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

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

I hope this can be helpful to someone.

Answer 2

Did you see type

 var courselist = DBEntities.Courses.Where(c => ids.Contains(c.CourseId))) 

the above expression will not return the actual list of courses. The request has not yet been completed. It just returns IQuereable. The request is executed when you actually call the .ToList () method on it

So your decision ...

• Create an array of identifiers using for a loop and then just run the following query

• var courselist = DBEntities.Courses.Where(c => ids.Contains(c.CourseId))).ToList()