IN clause mistake in C# lambda

c# entity-framework lambda linq

Question

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

1
4
7/29/2011 3:16:09 PM

Accepted Answer

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!

2
8/1/2011 6:22:10 PM



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow