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?

Answer 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");
}