LINQ can't use string.contains?
Try .IndexOf
. It is not LINQ that can't do Contains
, it's LINQ to Entities and LINQ to SQL that can't.
string queryString = "Marco";
utenti = db.User.Where(p =>
queryString.IndexOf(p.Nickname, StringComparison.OrdinalIgnoreCase) >= 0 ||
queryString.IndexOf(p.Nome, StringComparison.OrdinalIgnoreCase) >= 0 ||
queryString.IndexOf(p.Cognom, StringComparison.OrdinalIgnoreCasee) >= 0)
.ToList();
Why?
LINQ uses deferred execution. This means it waits until you want to iterate over your query results before it does anything. There are 3 main types of LINQ:
- LINQ to Objects - when your
IEnumerable
is already on the heap. - LINQ to Entities - when you want to query a database using Entity Framework.
- LINQ to SQL - when you want to query a database using LINQ to SQL.
Deferred execution in the context of the second 2 means that your query is not executed on the database until you enumerate the results in a foreach
block, or invoke an enumeration method like .ToList
, .ToArray
, etc. Until then, your query is just stored as expression trees in memory.
Your query would work just peachy if db.User
was a collection in memory. However when the data is in a database, LINQ to Entities (or LINQ to SQL) must translate your expression trees to what it calls a "store expression" -- which is just fancy talk for "convert my LINQ expressions to SQL".
Now imagine you had a custom C# algorithm you wanted to use for your query, and you did something like this:
var result = db.User.Where(x => MyCustomMethod(x));
There is no way today that LINQ to Entities can convert your C# code into a SQL query (store expression). It is the same with a lot of other C# methods you rely on daily. It also does not support .ToLower
, .ToUpper
, .StartsWith
, .EndsWith
, etc. There is a limited number of C# methods that can be converted to store expressions, and .IndexOf
just happens to be one of them.
However keep in mind that it is only the string object's Contains
method that we are talking about here that is not supported for store expressions. LINQ to Entities does support .Contains
on IEnumerable
s. The following is valid and will work with LINQ to Entities (not sure about LINQ to SQL):
var idsIWantToFind = new[] { 1, 2, 3 };
var users = db.Where(x => idsIWantToFind.Contains(x.UserId));
The above is the equivalent of doing a SQL WHERE UserId IN (1, 2, 3)
predicate.
Related videos on Youtube
markzzz
Updated on June 17, 2022Comments
-
markzzz almost 2 years
This is my code:
string queryString = "Marco".ToLower(); utenti = db.User.Where(p => queryString.Contains(p.Nickname.ToLower()) || queryString.Contains(p.Nome.ToLower()) || queryString.Contains(p.Cognome.ToLower())).ToList();
but I get:
Only arguments that can be evaluated on the client are supported for the String.Contains method.
Why? Can't I use
.Contains()
? -
Mike Perrenoud over 10 yearsTo expand on this, and help maybe clarify the issue, the code inside the
Where
needs to be converted to real SQL. Not every function can do that. Therefore, you have a subset of things that can be converted (e.g.IndexOf
) and then you also have the entity database extensions that can do some stuff like date comparisons and such. -
danludwig over 10 yearsTo expand on @neoistheone's comment, think of trying to do something like
.Where(x => MyCustomMethod(x))
. The database does not know how to invoke that custom method in SQL. It's the same with other methods like.StartsWith
,.Contains
, etc. Some methods are supported when they can be translated to a store expresion (SQL), the rest are not. -
Mike Perrenoud over 10 yearsI think we've got it all now. :D
-
Sriram Sakthivel over 10 yearsI'd suggest you to add both these comments to answer as they are more worthy that what is in answer now(which makes people to understand why it won't work) :)
-
Raphaël Althaus over 10 yearsI've never faced any error while using
ToLower
,ToUpper
,StartsWith
,EndsWith
,Contains
(on string) inlinq to entities
... So either your explanation is valid forlinq to sql
(I've no experience with it) only, or this is another problem. -
andres.chort over 10 yearsMaybe it is related to the database provider. I can confirm Contains and related methods of String work well with EF5 against SQL Server.