SQL to linq for Many to Many Relation (C# ASP.net MVC)

Question

I'm looking for some help to be able to transcript some LINQ from a SQL query :

Here is a quick look of my database :

Entity framework "simplify" my "etudiant" model like this :

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<etuResult> etuResult { get; set; }

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<cours> cours { get; set; }

I'm able to find the "libellé" of my "cours" where the "etudiant_id" == 1 in SQL like this :

select c.libelle
from cours c 
where c.cours_id in (
    select ec.cours_id
    from etuCours ec
    where ec.etudiant_id in (
        select e.etudiant_id
        from etudiant e
        ))

But i dont find how to make the query with linq (i use LinqPad 4 for my test)

Thanks for you help in advance

Answer 1

Why not call the collection directly on the entity? If it is mapped then the filtering happens automatically. No need to create another query.

var courses = myEtudiantInstance.cours;

If you want the libelle then

var libelles = myEtudiantInstance.cours.Select(c => c.libelle);

This requires either lazy loading to be enabled OR use Include when you get the Etudiant instance on the collection.

var libelles = dbContextInstance.Etudiants
  .Include(e => e.cours)
  .Single(e => e.EtudiantId == 1) // will throw exception if entity not found
  .cours.Select(c => c.libelle); // get all libelle's