How to apply a custom collation to specific columns of an SQLite database?

Question

In a C# application that uses iBATIS.NET to map data to an SQLite database I want to be able to sort specific columns by an alphabet that slightly differs from the English alphabet (there are some special characters and the order of some characters is different). I want to get from the database the results that are already sorted, because sorting in the application itself is impossible due to pagination.

After some research I concluded that applying a custom collation may be the only option here. Is this conclusion correct?

However, I couldn't find out how this could be done in the described situation. iBATIS API seemingly has nothing to do with collations and I don't see an obvious way to achieve this tinkering with the database configuration. So, how could this be done?

@BenRobinson the OP asks about SQLite, where there are only 3 built-in collations and you need to provide your own C function to create a custom collation — Panagiotis Kanavos, Dec 11 '14 at 10:57

score 4 · Accepted Answer · edited May 23 '17 at 12:05

User-defined functions and collations are a feature of the ADO.NET driver, not the ORM that sits on top of it. The SQlite ADO.Net driver does allow you to define custom functions, check the second answer to this question :

/// <summary>
/// User-defined collating sequence using the current UI culture.
/// </summary>
[SQLiteFunction(Name = "MYSEQUENCE", FuncType = FunctionType.Collation)]
class MySequence : SQLiteFunction
{
  public override int Compare(string param1, string param2)
  {
    return String.Compare(param1, param2, true);
  }
}

You need to register the function in your code before using it with

SQLiteFunction.RegisterFunction(typeof(MySequence));

Performance may be an issue as you'll pay the price of an Interop call each time a comparison is made

How to apply a custom collation to specific columns of an SQLite database?

1 Answers1

Linked