Ignoring accents in SQL Server using LINQ to SQL
How can I ignore accents (like ´, `, ~) in queries made to a SQL Server database using LINQ to SQL?
Still haven't figured out how to do it in LINQ (or even if it's possible) but I managed to change the database to solve this issue. Just had to change the collation on the fields I wanted to search on. The collation I had was:
The CI stans for "Case Insensitive" and AS for "Accent Sensitive". Just had to change the AS to AI to make it "Accent Insensitive". The SQL statement is this:
ALTER TABLE table_name ALTER COLUMN column_name column_type COLLATE collation_type
In SQL queries (Sql Server 2000+, as I recall), you do this by doing something like select MyString, MyId from MyTable where MyString collate Latin1_General_CI_AI ='aaaa'.
I'm not sure if this is possible in Linq, but someone more cozy with Linq can probably translate.
If you are ok with sorting and select/where queries ALWAYS ignoring accents, you can alter the table to specify the same collation on the field(s) with which you are concerned.
See the following answer:
Basically you need to alter the field type in SQL Server, e.g.
ALTER TABLE People ALTER COLUMN Name [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AI
There does not seem to be a way to do this using LINQ, apart from calling a custom method to remove diacritics (which would not be performant).