CODEWITHSUNDEEP
.netasp.net-mvclinqentity-frameworklinq-to-sql
Prabhu
Prabhu

Reputation: 13335

LINQ to SQL: How to check if any item in one entity collection exists in another entity collection?

I'm using MVC2 and Entity Framework.

I have 2 Entity Collections and I need to compare them and check if they have any items in common. For example, say I have EntityCollection<Candidate> and EntityCollection<Job>. I'm trying to return all candidates that have a skill that is listed in the job's preferred skills. Is this correct:

public IQueryable<Candidate> GetMatchingCandidates(Job job)
{                
     return from candidate in _db.Candidates
     where (candidate.CandidateSkills.Where(c => job.JobPreferredSkills.Any(j => j.SkillId== c.SkillId)).Count() > 0) 
     select candidate;                                
}

Similarly, I'd also like to get candidates that have ALL skills that are listed in the preferred skills.

Upvotes: 2

Views: 2123

Answers (1)

BrokenGlass
BrokenGlass

Reputation: 160942

I'd use Any() in the first case:

    public IQueryable<Candidate> GetMatchingCandidates(Job job)
    {
        return from candidate in _db.Candidates
               where (candidate.CandidateSkills.Any(c => job.JobPreferredSkills.Any(j => j.SkillId == c.SkillId)))
               select candidate;
    }

Then use All() for your second case (all skills have to be in the preferred skills)

    public IQueryable<Candidate> GetMatchingCandidates(Job job)
    {
        return from candidate in _db.Candidates
               where (candidate.CandidateSkills.All(c => job.JobPreferredSkills.Any(j => j.SkillId == c.SkillId)))
               select candidate;
    }

Upvotes: 3

Related Questions