Linq OrderBy String Property Length Has To Be in Select List

Question

I am attempting to order the results of a Linq query by the length of a property and then by the property itself in order to order a string as an integer but the generated SQL is not ordering as I would expect it to.

I am joining multiple tables, filtering it down, selecting a DTO out with:

query = basequery.Select(s => new HeadersDTO
{
    headerid = s.Header.id,
    orderno = s.Header.orderno,
    customer = s.Header.customer,
    dateoforder = s.Header.dateoforder,
    consignee = s.Location.name,
    city = s.Location.name,
    state = s.Location.state
}).Distinct();

Then trying to order by s.Header.orderno

 query = query.OrderByDescending(x => x.orderno.Length).ThenByDescending(x => x.orderno)
.Skip(() => offset).Take(() => criteria.per_page);

This still orders it the normal way strings are ordered with first character taking precedence.

But if I select the x.orderno.Length out into it's own property and then order by that it works e.g.

query = basequery.Select(s => new HeadersDTO
{
    ordernolength = s.Header.orderno.Length <---- added this
    headerid = s.Header.id,
    orderno = s.Header.orderno,
    customer = s.Header.customer,
    dateoforder = s.Header.dateoforder,
    consignee = s.Location.name,
    city = s.Location.name,
    state = s.Location.state
}).Distinct();

 query = query.OrderByDescending(x => x.ordernolength).ThenByDescending(x => x.orderno)
.Skip(() => offset).Take(() => criteria.per_page);

Is there a way to do this where I don't have to create a new property in the select list? I can add more information if needed.

@SamAxe I've seen that before but it throws a `Linq To Entities does not recognize Int 32 Parse` error. — Steven B., Sep 15 '17 at 23:07
@DavidEspino it's always an integer in an nvarchar column (I did not design the schema). — Steven B., Sep 15 '17 at 23:08
Can you show us some sample data, how they are **currently** ordered and how you **want** them to be ordered? _Don't make up the data, use actual data you are experiencing now._ — mjwills, Sep 15 '17 at 23:08
Have you examined the projected SQL between the two cases to see what's different? — ErikE, Sep 15 '17 at 23:31
@lamelemon materialize the query then apply the orderby. That lets tge sort happen in linq to objects instead of EF. — Sam Axe, Sep 16 '17 at 04:21

score 0 · Answer 1 · answered Sep 15 '17 at 23:14

0

Try to create a custom Comparer using IComparer where you do the Int32 check for this field. Here is an example for this:

Use own IComparer<T> with Linq OrderBy

Hope this helps

answered Sep 15 '17 at 23:14

David Espino

2,177
14
21

Or you could use the comparer to also compare with the `length` as the example does. – David Espino Sep 15 '17 at 23:15
2

An `IComparer` won't help because the query is being used to project SQL. I don't know of an ORM that can project binary `IComparer` code functionality out to SQL, do you? – ErikE Sep 15 '17 at 23:29

Linq OrderBy String Property Length Has To Be in Select List

1 Answers1