Concatenating lists inside a LINQ query

My data structure is set up this way

• A user takes a number of modules
• A module contains a number of courses

Here's how the relationship looks like:

How do I get a list of courses the user takes?

The query I have now is:

  var courses = (from ClassEnrollment enrolment in entities.ClassEnrollment
                       where enrolment.UserID == UserID
                       join Module module in entities.Module
                         on enrolment.ModuleID equals module.ID

                         select module.Course
                     ).ToList();

However, this do开发者_JAVA百科esn't result in a list of courses, but rather a list of list of courses.

How can I flatten this query to a list of distinct courses?

According to your data structure screenshot, you have a one-to-many relationship between the ClassEnrollment and Module, as well as navigational property called Module. You also have a many-to-many relationship between Module and Course, but the navigational property should be called Courses. Given your code, you want something like this:

var courses = entities.
              ClassEnrollment.
              Where(e => e.UserID == UserID).
              SelectMany(e => e.Module.Courses).
              ToList();

Your question, however, mentions a user: A user takes a number of modules, How do I get a list of courses the user takes?. I don't see any User entity anywhere else, though, so it would be nice if you could clarify. Are you using LINQ-to-SQL, btw?

Something like this:

var courses = from ClassEnrollment enrolment in entities.ClassEnrollment
              from module in entities.Module
              where enrolment.ModuleID equals module.ID && enrolment.UserID equals UserID
              select module.Course

Use SelectMany.

You can use

courses.SelectMany(c => c);

In your query you don't need explicitly specify the type for the range variables

Or you can join course to the query

var query = from enrolment in entities.ClassEnrollment
            join module in entities.Module on enrolment.ModuleID equals module.ID
            join course in entities.Course on module.CourseID equals course.ID
            where enrolment.UserID == UserID
            select course;

var course = query.ToList();