bap bap -4 years ago 138
C# Question

SQL to linq for Many to Many Relation (C# MVC)

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 :
database schema

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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download