LINQ Include a child array of select

user_6787447 0
Viewed 1286

I have the following LINQ query which is working great :

public IList<Course> GetEmployeeCourses(int id) {
  var employeeCourses = Context.Employees
    .Where(e => e.Id == id)
    .SelectMany(e => e.employeeCourses.Select(ec => ec.Course))
    .ToList();

  return employeeCourses;
}

The issue is, I now need to also include a child array (Urls) of the selected Course when returning the Course array.

Something like :

public IList<Course> GetEmployeeCourses(int id) {
  var employeeCourses = Context.Employees
    .Where(e => e.Id == id)
    .SelectMany(e => e.employeeCourses.Select(ec => ec.Course))
    .Include(c => c.Urls)
    .ToList();

  return employeeCourses;
}

With it returning JSON like this : (The Course model has over 15 properties though)

[
  {
     "Name": "Course1",
     "Type": "Maths",
     "Location": "Block C",
     "Urls": [
          {
            "Link": "https://url1forcourse1.com"
          },
          {
            "Link": "https://url2forcourse1.com"
          }
      ]
  }
  {
     "Name": "Course2"
     "Type": "Computer Science",
     "Location": "Block A",
     "Urls": [
          {
            "Link": "https://url1forcourse2.com"
          },
          {
            "Link": "https://url2forcourse2.com"
          }
      ]
  },
  {
     "Name": "Course3"
     "Type": "The Art of Dish Washing",
     "Location": "Block E",
     "Urls": [
          {
            "Link": "https://url1forcourse3.com"
          },
          {
            "Link": "https://url2forcourse3.com"
          }
      ]
  }
]

How would I achieve this in the most efficient way? I cant seem to get the child array called 'Urls' at all at the moment, its always null.

Also, i'm using Fluent API with this EmployeeCourse config:

ToTable("EmployeeCourses");

HasKey(q => new { q.CourseId, q.Employee.Id})

HasRequired(x => x.Employee)
   .WithMany(x => x.EmployeeCourses)
   .HasForeignKey(x => x.CourseId);

HasRequired(x => x.Course)
   .WithMany(x => x.EmployeeCourses)
   .HasForeignKey(x => x.EmployeeId);
entity-framework-6 linq c#
2 Answers

From Eagerly loading multiple levels:

Eager loading is the process whereby a query for one type of entity also loads related entities as part of the query. Eager loading is achieved by use of the Include method.

You can try like this, therefore it allows you to load all EmployeeCourses and related Courses

public IList<Course> GetEmployeeCourses(int id) {
  var employeeCourses = Context.Employees
    .Where(e => e.Id == id)
    .Include(e => e.employeeCourses.Select(ec => ec.Course))
    .SelectMany(e => e.employeeCourses.Select(ec => ec.Course))
    .ToList();

  return employeeCourses;
}
user_55197090

You may like to use chained SelectMany as:

public IList<Course> GetEmployeeCourses(int id)
{
  var employeeCourses = Context.Employees
                        .Where(e => e.Id == id)
                        .SelectMany(e => e.employeeCourses
                        .SelectMany(ec => ec.Course))
                        .Include(c => c.Urls)
                        .ToList();



    return employeeCourses;
}
user_81764780
Related