Entity Framework Core

SQLite — COLLATE NOCASE

So here’s a very simple problem, you define an entity class in Entity Framework, you add it to the context, you pump a load of data into it and then pull it out at a later date, sorted on a text field.

You then get a surprise, especially if you were coming from a SQL Server view of the world, because the data comes out sorted in case-sensitive order. So now what do you do, and in particular how do you do it to keep your nice ORM model…

There are a number of techniques, the easiest would be to do client side sorting but that’s hardly efficient. You can use the FromSql method on the query and specify collate nocase but that means you can’t compose so nicely, and you have to worry about how things would work in SQL Server…

Current Solution

So, after going round in circles, I came across a comment against a post from Brice Lambson: http://www.bricelam.net/2015/04/29/sqlite-on-corefx.html suggesting:

The simplest way would probably be to just create the table columns with COLLATE NOCASE.

So how do you do that? Well, the best-practice place should be in OnModelCreating(ModelBuider builder) and that turns out to work but only because they don’t do any real checking on type names:

builder.Entity<AnEntity>(e =>
{
  e.Property(o => o.ATextProperty)
   .ForSqliteHasColumnType("TEXT COLLATE NOCASE");
});

Turns out this works, the column gets created as a COLLATE NOCASE column and all your searches / ordering against it are now case insensitive and your query composition works as usual :-)

Search

Having gone round the loop a few times on this, it does seem that search doesn’t work really either. The fundamental problem being that any search is case-sensitive which requires the user to know the case before starting the search. This is considered in detail in the following github issue.

[SQLite] Filtering by string fields is case sensitive · Issue #8033 · aspnet/EntityFramework

So it looks as though they are starting to think of a structurally sound way of solving this problem, meanwhile we have to work out how to adjust the app to take care of the issue.

Current thinking is to do this all client-side and leave the server just as the persistence mechanism?