How to Sort a String of Text Followed by a Number Using Linq

How can I sort a string of text followed by a number using LINQ

It's because you are sorting them as strings, and for strings 11 comes before 2. You'll need to parse the ShortTitle to give you the (I'm assuming) int value at the end, and sort by that.

Your LINQ query can be changed to this for it to work:

var query = _cityRepository.GetAll(
           .OrderBy(item => item.RowKey.Substring(0, 4))
           .ThenBy(item => item.ShortTitle.Split('-').First())
           .ThenBy(item => Convert.ToInt16(item.ShortTitle.Split().Last()));

sort string as number in linq to entity

You have two problems here:

1. You are storing numbers as strings in your database and
2. Youre trying to execute C# code on Sql Server

The exception you are receiving is due to the fact that the compiler cannot translate the comparison logic from SemiNumericComparer class into a sql query.

In order to achieve the desired result you could:

a) Load all data in memory and perform the comparison using SemiNumericComparer in memory by iterating through the selected results and ordering them after that like this:

var model = (from c in General.db.GlbTbComboBases
             where c.ClassCode.Equals(classCode)
             select new ReturnData { id = c.BaseCode, name = c.FAName })
            .ToList() // this will load data into memory
            .OrderBy(c => c.id, new SemiNumericComparer());

This, however is not a good approach because it will add a lot of useless memory consumption if the dataset is quite small and will crash your application if your dataset is larger than the available memory at a given time.

Edit As pointed out by @gsubiran this approach is not valid.

b) Convert your strings into numbers on Sql Server using SqlFunctions and order them as numbers using the ordering provided by Sql Server:

var model = (from c in General.db.GlbTbComboBases
             where c.ClassCode.Equals(classCode)
             select new ReturnData { id = c.BaseCode, name = c.FAName })
            .OrderBy(c => SqlFunctions.IsNumeric(c.id));

LINQ OrderBy or Sort does not order correctly for integer string list?

This particular question comes up all the time and I'm pretty sure there are good duplicate questions here on Stack Overflow covering the topic. Please close the question as a proper duplicate and I'd be more than happy to delete my answer in response.

In the meantime...

The problem is us humans.

We see numbers so we think in numbers. To us, 1 is less than 12, and 12 is less than 100. No problems here.

The problem, however, is that we're asking a computer. And a computer is a bit more finicky. In particular, if we're asking a computer to do a alphabetical sort, it will invariably treat our "things" to sort as text. And this is where the problems occur. Or rather, this is where our expectations no longer match what the computer is going to do.

The computer, when asked to sort strings, sort them as any other string sort, character by character.

Let's look at the items:

1
100
12

To us, the natural sort order would be 1, 12, 100. Increasing order.

To the computer, asked to do a text sort, the natural order is different.

The reason is that it's going to compare strings one character at a time.

Broadly it is going to say "all strings that start with 1 is going to come before strings that start with 2", which means "1" and "100" is going to come before "2". This is exactly the same as saying "all words that start with the letter A comes before words that start with the letter B".

Then it's going to say "all strings that then have a 0 comes before strings that have a 1, 2, 3, etc. up to 9, including space", because this is how text sorting is done.

In other words, when you ask "Sort does not order correctly for integer string list?" the simple answer is "Well, no, because when you sort as text, the integer part is never considered".

C# - How to sort list string number by linq?

Since the dates are in the format that can be ordered lexicographically, you could sort by the date prefix using string ordering, and resolve ties by parsing the integer:

var sorted = listStr
    .OrderBy(s => s.Split('_')[0])
    .ThenBy(s => int.Parse(s.Split('_')[1]));

Demo.

How can I sort a list of numerical strings? Preferably using LINQ

If that format is strict you could parse to Version and order by that:

string[] orderedThings = things
   .Select(t => new { Thing = t, Numbers = (t + "_0").Split('_') })
   .Where(x => x.Numbers.All(s => s.All(char.IsDigit)))
   .Select(x => new { x.Thing, VersionStr = String.Join(".",x.Numbers.Take(4)) })
   .OrderBy(x => new Version(x.VersionStr))
   .ThenBy(x => x.Thing)
   .Select(x => x.Thing)
   .ToArray();

The t + "_0" trick was necessary to ensure that also single digits can be parsed. A version needs at least a major and aminor part. This "works" also if there are more than 4 tokens(major, minor, build, and revision). Then only the first 4 are taken to initialize the Version instance.

Linq query order by for string

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

How to use Linq to sort an array by Length and then value

Try this snippet:

var sortedValues = values
    .OrderByDescending(x => x.Length)
    .ThenByDescending(x => Convert.ToInt32(x));

If you really need use it as a List, then add ToList() at the end.

---

Related Topics