I have a database table that contains names with accented characters. Like
Ã¤ and so on.
I need to get all records using EF4 from a table that contains some substring regardless of accents.
So the following code:
myEntities.Items.Where(i => i.Name.Contains("a"));
should return all items with a name containing
a, but also all items containing
Ã¢ and so on. Is this possible?
If you set an accent-insensitive collation order on the Name column then the queries should work as required.
Setting an accent-insensitive collation will fix the problem.
You can change the collation for a column in SQL Server and Azure database with the next query.
ALTER TABLE TableName ALTER COLUMN ColumnName NVARCHAR (100) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI NOT NULL
SQL_LATIN1_GENERAL_CP1_CI_AI is the collation where
LATIN1_GENERAL is English (United States),
CP1 is code page 1252,
CI is case-insensitive, and
AI is accent-insensitive.