ASP.Net EF OrderBy not natural sorting

Question

I am writing to search function. My Db field as string (Nvarchar(MAX)) and this field have string and number

DB DATAS    EF ORDER          I want to
a 1          a 1                a 1
a 20         a 12               a 2
a 2          a 2                a 12
a 12         a 20               a 20
a 25         a 25               a 25
b 1          b 1                b 1
b 5          b 5                b 5
....         ...                ....

In LINQ you may use `OrderBy` followed with `ThenBy`, e.g. `list.OrderBy(x => x.LetterFieldName).ThenBy(x => x.NumericFieldName)`. If you want to perform further comparison, create a method extending `IComparer` for numeric field and pass it in `OrderBy`. — Tetsuya Yamamoto, May 15 '17 at 08:42
@TetsuyaYamamoto i have one field => `a 2` data is only a column in db — OnePage, May 15 '17 at 08:58

ViVi · Answer 1 · 2017-05-15T09:25:50.097

You need to implement the IComparer interface and create a NaturalStringComparer to compare strings in the way you want. See code below.

public class NaturalStringComparer : IComparer<string>
{
    private static readonly Regex _re = new Regex(@"(?<=\D)(?=\d)|(?<=\d)(?=\D)", RegexOptions.Compiled);

    public int Compare(string x, string y)
    {
        x = x.ToLower();
        y = y.ToLower();
        if (string.Compare(x, 0, y, 0, Math.Min(x.Length, y.Length)) == 0)
        {
            if (x.Length == y.Length) return 0;
            return x.Length < y.Length ? -1 : 1;
        }
        var a = _re.Split(x);
        var b = _re.Split(y);
        int i = 0;
        while (true)
        {
            int r = PartCompare(a[i], b[i]);
            if (r != 0) return r;
            ++i;
        }
    }

    private static int PartCompare(string x, string y)
    {
        int a, b;
        if (int.TryParse(x, out a) && int.TryParse(y, out b))
            return a.CompareTo(b);
        return x.CompareTo(y);
    }
}

Example for sorting mix of string and integers :

var x = new List<string>() { "a 1", "a 20", "a 2", "a 12" };
var y = x.OrderBy(m => m, new NaturalStringComparer()).ToList();

Input : "a 1", "a 20", "a 2", "a 12"

Output: "a 1", "a 2", "a 12", "a 20"

already i use it check the post and code area this middle list is EF orderby list — OnePage, May 15 '17 at 08:56
@OnePage : I have edited my answer. Please see the test example I have posted. It works fine for me and should suit your requirement too. — ViVi, May 15 '17 at 09:14

Ashiquzzaman · Answer 2 · 2017-05-15T09:37:06.720

0

You can do something like:

    var lists = strs.Select(str => new Regex(@"([a-zA-Z]+)(\d+)").Match(Regex.Replace(str, @"\s+", "")))
        .Select(result => new
        {
            str = result.Groups[1].Value,
            num = result.Groups[2].Value
        })
        .ToList().OrderBy(s=>s.str).ThenBy(s=>s.num).Select(s=> new {result=s.str+" "+s.num}).ToList();

edited May 15 '17 at 09:37

answered May 15 '17 at 08:50

Ashiquzzaman

5,129
3
27
38

i have one field and this data one item – OnePage May 15 '17 at 08:55
Is data ex:`a 1 ` contain space?@OnePage – Ashiquzzaman May 15 '17 at 08:57
Numeric value is specific. It may or may not be. – OnePage May 15 '17 at 09:00

score 0 · Accepted Answer · edited May 23 '17 at 12:18

In your case (a letter followed with numbers), you may require number padding like this (credits to Nathan):

public static string PadNumbers(string input)
{
    // replace number "2" into number of digits you want to group with
    return Regex.Replace(input, "[0-9]+", match => match.Value.PadLeft(2, '0'));
}

This way enables padding on numeric portion in OrderBy clause, which should be used like this:

var naturalOrder = list.OrderBy(x => PadNumbers(x));

By using padding regex above, OrderBy will see the number part like this:

a 01
a 20
a 02
a 12
a 25
...

and then ordering them like this:

a 01
a 02
a 12
a 20
a 25
...

Of course, the padding only used in comparison when deciding number orders and the original strings still preserved.

The reason behind padding usage is the default ordering used with OrderBy in lexicographical order which process characters in string from left to right.

Working example: NET Fiddle Demo

Similar issue:

Alphanumeric sorting using LINQ

ASP.Net EF OrderBy not natural sorting

3 Answers3