linq通过分组生成嵌套的POCO

Question

我有四个表连接起来以生成数据，如下所示：

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    

如何使用LINQ group-by将上述数据转换为如下嵌套形式？这是一个.NET CORE WEB API项目，使用LINQ查询数据中的DTO对象投影。

[
{
"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
}
}
]

下面我有两个viewModel类，用于生成从查询返回的嵌套POCO数据结构。如果我不使用GroupBy，我可以生成一个简单的无测试POCO，但我不想将响应数据作为单独的对象重复。这是一个.NET核心web api项目。我觉得我很接近，但LINQ的小组让我很失望…

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()

更新：在了解了lINQ中的多组By之后，我的.NET Core WEB API仍然抱怨bad request，并且没有返回嵌套的JSON。我确实用decorator将LineItems道具更新为IDictionary类型。有趣的是，如果我注释掉LineItems的DTO部分并将其设置为null，那么响应会很好。你能帮我解决这里的问题吗？

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

Answer 1

您可以通过对查询和结果数据结构进行轻微更改来实现这一点。例如

将数据结构更改为

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

请注意，LineItems已转换为Dictionary，并用JsonExtensionDataAttribute进行了修饰。

现在您可以将您的Group By Query更改为

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

对结果数据进行序列化将得到所需的Json

演示代码

Answer 2

更改Group by和Select如下：

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()