Ignoring accents in SQL Server using LINQ to SQL

Question

How can I ignore accents (like ´, `, ~) in queries made to a SQL Server database using LINQ to SQL?

UPDATE:

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:

SQL_Latin1_General_CP1_CI_AS

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

Solution 1

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.

Solution 2

See the following answer:

LINQ Where Ignore Accentuation and Case

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).

Solution 3

LINQ to SQL doesn't have any specific functionality for setting the collation used for a query and so it will always use the database default.

Solution 4

It seems that there is a way to ignore the collation differences in Linq to SQL by using t-sql functions:

CREATE FUNCTION [dbo].[func_ConcatWithoutCollation]
(
    @param1 varchar(2000),
    @param2 varchar(2000)
)
RETURNS varchar(4000)
AS
BEGIN
    IF (@param1 IS NULL) SET @param1 = ''
    IF (@param2 IS NULL) SET @param2 = ''
    RETURN @param1 COLLATE Latin1_General_CS_AS + @param2 COLLATE Latin1_General_CS_AS
END

to get this function in linq to sql, there is a switch for SqlMetal: /functions. Example:

"%ProgramFiles%\Microsoft SDKs\Windows\v7.0A\Bin\SqlMetal.exe" /server:. /database:NameOfDatabase /pluralize /code:ContextGenerated.cs /sprocs /views /functions

Use this function in Linq to sql like this:

from s in context.Services
where context.Func_ConcatWithoutCollation(s.Description, s.Email) == "whatever"
select s

It helped me, maybe somebody finds this useful too.

View more solutions

Comments

[David Lay]

Thanks, I was just to apply regex replace. That would have been terrible!

[TravisO]

The purpose of tags isn't to just make anything up, the subject is linq, so just use 'linq', don't create 'sqltolinq' because nobody else will ever use such a random tag again, so it's a waste.

[Farinha]

Hhmm, I didn't just create a new tag, I used 'linqtosql' that has been used in about 290 questions...

[Alicia]

Here's a sample, I probably picked this up from one of my web searches but can't remember the URL. SELECT * FROM nametable WHERE name = 'abbee' COLLATE Latin1_General_CI_AI -- Case-Insensitive, Accent-Insensitive comparison AND <anotherColumn> LIKE '%Accent%' COLLATE Latin1_General_CI_AS -- Case-Insensitive, Accent-Sensitive comparison ORDER BY <yetAnotherColumn> DESC COLLATE Latin1_General_CS_AS -- Case-Sensitive, Accent-Sensitive sort order