LINQ查询以将数据放入嵌套对象 [英] LINQ Query to put data in a nested object

问题描述

我正在编写C#LINQ查询以从下面的SQL表获取数据

I am working on writing a C# LINQ query to get data from a SQL table below

表1

Id Status SubStatus
1  Review Pending 
2  Review Complete
3  Review Approved
4  Review Denied    
5  Info   Processing
6  Info   Pending
7  Info   Requested
8  Info   Received
9  Approval Approved
10 Review Approved

从上表中，我想将数据放入类似下面的对象中

From the above table I would like to put the data in an object like below

public class LastStatusDto
    {
        public string StatusName { get; set; }
        public int StatusId { get; set; }
        public int Count { get; set; }
        public IEnumerable<LastStatusChildDataDto> drillDownData { get; set; }
    }

public class LastStatusChildDataDto
{
    public string SubStatusName { get; set; }      
    public int Count { get; set; }
}

和示例结果(状态为审阅")如下所示

and the sample result(for Status "Review") looks like below

            {
              "statusName": "Review",
              "statusId": 0,
              "count": 5,
              "drillDownData": 
              [{
               "subStatusName":"Approved",
               "count":2 
              },
              {
               "subStatusName":"Complete",
               "count":1 
              },
             {
              "subStatusName":"Pending",
              "count":1 
              },
              {
               "subStatusName":"Denied",
               "count":1 
              },
            ]
        }

为上述情况编写LINQ查询的好方法是什么.我尝试按 status 进行分组，但是无法找到一种将 substatuses 放入如下嵌套对象中的方法

Whats a good way to write a LINQ query for the above scenario. I tried grouping by status but not able to figured a way to put substatuses in the nested object like below

var res = from status in context.Table1
          group status by status.Status into s

          select new LastStatusDto
          {
               Count = s.Count(),
               StatusName = s.Key,
               drilldownData = {/* ? */}                          
          };

推荐答案

这应该做到:

 var res = table1.GroupBy(t => t.Status)
            .Select(g => new LastStatusDto()
            {
                StatusId = 0,
                StatusName = g.Key,
                Count = g.Count(),
                drillDownData = g.GroupBy(s => s.SubStatus)
                    .Select(st => new LastStatusChildDataDto()
                    {
                        SubStatusName = st.Key,
                        Count = st.Count()
                    })
            });

正如我在上面的评论中所提到的， StatusId 在 LastStatusDto 的属性中没有位置，因为它是一个聚合，因此我将其设置为 0 只是为了匹配您的结构.

As I mentioned in a comment above, StatusId has no place as a property of LastStatusDto because it's an aggregate so I set it 0 just to match your structure.

这篇关于LINQ查询以将数据放入嵌套对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！