ToDictionary Querying from second table returns Null

Question

I have two models as below:

public class Complaint
{
    [Key]
    public int COMP_ID { get; set; }
    public Nullable<DateTime> Received_DT { get; set; }
    public virtual ICollection<CHECKLIST> CHECKLISTs { get; set; }
}


public class CHECKLIST
{
    [Key]
    public int CL_ID { get; set; }
    public int EmpID { get; set; }
    public virtual COMPLAINT Complaints { get; set; }
}

I have a repository that queries and returns counts of all the checklist entered in by EMPID but when I add another filter from the parent table of .Where, it returns null. When I take out the .Where clause from the parent table, it works just fine.

Edit Attempted including the table

public Dictionary<int, int> GetAllChecklistCount()
    {
        try
        {
            return _context.Checklists
                .Where(t => t.Complaints.Received_DT.Value.Year == 2016) 
                .Include(t => t.Complaints)
                .GroupBy(a => a.EmpID)
                .ToDictionary(g => g.Key, g => g.Count());
        }
        catch (Exception ex)
        {
            _logger.LogError("Could not get am with checklist", ex);
            return null;
        }
    }

Error being thrown

Exception thrown: 'System.ArgumentException' in mscorlib.dll System.ArgumentException: Expression of type System.Func2[Microsoft.Data.Entity.Query.EntityQueryModelVisitor+TransparentIdentifier2[CRAMSV3_2.Models.CHECKLIST,Microsoft.Data.Entity.Storage.ValueBuffer],System.Int32]' cannot be used for parameter of type System.Func2[CRAMSV3_2.Models.CHECKLIST,System.Int32]' of method 'System.Collections.Generic.IEnumerable1[System.Linq.IGrouping2[System.Int32,CRAMSV3_2.Models.CHECKLIST]] _GroupBy[CHECKLIST,Int32,CHECKLIST](System.Collections.Generic.IEnumerable1[CRAMSV3_2.Models.CHECKLIST], System.Func2[CRAMSV3_2.Models.CHECKLIST,System.Int32], System.Func2[CRAMSV3_2.Models.CHECKLIST,CRAMSV3_2.Models.CHECKLIST])'

Question What is the best way to return results with multiple tables or just one table but making sure the dates from another table are Queryable.

Answer 1

You need to Include the Complaints object.

return _context.Checklists
            .Where(t => t.Complaints.Received_DT.Value.Year == 2016)
            .Include(t => t.Complaints)
            .GroupBy(a => a.EmpID)
            .ToDictionary(g => g.Key, g => g.Count());

See here for details