包括第二个表Linq to Sql

Question

我有一个字典结果，它返回输入的每个检查表的计数。

类是这样的:

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 int COMP_ID { get; set; }
}

当我只返回_context。清单我得到了我想要返回的东西的总数，如下所示。

public Dictionary<int, int> GetAllComplaintsCount()
    {
        try
        {
            return _context.Checklists
                .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;
        }
    }

但是当我添加投诉表时，我得到一个错误，即EmpID不在投诉表中，但它在检查表中。如何根据EmpID包括投诉表和组，以及在收到日期之间进行查询?

public Dictionary<int, int> GetAllComplaintsCount()
    {
        try
        {
            return _context.Complaints
                .Include(t=> t.Checklists)
                .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;
        }
    }

Answer 1

在Checklist中仍然缺少您的投诉属性，但假设这是另一个疏忽，我认为这是您想要的:

public Dictionary<int, int> GetAllComplaintsCount(DateTime start, DateTime finish)
{
  try
  {
    return _context.Checklists
      .Where(a=>a.Complaint.Received_DT>=start && a.Complaint.Received_DT<finish)
      .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;
  }
}