EF: LINQ - orderby using child collection with condition - ArgumentException

Question

I'm running into troubles trying to sort IQueryable of my EF Entity.

My object structure is something like this:

Item
Item.CustomFieldValue [List<CustomFieldValue>]
Item.CustomFieldValue.DefinitionID
Item.CustomFieldValue.Value

and I'm working with

IQueryable<Item>

I'd need to sort it conditionally with values having desired definition id being sorted first something like this:

queryable = queryable
    .OrderBy(p => p.CustomFieldValue
        .Where(p2 => p2.DefinitionID == defId)
        .Select(p3 => p3.Value)
        .OrderBy(p4 => p4)
    );

This however throws ArgumentException "DbSortClause expressions must have a type that is order comparable.".

I indeed understand what's the exception trying to say to me, I just can't figure out on how to change this so that valid query is generated.

Any help greatly appreciated

EDIT:

To bring some more light into the issue, I want to achieve something similar that this query does

SELECT * FROM ticketnumber t, customfieldvalue c 
WHERE t.id like '%00000047%' and c.ticketnumberid = t.id
ORDER BY CASE
    WHEN DefinitionId = 2125 THEN 1
    ELSE 2
END, c.Value ASC

Alternatively, as time is starting to become a factor for me, is there a way I could append OrderBy in string form?

Can you explain your exact sort requirements, with an example? Do you want to sort your `Item` collection, or your values in the Item? I think you want to sort your `Item` collection based upon certain `Item.CustomFieldValue`'s, but which ones, and which values come first? — Maarten, May 27 '14 at 14:03
I have queryable of item, this item contains collection of CustomFieldValue. Let's say we have 5 items and each of the items has 5 fields in CustomFieldValue collection [with defId 1,2,3,4,5]. I need to have it sorted by field in CustomFieldValue having specified DefinitionId. So e.g. I need to order Items by CustomFieldValue.Value where defId=3 .... hope I made things a bit brighter — Bruno Laurinec, May 27 '14 at 14:47

score 1 · Answer 1 · edited May 27 '14 at 14:01

1

You probably want to use FirstOrDefault() at the end of the end of the first OrderBy so you won't be dealing with enumerables but with values.

queryable = queryable
    .OrderBy(p => p.CustomFieldValue
        .Where(p2 => p2.DefinitionID == defId)
        .Select(p3 => p3.Value)
        .OrderBy(p4 => p4)
        .FirstOrDefault()
    );

edited May 27 '14 at 14:01

Maarten

22,527
3
47
68

answered May 27 '14 at 13:46

Joanvo

5,677
2
25
35

score 0 · Answer 2 · answered May 27 '14 at 21:33

0

Modification of Joanvo's answer did the trick, this is the working code [I've removed the inner OrderBy]

queryable = queryable.OrderBy(p => p.CustomFieldValue.Where(p2 => p2.DefinitionID == defId).Select(p3 => p3.Value).FirstOrDefault());

answered May 27 '14 at 21:33

Bruno Laurinec

910
2
11
27

EF: LINQ - orderby using child collection with condition - ArgumentException

2 Answers2

Linked