CODEWITHSUNDEEP
entity-framework
Samirovic
Samirovic

Reputation: 74

Entity Framework Many-to-Many Order

I have 3 tables: Users, Roles and an intermediate table UsersRoles. how can I Order the users according to their Role names?

public List<USERS> Find(UserFilter filter, string sortExpression, int maxRows, int startIndex)
{
            var query = db.USERS.Include("ROLES").AsQueryable();

            query = ApplyFilter(query, filter);
            query = query.OrderBy(sortExpression);
            query = ApplyPaging(query, maxRows, startIndex);

            var result = query.ToList();
            return result;
}

private IQueryable<USERS> ApplyOrder(IQueryable<USERS> query, UserFilter filter)
{
        // what should I do here to sort/group according to role name ?
}

private IQueryable<USERS> ApplyFilter(IQueryable<USERS> query, UserFilter filter)
{
        if (!string.IsNullOrEmpty(filter.UserName))
                    query = query.Where(u => u.USERNAME == filter.UserName);
}

private IQueryable<USERS> ApplyPaging(IQueryable<USERS> query, int maxRows, int startIndex)
{
            var result = query;
            if (maxRows != 0)
                result = query.Skip(startIndex).Take(maxRows);
            return result;
}

I'm calling Find method from the controller and I'm using AsQueryable so how can I group the users according to their Roles (the roles should be sorted according to role name)? what should I write in ApplyOrder method?

Upvotes: 0

Views: 44

Answers (1)

Harald Coppoolse
Harald Coppoolse

Reputation: 30464

Consider the following example:

var users = new user[]
{
     new user()
     {
         Name = "Jan",
         Roles = new Role[]
         {
             new Role() {Name = "Supervisor},
             new Role() {Name = "Teacher"},
         }
     },
     new user()
     {
         Name = "Pierre",
         Roles = new Role[]
         {
             new Role() {Name = "Student"},
             new Role() {Name = "Scholar"},
         }
     }
}

how can I Order the users according to their Role names?

What do you mean? Do you want users with Roles, where Roles are ordered by name? Or do you want Roles ordered by name with their users?

Anyway, if you've followed the entity framework code first conventions your classes for the many-to-many will be similar to the following:

class User
{
    public int Id {get; set;}
    public string Name {get; set;}

    // every User has zero or more Roles (many-to-many)
    public virtual ICollection<Role> Roles {get; set;}
}
class Role
{
    public int Id {get; set;}
    public string Name {get; set;}

    // every Role has zero or more Users (many-to-many)
    public virtual ICollection<User> Users {get; set;}
}
class MyDbcontext : DbContext
{
    public DbSet<User> Users {get; set;}
    public DbSet<Role> Roles {get; set;}
}

In entity framework the columns of the tables are represented by non-virtual properties. The virtual properties represent the relations between tables.

This is enough for entity framework to detect that you intended a many-to-many relationship. Entity framework will create the junction table for you, and use this table when needed.

It might be that you want other names for tables and columns, in that case you'll need fluent API or attributes. The classes however will be similar.

Get Users with their Roles ordered by Name

var result = dbContext.Users
    .Where(user => ...)                    // only if you don't want all Users
    .Select(user => new
    {
         // select only the properties that you plan to use
         Id = user.Id,
         Name = user.Name,

         Roles = user.Roles
             .Where(role => ...)           // only if you don't want all Roles
             .OrderBy(role => role.Name)
             .Select(role => new
             {   // again: select only the properties you plan to use
                 Id = role.Id,
                 Name = role.Name,
             })
             .ToList(),
    });

One of the slower parts of a query is the transport of the selected data to your local process. Hence it is wise not to select more data than you actually plan to use. If you use include to fetch the data you select all properties of the class, inclusive the foreign keys.

For example, if you fetch Teacher with Id 4, with all his Students, you know that every one of his 1000 Students will have a foreign key TeacherId with a value 4. What a waste to transport all these foreign keys!

Advice: when querying data use Select to select the properties you actually plan to use. Only use Include if you plan to change the included object

Upvotes: 1

Related Questions