Linq query order by for string

Question

Need suggestions on how to order the strings in order in a linq query.

Example of strings in db [1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 1.9 1.10 1.11 1.12 1.13 1.14 1.15 1.16 1.17 1.18 1.19 1.20 2.1a(i) 2.1a(ii) 2.1a(iii) 2.1a(iv) 2.1a(v) 2.1a(vi) 2.1a(vii) , ....]

In order to solve the issue for a job case.

I have written a linq query which suppose to order the jobs based on band level

var GetOrder =(from a in db.Details

           join b in db.Information on a.InfromationId equals b.Id

           where c.JobId == JobId

           select new {a.Name, a.LastName, b.BandLevel}

           //.OrderBy(b=>b.BandLevel)

           .ToList();

I added the below query for it can order and sort for strings.

GetOrder.Sort((a, b) => a.BandLevel.CompareTo(b.BandLevel));

This query is suppose to sort the stings in order but it fails to set for some strings.

Instead it orders in this format using the above query.

1.1 , 1.10, 1.19 ,1.2, 2.1a(i) ,2.21(v)

It should be in this desired list .

1.1 ,1.2, 1.10, 1.19 , 2.1a(i) ,2.21(v)

Any suggestions on how to sort it in the suggested order for linq queries.

Answer 1

Here's my stab at it. First split the string into various parts, e.g. 2.11a(ii) will become 2, 11, a, and ii. The first two parts can be parsed as regular integers. The second part is parsed as a integer with a=1, b=2 and so on. The third part is parsed as a Roman numeral (I used a modified version of the algorithm presented in this answer). You collect these parts as an array of integers (I call them indexes), and compare the array from one item to the next such that if the first index of each is equal, the items are equal, and so on until an index is unequal.

public static int CustomComparison(string x, string y)
{
    var xIndexes = StringToIndexes(x);
    var yIndexes = StringToIndexes(y);

    for (int i = 0; i < 4; i++)
    {
        if (xIndexes[i] < yIndexes[i])
        {
            return -1;
        }
        if (xIndexes[i] > yIndexes[i])
        {
            return 1;
        }
    }

    return 0;
}

private static int[] StringToIndexes(string input) {
    var match = Regex.Match(input, @"^(\d+)\.(\d+)([a-z]+)?(?:\(([ivx]+)\))?$");
    if (!match.Success)
    {
        return new[] { 0, 0, 0, 0 };
    }
    return new[] {
        int.Parse(match.Groups[1].Value),
        int.Parse(match.Groups[2].Value),
        AlphabeticToInteger(match.Groups[3].Value),
        RomanToInteger(match.Groups[4].Value),
    };
}

private static int AlphabeticToInteger(string alpha)
{
    return alpha.Aggregate(0, (n, c) => n * 26 + (int)(c - 'a' + 1));
}

private static Dictionary<char, int> RomanMap = new Dictionary<char, int>()
    {
        {'i', 1},
        {'v', 5},
        {'x', 10},
    };

public static int RomanToInteger(string roman)
{
    int number = 0;
    for (int i = 0; i < roman.Length; i++)
    {
        if (i + 1 < roman.Length && RomanMap[roman[i]] < RomanMap[roman[i + 1]])
        {
            number -= RomanMap[roman[i]];
        }
        else
        {
            number += RomanMap[roman[i]];
        }
    }
    return number;
}

Answer 2

Well, p.s.w.g has a great answer, but since I worked on this a little I figured I would post mine as well.

My suggestion is to create a class that encapsulates the data from the string, which can only be instantiated from a static Parse method. This Parse method takes in a string and then parses it, setting the properties of the class as it does, and then returns the instance of the class.

The class also implements IComparable, so we can use the Sort and OrderBy methods on a list of these items.

I also used the same answer for parsing roman numerals that was used above (it's the first result when searching for "roman numeral comparer").

Here's the class:

public class BandLevelComponent : IComparable<BandLevelComponent>
{
    public int Major { get; private set; }
    public int Minor { get; private set; }
    public string Revision { get; private set; }
    public string RomanNumeral { get; private set; }

    private static Dictionary<char, int> _romanMap = new Dictionary<char, int>
    {
        {'I', 1},
        {'V', 5},
        {'X', 10},
        {'L', 50},
        {'C', 100},
        {'D', 500},
        {'M', 1000}
    };

    private BandLevelComponent()
    {
    }

    public static BandLevelComponent Parse(string input)
    {
        if (string.IsNullOrWhiteSpace(input)) return null;

        BandLevelComponent result = new BandLevelComponent();

        int temp;
        var parts = input.Split('.');
        int.TryParse(parts[0], out temp);
        result.Major = temp;

        if (parts.Length > 1)
        {
            var minor = string.Concat(parts[1].TakeWhile(char.IsNumber));
            int.TryParse(minor, out temp);
            result.Minor = temp;

            if (parts[1].Length > minor.Length)
            {
                var remaining = parts[1].Substring(minor.Length);
                var openParen = remaining.IndexOf("(");

                if (openParen > 0) result.Revision = remaining.Substring(0, openParen);
                if (openParen > -1)
                    result.RomanNumeral = remaining
                        .Split(new[] {'(', ')'}, StringSplitOptions.RemoveEmptyEntries)
                        .Last();
            }
        }

        return result;
    }

    public int CompareTo(BandLevelComponent other)
    {
        if (other == null) return 1;
        if (Major != other.Major) return Major.CompareTo(other.Major);
        if (Minor != other.Minor) return Minor.CompareTo(other.Minor);
        if (Revision != other.Revision) return Revision.CompareTo(other.Revision);
        return RomanNumeral != other.RomanNumeral
            ? RomanToInteger(RomanNumeral).CompareTo(RomanToInteger(other.RomanNumeral))
            : 0;
    }

    public override string ToString()
    {
        var revision = Revision ?? "";
        var roman = RomanNumeral == null ? "" : $"({RomanNumeral})";
        return $"{Major}.{Minor}{revision}{roman}";
    }

    private static int RomanToInteger(string romanNumeral)
    {
        var roman = romanNumeral?.ToUpper();
        var number = 0;

        for (var i = 0; i < roman?.Length; i++)
        {
            if (i + 1 < roman.Length && _romanMap[roman[i]] < _romanMap[roman[i + 1]])
            {
                number -= _romanMap[roman[i]];
            }
            else
            {
                number += _romanMap[roman[i]];
            }
        }

        return number;
    }
}

And here's a sample usage:

private static void Main()
{
    var dbStrings = new[]
    {
        "1.1", "1.2", "1.3", "1.4", "1.5", "1.6", "1.7", "1.8", "1.9", "1.10", "1.11",
        "1.12", "1.13", "1.14", "1.15", "1.16", "1.17", "1.18", "1.19", "1.20", "2.1a(i)",
        "2.1a(ii)", "2.1a(iii)", "2.1a(iv)", "2.1a(v)", "2.1a(vi)", "2.1a(vii)"
    };

    // Custom extension method for shuffling
    dbStrings.Shuffle();

    // Select each string into our custom class
    var bandLevels = dbStrings.Select(BandLevelComponent.Parse).ToList();

    Console.WriteLine("\nShuffled List");
    Console.WriteLine(string.Join(", ", bandLevels));

    // Sort the list 
    bandLevels.Sort();

    Console.WriteLine("\nSorted List");
    Console.WriteLine(string.Join(", ", bandLevels));

    // Order the list descending (largest first)
    bandLevels = bandLevels.OrderByDescending(b => b).ToList();

    Console.WriteLine("\nOrderByDescending List");
    Console.WriteLine(string.Join(", ", bandLevels));

    GetKeyFromUser("\nDone! Press any key to exit...");
}

Output