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.

Looking at the screenshot, it looks like your code is preferring a different method that's accepting an expression. "Vanilla" LINQ definitely has an overload with two parameters where the second is a comparer: https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.orderby#system-linq-enumerable-orderby-2(system-collections-generic-ienumerable((-0))-system-func((-0-1))-system-collections-generic-icomparer((-1))) — Martin Costello, Jul 24 '23 at 14:40
You can't execut C# code on SQLite directly using the Linq provider, it doesn't support it (nor do any other Linq-to-database providers). Sounds like you should be changing the collation https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/collation Are you using SQLite.Net or Microsoft.Data.Sqlite? — Charlieface, Jul 24 '23 at 14:40
First call ToList and then OrderBy so its executed local and the Comparer has a chance to actually run? — Ralf, Jul 24 '23 at 14:50
`await Database.Table().Where(i => i.DictionaryId == id).OrderBy(w => w.OriginalWord).ToListAsync();` is **not** LINQ, that's the SQLite implementation and its `OrderBy()` method doesn't take two arguments: https://github.com/praeclarum/sqlite-net/blob/e8a24a8b2ecb4fd700c5fe46062239a9b08472fd/src/SQLiteAsync.cs#L1333 — Julian, Jul 24 '23 at 14:50
I use `SQLite.net`. If I have to add the `Collation`, I have to add `Microsoft.Data.Sqlite`, get rid of SQLite and change all the generic function in `T` for all CRUD. If I don't change, the order is broken... — Enrico, Jul 24 '23 at 15:34

score 2 · Answer 1 · answered Jul 24 '23 at 15:05

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);

score 1 · Answer 2 · answered Jul 26 '23 at 13:01

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

Linq Orderby doesn't have 2 parameters

2 Answers2