where linq with trim replace character and ignore case-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

The simple answer is that EF complains it cannot translate your query to SQL (because that's what EF tries to do for any expression you pass to it). If you can write your query in SQL then you can probably find a way to translate that to C# code that will get translated back to equivalent SQL. Or just use a stored procedure.