How to use case-insensitive query with Sqlite provider ?

[John0King]

I'm not sure if I remember wrongly,
I see the COLLATE NOCASE append in the sql query very long time ago (may be not COLLATE NOCASE,but something else , all I remember is my query is case insensitive),

but recently I use Sqlite again , and it is case sensitive , and I can not compare an "Email" filed with x.Email == myvariable

[bricelam]

Some users have been successful by configuring the property like this: (although it's certainly not how the API was intended to be used)

modelBuilder.Entity<X>().Property(x => x.Email).HasColumnType("TEXT COLLATE NOCASE");

[bricelam]

Currently, the only way to do it per-query (not configuring the column) is by using raw SQL:

db.X.FromSql($"SELECT * FROM X WHERE Email = {myvariable} COLLATE NOCASE").Where(...);

[bricelam]

Also note, SQLite only performs case-insensitive comparisons of ASCII characters. Override the default to include all Unicode characters:

var connection = (SqliteConnection)db.Database.GetDbConnection();
connection.CreateCollation("NOCASE", (x, y) => string.Compare(x, y, ignoreCase: true));

[John0King]

@bricelam thanks a lot,
but, I don't understand your last code, what is SqliteConnection.CreateCollation() ?
and may I ask , does Sqlite "case-insensitive" in EF 1.x ?

[bricelam]

The last code listing uses SQLite's ADO.NET APIs (the layer beneath EF Core) to replace SQLite's built-in NOCASE comparison with .NET's more complete implementation. (using pure unicorn magic 🦄)

Yes, my first two comments also apply to EF Core 1.x.

[John0King]

@bricelam Thanks again.

to replace SQLite's built-in NOCASE comparison with .NET's more complete implementation.

Is that because Sqlite's ADO.Net provider is also the database engine so it can do that ? otherwise AFAK doing comparison in "client" side is pointless .

[bricelam]

SQLite is an in-process database. The snippet above works by passing a function pointer to SQLite that it can use to invoke the lambda. So technically, it is happening "on the server".

[bricelam]

This is different from EF's client-evaluated queries where extra data may be retrieved from the server so additional processing can be done on the client before returning the final result set.

[bricelam]

I walk through the differences between client-eval and passing pointers to SQLite in my SQLite & EF Core: UDF all the things! post.

[ajcvickers]

@bricelam Can this be closed?

[bricelam]

Yep. @John0King feel free to continue the conversation here even though the issue is closed.

[nbelley]

Quick question, when should I execute the code:

var connection = (SqliteConnection)db.Database.GetDbConnection();
connection.CreateCollation("NOCASE", (x, y) => string.Compare(x, y, ignoreCase: true));

And, is it enough or I also need to use the .HasColumnType("TEXT COLLATE NOCASE"); trick?

If I do it in the OnModelCreating I get a CreateCollation can only be called when the connection is open. execption

[TMendys]

I have had luck with using EF.Functions.Like() and it works. You add the property and the searchstring like this: EF.Functions.Like(person.LastName, $"%{searchString}%").

[roji]

For anyone seeing this, since this issue was opened, EF Core added support for collations - see this page.

[fineboy0407]

Great! I've learned something new.
Thanks for your efforts.

[winstond]

I set a collation for a column to "NOCASE" and confirmed in SQLiteStudio that it was set properly.
However when doing a .Where, I'm still finding the results are coming back case sensitive (If I search for "a" I don't get things that have "A").
Is that an issue with .Where?

[roji]

@winstond please open a new issue with a minimal, runnable repro so we can see what exactly you're doing.