How to do Linq Many to Many join with or without navigation properties

var listOfStudents = db.Student.Select(x => new { Id = x.Id, Name = x.StudentName, Subjects = x.StudentsSubjects.Select(y => y.Subject) }); 

If you remove Id field from the table StudentsSubject then remove this table from the model and update your model, EF will automatically convert this table in two navigational properties Subjects and Students for Student and Subject entities respectively.

If you must leave StudentsSubject table schema intact you can use Include() method to get both students and their subjects:

var students = dbContext.Students.Include("StudentsSubject.Subject") 

With 'normal' navigation properties you could write the following:

var students = dbContext.Students.Include("Subjects") 

Sometimes you need to assembly large graphs then Include() and lazy loading can affect performance. There is a small trick for this case:

// switch off for performance DbContext.Configuration.AutodetectChangesEnabled = false; // load root entities var roots = dbContext.Parents.Where( root => %root_condition%).ToList(); // load entities one level lower dbContext.DependentEntities.Where( dependent => dependent.Root%root_condition% && %dependent_condition%).ToList(); // detect changes dbContext.ChangeTracker.DetectChanges(); // enable changes detection. DbContext.Configuration.AutodetectChangesEnabled = true; 

Now root.Dependents collections are filled for roots.

Here is the tradeoff between join redundancy (include or join) and several db requests along with the increasing complexity of requests.

With 'includes' data for joined nodes is duplicated so a chain of includes can produce enormous traffic from DB to client.
With the second approach each level requires filtering conditions of all upper levels in Where() and EF generates the query with N-1 joins for the N-th level, but there is no place for redundancy.

As far as I know EF now works fine with Contains() and conditions for parent nodes can be substituted with Contains():

// load root entities var roots = dbContext.Parents.Where( root => %root_condition%).ToList(); var rootIds = new List<int>( roots.Select( root => root.Id)); // load entities one level lower dbContext.DependentEntities.Where( dependent => %dependent_condition% && rootIds.Contains( dependent.RootId)).ToList(); 

A regular LINQ join should do the trick. Here's a reduced test case:

Public Class Student Public Property Id As String Public Property StudentName As String Public Property GPA As String End Class Public Class StudentsSubject Public Property SubjectId As String Public Property StudentId As String Public Property Id As String End Class Public Class Subject Public Property Id As String Public Property SubjectName As String End Class Sub Main() Dim students As New List(Of Student) students.Add(New Student With {.Id = "1", .GPA = "GPA1", .StudentName = "John"}) students.Add(New Student With {.Id = "2", .GPA = "GPA2", .StudentName = "Peter"}) Dim subjects As New List(Of Subject) subjects.Add(New Subject With {.Id = "100", .SubjectName = "Maths"}) subjects.Add(New Subject With {.Id = "200", .SubjectName = "Physics"}) Dim studentsSubject As New List(Of StudentsSubject) studentsSubject.Add(New StudentsSubject With {.Id = "10", .StudentId = "1", .SubjectId = "100"}) studentsSubject.Add(New StudentsSubject With {.Id = "20", .StudentId = "1", .SubjectId = "200"}) studentsSubject.Add(New StudentsSubject With {.Id = "30", .StudentId = "2", .SubjectId = "100"}) Dim listOfStudents = From st In students Join ss In studentsSubject On ss.StudentId Equals st.Id Join sb In subjects On ss.SubjectId Equals sb.Id Select st.StudentName, st.GPA, sb.SubjectName End Sub