Alphanumeric sorting in LINQ to Entities for an Grid (RadGrid)

Question

Using VB.NET, Framework 4.0, EF5, VS2010

As a beginner in Linq I have tried a lot of things, but can't make it to work.

Dim ctx As New Context

Dim query = (From a In ctx.TblAddress
             Where (a.ID = 103)
             Order By a.StreetAddress
             Select a.ID, a.StreetAddress).ToList()

This entity I then bind to a RadGrid.

But I want to sort the StreetAddresses as alphanumeric.

I can managed it to work with an array of strings like this:

Dim address As String() = {"Street Name C21", "Street Name A12", 
      "Street Name B26", "Street Name B17", "Street Name C2", "Street Name B6"}
Dim sortedAddresses() As String = address.OrderBy(Function(s) Regex.Match(s, "^\D").Length =0).ThenBy(Function(s) Regex.Match(s, "\D*").Value).ThenBy(Function(s) Int32.Parse(Regex.Match(s,"\d+").Value)).ToArray()

but I can't make it to work in the query.

Or even in a second query. Something like this:

FAILS:

Dim q2 = query.AsEnumerable().OrderBy(Function(s) Regex.Match(s, "^\D").Length = 0).ThenBy(Function(s) Regex.Match(s, "\D*").Value)

I also saw a MSSQL query that did the work, but I rather get it to work in linq to entities.

The SQL query looked like this (and it did the job):

Select StreetAddress, Row_Number() Over (Order by Case When IsNumeric(ID) = 1 then Right(Replicate('0',21) + ID, 20)
                          When IsNumeric(ID) = 0 then Left(ID + Replicate('',21), 20)
                        Else ID
               END) As RowNumber, ID From TblAddress where ID = 103

And yes, I have done a lot of googling for 2 days now, and searched here at SO.. With no luck so far :-(

I also saw a way to the solution here Alphanumeric sorting using LINQ but I couldn't figure out how to solve it.

Then I saw this solution in C# but for a ListBox, and now were getting closer to the solution (at least that's what I thought...) But after trying to make it to work with a RadGrid I finally gave it up..

var list = listBox.Items.Cast<ListItem>()
            .OrderBy(item => int.Parse(item.Text.TrimStart('B')));

listBox.Items.Clear();
listBox.Items.AddRange(list.ToArray());

I'm totally lost, and take pieces everywhere without expected result. :)

Please assist...

Is there no way to get the streets and numbers separately from the database? If not, is it guaranteed that addresses always have the same format? — Gert Arnold, Sep 29 '13 at 18:48
To clarify: **LINQ** is a standard set of methods (`Where`, `Select`, `Average`) that the compiler can translate into from a set of keywords (such as `From`, `Distinct`, `Group By`). When you use **LINQ to Objects**, you are passing in `Func` delegates to these methods; the delegates are run in-memory, and can make use of the entire capability of .NET (such as the `Regex` class). When using these methods with **LINQ to Entities** (and other `IQueryable` implementations) you are passing `Expression` paramaters which are then translated to equivalent SQL. Thus, you can only use methods ... — Zev Spitz, Sep 29 '13 at 19:07
and expressions that the Entity Framework provider can translate into SQL -- `String.Contains`, `+`, `-` and others. Since SQL Server doesn't have native regular expression functions, it is quite obvious that you can't use regexes in an expression passed to an Entity Framework query. You'll have to extract the address parts using only those expressions which have been translated to SQL. See [here](http://msdn.microsoft.com/en-us/library/bb386964.aspx) for more details. See also [here](http://msdn.microsoft.com/en-us/library/bb738681.aspx) for the exact mapping of CLR methods to SQL functions. — Zev Spitz, Sep 29 '13 at 19:10
@GertArnold No I cant get the street and numbers separately. But the streets I have will always be of equal length. Except the following numbers. — Daytrade Penny, Sep 29 '13 at 19:28

score 0 · Answer 1 · answered Sep 30 '13 at 02:11

The sort your describing is referred to as a "natural sort". That should help if you want to search further. I found an implementation with a LINQ example here:

http://zootfroot.blogspot.com/2009/09/natural-sort-compare-with-linq-orderby.html?showComment=1258020708758#c5034722582649839449

I've written natural sort comparers before, but I haven't consumed them in LINQ. So, I'm glad I found this.

Alphanumeric sorting in LINQ to Entities for an Grid (RadGrid)

1 Answers1