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

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

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

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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related