Compiled Linq & String.Contains

Question

i'm using linq-to-sql and i'm use complied linq for better performance.

I have a users table with a INT field called "LookingFor" that can have the following values.1,2,3,12,123,13,23.

I wrote a query to return the users based on the "lookingFor" column i want to return all users that contains the "lookingFor" value (not only those equal to it).

In example if user.LookingFor = 12 , and query paramter is 1 this user should be selected.

private static Func<NeDataContext, int, IQueryable<int>>
      MainSearchQuery = CompiledQuery.Compile((NeDataContext db, int lookingFor) =>
         (from u in db.Users
          where (lookingFor == -1 ? true : u.LookingFor.ToString().Contains(lookingFor)                         
    select u.username);

This WORKS on non complied linq but throws error when using complied. How do i fix it to work using complied linq?

I get this error:

Only arguments that can be evaluated on the client are supported for the String.Contains method.

Answer 1

You're performing operations on the lookingFor argument that cannot be translated to SQL. I'm actually surprised that this works when not compiling the query.

If it's possible, I suggest that you change your database to make this query easier. Create a table to store the LookingFor property for users. Or you can change the datatype of lookingFor to a string, which has less of an impact on the database design.