linq group by to generate nested POCO

Question

I have four tables joined to produce data something like below:

Name        Grade   CardID   Date       Class               Listen  Read    Write
Jane Doe    A       1001    2020-10-01  Period 1 - Spanish  500     500     500  
John Doe    B+      1002    2010-10-02  Pereiod 2 - English 1000    1000    1000     
Jane Doe    A       1001    2020-10-01  Period 3 - Englsih  500     1000    1000

How do I convert the above data into a nested form like below using LINQ group by? This is a .NET CORE WEB API project and uses DTO objects projections from the LINQ query data.

[
  {
    "cardId": 1001,
    "studentName": "Jane Doe",
    "grade": "A",
    "evaluationDate": "2020-10-01T00:00:00",
    "Period 1 - Spanish": {
      "Listen": 1000,
      "Read": 500,
      "Write": 500
    },
    "Period 3 - English": {
      "Listen": 1000,
      "Read": 500,
      "Write": 1000
    }
  },
  {
    "cardId": 1002,
    "studentName": "John Doe",
    "grade": "B+",
    "evaluationDate": "2010-10-01T00:00:00",
    "Period 2 - English": {
      "Listen": 500,
      "Read": 500,
      "Write": 1000
    }
  }
]

Below I have two viewModel classes which I am using to generate the nested POCO data stracture to be returned from the query. If I don't use GroupBy, I can generate a simple unnested POCO but I don't want to repeat the response data as separate object. This is for a .NET core web api project . I feel like I am close, but the group by in LINQ is throwing me off...

