使用linq对多个表中的数据进行分组

Question

我正在加入几个表，并试图获得每个状态的计数。

EmployeeEvaluationsStatuses包含以下字段：

身份，头衔。

EmployeeEvaluationsStatuses中的数据如下所示：

Id  Title
1   New - Incomplete
2   Submitted – All Docs
3   Approved
4   Rejected - Need More Info
5   Qualified

我想得到如下结果：

StatusCount  Status

60           New - Incomplete 
42           Submitted – All Docs 
20           Qualified 

下面是我的查询：

from ep in EmployeePositions.Where(a => a.CorporateId == 1596)
join ee in EmployeeEvaluations.Where(e => e.TargetGroupId != null) on ep.EmployeeId equals ee.EmployeeId
join ees in EmployeeEvaluationStatuses  on ee.EvaluationStatusId  equals ees.Id

group ees by ees.Id into g
select new
{
   StatusCount = g.Count()
   ,Status= ees .title      
}

我收到一个错误"The name 'ees' does not exist in the current context“

我不确定导航属性。

 public partial class WotcEntities : DbContext
    {
        public WotcEntities()
       : base(hr.common.Database.EntitiesConnectionString("res://*/ef.WotcModel.csdl|res://*/ef.WotcModel.ssdl|res://*/ef.WotcModel.msl", "devConnection"))
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }


      public virtual DbSet<EmployeeEvaluations> EmployeeEvaluations { get; set; }
      public virtual DbSet<EmployeeEvaluationStatus> EmployeeEvaluationStatus { get; set; }
        public virtual DbSet<EmployeePositions> EmployeePositions { get; set; }

    }




public partial class EmployeeEvaluationStatus
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public EmployeeEvaluationStatus()
        {
            this.EmployeeEvaluations = new HashSet<EmployeeEvaluations>();
            this.Vouchers = new HashSet<Vouchers>();
        }

        public int Id { get; set; }
        public string Title { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<EmployeeEvaluations> EmployeeEvaluations { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Vouchers> Vouchers { get; set; }
    }

Answer 1

您必须在group by中使用组合键并使用g.Key进行导航

from ep in EmployeePositions.Where(a => a.CorporateId == 1596)
join ee in EmployeeEvaluations.Where(e => e.TargetGroupId != null) on ep.EmployeeId equals ee.EmployeeId
join ees in EmployeeEvaluationStatuses on ee.EvaluationStatusId  equals ees.Id

group ees by new { ees.Id, ees.title } into g
select new
{
    StatusCount = g.Count(),
    Status= g.Key.title
}