Accent insensitive search using EF6 and SQL Server 2008 R2 - Czech language

Question

How to do accent insensitive search query using SQL Server 2008 R2 and EF 6?

I need to do accent insensitive search on let's say user.name column using the Entity framework 6. I have tried to change the collation on column from default Czech_CI_AS to Czech_CI_AI. But it does not work for some Czech letters with wedges like Č, Ř, Š, Ž because the collation treats them as different letters :

http://collation-charts.org/mssql/mssql.0405.1250.Czech_CI_AI.html

I have found similar question here:

How do I perform an accent insensitive compare in SQL Server for 1250 codepage

But the proposed solution using collation Czech_100_CI_AI does not work either (for those special letters).

I have also found few sources how to do it in plain T-SQL. Like this:

SELECT * 
FROM [dbo].[User] 
WHERE name LIKE '%c%' COLLATE Latin1_General_CI_AI

It works fine. But I do not want to use plain SQL queries. I would like to manage it in an EF way.

Answer 1

I have end up with this solution:

Create view with two columns - one for the search, second for presentation (latin collation will remove some accents from the result).

CREATE VIEW [dbo].[v_UserSearch]
AS
SELECT    
           dbo.[User].name AS FirstName,
           dbo.[User].name COLLATE Latin1_General_CI_AI AS FirstNameCI
FROM       dbo.[User]  

Create DB mapping for the view in EF context.

Use the FirstNameCI column for the search in EF.

if (!string.IsNullOrWhiteSpace(filter.FirstName))
   query = query.Where(x => x.c.FirstNameCI.StartsWith(filter.FirstName));

Use the FirstName column for presentation.