CODEWITHSUNDEEP
sql-serverlinqlinq-to-sql
Farinha
Farinha

Reputation: 18091

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?

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

Upvotes: 11

Views: 12372

Answers (5)

João Neto
João Neto

Reputation: 79

A solution could be create an SQL Function to remove the diacritics, by applying to the input string the collation SQL_Latin1_General_CP1253_CI_AI, like so:

CREATE FUNCTION [dbo].[RemoveDiacritics] (
@input varchar(max)
)   RETURNS varchar(max)

AS BEGIN
DECLARE @result VARCHAR(max);

select @result = @input collate SQL_Latin1_General_CP1253_CI_AI

return @result
END

Then you could add it in the DB context (in this case ApplicationDbContext) by mapping it with the attribute DbFunction:

public class ApplicationDbContext : IdentityDbContext<CustomIdentityUser>
    {
        [DbFunction("RemoveDiacritics", "dbo")]
        public static string RemoveDiacritics(string input)
        {
            throw new NotImplementedException("This method can only be used with LINQ.");
        }

        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }
}

And finally use it in LINQ query, for example (linq-to-entities):

var query = await db.Users.Where(a => ApplicationDbContext.RemoveDiacritics(a.Name).Contains(ApplicationDbContext.RemoveDiacritics(filter))).tolListAsync();

Upvotes: 0

Jan
Jan

Reputation: 11

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.

Upvotes: 1

Dunc
Dunc

Reputation: 18922

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

Upvotes: 2

DamienG
DamienG

Reputation: 6665

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.

Upvotes: 1

JasonTrue
JasonTrue

Reputation: 19609

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.

Upvotes: 3

Related Questions