NeuroAgent

Case-insensitive search in EF Core 9 with SQLite

Solution for case-insensitive search for Russian language in EF Core 9 with SQLite. Learn effective methods for configuring custom collations and full-text search.

#collation-sqlite#ef-core-poisk#entity-framework-core#fts3-fts4#registronezavisimyy-poisk#russkiy-tekst#sqlite-russkiy-yazyk

How to perform case-insensitive matching searches in EF Core 9 on Russian text?

I’m using SQLite.
If the search is in English, everything works fine. But for Russian text, the search is case-sensitive.
What I’ve tried and that doesn’t work:
Result = await _context.Podcasts
//1) .Where(p => p.Title.StartsWith(value))
//2) .Where(p => p.Title.ToLower().StartsWith(value.ToLower()))
//3) .Where(p => EF.Functions.Like(p.Title, “{value}%”))
//4) .Where(p => EF.Functions.Like(p.Title.ToLower(), “{value.ToLower()}%”))
//5) .FromSqlRaw(“SELECT * FROM Podcasts WHERE LOWER(Title) LIKE LOWER({0})”, “{value}%”)
.ToListAsync();

NeuroAgent

Implementing Case-Insensitive Search for Russian Language in EF Core 9 with SQLite

To implement case-insensitive search for Russian language in EF Core 9 with SQLite, specialized approaches are required since standard methods don’t work correctly with Cyrillic characters. The main solutions include configuring custom collations, using FTS3/FTS4 full-text search, or applying transformation functions at the database level.

Contents

SQLite is case-sensitive by default for all characters, including Cyrillic. As noted in research, the standard COLLATE NOCASE function works correctly only for ASCII characters but not for Cyrillic letters.

This means that your attempts to use standard methods:

csharp
// These methods don't work for Russian language
.Where(p => p.Title.ToLower().StartsWith(value.ToLower()))
.Where(p => EF.Functions.Like(p.Title, "{value}%"))

won’t produce the expected results for Russian text. The problem is that SQLite’s LOWER() function only works with ASCII, while Cyrillic characters require special handling.

Method 1: Setting Up Custom Collation

The most effective solution is to create a custom collation that correctly handles Cyrillic characters.

Implementation through DbContext

csharp
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    if (!optionsBuilder.IsConfigured)
    {
        optionsBuilder.UseSqlite($"Data Source=podcasts.db");
        
        // Setting up custom collation for SQLite
        var connection = optionsBuilder.UseSqlite().Options.FindExtension<SqliteOptions>()?.Connection;
        if (connection != null)
        {
            connection.Open();
            try
            {
                connection.CreateCollation("RUSSIAN_NOCASE", (x, y) =>
                {
                    if (x == null && y == null) return 0;
                    if (x == null) return -1;
                    if (y == null) return 1;
                    
                    return string.Compare(x, y, StringComparison.OrdinalIgnoreCase);
                });
            }
            finally
            {
                connection.Close();
            }
        }
    }
}

Usage in Queries

csharp
Result = await _context.Podcasts
    .Where(p => EF.Functions.Collate(p.Title, "RUSSIAN_NOCASE").StartsWith(value))
    .ToListAsync();

This approach allows you to create a custom collation rule that correctly handles the Russian language according to Microsoft.Data.Sqlite recommendations.

Method 2: Using FTS3/FTS4 for Full-Text Search

For full-text search with Russian language support, it’s recommended to use FTS3 or FTS4 virtual tables.

Creating a Virtual Table

csharp
// Creating FTS4 virtual table
using var command = _context.Database.GetDbConnection().CreateCommand();
command.CommandText = @"
    CREATE VIRTUAL TABLE IF NOT EXISTS Podcasts_fts 
    USING fts4(Title, content='Podcasts', tokenize='unicode61');
";
command.ExecuteNonQuery();

// Populating the table with data
command.CommandText = @"
    INSERT INTO Podcasts_fts(rowid, Title) 
    SELECT rowid, Title FROM Podcasts WHERE rowid NOT IN (SELECT rowid FROM Podcasts_fts);
";
command.ExecuteNonQuery();

Searching Through FTS

csharp
Result = await _context.Podcasts
    .FromSqlRaw("SELECT p.* FROM Podcasts p JOIN Podcasts_fts f ON p.rowid = f.rowid WHERE f.Title MATCH {0}", value)
    .ToListAsync();

This method is particularly effective for complex search operations, as noted in Stack Overflow discussions.

