时间戳

首页 > 解决方案 > 按使用 linq 完成项目的用户对结果进行分组

c# - 按使用 linq 完成项目的用户对结果进行分组

问题描述

我想通过使用多个联接在最后对结果进行分组。

共有三个列表User、Item和ItemCompleted：

用户列表：

//users
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}

//output
[{
    "Id": 2154,
    "Name": "Joanne"
}, {
    "Id": 2207,
    "Name": "Kylie"
}, {
    "Id": 2159,
    "Name": "Madeleine"
},{
    "Id": 2160,
    "Name": "Mat"
}]

物品清单

//items
public class Item
{
    public string Item { get; set; }
    public int Id { get; set; }
}

//output
[{
    "Id": 1,
    "Name": "Item 1"
}, {
    "Id": 2,
    "Name": "Item 2"
}, {
    "Id": 3,
    "Name": "Item 3"
},{
    "Id": 291,
    "Name": "Item 291"
},{
    "Id": 288,
    "Name": "Item 288"
},{
    "Id": 290,
    "Name": "Item 290"
}]

]

项目完成列表

//completedItems
public class ItemCompleted
{
    public int UserId { get; set; }
    public int ModuleId { get; set; }
    public string Event { get;set; }
}

//output
[{
    "UserId": 2154,
    "ModuleId": 1,
    "Event": "Lesson"
}, {
    "UserId": 2154,
    "ModuleId": 2,
    "Event": "Interactive Content"
}, {
    "UserId": 2207,
    "ModuleId": 3,
    "Event": "Interactive Content"
}, {
    "UserId": 2159,
    "ModuleId": 2,
    "Event": "Interactive Content"
}, {
    "UserId": 2207,
    "ModuleId": 2,
    "Event": "Interactive Content",
}, {
    "UserId": 2159,
    "ModuleId": 1,
    "Event": "Lesson"
}, {
    "UserId": 2207,
    "ModuleId": 1,
    "Event": "Lesson"
}]

应用了左连接，Item并CompletedItems列出了 CompletedItem 的列表：

var itemsResult = from item in items
                  join completed in completedItems on item.Id equals completed.ModuleId into cmpltItem
                  from ct in cmpltItem.DefaultIfEmpty()
                  select new CompletedItem()
                  {
                      ModuleId = item.Id,
                      ItemType = ct?.Event,
                      Item = item.Activity,
                      UserId = ct?.UserId
                  };

如果该项目未由任何用户完成：

[{
    "ModuleId": 291,
    "Item": "Item 291",
    "ItemType": null,
    "UserId": null
}, {
    "ModuleId": 288,
    "Item": "Item  288",
    "ItemType": null,
    "UserId": null
}, {
    "ModuleId": 290,
    "Item": "Item  290",
    "ItemType": null,
    "UserId": null
}]

现在我想对用户名完成的项目数进行分组：

var groupedUserbyItem = users
                     .Select(c => new UserCompletedItem
                     {
                         Name = c.Name,
                         UserId = c.Id,
                         CompletedItems = itemsResult.Where(x => x.UserId == c.Id)
                             .Select(t => new CompletedItem { }).ToList()
                     })
                      .ToList();

用户完成项

public class UserCompletedItem
{
    public string Name { get; set; }
    public int UserId { get; set; }
    public IEnumerable<CompletedItem> CompletedItems { get; set; }
}

public class CompletedItem
{
    public int? ModuleId { get; set; }
    public string Item { get; set; }
    public string ItemType { get; set; }
    public int? UserId { get; set; }
}

但我被困在与用户的项目分组中。

期望的输出：如果用户完成了一项、两项或没有项。

[{
        "UserId": 2154,
        "Name": "Joanne",
        "CompletedItems": [{
                "id": 1,
                "Item": "Item 1",
                "ItemType": "Lesson"
            },
            {
                "id": 2,
                "Item": "Item 2",
                "ItemType": "Interactive Content"
            }
        ]
    },
    {
        "UserId": 2160,
        "Name": "Mat",
        "CompletedItems": []
    },
    {
        "UserId": 2159,
        "Name": "Madeleine",
        "CompletedItems": [{
            "id": 2,
            "Item": "Item 2",
            "ItemType": "Interactive Content"
        }, {
            "id": 1,
            "Item": "Item 1",
            "Event": "Lesson"
        }]
    },
    {
        "UserId": 2207,
        "Name": "Madeleine",
        "CompletedItems": [{
            "id": 3,
            "Item": "Item 3",
            "ItemType": "Interactive Content"
        }, {
            "id": 2,
            "Item": "Item 2",
            "ItemType": "Interactive Content"
        }, {
            "id": 1,
            "Item": "Item 1",
            "Event": "Lesson"
        }]
    }
]

标签： c#linq

解决方案

您可以使用两个查询，如以下代码：

1 - 获得完成的项目：

var query1 = (from itemCompleted in itemCompleteds
              join item in items on itemCompleted.ModuleId equals item.Id
              select new
              {
                 UserId = itemCompleted.UserId, 
                 ModuleId = item.Id, 
                 Item = item.Name, 
                 ItemType = itemCompleted.Event 
              })
              .Distinct()
              .ToList();

2 - 按用户分组完成的项目并构建UserCompletedItem：

var finalResult = (from user in users
                   join itemCompleted in query1 on user.Id equals itemCompleted.UserId into leftResults
                   from leftedResult in leftResults.DefaultIfEmpty()
                   group leftedResult by new { user.Id, user.Name } into grp
                   select new UserCompletedItem
                   {
                       UserId = grp.Key.Id,
                       Name = grp.Key.Name,
                       CompletedItems = grp
                       .Where(x => x != null)
                       .Select(x => new CompletedItem
                       {
                           ModuleId = x.ModuleId,
                           Item = x.Item,
                           ItemType = x.ItemType
                       }).ToList()
                   }).ToList();

我希望你觉得这有帮助。

推荐阅读