Compiled Linq & String.Contains

I'm using Linq-to-SQL and I use compiled Linq for better performance.

I have 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 and I want to return all users that contain the "lookingFor" value (not only those equal to it).

In example if user.LookingFor = 12 , and query paramter is 1, and 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 compiled. How do I fix it using compiled Linq?

I get this error:

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

I'm facing the same problem. I have managed for now just one workaround for this problem. Instead of using

u.LookingFor.ToString().Contains(lookingFor)

I've used

u.LookingFor.ToString().IndexOf(lookingFor) >= 0

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.