How to replace multiple characters with one character in linq c#-CodePudding

We are trying to replace multiple character in property(string) that we query from database.

var list = _context.Users.Where(t => t.Enable).AsQueryable();
list = list.Where(t => t.Name.ToLower().Contains(searchValue));

Property Name should be without characters (.,-'). We have tryed:

list = list.Where(t => t.Name.ToLower().Replace(".","").Replace(",","").Replace("-","").Contains(searchValue));

and it works like this, but we don't wanna use replace multiple times.

Is there any other ways that works with IQuerable. Thanks.

CodePudding user response：

We have decided to do it in database,SQL, creating View like this:

CREATE OR ALTER VIEW Users_View 
AS
SELECT Id,CreationDate, UserName =  REPLACE(TRANSLATE(Users.UserName, '_'',.-', '#####'), '#', '')
FROM Users;

and than we just do query on view, like this UserName is already without special characters.

CodePudding user response：

As in the comment of "aca", I think that in these cases it's better the use of a method instead a LINQ forced solution. I use a bit different extension method:

public static class StringExtends
{
    public static string RemoveChars(this string text, params char[] chars)
    {
        var invalidChars = new HashSet<char>();
        foreach (var item in chars)
        {
            invalidChars.Add(item);
        }

        var builder = new StringBuilder();
        foreach (var letter in text)
        {
            if (!invalidChars.Contains(letter))
            {
                builder.Append(letter);
            }
        }

        return builder.ToString();
    }
}

My method allow you select the chars to remove. It must fill the hash but also do only one iteration in the string, instead of a multiple replacement.

If you want optimize a bit more, you can create other method only for your chars and fill invalidChars only once (must be a static variable).

With this method, you can solve your problem in this way:

list = list.Where(t => t.Name.RemoveChars('.', ',', '-').Contains(searchValue));

CodePudding user response：

Well, the first thing you want to do is add a column to your table called "CanonicalName"

Then your query becomes just:

var list = (from t in _context.Users
           where t.Enable && t.CanonicalName.Contains(searchValue)
           select t).ToList();

Now, you need to populate CanonicalName. Since you only have to do this once ever for each record, it doesn't have to be that efficient, but here goes:

public string Canonicalize(string str)
{
 var sb = new StringBuilder(str.Length);
 foreach(var c in str)
 {
     if (c == '.' || 
         c == ',' ||
         c == '-')    // you may wish to add others
             continue;
     c = Char.ToLower(c);
     sb.Append(c);
 }
return sb.ToString();
}

UPDATE: Since people need everything spelled out...

 foreach(var u in _context.Users)
        u.CanonicalName = Canonicalize(u.Name);
_context.SaveChanges();