where linq with trim replace character and ignore case

Question

I'm using EF and i would like to make a search on field Name, this search must ignore character - and space and the case

on my table i have

id Name
1  Jean -philippe

when i'm make the search on my resqust sometime i have jeanphilippe, some times jean philippe

I need to match this with the record on db like:

await repository.FindAsync(m=>m.Name.ToLower().Replace("-", string.Empty).Trim()==request.Name.ToLower().Replace("-", string.Empty).Trim())

 public async Task<User> FindAsync(Expression<Func<User, bool>> match)
        {
            return await _databaseContext.user.FirstOrDefaultAsync(match).ConfigureAwait(false);
        }

but i have EF error

The LINQ expression 'DbSet<user> where (.....) could not be translated.

How can i resolve this please?

Does this answer your question? [The LINQ expression could not be translated and will be evaluated locally](https://stackoverflow.com/questions/57872910/the-linq-expression-could-not-be-translated-and-will-be-evaluated-locally) — Abdelkrim, Oct 19 '21 at 11:53
Are you sure that "request.Name" has a value? Also another issue is that "Trim" only removes spaces from the beginning and the end of a string, so you will need to do a "replace" on those as well, that doesn't explain the error message though. — Culme, Oct 19 '21 at 11:53
You probably don't need the `ToLower` because SQL Server defaults to a collation that is case insensitive. For the rest you need to determine the correct functions to use that will translate based on your version of EF. — juharr, Oct 19 '21 at 12:18
All used functions a listed as [supported](https://learn.microsoft.com/en-us/ef/core/providers/sql-server/functions#string-functions) by EF For SQL Server. Try moving `request.Name.ToLower().Replace("-", string.Empty).Trim()` to outside variable and "play" with the `m.Name.ToLower().Replace("-", string.Empty).Trim()` part, to see what fails to be translated (remove some of function calls, change `string.Empty` to `""` and so on). — Guru Stron, Oct 19 '21 at 12:25
Which version of EF Core do you use? in EF Core 5 your query works. — Svyatoslav Danyliv, Oct 19 '21 at 12:47

score 4 · Accepted Answer · answered Oct 19 '21 at 12:30

Everything will be done on the server side, so if the search is case-sensitive or not, will be decided by the database collation.

Maybe to ignore such things you should split up your search term be all whitespaces and send a like statement for each one and AND all of them. Maybe something like this sketch:

var name = "Foo -Bar";
var cleanedName = name
    //.Replace(".", string.Empty)  // Maybe other characters to remove before search
    .Replace("-", string.Empty);

var elements = cleanedName.Split();
IQueryable<Foo> query = respository.Foo;

foreach (var element in elements)
{
    query = query.Where(foo => EF.Functions.Like(foo.Name, $"%{element}%"));
}

var results = await query.ToListAsync();

Be aware, that this is only a sketch. Especially simply using the element directly can produce false outcomes, if the search term entered by the user contains characters, that are interpreted by the LIKE function of the database like %, _, [ or ] (probably more).

Also be aware that doing a bunch of LIKE statements on a table maybe containing a lot of rows containing maybe a lot of text could lead to a HUGE processing time for the SQL database. Don't forget to measure the performance and maybe check for some full text indices on the server side to improve performance if needed.

where linq with trim replace character and ignore case

1 Answers1