IN clause mistake in C# lambda

c# entity-framework lambda linq

Question

Using C# and lambdas, I'm having trouble constructing an IN clause.

I use the next technique.GetUserList(string filtersByRoles)

the elementstring filtersByRoles may store a value separated by commas, such as "oe1,2" or "1,2,3" or "1,3,4", etc. Each of these numbers corresponds to a distinct role number (in other words, RoleId).

Then I have the following C# lambda expression:

var query = _userRepository.GetUserList();

which yields anIQueryable<User> where User from my EntityFramework, is a table.

then I check to see whether thefiltersByRoles if argument is not empty or null, I must create anIN a clause like:

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 code above compiles, but fails with the following error notice at RUNTIME:

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 managed to come up with a fix, however it necessitates calling the.ToList() I think there is a technique that inserts a Where() clause after retrieving all the data from my database. But wouldn't it undermine the goal or lead to some performance problems?

What I did was as follows:

if (!string.IsNullOrEmpty(filtersByRoles))
{
      string[] myArray = filtersByRoles.Split(',');
      query = query.ToList().Where(u => myArray.Contains(u.RoleId.ToString())).AsQueryable();
}

I'd rather have not create the.ToList() Call instead of downloading all the info.

Is there an other way to do this?

EDIT: Entity Framework 1.0 and.NET Framework 3.5 are what I'm utilizing.

Thanks Sincerely

Vince

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

Accepted Answer

Now for my two cents:

Perhaps the Dynamic LinQ will assist in resolving your issue: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

You might construct your where clause as a string, like this:

string sWhereClause = "1 = 1";
foreach(string rId in filtersByRoles.Split(','))
   sWhereClause += " OR RoleId = " + rId;

(I would advise using StringBuilder rather than +concat, but it is irrelevant for the purposes of this response.)

after that

query = query.Where(sWhereClause);

Although I haven't tried it, it seems reasonable for fixing your issue. Although it seems to be a SQL injection... Well, things can be made better.

EDIT: I manage to come up with this fresh concept after more reflection:

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

This allows you to add whatever code you choose to the Func... Delegate, provided the function 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 is useful!

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