SQLite collation attribute

Question

I'm using a sqlite database on my Xamarin app and i'm tring to set collation attribute on my data model

public class Customer
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    [Collation("NOCASE")]
    public string Name { get; set; }

    public string Email { get; set; }

}

but I do not found anywhere which value must I set into collation attribute to control case sensitive / case insensitive collation

this is my test code:

var dbfile = DependencyService.Get<IFileHelper().GetLocalFilePath("data.db");

db = new SQLiteAsyncConnection(dbfile);
db.CreateTableAsync<Customer>().Wait();
var result = await db.Table<Customer>().OrderBy(c => c.Name).ToListAsync();

expected result: case insensitive order in name column,

actual result: the order is case sensitive.

1) var sortedWords = words.OrderBy(a => a.Name, StringComparer.OrdinalIgnoreCase); 2) sortedWords = words.OrderBy(a => a.Name, StringComparer.CurrentCultureIgnoreCase); 3) sortedWords = words.OrderBy(a => a.Name, StringComparer.InvariantCultureIgnoreCase); — Ziyad Godil, May 23 '17 at 11:36
sorry but I did not found an overload of method OrderBy where StringComparer could be specified — danilonet, May 23 '17 at 13:13

score 0 · Accepted Answer · answered Aug 05 '17 at 10:36

0

solved, after the attribute

[Collation("NOCASE")]

is added, then the table must be dropped and re-receated. Usually sqlite automatically update the table after a change when the command

db.CreateTableAsync<Customer>().Wait()

is executed, but in this case the drop / delete table is required.

answered Aug 05 '17 at 10:36

danilonet

1,757
16
33

Please use `await db.CreateTableAsync...` instead of `.Wait()` - https://stackoverflow.com/a/13140963/121309 – Hans Keﬆing Feb 23 '21 at 08:20

SQLite collation attribute

1 Answers1