Querying Many to Many relationships Entity Framework (doing wrong?? )

Question

I've been doing some research on this topic and figure out a way to achieve this queries in my project but I'm not sure if something here is wrong. please help.

in summary I've created the entities like this:

class Student
    {
    public int StudentId { get; set; }
    public string Name { get; set; }
    
    public ICollection<Courses> Courses {get;set;} //or public List <Courses> {get;set;}
    }
    
    class Course
    {
    public int CourseId { get; set; }
    public string Name { get; set; }
    
    public ICollection<Students> Students {get;set;} //or public List<Students> {get;set;}
    }

    // We can see here that the database creates the Join Table Correctly

What I want to do:

Display in a grid view each student and for each of the students display the courses in which they are enrolled.

If I made a simple query like

dbContex.Students.ToList(); 

and we look at the list the Collection of courses value is null. What is happening here? Shouldn't EF map this and make a query to SQL to get the info?

After this y could not solve the problem because the info that I found was using other approach of the framework (Diagram First, I think) and they set up things in the entities diagram.

How did I work out the problem:

Find out in a WordPress Post a Query that I haven't tried out and add some other lines of code to achieve what I wanted:

aux_S = contexto.Students.ToList();

 foreach(var element in aux_S)
         
   {
                
element.Courses= contexto.Courses.Where(c => c.Students.Any(s => s.StudentId == element.StudentId)).ToList();
          
  }

// I know I can make a projection to dismiss all the fields that I do not need, this is just to try it out

Am I wrong doing this?

It worked, but how is it possible?

Answer 1

One of the slower parts of a database query is the transfer of the data to your machine. So it is good practice to transfer only the data you plan to use.

When you use LINQ in entity framework, using Queryable.Select is a good way to specify exactly what data you want to transfer. This is usually done just before your final ToList / ToDictionary / FirstOrDefault / Single / ...

You want all Students, each with all his Courses. If you look at your tables, you'll see that there is more data in the tables then you want. For instance, each Student has an Id, each of his Courses have the same value for StudentId. So if a Student attends 20 Courses, you would have transferred the same value for StudentId 21 times.

So to make your query efficient: Select only the Properties of Students you plan to use, with only the Properties of the Courses of these Students you are interested in.

This will automatically solve your problem:

var result = myDbcontext.Students

    // if you don't want all Students, use a Where:
    .Where(student => student.City = "Guadalajara")

    // Select only the properties you plan to use:
    .Select(student => new
    {
        Id = student.Id,
        Name = student.Name,
        Birthday = student.Birthday,
        Address = new 
        {
            Street = student.Street,
            City = student.City,
            ...
         }

         Courses = student.Courses
             // if you don't want all courses: use a where
             .Where(course => course.Start.Year == 2018)
             // again: select only the properties you plan to use
             {
                  Name = course.Name,
                  Location = course.Location,
                  ...
                  // One of the useless properties to transfer:
                  // StudentId = course.StudentId
              })
              .ToList();
      });