I'm trying to achieve a compiled SQL query from LINQ which will check if query
is substring of any of three columns in database (case insensitive).
I'm using .NET Core 1.1
Query that I come up with is as follows:
users.Select(u => new
{
User = u,
query = u.FirstName.ToLower() + u.LastName.ToLower() + u.Email.ToLower()
}).Where(x => x.query.Contains(query))
But when looking at debug informations I am getting this warning:
The LINQ expression '(([u].FirstName.ToLower() + [u].LastName.ToLower()) + [u].Email.ToLower()).Contains(__query_0)' could not be translated and will be evaluated locally.
Second query that I tried:
users.Where(x => u.FirstName.ToLower().Contains(query) || u.LastName.ToLower().Contains(query) || u.Email.ToLower().Contains(query))
but it gives me exactly the same warning.
Why is is the case? I am looking for something like:
SELECT * FROM USERS WHERE FirstName LIKE query OR LastName LIKE query OR Email LIKE query
UPDATE
I did one more experiment :
users.Where(u =>
u.FirstName.Contains(query) ||
u.LastName.Contains(query) ||
u.Email.Contains(query));
And this also resulted in
The LINQ expression '(([u].FirstName.Contains(__query_0) OrElse [u].LastName.Contains(__query_1)) OrElse [u].Email.Contains(__query_2))' could not be translated and will be evaluated locally.
It is because .ToLower()
and .Contains()
are functions in the string class and cant be translated to SQL by the linq provider.
All queries (unless explicitly specified) will follow the database collation, and if it is CI
it is Case Insensitive and you do not need the .ToLower()
.
As for .Contains()
you need to use entity function Like
.
users.Where(u =>
EF.Functions.Like(u.FirstName, "%" + query + "%") ||
EF.Functions.Like(u.LastName, "%" + query + "%") ||
EF.Functions.Like(u.Email, "%" + query + "%"));
However this seems to be added in EF core 2.0. For 1.1 I dont think there is any way to do it. I would recommend skipping EF and writing plain old SQL directly.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With