How to sort in a related table in ASP.NET MVC3

Question

This is my (simplified) code:

Controller

var userQuery = from u in _db.Users
                where u.Username.ToLower().Contains(search.ToLower())
                    || u.FirstName.ToLower().Contains(search.ToLower())
                    || u.LastName.ToLower().Contains(search.ToLower())
                select u.FirmID;

var query = from f in _db.Firms
            where f.Name.ToLower().Contains(search.ToLower()) 
                || f.Keyword.ToLower().Contains(search.ToLower()) 
                    || f.KeywordList.ToLower().Contains(search.ToLower())
                    || userQuery.Contains(f.ID)
            select f;

// order by firm name
query = query.OrderBy(f => f.Name);

User Model

public class User
{
    [Key]
    public int ID { get; set; }

    public string Username { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public int FirmID { get; set; }
    [ForeignKey("FirmID")] 
    public virtual Firm Firm { get; set; }
}

Firm Model

public class Firm
{
    [Key]
    public int ID { get; set; }

    public string Keyword { get; set; }
    public string KeywordList { get; set; }
    public string Name { get; set; }

    public virtual ICollection<User> Users { get; set; }
}

With this code, I get a list of Firms with Users which matches a certain search string. The list is ordered by Firms name. What I wanna do now, is to sort also by Users last name. How can I do that?

Now:

Firm a
- User b
- User a
- User c
Firm b
- User z
- User d

Desired:

Firm a
- User a
- User b
- User c
Firm b
- User d
- User z

Hmm shouldn't that be query = query.OrderBy(f => f.Name).ThenBy(f => f.Users.LastName); ? — android
– android, Commented May 29, 2012 at 15:33
Sorry, I mean query = query.OrderBy(f => f.Name).ThenBy(f => f.Users.LastName); does not work. LastName isn't available for some reason... — android
– android, Commented May 29, 2012 at 15:51

Jorge · Accepted Answer · 2012-05-29 21:07:46Z

1

tried like this

query.OrderBy(f => f.Name).Select(aux => new 
{
   Firm = aux.Firm,
   Users = aux.Users.OrderBy( x => x.Username )
})

answered May 29, 2012 at 21:07

Jorge

18.3k19 gold badges82 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

android · Accepted Answer · 2012-05-31 16:04:37Z

0

Well, I finally found the best solution for me:

query.OrderBy(f => f.Name)
     .ToList()
     .ForEach(f => f.Users = f.Users.OrderBy(u => u.LastName).ToList() );

This means, I don't have to create an anonymous select object (like Jorge's answer), which makes the model a lot easier to handle in a view.

Another solution could be to sort the users not in the controller but in the view. For example:

@foreach (var user in Model.Users.OrderBy(u => u.LastName)) {  }

answered May 31, 2012 at 16:04

android

6529 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to sort in a related table in ASP.NET MVC3

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related