如何使用Entity Framework按成员分组取每组Expdate最大的一行数据

问题根因 #

你当前代码仅返回全局最大ExpDate记录的原因是：OrderByDescending(u=> u.ExpDate).Take(1) 是对所有过滤后的结果整体排序后取第一条，没有按成员维度做分组处理，自然无法得到每个成员的最大有效期记录。

代码修改示例 #

你只需要保留原有过滤逻辑，删除全局排序取1条的代码，新增按成员ID分组、每组取最大有效期记录的逻辑即可，修改后完整代码如下：

private void R2()
{
    int? MemberIDAsInt = string.IsNullOrEmpty(MemberID) ? null : (int?)Convert.ToInt32(MemberID);
    int? MainPackageCodeAsInt = string.IsNullOrEmpty(MainPackageCode) ? null : (int?)Convert.ToInt32(MainPackageCode);
    int? SubPackageCodeAsInt = string.IsNullOrEmpty(SubPackageCode) ? null : (int?)Convert.ToInt32(SubPackageCode);
    int? PackageTypeCodeAsInt = string.IsNullOrEmpty(PackageTypeCode) ? null : (int?)Convert.ToInt32(PackageTypeCode);
    int? PackageCodeAsInt = string.IsNullOrEmpty(PackageCode) ? null : (int?)Convert.ToInt32(PackageCode);
    int? CorporateCodeAsInt = string.IsNullOrEmpty(CorporateCode) ? null : (int?)Convert.ToInt32(CorporateCode);
    int? SalesCodeAsInt = string.IsNullOrEmpty(SalesCode) ? null : (int?)Convert.ToInt32(SalesCode);
    int? TrainerCodeAsInt = string.IsNullOrEmpty(TrainerCode) ? null : (int?)Convert.ToInt32(TrainerCode);
    int? RelationshipCodeAsInt = string.IsNullOrEmpty(RelationshipCode) ? null : (int?)Convert.ToInt32(RelationshipCode);
    int? NationalityCodeAsInt = string.IsNullOrEmpty(NationalityCode) ? null : (int?)Convert.ToInt32(NationalityCode);
    bool? GenderAsBool = string.IsNullOrEmpty(Gender.ToString()) ? null : (bool?)Convert.ToBoolean(Gender);
    DateTime? StartDatePackageFromAsDate = StartDatePackageFrom == string.Empty ? null : (DateTime?)Convert.ToDateTime(StartDatePackageFrom);
    DateTime? StartDatePackageToAsDate = StartDatePackageTo == string.Empty ? null : (DateTime?)Convert.ToDateTime(StartDatePackageTo);
    DateTime? EndDatePackageFromAsDate = EndDatePackageFrom == string.Empty ? null : (DateTime?)Convert.ToDateTime(EndDatePackageFrom);
    DateTime? EndDatePackageToAsDate = EndDatePackageTo == string.Empty ? null : (DateTime?)Convert.ToDateTime(EndDatePackageTo);

    using (var dbcontext = new database.GymEntities())
    {
        var Employee_Data = dbcontext
                         .VPackageForMembers
                         .Where(u => (MemberIDAsInt == null) ? true : u.FKMemberID == MemberIDAsInt)
                         .Where(u => (MainPackageCodeAsInt == null) ? true : u.FKMainPakage == MainPackageCodeAsInt)
                         .Where(u => (SubPackageCodeAsInt == null) ? true : u.FKSubPackage == SubPackageCodeAsInt)
                         .Where(u => (PackageTypeCodeAsInt == null) ? true : u.FKPackageType == PackageTypeCodeAsInt)
                         .Where(u => (PackageCodeAsInt == null) ? true : u.FKPackage == PackageCodeAsInt)
                         .Where(u => (CorporateCodeAsInt == null) ? true : u.FKCorporate == CorporateCodeAsInt)
                         .Where(u => (SalesCodeAsInt == null) ? true : u.FKSalesPerson == SalesCodeAsInt)
                         .Where(u => (TrainerCodeAsInt == null) ? true : u.FKTrainer == TrainerCodeAsInt)
                         .Where(u => (RelationshipCodeAsInt == null) ? true : u.FKRelationShip == RelationshipCodeAsInt)
                         // 注意：你原有代码这里国籍筛选误用了FKRelationShip字段，建议修正为对应国籍的外键字段
                         .Where(u => (NationalityCodeAsInt == null) ? true : u.FKNationality == NationalityCodeAsInt)
                         .Where(u => (GenderAsBool == null) ? true : u.Gender == GenderAsBool)
                         .Where(u => (StartDatePackageFromAsDate == null) ? true : u.StartDate >= StartDatePackageFromAsDate && u.StartDate <= StartDatePackageToAsDate)
                         .Where(u => (EndDatePackageFromAsDate == null) ? true : u.ExpDate >= EndDatePackageFromAsDate && u.ExpDate <= EndDatePackageToAsDate)
                         // 按成员ID分组
                         .GroupBy(u => u.FKMemberID)
                         // 每个分组内取ExpDate最大的一条记录
                         .Select(g => g.OrderByDescending(x => x.ExpDate).FirstOrDefault())
                         // 映射需要的字段
                         .Select(u => new
                         {
                             u.FKMemberID,
                             u.MemberEnglishName,
                             u.MainPackageEnglishName,
                             u.SubPackageEnglishName,
                             u.PackageTypeEnglishName,
                             u.PackageEnglishName,
                             u.ExpDate,
                             u.PhoneNo1
                         }
                         )                                
                         .ToList();

        datasource = new ReportDataSource("MemberExpireData", Employee_Data);
    }
}

额外注意事项 #

• 上述写法兼容EF6及EF Core所有主流版本，会被自动翻译为高效的SQL分组取最大值逻辑，不会在本地做内存计算
• 如果你使用的是极老版本的EF不支持分组后直接取FirstOrDefault，可以换用关联子查询的写法实现相同效果

内容的提问来源于stack exchange，提问作者Ahmed Abdo