Query Users who have all requested records in two tables

Question

Data structure looks like:

User(id)

UserApp(user_id, app_id)

UserSkill(user_id, skill_id)

Using linq-to-sql or EF, how would I construct a query to elegantly return only users who possess every requested app and skill?

In addition, how would I adjust the query to return any user who possesses at least one of the requested apps or skills? Essentially an OR vs AND (above).

UPDATE 1:

So I think we're close. Basically I want to only return users who have ALL the requested apps and skills. If we have two arrays of requested ids for skills and apps:

int[] requestedAppIDs // [1, 2, 3]
int[] requestedSkillIDs // [4, 5, 6]

I would only want to return a user if they have apps 1,2,3 AND skills 4,5,6.

var usersWithAllSelectedAppsAndSkills = 
    context.Users
    .GroupJoin(context.UserApp,
        k => k.id,
        k => k.user_id,
        (o, i) => new { User = o, UserApps = i })
    .GroupJoin(context.UserSkill,
        k => k.User.id,
        k => k.user_id,
        (o, i) => new { User = o.User, o.UserApps, UserSkills = i })
    .Where(w => !requestedAppIDs.Except(w.UserApps.Select(x => x.app_id).ToArray()).Any() && !requestedSkillIDs.Except(w.UserSkills.Select(x => x.skill_id).ToArray()).Any())
    .Select(s => s.User)
    .ToList();

Obviously, LINQ does not know how to translate the UserSkills.Select().ToArray()'s in my Where() to SQL. How can I accomplish this?

And, secondarily the OR solution as well (user has any one of the requested apps or skills).

Answer 1

This will do the job as long as the user_id – app_id and user_id – skill_id values in the UserApp and UserSkill tables are unique.

var requestedSkillIDs = new[] { 4, 5, 6 };
var skillCount = requestedSkillIDs.Length;
var requestedAppIDs = new[] { 1, 2, 3 };
var appCount = requestedAppIDs.Length;

using (var context = new TestContext()) {
    context.Database.CreateIfNotExists();

    var appQuery = context.UserApp.Where(p => requestedAppIDs.Contains(p.AppId))
                        .GroupBy(p => p.UserId)
                        .Where(p => p.Count() == appCount);

    var skillQuery = context.UserSkill.Where(p => requestedSkillIDs.Contains(p.SkillId))
                        .GroupBy(p => p.UserId)
                        .Where(p => p.Count() == skillCount);

    var result = from a in appQuery
                    join s in skillQuery on a.Key equals s.Key
                    join u in context.Users on s.Key equals u.Id
                    select u;


    var users = result.ToList();
}

Answer 2

Here's one way to do it, I hope i got all the syntax right :)

   using (var context = new YourContext())
           {
              var usersWithAllSkills = context.User
                                 .Where(w => w.id == yourId)
                                 .Join(context.UserApp,
                                        k => k.id,
                                        k => k.user_id,
                                        (o,i) => o)
                                 .Join(context.UserSkill,
                                        k => k.id,
                                        k => k.user_id,
                                        (o,i) => o)
                                 .ToList();

         var usersWithAnySkill = context.User
                            .Where(w => w.id == yourId)
                            .GroupJoin(context.UserSkill,
                                        k => k.id,
                                        k => k.user_id,
                                        (o,i) => new { User = o, UserSkills = i })
                            .GroupJoin(context.UserApp,
                                        k => k.User.id,
                                        k => k.user_id,
                                        (o,i) => new { User = o.User, o.UserSkills ,UserApps = i  })
                            .Where(w => w.UserSkills != null || w.UserApps != null)
                            .Select(s => s.User)
                            .ToList();

       }