ASP.NET Core 8中多列GroupBy LINQ分组实现问题求助

问题场景 #

存储过程返回包含CategoryName、SubCategoryName、MessageText字段的DataTable，数据结构为：一个分类对应多个子分类，一个子分类对应多条消息文本。需要在ASP.NET Core 8项目的Get接口中，以数组嵌套对象的格式返回这些数据。

数据示例 #

现有错误 #

在DAL层编写私有方法时，尝试按CategoryName和SubCategoryName分组，出现编译错误：

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<System.Collections.Generic.List<Char>>' to 'String'

错误代码 #

private List<ARMCategory> CategoryDetailsCUP(DataSet mediaReviewDetails)
{
    List<ARMCategory> ARMCategory = new List<ARMCategory>();
    if (mediaReviewDetails != null)
    {
        foreach (DataRow dataRow in mediaReviewDetails.Tables[2].Rows)
        {
            List<ARMSubCategory> results2 = ARMCategory
            .GroupBy(p => new { p.CategoryName, p.SubCategoryNames },
                     (k, c) => new ARMCategory()
                     {
                         CategoryName = k.CategoryName,
                         SubCategoryNames = c.Select(cs => cs.SubCategoryNames.ToList()),
                         MessageTexts = c.Select(cs => cs.MessageTexts.ToList())
                     }
                    ).ToList();
            return ARMCategory;
        }
    }
    //return result2;
}

正确实现方案 #

1. 定义层级实体类 #

明确嵌套结构：分类包含子分类列表，子分类包含消息文本列表：

public class ARMSubCategory
{
    public string SubCategoryName { get; set; }
    public List<string> MessageTexts { get; set; }
}

public class ARMCategory
{
    public string CategoryName { get; set; }
    public List<ARMSubCategory> SubCategories { get; set; }
}

2. DAL层分组逻辑实现 #

错误根源：未先将DataTable数据转换为实体、分组逻辑层级错误、属性名不匹配。以下是正确代码：

private List<ARMCategory> CategoryDetailsCUP(DataSet mediaReviewDetails)
{
    List<ARMCategory> result = new List<ARMCategory>();
    
    if (mediaReviewDetails?.Tables.Count > 2 && mediaReviewDetails.Tables[2].Rows.Count > 0)
    {
        // 将DataTable行转换为匿名对象，简化分组操作
        var dataItems = mediaReviewDetails.Tables[2].Rows.Cast<DataRow>()
            .Select(row => new 
            {
                CategoryName = row["CategoryName"].ToString(),
                SubCategoryName = row["SubCategoryName"].ToString(),
                MessageText = row["MessageText"].ToString()
            })
            .ToList();

        // 双层分组：先按分类分组，再在分类内按子分类分组
        result = dataItems.GroupBy(item => item.CategoryName)
            .Select(categoryGroup => new ARMCategory
            {
                CategoryName = categoryGroup.Key,
                SubCategories = categoryGroup.GroupBy(subItem => subItem.SubCategoryName)
                    .Select(subGroup => new ARMSubCategory
                    {
                        SubCategoryName = subGroup.Key,
                        MessageTexts = subGroup.Select(msg => msg.MessageText).ToList()
                    })
                    .ToList()
            })
            .ToList();
    }

    return result;
}

3. 接口返回格式示例 #

调用该方法后，接口将返回如下JSON结构：

[
    {
        "categoryName": "Framing",
        "subCategories": [
            {
                "subCategoryName": "Face Covered",
                "messageTexts": [
                    "Child's face can not be clearly seen",
                    "food detected"
                ]
            }
        ]
    },
    {
        "categoryName": "Lighting",
        "subCategories": [
            {
                "subCategoryName": "Incorrect lighting",
                "messageTexts": [
                    "Poor lighting detected",
                    "Not recommended"
                ]
            },
            {
                "subCategoryName": "Too dark",
                "messageTexts": [
                    "Sun behind the Child detected"
                ]
            }
        ]
    }
]

内容的提问来源于stack exchange，提问作者Sindhu C L