LINQ numeric sort replace non-numeric characters

Question

Ive got the following LINQ query:-

(from a in MyData
where a.Field1.Replace(" ","").Contains("ABCDEFG")
select a.Field2).Take(50)

It returns:-

I need to be able to sort this list numerically which i could do like this:-

(from a in MyData
where a.Field1.Replace(" ","").Contains("ABCDEFG")
select a.Field2).Take(50).OrderBy(x => Convert.ToInt32(x))

Problem is this returns:-

Conversion failed when converting the nvarchar value '111 A ' to data type int.

Because it obviously cannot convert the '111 A ' to an integer.

Is there any way of stripping out all non-numeric characters just for the sorting ?

What you want is a natural sort. There are tons of resources for this on the net. Basically, create a custom `IComparer` that implements the natural sort and pass an instance of it to the OrderBy: `OrderBy(x => x, new NaturalStringComparer())`. — Daniel Hilgarth, Mar 20 '13 at 14:41
TO support previous comment: here's stackoverflow question about natural sort in C#: http://stackoverflow.com/questions/248603/natural-sort-order-in-c-sharp — J0HN, Mar 20 '13 at 14:41
@DanielHilgarth it looks like he's using LINQ to SQL, a custom IComparer would not be suitable. — Doctor Jones, Mar 20 '13 at 14:42
@DoctorJones: You might be right. In that case a `.AsEnumerable()` before the `OrderBy` will do the trick. As long as the `OrderBy` is the last operation being performed, the performance and memory impact of performing the sorting in the application instead of the database is marginal. — Daniel Hilgarth, Mar 20 '13 at 14:44
@Bob Vale I agree, very interesting! Edited, cheers. Although thinking about it, where the order by is performed depends on the user's requirements... — Oliver, Mar 20 '13 at 14:45

Oliver · Accepted Answer · 2013-03-20T14:58:34.350

3

Try:

MyData.Where(d => Field1.Replace(" ","").Contains("ABCDEFG"))
    .AsEnumerable()
    .OrderBy(d => Convert.ToInt32(Regex.Split(d.Field2, @"\D+")[0]))
    .Take(50)
    .Select(d => d.Field2);

You can swap the OrderBy() and Take() depending on your desired result. However as Bob Vale has noted below:

If Take() is first then it should be before the AsEnumerable()

So that you do not load all of the records from the source.

edited Mar 20 '13 at 14:58

answered Mar 20 '13 at 14:41

Oliver

8,794
2
40
60

If `Take()` is first then it should be before the `AsEnumerable()`. – Bob Vale Mar 20 '13 at 14:54
`Take` doesn't really mean anything unless the collection is ordered in the first place from the source - so to me, the only way to do a custom (non-database) sort and return the top 50 would be to bring in the entire resultset into memory, then run the custom `OrderBy`, then the `Take`. The only way I know of to bring back only the top 50 sorted from the database would be to do the custom sort in the original query (via stored proc or view or something), rather than plain LINQ2SQL or EF. – Joe Enos Mar 20 '13 at 15:03
1

@Joe Enos Totally agree, however we don't specifically know in this case what the OP's "MyData" collection represents. – Oliver Mar 20 '13 at 15:17

score 0 · Answer 2 · answered Mar 20 '13 at 14:42

Not the prettiest code out there, but in your sort lambda, you can create a new string made up of only the digits, then convert that to an integer for your sort value.

.OrderBy(x => 
    Convert.ToInt32(new string(x.Where(c => char.IsDigit(c))
                                .ToArray()
                              )
                   )
        );

LINQ numeric sort replace non-numeric characters

2 Answers2