where to add grouping to get exact output-CodePudding

var query = _sqlDbContext.RequestTemplates
.Select(rt => new TemplateViewModel
{
    templateName = rt.TemplateName,
    requests = _sqlDbContext.RequestTemplateItems
        .Where(ti => ti.RequestTemplateID == rt.RequestTemplateID)
        .Select(ti => new RequestViewModel 
        {
            category = new CategoryViewModel 
            {
                categoryId = ti.RequestCategoryID,
                categoryName = ti.RequestCategory.RequestCategoryName 
            },
            
            items = new List<ItemViewModel> { new ItemViewModel 
            {
                itemName = ti.ItemName,
                dueDate = ti.DueDate,
                notes = ti.Notes 
            }}
        })
        .ToList()
});

As per above LINQ query I am getting below result :-

     "requests":[
        {
           "category":{
              "categoryId":2,
              "categoryName":"2"
           },
           "items":[
              {
                 "itemName":"2 Item 1",
                 "dueDate":"",
                 "notes":"Item 1 Note"
              }
           ]
        },
        {
           "category":{
              "categoryId":2,
              "categoryName":"2"
           },
           "items":[
              {
                 "itemName":"2 Item 2",
                 "dueDate":"",
                 "notes":""
              }
           ]
        }

for same category id , items are returning two time . i was unable to find where i need to add grouping to get below output

         "requests":[
            {
               "category":{
                  "categoryId":2,
                  "categoryName":"2"
               },
               "items":[
                  {
                     "itemName":"2 Item 1",
                     "dueDate":"",
                     "notes":"Item 1 Note"
                  },
{
                     "itemName":"2 Item 2",
                     "dueDate":"",
                     "notes":"Item 1 Note"
                  }
               ]
            }

Model information as below

RequestTemplates having list of RequestTemplateItem with primary key RequestTemplateID. RequestTemplateItem having object of RequestTemplate and RequestCategory with FK_RequestTemplateID and FK_RequestCategoryID.

CodePudding user response：

Haven't tested but something like this should work:

var query = _sqlDbContext.RequestTemplates
.Select(rt => new TemplateViewModel
{
    templateName = rt.TemplateName,
    requests = _sqlDbContext.RequestTemplateItems
        .Where(ti => ti.RequestTemplateID == rt.RequestTemplateID)
        .GroupBy(ti => ti.RequestCategoryId)
        .Select(group => new RequestViewModel 
        {
            category = new CategoryViewModel 
            {
                categoryId = group.Key,
                categoryName = group.First().RequestCategoryName 
            },
            
            items = group.select(ti => new ItemViewModel 
            {
                itemName = ti.ItemName,
                dueDate = ti.DueDate,
                notes = ti.Notes 
            }
        })
        .ToList()
});