C# Linq to entities - How to select only records which have all references from array

Question

I have 2 entities:

Students
   Id     Name
   1      John Doe
   2      Jack Daniels
   3      Peter Green

Languages
   Language    Student    Result
   English     1          A
   Spanish     2          A
   Italian     2          B
   English     3          B
   Spanish     1          A

I have a query to select students by language results

string[] langIds = new string[] { "English", "Italian" };
var result = (from students in context.Students
             where students.Languages.Where(s => langIds.Contains(s.Language)).Count > 0
             orderby students.Languages.Where(s => langIds.Contains(s.Language)).Sum(m => m.Result) descending
             select students);

The query gives me the list of students ordered by sum of the language results.

But this list includes students who passed as minimum one language result from the array specified, where I need to select only those students who has all language results from this array.

Any suggestion how to resolve the matter ?

Answer 1

There seems to be a many-to-many relationship between Students and Languages: every Student speaks zero or more Language, and every Language is spoken by zero or more Students.

If you've followed the Entity Framework Coding Conventions, you'll have something similar to this:

class Student
{
    public int Id {get; set;}
    ... // other properties

    // Every student speaks zero or more Languages (many-to-many)
    public virtual ICollection<Language> Languages {get; set;}
}
class Language
{
    public int Id {get; set;}
    ... // other properties

    // Every Language is spoken by zero or more Students(many-to-many)
    public virtual ICollection<Student> Students {get; set;}
}

For completeness the Dbcontext:

class MyDbContext : DbContext
{
    public DbSet<Student> Students {get; set;}
    public DbSet<Language> Languages {get; set;}
}

Because you stuck to the conventions, entity framework detects the many to many relation. You don't have to specify the junction table for this, you can use the virtual ICollection instead.

Give me all Students that speak at least all languages in object LangIds

Whenever you want to check if collection A contains at least all elements from collection B, consider checking B.Except(A).Any(). If true, then apparently some elements from B are not in A.

So if LangIds.Except(Student.Languages).Any() is true, then you know that there are languages that are not spoken by the Student. You don't want this Student. You only want those Students where Any results in false.

Once you know this, the code is very easy:

var result = dbContext.Students
    .Where(student => !langIds.Except(student.Languages).Any() // note the "!"
    .ToList();

This will probably fetch more properties than you'll actually plan to use, so consider to add a Select:

var result = dbContext.Students
    .Where(student => !langIds.Except(student.Languages).Any()
    .Select(student => new
    {
        // Select only the Student properties that you plan to use
        Id = student.Id,
        Name = student.Name,
        ...

        // Only if you plan to use all languages that the Student speaks:
        Languages = student.Languages.Select(language => new
        {
            // again: only the language properties that you plan to use:
            Id = language.Id,
            Code = language.Code,
            Abbreviation = language.Abbreviation,
            ...

            // no need to fill language.Students
        })
        .ToList(),
    });

Answer 2

Absolutely easy and obvious solution stated by Jonas Høgh in comments: Instead of

where students.Languages.Where(s => langIds.Contains(s.Language)).Count > 0

use

where students.Languages.Where(s => langIds.Contains(s.Language)).Count == langIds.Length

Thank you very much, Jonas