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().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.