How can I apply sorting on unicode character

[sonkt]

Hi all!
I have a problem with sorting data on unicode character (vietnamese).

In above picture, row 105 is between 104 and 106. In my code, I sorted with 'LastName DESC' column (not show in picture). The 'D' character and 'Đ' are two different characters.

Below is my code
var items = await base.SetOrderBy("LastName DESC").SetLimit(10u, 11u).FindAllAsync(predicate);

@phnx47

Thanks a lot.

[phnx47]

@sonkt Hi,

This library help you generate SQL and execute via Dapper only. You cannot pass a convert function via this library.

Possible solutions:

• You can resolve in your Database setting. For SQL Server check documentation: Collation and Unicode support.
• You can send Raw SQL via Dapper.

[sonkt]

@phnx47 , with EF Core, I can pass a convert function to non-unicode for searching and sorting. But in this lib, It can't parse C# convert function to SQL. Can I use SQL function vis parameter?

[phnx47]

@sonkt You can send Raw SQL via Dapper.

[sonkt]

@phnx47 thanks, I don't want to user Raw SQL, I like to use 'FindAllAsync' method.

[phnx47]

@sonkt I updated answer and include addition info. If it answer your question, click "Mark as answer", please

[sonkt]

@phnx47 Thanks a lot. The problem resolved.
I put 'COLLATE Vietnamese_CI_AS' to Orderby clause like : "LastName COLLATE Vietnamese_CI_AS " and use CustomQuery for SetOrderBy method.