CODEWITHSUNDEEP
c#linqentity-framework-core
Stian
Stian

Reputation: 1602

Could/should these these three queries be combined into one?

I have these three steps to make a list of users that are in a group, but not in the current project:

1: Get all the users in the current Group:

List<ApplicationUser> allGroupUsers = await db.UsersInGroups
    .Where(g => g.GroupId == groupId)
    .Select(u => u.User)
    .ToListAsync();

2: Get all the users in the current Project:

List<ApplicationUser> projectUsers = await db.UsersInProjects
    .Where(p => p.ProjectId == id)
    .Select(u => u.User)
    .ToListAsync();

3: Make a list of Group users that are not in the Project:

List<ApplicationUser> availableUsers = allGroupUsers.Except(projectUsers).ToList();

These are all the relevant models:

public class UserInGroup
{
    public int Id { get; set; }
    public Guid UserId { get; set; }
    public int GroupId { get; set; }
    public bool IsAdmin { get; set; }
    public ApplicationUser User { get; set; }
    public Group Group { get; set; }
}

public class UserInProject
{
    public int Id { get; set; }
    public Guid UserId { get; set; }
    public int ProjectId { get; set; }
    public ApplicationUser User { get; set; }
    public Project Project { get; set; }
}

public class ApplicationUser : IdentityUser<Guid>
{
    public int? UserInGroupId { get; set; }
    public UserInGroup UserInGroup { get; set; } // Can only be in one group
    public List<UserInProject> UserInProjects { get; set; } // Can be in many projects
    // some more properties
}

public class Group
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<UserInGroup> UsersInGroup { get; set; }
    public List<Project> Projects { get; set; }
    // some more properties
}

public class Project
{
    public int Id { get; set; }
    public int GroupId { get; set; }
    public int? ProjectGroupId { get; set; }
    public string Title { get; set; }
    public Group Group { get; set; }
    public List<UserInProject> ProjectUsers { get; set; }
    // some more properties
}

Is it trivial to combine these three queries into one, saving one trip to the database, and making the code cleaner?

Upvotes: 0

Views: 34

Answers (1)

Jakub Kozera
Jakub Kozera

Reputation: 3473

Try this query:

            List<ApplicationUser> availableUsers = await db.ApplicationUser
                .Include(u => u.UserInProjects)
                .Where(u => (u.UserInGroupId == groupId && !u.UserInProjects.Any(p => p.ProjectId == id)
                             || (u.UserInGroupId != groupId && u.UserInProjects.Any(p => p.ProjectId == id))))
                .ToListAsync();

so either get the users that are in that group, and not in project, or the other way around - not in group, but in a project

Upvotes: 1

Related Questions