CODEWITHSUNDEEP
c#entity-frameworkdiacriticsaccent-insensitive
Dan
Dan

Reputation: 1575

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

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

Upvotes: 3

Views: 1630

Answers (2)

Dan
Dan

Reputation: 1575

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.

Upvotes: 0

Petr Adam
Petr Adam

Reputation: 1437

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

Upvotes: 1

Related Questions