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

Igor · Accepted Answer · 2017-02-09 14:14:59Z

0

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

answered Feb 9, 2017 at 14:14

Igor

62.5k10 gold badges111 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bap Over a year ago

I didnt get everything on how you did it but you solved my problem ! Thanks, i will continue learning about this language :)

Community · Accepted Answer · 2017-05-23 12:00:14Z

0

Edit : in case this can be usefull to someone :

It's simpler to use a DAL folder (for Data Access Layer) as show on the following tutorial (under the subtitle "Create the Database Context").

In case you don't understant what's the purpose on this folder, I suggest you this link.

If i made my MVC better and follow this tutorial, i wouldn't be in trouble for my Linq Queries

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Feb 28, 2017 at 13:55

bap

251 silver badge8 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related