Geek Answers Handbook

C # lambda IN clause error

I'm having trouble creating an IN clause using C # and lambdas.

I have the following GetUserList(string filtersByRoles) method

The variable string filtersByRoles can contain a comma-separated value, such as: "1,2" or "1,2,3" or "1,3,4", etc ... each number represents a unique number of Roles (in other words, RoleId).

Then I have the following request in C #:

 var query = _userRepository.GetUserList();

Which returns an IQueryable<User> , where User is the table from my EntityFramework.

As soon as I check if the filtersByRoles parameter is filtersByRoles empty or empty, I need to make an IN clause, for example:

 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 in RUNTIME it crashes with the following error message:

LINQ to Entities does not recognize the method 'Boolean Contains [Int32] (System.Collections.Generic.IEnumerable`1 [System.Int32], Int32)', and this method cannot be translated into the expression store.

I managed to find a workaround, but it involves calling the .ToList() method, which, I believe, extracts all the data from my database, and then adds the Where () clause. But won't this defeat the goal or create some performance issues?

This is what I did:

 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 a .ToList() call and not retrieve all the data.

Is there any other way to achieve this?

EDIT: I am using Entity Framework 1.0 and .NET Framework 3.5

Thank you sincerely

Vince

+4
source share
3 answers

Here are my 2 cents:

Perhaps Dynamic LinQ can 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 can create a 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 using StringBuilder instead of + concat, but for the purposes of this answer it doesn't matter)

and then

 query = query.Where(sWhereClause);

I have not tried this, although it sounds fair to solve your problem. Although this is similar to SQL injection ... Well, improvements can be made.

EDIT: As a second thought, I manage to come up with this new idea:

 string filterByRoles = "1,2,3"; query = query.Where(new Func<User, bool>(u => { return filterByRoles.Contains(u.RoleId.ToString()); })).AsQueryable();

Thus, you can add any code that you want in the Func {...} if it returns a boolean value (I assumed that your TInput was a "User" class, of course, change it to use one that suits your needs )

Hope this helps!

+2
source

Based on some of your answers, I manage to pull up something like this:

 int[] myArray = filtersByRoles.Split(',').Select(x => int.Parse(x)).ToArray(); int count = myArray.Count(); int role1; int role2; int role3; int role4; switch (myArray.Length) { case 1: role1 = myArray[0]; query = query.Where(u => u.RoleId.Equals(role1)); break; case 2: role1 = myArray[0]; role2 = myArray[1]; query = query.Where(u => u.RoleId.Equals(role1) || u.RoleId.Equals(role2)); break; case 3: role1 = myArray[0]; role2 = myArray[1]; role3 = myArray[2]; query = query.Where(u => u.RoleId.Equals(role1) || u.RoleId.Equals(role2) || u.RoleId.Equals(role3)); break; case 4: role1 = myArray[0]; role2 = myArray[1]; role3 = myArray[2]; role4 = myArray[3]; query = query.Where(u => u.RoleId.Equals(role1) || u.RoleId.Equals(role2) || u.RoleId.Equals(role3) || u.RoleId.Equals(role4)); break; }

When you try directly with myArray [xxx]:

 query = query.Where(u => u.RoleId.Equals(myArray[0]));

I was getting this:

A LINQ node value of type 'ArrayIndex' is not supported in LINQ to Entities.

Therefore, creating 4 (integer) variables!

Now it works, but some optimization may be required ...

thanks

0
source

More articles:


All Articles