Linq Orderby doesn't have 2 parameters

Question

I want to OrderBy a field using Linq and SQLite. Table:

OriginalWord
Test1
eat
test2

I want to order by OriginalWord:

await Database.Table<Word>()
      .Where(i => i.DictionaryId == id)
      .OrderBy(w => w.OriginalWord)
      .ToListAsync();

Order in SQLite is case sensitive. I found a solution:

internal sealed class NameComparer : IComparer<string> {
    private static readonly NameComparer DefaultInstance = new NameComparer();

    static NameComparer() { }
    private NameComparer() { }

    public static NameComparer Default {
        get { return DefaultInstance; }
    }

    public int Compare(string x, string y) {
        int length = Math.Min(x.Length, y.Length);
        for (int i = 0; i < length; ++i) {
            if (x[i] == y[i]) continue;
            if (x[i] == '-') return 1;
            if (y[i] == '-') return -1;
            return x[i].CompareTo(y[i]);
        }

        return x.Length - y.Length;
    }
}

var sorted = names.OrderBy(name => name, NameComparer.Default).ToList();

This is not working because OrderByhas has only 1 parameter:

I'm using NET7 (MAUI) and SQLite.

Answer 1

The problem is that databases may use an index to do sorting efficiently. But this index may be case sensitive, and may follow some other rules regarding ordering of strings. This is called Collation, and you should be able to specify this in your database, either for the entire database, or for specific columns.

So if you want your query to run efficiently in the database you likely need to change the collation.

A workaround is to just load the unsorted list into memory, and sort it in memory instead. This will have some performance penalty since no index can be used. But sorting is fairly efficient, so I would expect it to be fast enough:

(await Database.Table<Word>()
      .Where(i => i.DictionaryId == id)      
      .ToListAsync())
      .OrderBy(w => w.OriginalWord, StringComparer.CurrentCultureIgnoreCase);

Answer 2

You can't call your C# function inside the OrderBy because OrderBy for SQLite does not provide a comparer parameter.

Instead you need to define a collation on the column

You have two options:

• using one of the predefined collations:

public class Word
{
// whatever
    [Collation("NOCASE")]
    public string OriginalWord

• Create your own collation and connect it up using some PInvoke. (Note this code is untested, and not sure if it works at all in MAUI).

[DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
SQLite3.Result sqlite3_create_collation16(
  IntPtr handle,
  [MarshalAs(UnmanagedType.LPWStr)]
  string zName,
  int eTextRep,
  IntPtr pArg,
  CompareCallback xCompare
);

const int SQLITE_UTF16_ALIGNED = 8;

delegate int CompareCallback(IntPtr pArg, int length1, IntPtr pointer1, int length2, IntPtr pointer2);

internal sealed class NameComparer : IComparer<string>
{
    public readonly CompareCallback = ComparePinvoke;
    // MUST keep delegate alive while the connection is open

    private int ComparePinvoke(IntPtr pArg, int length1, IntPtr pointer1, int length2, IntPtr pointer2)
    {
        var str1 = PtrToStringUni(pointer1, length1);
        var str2 = PtrToStringUni(pointer2, length2);
        return Compare(str1, str2);
    }

    // etc
}

You can then add the collation to the connection like this

using (var connection = new SqliteConnection)
{
    var r = sqlite3_create_collation16(connection.Handle, "YourCollationNameHere", SQLITE_UTF16_ALIGNED, IntPtr.Zero, NameComparer.CompareCallback);
    if (r != SQLite3.Result.OK)
        throw SQLiteException.New (r, SQLite3.GetErrmsg(connection.Handle));

    // whatever
}

Note that you must keep the callback delegate alive while the connection is open. In your case it appears you have a static Singleton comparer, so use that to hold the callback delegate as shown. Otherwise keep the comparer alive using a field, or using GC.KeepAlive.

Finally,

public class Word
{
// whatever
    [Collation("YourCollationNameHere")]
    public string OriginalWord