.NET MAUI SQLite: creating custom function or collation for non-English characters

196 Views Asked by At

The problem

I'm sure there are similar questions here, but unfortunately I haven't found something helpful for this case. I'm trying to perform a basic case-insensitive search through a table in MAUI app using LINQ or raw like-query. The problem is that data contains non-English characters like cyrillic.

What I have tried to do

I've tried to create a custom collation for that case:

async Task Init()
{
    if (Database is not null) return;
    Database = new SQLiteAsyncConnection(Constants.DatabasePath);
    SQLitePCL.raw.sqlite3_create_collation(
        Database.GetConnection().Handle,
        "CYRILLIC_NC",
        null,
        (_, strA, strB) => string.Compare(strA, strB, true); //I've also tried to pass a CultureInfo object
}


// ...
// A search example
internal async Task<List<Song>> PerformSearch(string query)
{
    await Init();
    if (string.IsNullOrEmpty(query))
    {
        return await Database.QueryAsync<Song>($"select * from [Song] limit 10");
    }
    else
    {
        return await Database.QueryAsync<Song>($"SELECT * FROM [Song] WHERE Num LIKE '%{query}%' OR Name COLLATE CYRILLIC_NC LIKE '%{query}%' OR Keywords COLLATE CYRILLIC_NC LIKE '%{query}%'");
    }
}

This code "works", but the collation is ignored, even if I'm passing non-existing collation name. Another option is to use LINQ and define custom LOWER function, but now I'm getting a Null-reference exception:

private static void Lower(SQLitePCL.sqlite3_context ctx, object userData, SQLitePCL.sqlite3_value[] args)
{
    string str = SQLitePCL.raw.sqlite3_value_text(args[0]).utf8_to_string().ToLowerInvariant(); //utf8_to_string() returns null
    SQLitePCL.raw.sqlite3_result_text(ctx, str);
}

//...

SQLitePCL.raw.sqlite3_create_function(
    Database.GetConnection().Handle,
    "LOWER",
    1,
    null,
    Lower);

As you can see, I'm using sqlite-net-pcl as Microsoft recommends. Thanks in advance for your feedback.

0

There are 0 best solutions below