public class PointCardViewModel 
{
    public int CardId { get; set; }
    public string StudentName { get; set; }
    public string Grade { get; set; }
    public DateTime EvaluationDate { get; set; }
    public IEnumerable<LineItemViewModel> LineItems { get; set; }
}
public class LineItemViewModel
{
    public string ClassPeriod { get; set; }
    public int Listen { get; set; }
    public int Read { get; set; }
    public int Write { get; set; }
}
  ((from s in db.Students
  join dc in db.DailyCards on s.StudentId equals dc.StudentId
  join dcli in db.DailyCardLineItems on dc.CardId equals dcli.CardId
  join dcob in db.DailyCardOtherBehaviors on dc.CardId equals dcob.CardId
  select new
  {
      s.StudentName,
      s.StudentGrade,
      dc.CardId,
      dc.CardDate,
      dcli.ClassParticipationPoints,
      dcli.AssignmentCompletionPoints,
      dcli.BonusHomeworkPoints,
      dcli.ClassPeriod
  })
  .GroupBy(x => x.CardId)
  .Select(g => new PointCardViewModel()
  {
      CardId = g.Key,
      StudentName = g.Select(c => c.StudentName).First(),
      Grade = g.Select(c => c.StudentGrade).First(),
      EvaluationDate = x.CardDate,
      LineItems = g.Select(y => new LineItemViewModel()
                  {
                      //Class
                      //Read
                      //Listen
                      //Write
                  })
  }).toList()

Update: After understanding multiple group By in lINQ, my .NET Core WEB API is still complaining about bad request and doesn't return the nested JSON. I did update the LineItems prop to be IDictionary type with the decorator. Interestingly, if I comment out the DTO portion of LineItems and set it to null, the response comes back fine. Can you help what the issue is here?

    public async Task<List<PointCardViewModel>> GetPointCards()
    {
        var queryPointCards = 
            ((from s in db.Students
                join dc in db.DailyCards on s.StudentId equals dc.StudentId
                join dcli in db.DailyCardLineItems on dc.CardId equals dcli.CardId
                join dcob in db.DailyCardOtherBehaviors on dc.CardId equals dcob.CardId
                select new
                {
                    s.StudentName,
                    s.StudentGrade,
                    dc.CardId,
                    dc.CardDate,
                    dcli.ClassParticipationPoints,
                    dcli.AssignmentCompletionPoints,
                    dcli.BonusHomeworkPoints,
                    dcli.ClassPeriod,
                    dcob.PersonalAppearancePoints,
                    dcob.LunchPoints,
                    dcob.RecessOtherPoints,
                    dcob.AmHomeroomPoints,
                    dcob.PmHomeroomPoints
                })
                .GroupBy(x => new { 
                                    x.CardId, 
                                    x.StudentGrade, 
                                    x.StudentName, 
                                    x.CardDate, 
                                    x.PersonalAppearancePoints, 
                                    x.LunchPoints, 
                                    x.RecessOtherPoints,
                                    x.AmHomeroomPoints,
                                    x.PmHomeroomPoints 
                })
                .Select(x => new PointCardViewModel
                {
                    CardId = x.Key.CardId,
                    StudentName = x.Key.StudentName,
                    Grade = x.Key.StudentGrade,
                    EvaluationDate = x.Key.CardDate,
                    PersonalAppearancePoints = x.Key.PersonalAppearancePoints,
                    LunchPoints = x.Key.LunchPoints,
                    RecessOtherPoints = x.Key.RecessOtherPoints,
                    AMHomeRoomPoints = x.Key.AmHomeroomPoints,
                    PMHomeRoomPoints = x.Key.PmHomeroomPoints,
                    LineItems = null
                    //x.Select(c => new LineItemViewModel
                    //{
                    //    ClassPeriod = c.ClassPeriod,
                    //    ClassParticipationPoints = c.ClassParticipationPoints,
                    //    AssignmentCompletionPoints = c.AssignmentCompletionPoints,
                    //    BonusHomeworkPoints = c.BonusHomeworkPoints
                    //}).ToDictionary(key => key.ClassPeriod, value => (object)value)
                }
              )
          ).ToListAsync();

        if (db != null)
        {
            return await queryPointCards;
        }
        return null;
    }

Why are doing that, have you considered to use some sort of ORM? — Kamushek, Nov 15 '20 at 04:34
I am facing the exact same issue as here: https://stackoverflow.com/questions/59771711/nested-objects-in-asp-net-core-odata-with-open-types I wish someone knew how to fix this and guide me — AJSwift, Nov 15 '20 at 12:55
It's right from the start, "I have four tables joined", that I wonder: why not query the four tables directly and get the data structured as you want? Now you first flatten a data hierarchy and then try to unflatten it again. — Gert Arnold, Nov 15 '20 at 13:31
@GertArnold I agree that might solve my problem. But what if I already have a flat view like that to work with. My issue is I am not understanding why the nested DTO projection for `LineItems` blows everything up? — AJSwift, Nov 15 '20 at 13:34

Anu Viswan · Accepted Answer · 2020-11-15T04:43:00.170

You could achieve this with a slight change in your query and resultant Data structure. For example

Changing your Data Structures as

public class PointCardViewModel 
{
    public int CardId { get; set; }
    public string StudentName { get; set; }
    public string Grade { get; set; }
    public DateTime EvaluationDate { get; set; }
    [JsonExtensionData]
    public IDictionary<string, object> LineItems { get; set; }  //Change Here
}
public class LineItemViewModel
{
    public string ClassPeriod { get; set; }
    public int Listen { get; set; }
    public int Read { get; set; }
    public int Write { get; set; }
}

Note that the LineItems has been converted to a Dictionary and decorated with JsonExtensionDataAttribute.

And now you could Change your Group By Query as

.GroupBy(x=> new {x.Name,x.Grade,x.CardID,x.Date})
                .Select(x=> new PointCardViewModel
                        {
                            CardId=x.Key.CardID,
                            StudentName = x.Key.Name,
                            Grade = x.Key.Grade,
                            EvaluationDate = x.Key.Date,
                            LineItems = x.Select(c=> new LineItemViewModel
                            {
                                ClassPeriod = c.Class,
                                Listen = c.Listen,
                                Read = c.Read,
                                Write = c.Write
                                
                            }).ToDictionary(key=>key.ClassPeriod,value=>(object)value)
                        });

Serializing the resultant data would give the required Json

Demo Code

I really appreciate the fiddle and help me figure out the groupby issue. But I am still not getting my .NET Core WEB API method to return the correct response. Please see my updated question with the `get` method. — AJSwift, Nov 15 '20 at 12:44

score 1 · Answer 2 · answered Nov 15 '20 at 06:01

Change the Group by and Select as below:

var result=((from s in db.Students
  join dc in db.DailyCards on s.StudentId equals dc.StudentId
  join dcli in db.DailyCardLineItems on dc.CardId equals dcli.CardId
  join dcob in db.DailyCardOtherBehaviors on dc.CardId equals dcob.CardId
  select new
  {
      s.StudentName,
      s.StudentGrade,
      dc.CardId,
      dc.CardDate,
      dcli.ClassParticipationPoints,
      dcli.AssignmentCompletionPoints,
      dcli.BonusHomeworkPoints,
      dcli.ClassPeriod
  })
  .GroupBy(x => new { x.StudentName, x.CardId, x.StudentGrade, x.CardDate})
  .Select(g => new PointCardViewModel()
  {
      CardId =g.Key.CardId,
      StudentName = g.Key.StudentName,
      Grade = g.Key.StudentGrade,
      EvaluationDate = g.Key.CardDate,
      LineItems = g.Select(y => new LineItemViewModel
                  {
                      Class=y.Class,
                      Read=y.ClassParticipationPoints,
                      Listen=y.AssignmentCompletionPoints,
                      Write=y.BonusHomeworkPoints
                  })
  }).toList()

linq group by to generate nested POCO

2 Answers2