Query Users who have all the requested records in two tables

Question

The data structure is as follows:

user (identifier)

UserApp (user_id, app_id)

UserSkill (user_id, skill_id)

Using linq-to-sql or EF, how would I build a query to gracefully return only users who have every requested application and skills?

Also, how could I customize the query to return any user who has at least one of the requested applications or skills? Essentially OR vs AND (above).

UPDATE 1:

Therefore, I think we are close. Basically, I only want to return users who have ALL the required applications and skills. If we have two arrays of requested identifiers for skills and applications:

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

I would only like to return the user if they have applications 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 UserSkills.Select (). ToArray () in my Where () in SQL. How can i do this?

And secondly, the OR solution (the user has any of the requested applications or skills).

Answer 1

This will do the job if 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 is one way to do this, hope I got the whole syntax :)

  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(); } 

Answer 3

In the first case (AND), you just need to make an inner join, as shown below:

 from t1 in db.UserApp join t2 in db.UserSkill on t1.user_id equals t2.user_id where t1.app_id == "someID" && t2.skill_id == "someID" select new { t1.user_id,t1.user_app_id, t2.user_skill} 

For the second case, just replace & (AND) with || (OR).

Answer 4

There is a more direct way to record the required queries using L2E. To write these queries, you must forget thinking in SQL and start thinking in LINQ.

In the first case, find users who have all the skills:

 var usersWithAll = ctx.Users2.Where(u => appIds.All(aid => u.Apps.Any(a => a.AppId == aid)) && skillIds.All(sid => u.Skills.Any(s => s.SkillId == sid)) ); 

Translated how: to get users, where for all applications the user has an application with this application identifier and for all skills, the user has at least one skill with this id

And, in the second case, users who have any applications and any skills:

 var usersWithAny = ctx.Users2.Where(u => appIds.Any(aid => u.Apps.Any(a => a.AppId == aid)) && skillIds.Any(sid => u.Skills.Any(s => s.SkillId == sid)) ).ToList(); 

Translated how: to get users where, for at least one application, the user has an application with this application identifier and for any skills, the user has at least one skill with this id

If you run this test class, you will also see a executed query (note that for this I use the Log Database property. I think it is available only from EF6 on).

 namespace Tests { [TestClass] public class CheckSeveralRelationsAtOnce { [TestMethod] public void HasAllAppsAndSkills() { int[] appIds = {1, 2, 3}; int[] skillIds = {6, 7, 8}; using (var ctx = new MyDbContext()) { ctx.Database.Log = Console.Write; var usersWithAll = ctx.Users2.Where(u => appIds.All(aid => u.Apps.Any(a => a.AppId == aid)) && skillIds.All(sid => u.Skills.Any(s => s.SkillId == sid)) ).ToList(); Assert.IsNotNull(usersWithAll); } } [TestMethod] public void HasAnyAppsOrSkill() { int[] appIds = { 1, 2, 3 }; int[] skillIds = { 6, 7, 8 }; using (var ctx = new MyDbContext()) { ctx.Database.Log = Console.Write; var usersWithAny = ctx.Users2.Where(u => appIds.Any(aid => u.Apps.Any(a => a.AppId == aid)) && skillIds.Any(sid => u.Skills.Any(s => s.SkillId == sid)) ).ToList(); Assert.IsNotNull(usersWithAny); } } } } 

Answer 5

I believe the codeworx answer is correct for users to get all skills / applications

Aside - I recently answered the same question with a pure SQL solution (SQL Server), which could be converted into a stored procedure (with a table parameter) - see here if this is interesting. This will work better for a large number of values. The Entity structure will turn each skill / application into a list into its own SQL parameter, which is much slower.

Unfortunately, the Entity framework does not actually support table parameters. Although you can use a connection to the entity infrastructure to directly call a stored procedure with a table parameter (as per this article

Let's get back to the question ... I will add a (simpler) query to select a user with ANY of the skills and ANY of the applications:

 var result = from u in context.Users join _a in ( from a in context.UserApp where requestedAppIDs.Contains(a.AppId) select a.UserId; ) on u.Id equals _a into aGrp join _s in ( from s in context.UserSkill where requestedSkillIDs.Contains(s.SkillId) select s.UserId; ) on u.Id equals _s into sGrp where aGrp.Any() && sGrp.Any() select u; 

And just for completeness - ALL solution again:

 var skillCount = requestedSkillIDs.Length; var appCount = requestedAppIDs.Length; var result = from u in context.Users join _a in ( from a in context.UserApp where requestedAppIDs.Contains(a.AppId) select a.UserId; ) on u.Id equals _a into aGrp join _s in ( from s in context.UserSkill where requestedSkillIDs.Contains(s.SkillId) select s.UserId; ) on u.Id equals _s into sGrp where aGrp.Count() == appCount && sGrp.Count() == skillCount select u; 

and finally, an example where the main request object is fixed, but you can add different values ​​if the sentences depend on the AND / OR requirement

 bool onlyReturnWhereAllAreMatched = false; var skillCount = requestedSkillIDs.Length; var appCount = requestedAppIDs.Length; IQueryable<User> result; var query = from u in context.Users join _a in ( from a in context.UserApp where requestedAppIDs.Contains(a.AppId) select a.UserId; ) on u.Id equals _a into aGrp join _s in ( from s in context.UserSkill where requestedSkillIDs.Contains(s.SkillId) select s.UserId; ) on u.Id equals _s into sGrp select new {u, aCount = aGrp.Count(), sCount = sGrp.Count()}; if (onlyReturnWhereAllAreMatched) { result = from x in query where x.aCount == appCount && x.sCount == skillCount select xu; } else { result = from x in query where x.aCount > 0 && x.sCount > 0 select xu; }