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.