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

Question

In my database, I have a table in which cities are stored. Some cities have accents such as Foz do Iguacu.

In my MVC application, I have JSON that returns a list of word-based cities, however few users use accents to search for a city, such as "Foz do Iguacu".

in my database I have "Foz do Igua u u" but users users are looking for "Foz do Igua C u"

How can I search for entries 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

The solution is as follows:

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

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

Answer 2

In the column in the database, you can set the order without emphasis without sorting . Then the request should work. For example, if you set the CidNome column to SQL_LATIN1_GENERAL_CP1_CI_AI , the query will be executed as desired.

Use this SQL script:

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