How to search string using Entity Framework with .Contains and with accent-insensitive

Question

In my database, I have a table that stores cities. Some cities have accents like "Foz do Iguaçu".

In my MVC application, I have a JSON that return a list of cities based in a word, however, few users aren't using accents to search for the city, for example "Foz do Iguacu".

in my database I have "Foz do IguaÇu" but users users searches for "Foz do IguaCu"

How can I search records in my table, ignoring accents?

Here is my code:

    using (ServiciliEntities db = new ServiciliEntities())
    {
        List<Cidades> lCidades = db.Cidades.Where(c => c.CidNome.ToLower().Contains(q.Trim().ToLower())).OrderBy(c => c.CidNome).Take(10).ToList();
        ArrayList lNomes = new ArrayList();
        foreach (Cidades city in lCidades)
            lNomes.Add(new {city.CidNome, city.Estados.EstNome});

        return Json(new { data = lNomes.ToArray() });
    }

Answer 1

You can set accent-insensitive collation order on the column in database. The query then should work. For example, if you set SQL_LATIN1_GENERAL_CP1_CI_AI to the CidNome column, query will perform as wanted.

Use this SQL script:

ALTER TABLE dbo.YourTableName
ALTER COLUMN YourColumnName NVARCHAR (100) COLLATE SQL_Latin1_General_CP1_CS_AS NULL