EFCore: Query multiple related data

entity-framework-core razor

Question

I'm trying to project related data but keep getting errors.

I have a list of projects, for each project there may be multiple Baselines set (or none), each Baseline contains multiple Milestones. The projected list of Projects should contain a property which contains a particular milestone in the most recently set baseline.

My model is in SQL through EFCore:

public class Project
{
    public int ProjectID { get; set; }
    public string Name { get; set; }
    public ICollection<Baseline> Baselines { get; set; }
}

public class Baseline
{
    public int BaselineID { get; set; }
    public int ProjectID { get; set; }
    public Project Project { get; set; }
    public string Name { get; set; }
    public DateTime DateSet {get; set;}
    public string Description { get; set; }
    public ICollection<BaselineDate> BaselineDates { get; set; }
}

public class BaselineDate
{
    public int BaselineDateID { get; set; }
    public int BaselineID { get; set; }
    public Baseline Baseline { get; set; }
    public int MilestoneTypeID { get; set; }
    public MilestoneType MilestoneType { get; set; }
    public DateTime Date { get; set; }
    public string Comment { get; set; }
}

In my controller, I define the projected class:

public class ProjectInfo
{
    public int ProjectID { get; set; }
    public string ProjectName { get; set; }
    public DateTime? ProjectStart { get; set; }
}

public IList<ProjectInfo> ProjectInfoList { get; set; }

And then in a function I try to use efcore to query the models:

ProjectInfoList = await _context.Project
     .Where(project => project.Branch == Branch)
     .Select(project => new ProjectSummary
         {
             ProjectID = project.ProjectID,
             ProjectName = project.Name,
             ProjectStart = project.Baselines
                 .DefaultIfEmpty(new Baseline { BaselineDates = new List<BaselineDate>() })
                 .OrderByDescening(b => b.DateSet)
                 .FirstOrDefault()
                 .BaselineDates
                 .Where(d => d.Comment == "Project Start")
                 .FirstOrDefault()
                 .Date
          }
       .AsNoTracking()
       .ToListAsync();

This works fine as long as there is a Baseline for each Project. However when a project exists without a Baseline a null exception is thrown.

ArgumentNullException: Value cannot be null. Parameter name: source System.Linq.Enumerable.Where(IEnumerable source, Func predicate)

I try adding in .DefaultIfEmpty(new Baseline()) but it throws more exceptions.

1
1
12/17/2018 11:23:19 PM

Accepted Answer

Keep in mind that Entity Framework translates the whole LINQ expression into SQL (well, more precisely, tries to do that). Since there is no notion of null references in SQL you can safely use an expression that in C# code (or: LINQ-to-objects) would have thrown a null reference exception.

But that aside, the offending sub query can be rewritten so that even in LINQ-to-objects it won't throw an exception if the collection properties aren't null:

ProjectInfoList = await _context.Project
     .Where(project => project.Branch == Branch)
     .Select(project => new ProjectSummary
         {
             ProjectID = project.ProjectID,
             ProjectName = project.Name,
             ProjectStart = (from bl in project.Baselines
                from bd in bl.BaselineDates
                where bd.Comment == "Project Start"
                orderby bl.DateSet descending, bd.Date descending
                select (DateTime?)bd.Date).FirstOrDefault()
          }
       .AsNoTracking()
       .ToListAsync();

I do this in query syntax for better readability. The from ... from construct is SelectMany in method syntax. SelectMany syntax, when data from both parent and children is required (bl.DateSet, bd.Date), is pretty awkward.

1
12/19/2018 6:22:06 PM


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