Linq Group by获取数量和数据

最新推荐文章于 2022-07-26 19:00:17 发布

weixin_30662011

最新推荐文章于 2022-07-26 19:00:17 发布

阅读量729

点赞数

CC 4.0 BY-SA版权

文章标签： c# 数据库数据结构与算法

原文链接：http://www.cnblogs.com/tylertang/p/8607912.html

本文介绍了一个使用LINQ实现的C#方法，该方法用于从数据库中获取由特定员工创建的所有活动及其注册人数。通过左连接和分组操作，有效地实现了活动数据的检索。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

主表：

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;
        }