LINQ expression is not being translated to SQL

.net-core c# entity-framework entity-framework-core linq

Question

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.

1
5
10/26/2017 11:43:31 AM

Accepted Answer

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.

7
10/26/2017 11:49:19 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow