EFCore - Collate function

Search in SQL server query is generally case insensitive (by default or based on database level collation).

Suppose we have an employees table with a row having first-name column value as "My-First-Name", so if we want to do the case-sensitive search we have to explicitly use the related collate:

In EF 5 (currently in Release Candidate version [RC.2.20475.6]) Collate function got introduced which helps us to use our specific collation based search. 

C# with EF5 code sample:

var employeeCaseSensitiveSearch = _dbContext.Employees
    .Where(x => EF.Functions.Collate(x.FirstName, "Latin1_General_CS_AS") == "my-first-name")
    .FirstOrDefault();

A related database query will be something like this:

T-SQL: Case sensitive search (use specific collation e.g.: Latin1_General_CS_AS)

SELECT * FROM dbo.Employees AS e 
WHERE e.FirstName Collate Latin1_General_CS_AS = 'my-first-name'

Some of the useful CSharp function which gets converted into related SQL command is

• StartsWith
• EndsWith
• Contains