c# - 在 C# 中同时过滤和计数

Question

我正在使用实体框架工作，并从我的 Get 方法中获得以下 json。

[
    {
        "id": 2,
        "name": "Coffee",
        "icon": "alarm",
        "staticEvents": [
            
            
            {
                "id": 36,
                "categoryId": 2,
                "time": "2021-12-26T12:22:46.609+10:00",
                "comment": null
            },
            {
                "id": 38,
                "categoryId": 2,
                "time": "2021-05-28T00:00:00+10:00",
                "comment": null
            },
            {
                "id": 40,
                "categoryId": 2,
                "time": "2021-05-28T00:00:00+10:00",
                "comment": null
            },
            {
                "id": 42,
                "categoryId": 2,
                "time": "2021-05-28T00:00:00+10:00",
                "comment": null
            },
            {
                "id": 44,
                "categoryId": 2,
                "time": "2021-05-28T00:00:00+10:00",
                "comment": null
            }
        ]
    },
    {
        "id": 3,
        "name": "Water",
        "icon": "alert",
        "staticEvents": [
            {
                "id": 32,
                "categoryId": 3,
                "time": "2021-05-28T14:33:47.765643+10:00",
                "comment": null
            },
            {
                "id": 37,
                "categoryId": 3,
                "time": "2021-05-20T13:00:58.123+10:00",
                "comment": null
            },
            {
                "id": 39,
                "categoryId": 3,
                "time": "2021-05-28T00:00:00+10:00",
                "comment": null
            },
            {
                "id": 41,
                "categoryId": 3,
                "time": "2021-05-28T00:00:00+10:00",
                "comment": null
            },
            {
                "id": 43,
                "categoryId": 3,
                "time": "2021-05-28T00:00:00+10:00",
                "comment": null
            }
        ]
    }
]

我需要以下格式的东西：

[
    {
        "month": June,
        "report": [

            {
                "categoryId": 2,
                "occurrences": 5,
            },

 {
                "categoryId": 3,
                "occurrences": 4,
            }

    }
]

我想这与此线程中的 GroupBy + Select 类似，但我无法获得子列表中的出现次数。

编辑：我正在尝试逐步运行查询，但我遇到了错误。

• 当我运行时：

 var list = _context.Categories
            .SelectMany(c => c.StaticEvents )
            .GroupBy (staticEvent => new 
            {
                Year = staticEvent.Time.Year,
                Month = staticEvent.Time.Month
            })
            .ToList();

            foreach(StaticEvent i in list) {
                Console.WriteLine(i.CategoryId);
            }

我明白了

System.InvalidOperationException: Unable to translate the given 'GroupBy' pattern. Call 'AsEnumerable' before 'GroupBy' to evaluate it client-side.

所以我用：

var list = _context.Categories
            .SelectMany(c => c.StaticEvents )
            .AsEnumerable()
            .GroupBy (staticEvent => new 
            {
                Year = staticEvent.Time.Year,
                Month = staticEvent.Time.Month
            })
            .ToList();

然后我得到

System.InvalidCastException: Unable to cast object of type 'System.Linq.Grouping`2[<>f__AnonymousType2`2[System.Int32,System.Int32],api.Entities.StaticEvent]' to type 'api.Entities.StaticEvent'.

标签： c#asp.netlinq

Answer 1

仅使用 lambda 表达式的示例

            var json = File.ReadAllText(@"C:\Temp\Arquivo.txt");
            var objs = JsonConvert.DeserializeObject<List<RequestObj>>(json);

            var resultObjs = objs
                            .SelectMany(x => x.StaticEvents.Select(y => y.Time))
                            .GroupBy(x => x.Month)
                            .Select(x => new
                            {
                                Month = x.First().ToString("MMMM"),
                                Index = x.Key
                            }) 
                            .Select(x => new ResultObj
                            {
                                Month = x.Month,
                                Report = objs
                                            .Where(y => y.StaticEvents.Where(z => z.Time.Month == x.Index).Any())
                                            .Select(y => new Report
                                            {
                                                CategoryId = y.Id,
                                                Occurrences = y.StaticEvents.Where(z => z.Time.Month == x.Index).Count()
                                            })
                                            .ToList()
                            })
                            .ToList();