Retrieve users (with certain roles) not working on a certain project LINQ ASP.NET MVC 5

Question

I''m trying to retrieve users who are (Junior developers OR Team Leaders) and are not working on a certain project with the given id in the action parameter.

i'm using ASP.NET Identity to store the users

this is the view model i'm using in the action.

AssignedProjectViewModel

public class AssignedProjectViewModel
{
    public ApplicationUser User { get; set; }
    public Works_on Works_on { get; set; }
    public Project Project { get; set; }
}

in the action below. usersinRole retrieves a list of ID of users who are Junior Developers & Team Leaders. This works fine.

Then i'm trying to look for users who are of the specified roles(Junior Developers and Team Leader) and are not working on a certain project with the given id

SearchTLandJD action

public ActionResult SearchTLandJD(int projectid)
    {

        var usersinRole = context.Users.Where(u => u.Roles.Any(r => r.RoleId.Equals("d8bff653-2a05-4159-948d-6eabd17de838") || r.RoleId.Equals("3b12d84f-0ecc-4e04-8e9c-ff3d441e2ce0"))).Select(u => u.Id).ToList();

        var searchquery = from p in DBcontext.Projects
                        join work in DBcontext.Works_on on p.Id equals work.ProjectId
                        join member in DBcontext.Users on work.UserId equals member.Id
                        where !(from w in context.Works_on
                                where w.ProjectId == projectid
                                select w.UserId).Contains(member.Id) && usersinRole.Contains(member.Id)
                        select new AssignedProjectViewModel
                        {
                            Project = p,
                            Works_on = work,
                            User = member
                        };


        var users = searchquery.ToList();
        return View(users);
    }

how do i achieve that? thanks in advance!

Answer 1

First, hard-coding the role ids, is a hugely bad idea. Start by first selecting the roles you want to work with:

var roleIds = db.Roles.Where(m => m.Name == "Junior developers" || m.Name == "Team Leaders").Select(m => m.Id);

Then, you can fetch the users:

var users = db.Users.Where(u =>
    u.Roles.Any(r => roleIds.Contains(r.RoleId)) && 
    !u.Works_on.Any(w => w.ProjectId == projectId));