How to Load Related Data in MVC with complex table structure

Question

I have several classes which I will shorten for brevity. Below they are listed with the related properties/fields associated with this question.

public class AcademicYear
{
    [Key]
    public int AcademicYearId { get; set; }
}

public class Division
{
    public Division()
    {
        this.CareerFields = new HashSet<CareerField>();
    }

    [Key]
    public int    DivisionId     { get; set; }

    // Foreign Keys
    public int    AcademicYearId { get; set; }

    // Navigation Properites
    public virtual AcademicYear AcademicYear { get; set; }

    public virtual ICollection<CareerField> CareerFields { get; set; }
}

public class CareerField
{
    public CareerField()
    {
        this.Clusters    = new HashSet<Cluster>();
    }

    [Key]
    public int    CareerFieldId   { get; set; }

    // Foreign Keys
    public int    AcademicYearId  { get; set; }
    public int    DivisionId      { get; set; }


    // Navigation Properties
    public virtual AcademicYear AcademicYear { get; set; }
    public virtual Division     Division     { get; set; }

    public virtual ICollection<Cluster>    Clusters    { get; set; }
}

public class Cluster
{
    public Cluster()
    {
        this.CareerFields = new HashSet<CareerField>();
    }

    [Key]
    public int ClusterId { get; set; }
    {
        // Foreign Keys
        public int    AcademicYearId { get; set; }

        // Navigation Properties
        public virtual AcademicYear AcademicYear { get; set; }

        public virtual ICollection<CareerField> CareerFields { get; set; }
    }
}

public class Pathway
{
    public Pathway()
    {
        this.CareerMajors = new HashSet<CareerMajor>();
    }

    [Key]
    public int    PathwayId      { get; set; }

    // Foreign Keys
    public int    AcademicYearId { get; set; }
    public int    ClusterId      { get; set; }

    // Navigation Properties
    public virtual AcademicYear AcademicYear { get; set; }
    public virtual Cluster      Cluster      { get; set; }

    public virtual ICollection<CareerMajor> CareerMajors { get; set; }
}

public class CareerMajor
{
    public CareerMajor()
    {
        this.Courses        = new HashSet<Course>();
    }

    [Key]
    public int    CareerMajorId     { get; set; }
    public string FirstYearOffered  { get; set; }

    // Foreign Keys
    public int    AcademicYearId    { get; set; }
    public int    PathwayId         { get; set; }

    // Navigation Properties

    [HiddenInput(DisplayValue = false)]
    public virtual AcademicYear      AcademicYear      { get; set; }
    public virtual Pathway           Pathway           { get; set; }

    public virtual ICollection<Course>         Courses        { get; set; }

}

public class Course
{
    public Course()
    {
        this.CareerMajors  = new HashSet<CareerMajor>();
    }

    [Key]
    public int    CourseId       { get; set; }

    // Foreign Keys
    public int    AcademicYearId { get; set; }
    public int?   InstructorId   { get; set; }

    // Navigation Properties
    public virtual ICollection<CareerMajor>  CareerMajors  { get; set; }

    public virtual AcademicYear AcademicYear { get; set; }
}

I also have a ViewModel class to load all this for my controller

public class CMSIndex
{
    public IEnumerable<Division> Divisions { get; set; }
    public IEnumerable<CareerField> CareerFields { get; set; }
    public IEnumerable<Cluster> Clusters { get; set; }
    public IEnumerable<Pathway> Pathways { get; set; }
    public IEnumerable<CareerMajor> CareerMajors { get; set; }
    public IEnumerable<Course> Courses { get; set; }
}

I have a razor cshtml page (the auto CRUD defined Index page) where I start with the Division and I can load the related CareerFields. However, when I try to load Clusters, I get the error message

Server Error in '/' Application. Value cannot be null. Parameter name: source Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentNullException: Value cannot be null. Parameter name: source

Here is my index method from the controller (note the commented out lines are what I have tried as well)

public ViewResult Index(Int32? divisionID, Int32? careerFieldID, Int32? clusterID, Int32? pathwayID, Int32? careerMajorID, Int32? courseID)
    {
        var viewModel = new CMSIndex();

        viewModel.Divisions = db.Divisions
            .Include(d => d.AcademicYear)
            .Include(d => d.CareerFields)//
            .Include(d => d.CareerFields.Select(cf => cf.Clusters))
            //.Select(c => c.Clusters.Select(cl => cl.Pathways.Select(p => p.CareerMajors.Select(cm => cm.Courses)))))
            .OrderBy(d => d.DivisionName);

        if (divisionID != null)
        {
            ViewBag.DivisionID = divisionID.Value;
            viewModel.CareerFields = viewModel.Divisions.Where(d => d.DivisionId == divisionID.Value).Single().CareerFields;
        }

        if (careerFieldID != null)
        {
            ViewBag.careerFieldID = careerFieldID.Value;
            //viewModel.Clusters = viewModel.CareerFields.Where(cf => cf.CareerFieldId == careerFieldID.Value).Single().Clusters;
            //viewModel.Clusters = viewModel.CareerFields.SelectMany(cf => cf.Clusters);
            viewModel.Clusters = viewModel.CareerFields.Where(cf => cf.CareerFieldId == careerFieldID.Value).Single().Clusters;
        }

        return View(viewModel);
    }

I am following the Contoso tutorial on loading related data. I seem to be able to load 1:m relationships, but I am unsure how to do this with m:m (e.g. CareerFields and Clusters).

And, I do have an initializer that has loaded data to pull and I am passing an ID (e.g. careerFieldID).

Where the exception is being thrown is on the line:

viewModel.Clusters = viewModel.CareerFields.Where(cf => cf.CareerFieldId == careerFieldID.Value).Single().Clusters;

And each commented variation.

Any help would be extremely appreciated.

Answer 1

Not saying that this is the issue, but have you checked the values are not null? In the line

viewModel.Clusters = viewModel.CareerFields
    .Where(cf => cf.CareerFieldId == careerFieldID.Value).Single().Clusters;

is it possible that viewModel.CareerFields is null?

As some side notes:

• With your null checks when you have nullables it's better to use their HasValue method e.g.

  if (divisionID.HasValue)
  {
      ...
  }
  

• The use of Single() can be a bit temperamental and can throw exceptions if no elements exist or if more than one exist. First() can be used to handle more than one (but this throws if none match), SingleOrDefault() will handle zero or one result and FirstOrDefault() will handle most things.

• You can save a expression by putting your lambda in the single e.g.

  viewModel.CareerFields = viewModel.Divisions
      .Single(d => d.DivisionId == divisionID.Value).CareerFields;
  

Edit: I think the issue you are having is the same as in This Answer could you try changing your classes collections so the are initialised in the get when null. E.g for Division

public class Division
{
    [Key]
    public int    DivisionId     { get; set; }

    // Foreign Keys
    public int    AcademicYearId { get; set; }

    // Navigation Properites
    public virtual AcademicYear AcademicYear { get; set; }

    private ICollection<CareerField> careerFields;
    public virtual ICollection<CareerField> CareerFields { 
        get { return careerFields ?? (careerFields = new HashSet<CareerField>()); }
        set { careerFields = value; } 
    }
}

That's actually my preferred method anyway, this imposes it's own problem with your code as the fact that CareerFields was null means that for the given division, there are no associated CareerFields. This means that when your Single() call is hit it will throw the exception The source contains no elements as CareerFields will contain a empty hashset. This could be fixed by a change to SingleOrDefault().