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);
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)
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;
If you are sure that q
is well-formatted and sequenced:
var result = q.OrderBy(x => int.Parse(x.Split('.')[0]));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With