Method 3: Global Collation Configuration in EF Core

You can configure a global collation for all string properties in your model.

Configuration in OnModelCreating

csharp
protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder.Properties<string>().UseCollation("NOCASE");
}

However, as noted in GitHub issue, this approach may not work correctly for all cases, especially for LIKE operations. For Russian language, additional configuration will be required.

Method 4: Client-Side Filtering

For small datasets, you can use filtering on the application side.

csharp
Result = await _context.Podcasts
    .AsNoTracking()
    .ToListAsync();

// Filtering in memory
Result = Result.Where(p => 
    CultureInfo.CurrentCulture.CompareInfo.IndexOf(
        p.Title, 
        value, 
        CompareOptions.IgnoreCase | CompareOptions.IgnoreNonSpace
    ) >= 0
).ToList();

This method is simple to implement but inefficient for large data volumes since all data is loaded into memory.

Implementation Examples

Combined Approach with LINQ

csharp
public async Task<List<Podcast>> SearchPodcastsAsync(string value)
{
    // First, perform case-sensitive search through the database
    var dbResults = await _context.Podcasts
        .Where(p => p.Title.Contains(value))
        .Take(1000) // Limit to prevent loading all data
        .ToListAsync();
    
    // Then filter on the application side without case sensitivity
    return dbResults.Where(p => 
        CultureInfo.CurrentCulture.CompareInfo.IndexOf(
            p.Title, 
            value, 
            CompareOptions.IgnoreCase | CompareOptions.IgnoreNonSpace
        ) >= 0
    ).ToList();
}

Using EF.Functions.Collate

csharp
Result = await _context.Podcasts
    .Where(p => EF.Functions.Collate(p.Title, "NOCASE").StartsWith(value))
    .ToListAsync();

Example with Custom Collation

csharp
// Configuration in DbContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Podcast>()
        .Property(p => p.Title)
        .HasCollation("RUSSIAN_NOCASE");
}

// Usage in queries
Result = await _context.Podcasts
    .Where(p => p.Title.StartsWith(value))
    .ToListAsync();

Recommendations for Choosing an Approach

Approach Advantages Disadvantages Recommendations
Custom Collation High performance, built-in EF Core support Requires additional configuration Best choice for most cases
FTS3/FTS4 Excellent search performance, support for complex queries Requires separate table, more complex setup Ideal for catalogs and large data volumes
Global Collation Simple configuration May not work for all operations Suitable for simple cases
Client-Side Filtering Simple implementation Low performance on large data Only for small datasets

For most applications using SQLite and Russian language, custom collation as described in Method 1 is recommended. This provides an optimal balance between performance and implementation simplicity.

Conclusion

  1. Main issue - standard EF Core and SQLite methods don’t support correct case-insensitive search for Russian language

  2. Most effective solution - create a custom RUSSIAN_NOCASE collation through CreateCollation in OnConfiguring

  3. Alternative approaches include using FTS3/FTS4 for full-text search or combining database and client-side filtering

  4. For optimal performance it’s recommended to configure collation at the database level rather than loading all data into memory

  5. Testing should always be conducted with real Russian language data to verify the correctness of the chosen approach

For additional information, always consult the official documentation for Microsoft.Data.Sqlite and EF Core Collations.

Sources

  1. Collations and case sensitivity - EF Core | Microsoft Learn
  2. SQLite case insensitive search for Russian characters - Stack Overflow
  3. Another implementation of case-insensitive search for Cyrillic characters in SQLite - Sudo Null
  4. 5 ways to implement case-insensitive search in SQLite with full Unicode support - ShallowDepth
  5. Collation - Microsoft.Data.Sqlite | Microsoft Learn
  6. Setting global collation for SQLite doesn’t set collation for individual columns - GitHub
  7. UTF8 case insensitive like operator in sqlite - GitHub
  8. Entity Framework core - Contains is case sensitive or case insensitive? - Stack Overflow
  9. Entity Framework Core. SQLite — COLLATE NOCASE - Medium
  10. EF Core Advanced Topics - Collations and Case Sensitivity - RiT Tutorial
How to configure custom collation for all string properties in EF Core?What is the difference between FTS3 and FTS4 for full-text search in SQLite?How to optimize performance of case-insensitive search for large data volumes?Why doesn't the standard ToLower() method work for Russian language in SQLite?How to properly integrate FTS tables with the main model in EF Core?What are alternative solutions for case-insensitive search besides collations?
Ask NeuroAgent