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() });
    }

Mclain

Answer 1

The solution is the following:

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

Where LATIN1_GENERAL means English (US), CI means Case-Insensitive and AI means Accent-Insensitive.

Mclain

Answer 2

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

Barrettbarrette

Is there a way for doing this without change the column? – Mclain

Well I don't think so actually..at the end all the Entity Framework does is translating your query to SQL..the real comparison has to do the database..if you would use some hack, it must lead to loosing the performance. So why you cant change the column? – Barrettbarrette

@PetrAdam is right. This is solution to use. BTW you can configure Collation for the whole database, or in for your whole database server. The latter enable that for every database that will be created. – Ezra

@Mclain you can check this => #12055430 – Ezra

My column is already set to COLLATE SQL_Latin1_General_CP1_CS_AS, and that still didn't work. – Mclain