I’m having issues creating an IN clause using C# and lambdas.
I have the following method GetUserList(string filtersByRoles)
The variable string filtersByRoles
can hold a comma-delimited value such as: “1,2†or “1,2,3†or “1,3,4†etc...each number represents the unique number of a Role (in other words, RoleId).
I then have the following C# lambda query:
var query = _userRepository.GetUserList();
Which returns an IQueryable<User>
where User
is a table from my EntityFramework.
Once I verify if the filtersByRoles
parameter is not null-or-empty, I need to make an IN
clause such as:
if (!string.IsNullOrEmpty(filtersByRoles))
{
//Convert *filtersByRoles* to an array of integers
int[] myArray = filtersByRoles.Split(',').Select(x => int.Parse(x)).ToArray();
//Make the IN clause
query = query.Where(u => myArray.Contains(u.RoleId));
}
The above code compiles...but at RUNTIME it fails with the following error message:
LINQ to Entities does not recognize the method 'Boolean Contains[Int32](System.Collections.Generic.IEnumerable`1[System.Int32], Int32)' method, and this method cannot be translated into a store expression.
I’ve manage to find a workaround but it involves making a call to the .ToList()
method which I believe fetches all the data from my database and then, adds a Where() clause.
But wouldn’t that defeat the purpose or create some performance issues?
This is what I’ve done:
if (!string.IsNullOrEmpty(filtersByRoles))
{
string[] myArray = filtersByRoles.Split(',');
query = query.ToList().Where(u => myArray.Contains(u.RoleId.ToString())).AsQueryable();
}
I would prefer not to make the .ToList()
call and avoid fetching all the data.
Is there another way to achieve this?
EDIT: I'm using Entity Framework 1.0 and .NET Framework 3.5
Thanks Sincerely
Vince
Here are my 2 cents:
Maybe the Dynamic LinQ will help solve your problem: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
You could build your Where clause as a string, say something like :
string sWhereClause = "1 = 1";
foreach(string rId in filtersByRoles.Split(','))
sWhereClause += " OR RoleId = " + rId;
(I would suggest to use StringBuilder instead of +concat, but for the purpose of this answer, it doesn't matter)
and then
query = query.Where(sWhereClause);
I haven't tryed it though, but it sounds fair for solving your problem. Even though it looks like SQL injection... Well, improvements can be brought.
EDIT: As a second thought I manage to come with this new idea:
string filterByRoles = "1,2,3";
query = query.Where(new Func<User, bool>(u => {
return filterByRoles.Contains(u.RoleId.ToString());
})).AsQueryable();
This way, you can add whatever code you want in the Func{ ... } delegate, as long as it returns a boolean (I assumed here your TInput was a "User" class, of course change it to use the one corresponding to you needs).
Hope this helps!
This is supported with EF 4 : http://blogs.msdn.com/b/alexj/archive/2009/03/26/tip-8-writing-where-in-style-queries-using-linq-to-entities.aspx