DbSortClause expressions must have a type that is order comparable parameter Name :Key

Question

I am using Linq to entity and have the following query

IQueryable<DomainModel.User> userResult = 
      userResult.OrderBy(u => u.UserClientRoles.OrderBy(r => r.Role.RoleName));

But I am getting this error

DbSortClause expressions must have a type that is order comparable

parameter Name :Key

and it returns an empty collection.

Any idea what's going on?

Answer 1

.OrderBy(), when working with databases, is supposed to take in a delegate that returns only a single property that represents a column in your database. I'm not sure what you're trying to do, but it looks like

u.UserClientRoles.OrderBy(r => r.Role.RoleName)

Will return an enumeration of values, which can't be ordered.

Answer 2

I had the same problem, I solved it using this:

your code:

IQueryable<DomainModel.User> userResult = userResult.OrderBy(u => u.UserClientRoles.OrderBy(r => r.Role.RoleName));

my code:

List<Membership> results = new List<Membership>(); results.AddRange(memberships.OrderBy(m => m.Roles)); memberships = results.AsQueryable();

coincidences:

*.OrderBy(m => m.Roles)

solution:

*.OrderBy(m => m.Roles.Select(r => r.RoleId).FirstOrDefault())

possible problem's reason:

Maybe, you did what I did, and cause that 1 user/member could have more than 1 role in the same membership. That made a conflict with/to OrderBy() because the application can just "order" a single element at the time, when she call the Role (which is an ICollection of elements) the instead receive more than 1 element with no kind of priority's levels (even when we could assume that the application will take the role's index as priority's base level, actually its don't).

solution's explaination:

When you add the *.Select(r => r.RoleId), you are specifying to the application which element will be used to OrderBy(). But, as you shall see when you maybe reached at this point, just by using the *.Select(r => r.RoleId) could be not enough, because the application is still receiving multiple results with the same priority's level. Adding *.Select(r => r.RoleId).FirstOrDefault() you are basically saying: "...I don't care how many results you received from that element, just the focus on the first result, or order them by its default..." (default normally means EMPTY or NULL).

additional information:

I used non-official's simple concepts/meanings to explain a complex solution with simple words, which means that you could maybe have problems to find similar posts in the web by using the words/concepts used in this "answer". Otherwise, the code itself works and you shouldn't not have any problem by applying it and/or modifying it by yourself. GOOD LUCK!!! (^_^)

Answer 3

In my case, I was accidentally trying to order by an object instead of ordering by one of it's properties.

You should you use

var query = from Foo in Bar
            orderby Foo.PropertyName
            select Foo;

Instead of

var query = from Foo in Bar
            orderby Foo
            select Foo;

Note: you will get the same behaviour event if there is an override on Foo's ToString() method.