Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LINQ expression is not being translated to SQL

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.

like image 507
MaLiN2223 Avatar asked Oct 26 '17 10:10

MaLiN2223


1 Answers

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.

like image 59
Magnus Avatar answered Nov 09 '22 23:11

Magnus