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;
}
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