LINQ Include a child array of select

c# entity-framework-6 linq

Question

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);
1
3
3/2/2020 8:40:10 PM

Accepted Answer

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;
}
1
3/2/2020 2:08:36 PM

Popular Answer

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;
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow