none
How do I correctly sort a string containing roman numerals and numbers RRS feed

  • Question

  • I've been trying to figure out how to get LINQ to correct sort a string (from database) in the format below:

    string1 <roman numeral> - string2 <integer> - string3

    I need the 3 strings to be sorted aphabetically obviously, but I also need to sort by both the roman numerals and the integer.

    By default OrderBy is returing the following (for example):

    string1 IX - string2 19 - string3
    string1 IX - string2 8 - string3
    string1 V - string2 8 - string3

    As you can see, it is sorting the roman numerals alphabetically which is not what I want, and it is putting the 19 before the 8, again sorting alphabetically.

    Note: string2 will always be the exact same value for every string compared

    So, I tried to create an object for comparing the strings (code here uses hardcoded values - assume I have extracted the roman numeral as an integer and also the other integer):

    public class MyStringComparer : IComparer<string>
    {
        public int Compare(string strA, string strB)
        {

            Int32 romanNumeralValue_1 = 9;
            Int32 romanNumeralValue_2 = 4;

            Int32 integer_1 = 19;
            Int32 integer_2 = 8;

            //Return values
            //Less than zero -> strA is less than strB
            //Zero -> strA equals strB
            //Greater than zero -> strA is greater than strB

            if (romanNumeralValue_1 == romanNumeralValue_2)
            {
                if (integer_1 < integer_2)
                    return -1;
                else if (integer_1 > integer_2)
                    return 1;
                else
                    return 0;
            }
            else if (romanNumeralValue_1 < romanNumeralValue_2)
                return -1;
            else
                return 1;
        }
    }

    As you can see I first compare the roman numeral, and if they match I then base the comparison on the integer.

    Then I try to use the sort approx as follows:

                    var something = (from p in someTable select p);
    var x = something.OrderBy(q => q.TheString, new MyStringComparer());
                    return x.ToList();

    This compiles fine but I get nothing but an exception:

    Unsupported overload used for query operator 'OrderBy'


    Can anybody either help me sort this string properly or explain what I have done wrong, assuming I am even close.... I don't really know LINQ well enough to know whether my approach is even correct for this situation...

    Any help appreciated...!


    Wednesday, March 18, 2009 5:14 AM

All replies

  • LINQ to SQL can't translate the OrderBy overload with an IComparer<T> parameter into a SQL statement. You will have to use the AsEnumerable extension method before calling OrderBy so that your OrderBy call works on in-memory objects.

    var something = (from p in someTable select p);
    var x = something.AsEnumerable().OrderBy(q => q.TheString, new MyStringComparer());
    return x.ToList();
    Friday, March 20, 2009 3:38 PM
    Answerer