我正在尝试从SQL Server数据库中获取数据。
该数据库具有3个表,如下所示:
这些表使用主键和外键相互关联:
HALAQATI_View_GetAllMosques(父母)
HALAQATI_View_GetAllRings(儿童)
HALAQATI_View_GetAllStudents(儿童之子)
码:
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 };
输出看起来像这样
{
"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
}
}
]
}
问题是
Rings
和Students
在Json中出现在同一级别,这是错误的。students
必须位于Rings
下。预期结果必须是这样的:
{
"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
}
]
}
]
}
]
}
最佳答案
这大致就是您需要的模式。不必担心自己做JOIN:在考虑要查找的数据形状的同时,让框架负责连接表。
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 };