Linq Sort Nested Table

Question

Follow up question to this:

http://stackoverflow.com/questions/1488414/linq-combine-left-join-data

Say I have the following db tables:

Users
-------
UserId (PK)
UserName

Roles
-----
RoleId (PK)
RoleName

UserRoles
---------
UserId (PK)
RoleId (PK)
Users 1-M UserRoles M-1 Roles

Using LinqToSQL, I can return the following set (thanks to response from prior question):

[User1], [Role1, Role2, Role3]
[User2], [Role2, Role3] 
[User3], [Role3]

The twist is I am trying to sort by Roles. How can I sort the result by Roles?

Clarification

I have a grid, when the user clicks on the Roles column, the rows would be sorted by that column.

So to start the grid would look like this:

[User1], [Role1, Role2, Role3]
[User2], [Role2, Role3] 
[User3], [Role3]

Then if they sort on Roles column it would look like this

[User3], [Role3]
[User2], [Role2, Role3] 
[User1], [Role1, Role2, Role3]

Answer 1

Just change the original answer very slightly:

from u in dataContext.Users
select new { User = u, Roles = u.UserRoles.Select(ur => ur.Role)
                                          .OrderBy(r => r.RoleName) };

(That's assuming you want to sort each element of the result by the roles it contains. If that's not correct, please explain what you want in more detail.)

Answer 2

Could you not simpy use something like this?

// users is the collection you already got from Linq2Sql
var usersSorted = from u in users order by u.Roles select u;

Answer 3

int ascending = 1; //set to -1 for descending

from u in Users
orderby u.Roles.Count * ascending
select new { u, u.Roles.OrderBy(x => x.RoleName) }

Your query will need to cater for the many to many though (not shown).

Answer 4

Hey @zzz, the answers I've seen so far seem to indicate how to sort the rows for each user, whereas, if I understand your clarification, you do want that, but what you're asking for is how to then sort those statements alphabetically. I'll try to provide an answer to that question.

Though your request is very common, regrettably, SQL does not have a native way to convert a table (the Roles column) to a comma delimited string. This normally isn't a problem because you can simply return the Roles field as

{
    UserName = u.UserName,
    RolesList = string.Join(", ", 
     u.UserRoles.Select(ur => ur.Role.RoleName).ToArray())
}

This will work, surprisingly, even though I just mentioned that there is no equivalent function to string.Join in SQL. That's because LINQ is smart enough to just have SQL return the table and to apply the string.Join() at the last minute, in memory.

The problem comes when you then try to sort by the RoleList field as it is created in memory and not in SQL. If you do you'll get the following error message.

NotSupportedException: Method 'System.String Join(System.String, System.String[])' has no supported translation to SQL.

This leaves you with two choices:

1. Write a stored procedure to do this that utilizes a custom function to convert a table to a comma separated list.
2. OR bring the entire result set back into memory by returning it as .ToList() and then performing the sort ie (/my whole query/).ToList().OrderBy(q => q.Roles);

The second option will be much slower if you have a large dataset. If you user list is never going to grow very large or this query will only get called infrequently when an admin loads the user management screen, then the memory option may not be noticeably slower; however, if this query will be called frequently and/or the user table will get large, then this may not be a viable option.

I would suggest a third option. Reappraise the requirements. In this case, the user may really need a filtering feature where they can look at all users who are in a, b, c roles. If that is the true need, then sorting is not only much harder to implement, but it may also be a worse solution.

Good luck!