2

I have a query using lambda expressions and I want to be able to sort from a column in a table that is a child collection of a parent table. The query looks like this:

 var query = ctx.Timelines.Include("Rule.DocumentRules").Where(...).OrderBy(o => o.Rule.DocumentRules.OrderBy(t => t.SortOrder));

The SortOrder column resides 3 levels deep under the Timelines Entity and I don't know which extension to use to access it. When I use the code above, I get an error "DbSortClause expressions must have a type that is order comparable. Parameter name: key". I get the same error if I use a Select extension instead of the 2nd OrderBy. Does anyone know how I can sort by this column? This "SortOrder" column is not a primary or foreign key.

Thanks

rvenable
  • 31
  • 1
  • 4
  • 3
    It is nonsensical to order by an entire sequence of child entities. It would only makes sense for you to coalesce the child entity set into a single value that can then be used for ordering. This can be done by either choosing a single entity (via `First()`) or by using aggregation operators such as `Max` and `Sum`. – Kirk Woll May 30 '12 at 20:45
  • It looks like you're trying to sort by multiple things at the same time? Is there a 1 to 1 relationship between timelines and documentRules? – Jeff Lauder May 30 '12 at 20:46

3 Answers3

1

I know this is an old thread, but I came across this post as I was having the same issue and since it didn't have an answer I thought I would share my solution in the event it helped someone else.

Let's say you have a requirement to show an actual parent (ie...mom/dad) and their children but you want to show the children's names in alphabetical order in the report. If they are not in the database in the right order, you must order them by the "Name" field on the child table to display them based on the requirement.

Simple T-SQL example that works as expected:

select * from ParentTable p
inner join ChildTable c on c.ParentId = p.ParentId
where p.ParentId = 1
order by c.Name

I had tried to accomplish this the same way the original poster did (shown below) and got a "DbSortClause expressions must have a type that is order comparable" exception.

.OrderBy(x => x.ParentTable.ChildTable.OrderBy(y => y.Name))

I'm using projection so this may not work for all, but what I found is that if I did the OrderBy inside the projection of the child list it worked as I wanted it too.

Children = x.ParentTable.ChildTable.OrderBy(y => y.Name).Select(aa => new ChildTableModel

There may very well be a better/different way to accomplish this, but this did what I wanted so I thought I would share.

user1011627
  • 1,741
  • 1
  • 17
  • 25
0

It looks like you're trying to sort by multiple child items, which doesn't really make sense to the compiler. If there is indeed a 1 to 1 relationship between those two entities you could try something like

var query = ctx.Timelines.Include("Rule.DocumentRules").Where(...).OrderBy(o => o.Rule.DocumentRules.First().SortOrder));

this could result in a null exception depending on how your database is set up.

Jeff Lauder
  • 1,247
  • 8
  • 14
  • When I use the First() method I get "The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead." The FirstOrDefault() doesn't fail but it doesn't return them in the right order. There is no direct relationship between TimeLine and DocumentRule. A timeline can have one rule and a rule can have namy DocumentRules. The DocumentRule table has a SortOrder Column which I want to use in my OrderBy. I can achieve this in SQL like this: – rvenable May 31 '12 at 13:00
  • sorry...got cut off. select * from timeline t inner join [rule] r on t.ruleid = r.ruleid inner join documentrule d on t.ruleid = d.ruleid where order by d.sortorder I should say a timeline can have one rule and a rule can exist in many DocumentRules. The DocumentRule table is a lookup table and is just a subset of the Rule table. Thanks – rvenable May 31 '12 at 13:13
  • Ok. If there are 3 different document rules for a given timeline, which one of those three should I use to order by? If I have 2 different timelines who each have 3 different document rules, now I have nine separate sortable items but I'm only attempting to sort two things. So you'll have to write something that specifies which of the document rules to sort by. Conversely you can add a sort order to timelines – Jeff Lauder May 31 '12 at 13:15
  • In most cases I only display information from the Timeline table but if there are associated document criteria then I need to link up to the DocumentRule table to only display certain Rules that are listed in this table and I need to have them ordered by the Sortorder column. So if there are 7 records in this table, my sortorder column will be numbered from 1 to 7 accordingly. – rvenable May 31 '12 at 13:29
  • Also I didn't realize that about First(), using it, or FirstOrDefault() will result in you selecting the first documentRule. Both First and FirstOrDefault() will result in a null reference on sortorder if there are no document rules for a given rule – Jeff Lauder May 31 '12 at 13:32
  • I think it would be helpful if you could add some sample data for each table to your question, then an example of how it should be organized – Jeff Lauder May 31 '12 at 14:29
0

I've faced same issue when was trying to sort by column in related entities. The only one solution that works well was to use Entity SQL. Here is a simple example of using ESQL query which returns ObjectQuery: Converting ESQL to LINQ to Entities. Sort by related entities.

Hopefully this will help.

Community
  • 1
  • 1
Anatolii Gabuza
  • 6,184
  • 2
  • 36
  • 54