使用带连接的linq查询查询嵌套对象

Question

我有以下json结构，我从一个api:

    {
    "event_instance": [
        {
            "id": 55551244,
            "event_id": 11112,
            "name": "Brown Belt Karate Class",
            "staff_members": [
                {
                    "id": 12345,
                    "name": "John Smith"
                }
            ],
            "people": [
                {
                    "id": 111,
                    "name": "Jane Doe"
                },
                {
                    "id": 222,
                    "name": "Josh Smith"
                },
                {
                    "id": 333,
                    "name": "Ben Johnson"
                }
            ],
            "visits": [
                {
                    "id": 1234578,
                    "person_id": 111,
                    "state": "completed",
                    "status": "complete"
                },
                {
                    "id": 1239865,
                    "person_id": 222,
                    "state": "completed",
                    "status": "complete"
                },
                {
                    "id": 1239865,
                    "person_id": 333,
                    "state": "canceled",
                    "status": "cancel"
                }
            ]
        }
    ]
}

我使用JSON将其反序列化为以下.net对象。净:

    [JsonObjectAttribute("event_instance")]
    public class EventInstance
    {
        [JsonPropertyAttribute("id")]
        public int Id { get; set; }
        [JsonPropertyAttribute("event_id")]
        public int EventId { get; set; }
        [JsonPropertyAttribute("name")]
        public string Name { get; set; }
        [JsonPropertyAttribute("staff_members")]
        public List<StaffMember> StaffMembers { get; set; }
        [JsonPropertyAttribute("visits")]
        public List<Visit> Visits { get; set; }
        [JsonPropertyAttribute("people")]
        public List<Student> Students { get; set; }
    } 
    [JsonObjectAttribute("staff_members")]
    public class StaffMember
    {
        [JsonPropertyAttribute("id")]
        public int Id { get; set; }
        [JsonPropertyAttribute("name")]
        public string Name { get; set; }
    }
    [JsonObjectAttribute("people")]
    public class People
    {
        [JsonPropertyAttribute("id")]
        public int Id { get; set; }
        [JsonPropertyAttribute("name")]
        public string Name { get; set; }
    }
    [JsonObjectAttribute("visits")]
    public class Visits
    {
        [JsonPropertyAttribute("id")]
        public int Id { get; set; }
        [JsonPropertyAttribute("person_id")]
        public int PersonId { get; set; }
        [JsonPropertyAttribute("state")]
        public string State { get; set; }
        [JsonPropertyAttribute("status")]
        public string Status { get; set; }
    }

我使用以下代码来反序列化:

var event = (EventInstance)JsonConvert.DeserializeObject(json, typeof(EventInstance));

上面的工作很好，给了我一个准确的对象表示上面的json结构。现在我正试图查询此事件对象以过滤/项目到一个新的结构，然后我可以序列化回json并发送到浏览器。我需要返回学生的事件列表是在"完成"的状态和"完成"的状态。如您所见，people数组绑定到visitors数组(带有id和person_id)。我想产生以下简单的输出:

1. 11112，棕带空手道班，John Smith, 111, Jane Doe
2. 11112，棕带空手道班，约翰·史密斯，222，乔什·史密斯

我试过这样做:

var studentList =   from theClass in event
                    from staff in theClass.StaffMembers
                    from student in theClass.People
                    from visits in theClass.Visits
                    where visits.Status == "complete" 
                    && visits.State == "completed"
                           select new
                           {
                               event_id = theClass.EventId
                               class_name = theClass.Name,
                               instructor_name = staff.Name,
                               student_id = student.Id,
                               student_name = student.Name
                           };

string _data = JsonConvert.SerializeObject(studentList);

这自然会产生重复的学生名字。我是linq的新手。基本上，我需要加入/绑定人员和访问数组，所以我只是为该id返回一个学生，以及此事件的根数据。任何关于更好的方法的建议也非常感谢!

Answer 1

技巧是将学生和访问连接到包含两者数据的集合中:

from ei in eventInstances
from sm in ei.StaffMembers
from x in
(from vi in ei.Visits
 join st in ei.Students on vi.PersonId equals st.Id
 select new { vi, st }) // Here you get students and visits side-by-side
select new
{
    ei.EventId,
    Event = ei.Name,
    StaffMemeber = sm.Name,
    PersonId = x.st.Id,
    Student = x.st.Name
}