C# lambda IN clause error

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

The error is telling you that the linq optimizer doesn't know how to translate Contains to a corresponding procedure in sql. I've never tried this but could you build up multiple Where's and chain them together? Like .Where( u=> u.RoleId == myArray[0] ).Where( u => u.RoleId == myArray[1]) ... — Brian Dishaw, Jul 29 '11 at 15:42
See http://stackoverflow.com/questions/374267/contains-workaround-using-linq-to-entities — Ian Mercer, Jul 29 '11 at 15:54
Leaving the office now...but will give it a try during the weekend thanks Brian! — Vlince, Jul 29 '11 at 15:55

score 2 · Answer 1 · answered Jul 29 '11 at 15:08

2

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

answered Jul 29 '11 at 15:08

mathieu

30,974
4
64
90

Unfortunately, I'm using EF 1.0 – Vlince Jul 29 '11 at 15:14

Flo. · Accepted Answer · 2011-08-01T18:22:10.260

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!

Leaving the office now...but will give it a try during the weekend thanks Flo! — Vlince, Jul 29 '11 at 15:55

score 0 · Answer 3 · answered Aug 01 '11 at 17:43

Based on some of your replies, I’ve 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 directly trying with the myArray[xxx]:

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

I was getting this:

The LINQ expression node type 'ArrayIndex' is not supported in LINQ to Entities.

Hence the creation of the 4 (integer) variables!

It now works but may need some optimization…

Thanks

C# lambda IN clause error

3 Answers3