6

I have a query in linqtosql that returns a LabelNumber:

var q = from list in db.Lists
        select list.LabelNumber;

var q then becomes an IEnumerable<string> with elements like this:

{"1","2","2.A","2.B","3","3.A","3.B"}

I basically want to order the elements as they appear above, but I can't use the OrderBy(x=>x.LabelNumber) because "10" would get placed after "1" and before "2".

I assume I have to write a custom comparator function, but how do I do this with linq?

Edit: I think all of the answers below will work, but one caveat must be added to all responses.

If you are using Linq2SQL you cannot use array indexes within the query. To overcome this, you should have two queries. One that reads from SQL. The second does the ordering:

var q = from list in db.Lists
            select list.LabelNumber;

var q2 = q.AsEnumerable()
          .OrderBy(x => int.Parse(x.LabelNumber.Split('.')[0]))
          .ThenBy(x => x.Number
                        .Contains(".") ? 
                              x.LabelNumber.Split('.')[1].ToString() 
                              : 
                              string.Empty);
Improve this question
2
  • FYI, sorting the numbers the way you want is called a "natural sort." Commented Oct 3, 2010 at 4:44
  • stackoverflow.com/questions/2013198/… Commented Aug 5, 2019 at 13:19

4 Answers 4

Reset to default
13 
OrderBy(x=>x.LabelNumber, new AlphanumComparator())

where AlphanumComparator is the excellent Alphanum natural sort algorithm by David Koelle. No need to reinvent the wheel.

If you're gonna use the C# version change it to:

AlphanumComparator : IComparer<string>

and 

public int Compare(string x, string y)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This syntax doesn't work. Argument 3: cannot convert from 'AlphanumComparator' to 'System.Collections.Generic.IComparer<string>'
That's 'cause OrderBy expects a generic IComparer, while AlphanumComparator is non-generic. The change's quite easy to do, though. Add the <T>'s in the declarations, change all objects to Ts and remove casts.
Oops, I had forgotten that I had modified my local copy of AlphanumComparator. I submitted a patch to the author just now with my simple changes. Perhaps IComparer<string> support will be included in the official version in the future.
9

You probably don't have to write a custom comparer. If all your labels are in the form number.letter, you could use this.

var query = from list in db.Lists
            let split = list.LabelNumber.Split('.')
            let order = split.Length == 1
                ? new { a = int.Parse(split[0]), b = String.Empty }
                : new { a = int.Parse(split[0]), b = split[1] }
            orderby order.a, order.b
            select list.LabelNumber;

If you need more control, you could always convert the sortby fields (a and b) to the appropriate types rather than ints and strings.

If this is LINQ-to-SQL, this actually won't work since some methods used here are not supported. Here's a LINQ-to-SQL friendly version. It won't yield the prettiest query, but it will work.

var query = from list in db.Lists
            let dot = list.LabelNumber.IndexOf('.')
            let name = list.LabelNumber
            let order = dot == -1
                ? new { a = Convert.ToInt32(name.Substring(0, dot)), b = String.Empty }
                : new { a = Convert.ToInt32(name.Substring(0, dot)), b = name.Substring(dot+1) }
            orderby order.a, order.b
            select list.LabelNumber;
Improve this answer

4 Comments

I get this error when I run this: System.InvalidOperationException: Unrecognized expression node: ArrayIndex
This is in LINQ-to-SQL right? Of course this wouldn't work there and would require some tweaking.
Thanks for the edit. You say it won't yield the prettiest query because it probably has to use TSQL to do the substring stuff; is this correct? How does your second version compare performance wise to using the GetEnumerator() method. I would assume your method requires more work by the db server, but my method uses more memory. Are these accurate assumptions. Could you discuss the performance of these approaches? I'm just learning Linq and trying to understand what can be done versus what should be done.
The C# equivalent is more concise whereas the TSQL is very verbose. AFAIK, Substring() isn't near as bad as using IndexOf() as far as complicating the query goes. You probably don't have to worry. Your assumptions are accurate. Using AsEnumerable(), you're using LINQ-to-Objects and the following queries (filtering, sorting, etc.) are executed on your machine. If you kept it LINQ-to-SQL friendly, everything would be executed on the server. After all, it is just a TSQL query. For large sets of data, try to keep it L2SQL friendly to offload the processing. Otherwise, it's up to you.
1

If you are sure that q is well-formatted and sequenced:

var result = q.OrderBy(x => int.Parse(x.Split('.')[0]));
Improve this answer

2 Comments

You're assuming that only the numbers are out of order. What if you had 2.B,2,2.A?
@Michael: The OP diddn't say he wants to orderby the letters..and I'm lazy enough. Anyway it's quite probable that he wants it. LOL.
0

Here is my contribution.. using Regular Expression and LAMBDA expression

List<String> Lst = new List<string> { "1", "2", "2.A","10.A", "2.C", "3", "3.A", "3.B","2.B","11.D" };
Lst = Lst.Select(X => new
        {
            Number = int.Parse( Regex.Match(X, @"([0-9]*).?([a-zA-Z]*)").Groups[1].Value),
            Strings=Regex.Match(X, @"([0-9]*).?([a-zA-Z]*)").Groups[2].Value,
            OriginalString = X
        }).OrderBy(X => X.Number).ThenBy(X=>X.Strings)
        .Select(X => X.OriginalString).ToList();

Output: 

"1"
"2"
"2.A"
"2.B"
"2.C"
"3"
"3.A"
"3.B"
"10.A"
"11.D"
Improve this answer

3 Comments

Method 'System.Text.RegularExpressions.Match Match(System.String, System.String)' has no supported translation to SQL. Although it does appear to work in your example.
then make a list with your labelnumbers and then sort that using the above expression.
Got it solved. Thanks, I got you code running with Linq2SQL. I was unaware of the array limitation. I ended up using a simpler version, as regex over complicates my situation. The sub nesting will eventually get more complicated and I'll have to resort to your solution. Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.