LINQ TO Entities OrderBy Conundrum

Question

I am having trouble trying to understand how to perform an order by in a LINQ to Entities call to return data organized in the desired order. The database used is postgresql. The order by in postgres is:

SELECT
    *
FROM
    part
ORDER BY
    split_part(partnumber, '-',1)::int
    , split_part(partnumber, '-',2)::int

Partnumber is a string field which is formated into 2-3 segments which are numeric separated by '-'. eg:

1-000235
10-100364
9-123456

etc.

I would want the sorted result to return:

1-000235
9-123456
10-100364

I have a test VB.Net app I am trying to figure out how to do this:

Using ctx As New EFWeb.MaverickEntities
    Dim myparts = ctx.parts.
                        OrderBy(Function(e) e.partnumber).
                        ToList()

    For Each pt As part In myparts
        Console.WriteLine("{0} - {1}", pt.partnumber, pt.description)
    Next
End Using 

I tried doing: CInt(e.partnumber.Split("-")(0)) to force sorting for the first segment of the partnumber, but errored out because of the the compiler did not like the array referrence for the result of the Split() call.

If anybody knows of a good current reference for LINQ to Entities ... that would be appreciated.

Answer 1

You didn't share your Linq code. Anyway I would get the data to client side and then do the ordering. In C#:

var result = ctx.Parts.AsEnumerable()
    .Select(p => new {p, pnSplit = p.PartNumber.Split('-')})
    .OrderBy(x => int.Parse(x.pnSplit[0]))
    .ThenBy(x => int.Parse(x.pnSplit[1]))
    .Select(x => x.p);

In VB it should be:

Dim result = ctx.Parts.AsEnumerable()
        Select(Function(p) New With {p, .pnSplit = p.PartNumber.Split("-"c)}).
        OrderBy(Function(x) Integer.Parse(x.pnSplit(0))).
        ThenBy(Function(x) Integer.Parse(x.pnSplit(1))).
        Select(Function(x) x.p)

Note the integer.Parse. Otherwise it would be alphabetic sort.