Linq Group by获取数量和数据

主表：

public partial class Activity
    {
        [Key]
        public int pkActivity { get; set; }
        public int fkEmployee { get; set; }
        public string ActivityName { get; set; }
        public DateTime RegistrationStartDateTime { get; set; }
        public DateTime RegistrationEndDateTime { get; set; }
        public DateTime StartDateTime { get; set; }
        public DateTime EndDateTime { get; set; }
        public int MaxNumOfPeople { get; set; }
        public decimal PricePerPeople { get; set; }
        public string ActivityLocation { get; set; }
        public string ActivityStatus { get; set; }
        public string Description { get; set; }
        public string ImgUrl { get; set; }
        public DateTime CreateOn { get; set; }
        public string CreateBy { get; set; }
        public DateTime LastModifyOn { get; set; }
        public string LastModifyBy { get; set; }
    }

重表：

public partial class ActivityEmployee
    {
        [Key]
        public int pkActivityEmployee { get; set; }
        public int fkActivity { get; set; }
        public int fkEmployee { get; set; }
        public string Comments { get; set; }
        public DateTime CreateOn { get; set; }
        public string CreateBy { get; set; }
    }

获取当前活动注册人数linq 写法，用DefaultIfEmpty 表示left join，通过group by new 进行分组，并通过groupedTable.Key.属性 提取数据：

/// <summary>
        /// 获取我创建的活动列表。
        /// </summary>
        /// <param name="employeeKey"></param>
        /// <returns></returns>
        public IEnumerable<ActivityDTO> GetMyCreatedActivitiesList(int employeeKey)
        {
            var query = from activity in clientDbContext.Activity
                        join activityEmployee in clientDbContext.ActivityEmployee
                        on activity.pkActivity equals activityEmployee.fkActivity into temp
                        from t in temp.DefaultIfEmpty()
                        where activity.fkEmployee == employeeKey 
                        group t by new
                        {
                            activity.pkActivity,
                            activity.fkEmployee,
                            activity.ActivityName,
                            activity.RegistrationStartDateTime,
                            activity.RegistrationEndDateTime,
                            activity.StartDateTime,
                            activity.EndDateTime,
                            activity.ImgUrl,
                            activity.ActivityStatus,
                            activity.ActivityLocation,
                            activity.Description,
                            activity.CreateOn,
                            activity.CreateBy,
                            activity.LastModifyBy,
                            activity.LastModifyOn
                        }
                        into groupActivity
                        select new ActivityDTO()
                        {
                            Id = groupActivity.Key.pkActivity,
                            CreatedEmployeKey = groupActivity.Key.fkEmployee,
                            ActivityName = groupActivity.Key.ActivityName,
                            RegistrationStartDateTime = groupActivity.Key.RegistrationStartDateTime,
                            RegistrationEndDateTime = groupActivity.Key.RegistrationEndDateTime,
                            StartDateTime = groupActivity.Key.StartDateTime,
                            EndDateTime = groupActivity.Key.EndDateTime,
                            ActivityImageUrl = groupActivity.Key.ImgUrl,
                            ActivityStatus = groupActivity.Key.ActivityStatus,
                            ActivityLocation = groupActivity.Key.ActivityLocation,
                            Description = groupActivity.Key.Description,
                            CreateOn = groupActivity.Key.CreateOn,
                            CreateBy = groupActivity.Key.CreateBy,
                            LastModifyBy = groupActivity.Key.LastModifyBy,
                            LastModifyOn = groupActivity.Key.LastModifyOn,
                            RegisteredCount = groupActivity.Count(g=>g.fkActivity!=null)//报名人数,left join存在null数据
                        };

            return query;
        }