Sqlite EF Core 5 won't let me do case insensitive searches

Question

So I've tried doing a linq query with ef-core to do a where search, but no matter what I've tried I can't get it to do a case insensitive search.

As in, if I search for "tuesday" it won't fetch any data since everything in the database is saved as "Tuesday".

After searching I've found that you have to tell sqlite through ef core migrations that you want it to be case insensitive with the following code, for each property.

b.Property<string>("DayOfWeek")
    .HasColumnType("TEXT COLLATE NOCASE");

That info is in the ContextModelSnapshot.cs file.

So I did that, deleted the database and did an "Update-Database", a new database was created but nothing changed.

Now I could of course use the .ToLower() in every search, but that will just make a performance hit and increase the possibility for failure if I forget to add that to every where clause.

I'm using the following packages for databas creation, migration and access.

<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.1">
  <PrivateAssets>all</PrivateAssets>
  <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="5.0.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.1">

What am I missing or did I missunderstand the whole concept of case insensitive queries for sqlite?

Hi @Yinqiu, it still doesn't work. Might be that I'm using sqlite and not sqlserver and that command don't work for sqlite database. — Johan Knape, Dec 28 '20 at 09:01
Hi @Johan Knape,you can see this [link](https://stackoverflow.com/questions/973541/how-to-set-sqlite3-to-be-case-insensitive-when-string-comparing) — Yinqiu, Dec 28 '20 at 09:11
None of the answers below work for me. EF core sqlite provider adds a stupid second clause with a substr compare that does an exact match of the starts with term length AFTER the like query. — jjxtra, Jan 07 '23 at 02:13

score 5 · Answer 1 · answered Nov 29 '21 at 11:27

Unfortunately the provided answer didn't work for me. Here is how to iterate over all of the string properties of all of the exposed models to EF-Core and set their default collation to nocase:

public static void SetCaseInsensitiveSearchesForSQLite(this ModelBuilder modelBuilder)
{
    if (modelBuilder == null)
    {
        throw new ArgumentNullException(nameof(modelBuilder));
    }

    modelBuilder.UseCollation("NOCASE");

    foreach (var property in modelBuilder.Model.GetEntityTypes()
                                            .SelectMany(t => t.GetProperties())
                                            .Where(p => p.ClrType == typeof(string)))
    {
        property.SetCollation("NOCASE");
    }
}

And then call it this way:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    if (modelBuilder == null)
    {
        throw new ArgumentNullException(nameof(modelBuilder));
    }

    modelBuilder.SetCaseInsensitiveSearchesForSQLite();
}

The accepted answer didn't work for me neither (dotnet 6 + Microsoft.EntityFrameworkCore.Sqlite 6.0.5) possibly because of existing tables / columns. Your foreach did the trick. Thanks. — Jürgen Steinblock, May 22 '23 at 19:22

Yinqiu · Answer 2 · 2020-12-28T09:28:28.760

2

You can use the following code to configure your properties to be case-insensitive.

In your context:

 protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.UseCollation("BINARY");

        modelBuilder.Entity<Entry>().Property(c => c.DayOfWeek)
            .UseCollation("NOCASE");
    }

With this setting, DayOfWeek will not be case-sensitive, while other columns will still be distinguished.

edited Dec 28 '20 at 09:28

answered Dec 24 '20 at 02:22

Yinqiu

6,609
1
6
14

And I'm guessing you would have been able to give me that answer if I just said I needed all text fields to be case insensitive. Well next time I'll make a better question, you live and learn :-) – Johan Knape Feb 17 '21 at 10:28
Yes, I mistakenly thought you only need that column is case-insensitive, lol. – Yinqiu Feb 18 '21 at 02:58

Johan Knape · Accepted Answer · 2021-02-17T10:26:11.123

1

So after a lot of testing I finally found my error.

Turns out all you need is to change the collation in OnModelCreating to NOCASE and suddenly it works like a charm everywhere.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder.UseCollation("NOCASE");
}

edited Feb 17 '21 at 10:26

answered Dec 28 '20 at 09:49

Johan Knape

115
1
12

Hi @Johan Knape,Will it be effective if I configure it like my below? – Yinqiu Dec 28 '20 at 09:54
@Yinqiu not sure and it looks to make it more complex than it need to. If I did it that way I had to make sure my OnModelCreating had every text column in my database, which will be quite a few. If I instead only do it on the migration files, I only have to do those text columns there. I think that will make it easier to maintain. – Johan Knape Dec 29 '20 at 11:03

Sqlite EF Core 5 won't let me do case insensitive searches

3 Answers3