Help with LinqtoSql

Question

Im using the Repository pattern and I want to write a method that receives a role and returns an Iqueryable of the users that belong to that role. (Im not sure if the right way would be to receive the role object or the role_id... in any case, how can I do this?? I dont like the query structure, I prefer the method structure of linq. users and roles is many to many with a users_roles join table.

     private ClasesDataContext db = new ClasesDataContext();
        public IQueryable GetByRole(Role role)
        {
            return db.Users.Where();
        }

Answer 1

If you have an instance of the Role object

public IQueryable<User> GetByRole(Role role) {
       return db.Users.Where(u => u.Role == role);
}

would work.

If you don't but just know the Id or some other property of the role something like this might be better.

public IQueryable<User> GetByRoleId(int roleId) {
   return db.Users.Where(u => u.Role.Id == roleId);
}

Answer 2

Maybe try something like:

public IQueryable<User> GetByRoleId(Role role) {
   return db.UsersRoleJoinTable.Where(ur => ur.Role == role).select(ur => ur.User);
}

Where UsersRoleJoinTable is your many-to-many join table.

Hope it helps.

Update: the select(ur => ur.User) is telling linq that for every row returned by "db.UsersRoleJoinTable.Where(ur => ur.Role == role)" we want to get the user associated with the UsersRoleJoinTable object. If you wanted a list of user ids instead, you could tell linq to select only user.id by doing select(ur => ur.id). Think of linq's select as a some sort of "for every row do this and put it in the list returned instead of the original row"

There is one downside to this approach tho, I believe in this case Linq is generating the sql to get the rows from the Join table (UsersRoleJoinTable) and then for every row returned, is executing another query to look up the User. I might be wrong on this, so to check the SQL generated by Linq do:

string sql_query = db.UsersRoleJoinTable.Where(ur => ur.Role == role).select(ur => u.User).ToString();

and then print the value of sql_query or watch it in debug mode. If Linq is in fact doing multiple queries, then I think the best solution is to create a view or stored procedure in SQL Server to get the users associated with the role and then add the view or stored procedure to Visual Studio designer so that you can call the view like:

db.GetUsers(role_id) //if using a GetUsers stored procedure

or

db.UsersByRoleView.where(ur => ur.role_id == passed_role_id) //if using a UsersByRoleView view