Loai Loai - 5 months ago 10
JSON Question

How to use join in Entity Framework to make output Json objects in levels - not the same level

I am trying to fetch data from a SQL Server database.

The database has 3 tables as shown here:

enter image description here

The tables relate to each other using primary and foreign key:


  • HALAQATI_View_GetAllMosques (Parent)

  • HALAQATI_View_GetAllRings (Child)

  • HALAQATI_View_GetAllStudents (Child Of Child)



Code:

var result = (from m in db.HALAQATI_VIEW_GetAllMosques
join r in db.HALAQATI_VIEW_GetAllRings on m.MSQ_ID equals r.MSQ_ID
join s in db.HALAQATI_VIEW_GetAllStudents on r.Ring_ID equals s.Ring_ID
where m.Emp_ID == r.Emp_ID && m.Emp_ID == id
select new { MsqID = m.MSQ_ID, MsqName = m.MSQ_Name, Rings = r,Students = s}).Distinct();
return new { Mosques = result };


The output appears like this

{
"Mosques": [
{
"MsqID": 186,
"MsqName": "UNIVERSITY 1",
"Rings": {
"Ring_ID": 3730,
"Ring_Name": "Class 1",
"MSQ_ID": 186,
"Emp_ID": 3750
},
"Students": {
"Student_ID": 80065,
"Student_Name": "Student 1",
"Ring_ID": 3730
}
}
]
}


The problem is that
Rings
And
Students
appear on the same level in the Json and that's wrong.

The
students
must come under the
Rings
.

The expected result must be like this:

{
"Mosques": [
{
"MsqID": 186,
"MsqName": "UNIVERSITY 1",
"Rings": [
{
"Ring_ID": 3730,
"Ring_Name": "Class 1",
"MSQ_ID": 186,
"Emp_ID": 3750,
"Students": [
{
"Student_ID": 80065,
"Student_Name": "Student 1",
"Ring_ID": 3730
}
]
}
]
}
]
}

Answer

This is roughly the pattern you need. Don't worry about doing the JOINs yourself: let the framework take care of joining tables, while you think of the shape of that data you're looking for.

var result = 
     from m in db.HALAQATI_VIEW_GetAllMosques
     where m.Emp_ID == id
     select new {
         MsqID = m.MSQ_ID,
         MsqName = m.MSQ_Name, 
         Rings = from r in db.HALAQATI_VIEW_GetAllRings
             where m.MSQ_ID == r.MSQ_ID
             where m.Emp_ID == r.Emp_ID  // is this even necessary?
             select new {
                 r.Ring_ID,
                 ...
                 Students = from s in db.HALAQATI_VIEW_GetAllStudents
                     where r.Ring_ID == s.Ring_ID
                     select s
             }
     };
return new { Mosques = result };