Unable to Search for Specific Russian Text in SQL Server ntext Column Using LIKE Operator

Question

I'm currently working on an ASP.NET Core application that uses Entity Framework Core to interact with a SQL Server database. I have a specific issue where I'm unable to search for Cyrillic text in a column of type ntext.

Here's the method I'm using in my repository:

    public async Task<IEnumerable<Course>> GetAll(string searchString)
{
    var courses = await _db.Courses
        .Include(c => c.Subcategory) 
        .ToListAsync();

    if (!string.IsNullOrEmpty(searchString))
    {
        courses = courses
            .Where(c => c.Subcategory.FullDescription.Contains(searchString))
            .ToList();
    }

    return courses;
}

When I pass a Cyrillic string to this method, it returns an empty list even though there are matching records in the database.

I've tried using the LIKE operator in SQL Server with a Cyrillic collation, and it works as expected:

SELECT * FROM Subcategories
WHERE FullDescription COLLATE Cyrillic_General_CI_AI LIKE N'%картинкаТест%';

However, I'm not sure how to translate this SQL query into LINQ for use in my repository method.

I've seen this question about the N prefix in T-SQL, but my issue is specifically about how to perform a similar search using Entity Framework Core in C#.

Any help would be greatly appreciated!

Answer 1

You just need to make one little change. When using Unicode string literals (regardless if the column is using an ancient deprecated type, like ntext, which you should have stopped using around 2005, for a better and more modern type like nvarchar(max)), you need to preface those literals with an N (which stands for "national character" or so).

Repro:

CREATE TABLE dbo.SubCategories
(
  FullDescription ntext COLLATE Cyrillic_General_CS_AS
);

INSERT dbo.SubCategories(FullDescription) VALUES
  (N'no match'), (N'match тест for sure');

Your query:

SELECT FullDescription
  FROM dbo.SubCategories
  WHERE FullDescription LIKE '%тест%';

-- no results

Slight change:

SELECT FullDescription
  FROM dbo.SubCategories
  WHERE FullDescription LIKE N'%тест%';
  ---------------------------^

-- now you get a result