嵌套查询数据

开发工具与关键技术：vs 嵌套查询
作者：黄海峰
撰写时间：2019.07.17

在做项目的时候我遇到了一个问题，当我查询一条车辆信息的时候，他会有一条或者多条驾驶员信息匹配，就像开车有正驾驶，副驾驶一样，多个驾驶员共同驾驶这辆车，而我需要将这一对多的关系显示在一条数据，当我用内联查询的时候他会由一条数据变为三条数据显示，这与我们想要在一条数据内显示出来不相符，让后我经过查找资料和询问他人使用了嵌套写法。通过查询数据用列表的形式保存，然后在这个列表里再声明一个列表，然后我们就可以在这个列表里查询驾驶员信息了，而且还不会造成数据重复，
public ActionResult SelectVehicleAll(LayuiTablePage layuiTablePage)
{
//查询数据
List< VehicleVo> listVehicle = (from tbVehicle in myModels.SYS_Vehicle
join tbStationUnit in myModels.SYS_StationUnit on tbVehicle.StationUnitID equals tbStationUnit.StationUnitID
join tbOwner in myModels.SYS_Owner on tbVehicle.OwnerID equals tbOwner.OwnerID
orderby tbVehicle.VehicleID
select new VehicleVo
{
VehicleID = tbVehicle.VehicleID,
VehicleNumber = tbVehicle.VehicleNumber,
StrRecordDate = tbVehicle.RecordDate.ToString(),
PlateNumber = tbVehicle.PlateNumber,
Seat = tbVehicle.Seat,
InsertSeat = tbVehicle.InsertSeat,
LabelNumber = tbVehicle.LabelNumber,
VehicleWeight = tbVehicle.VehicleWeight,
EngineNumber = tbVehicle.EngineNumber,
EngineCode = tbVehicle.EngineCode,
VehicleIdentifyNumber = tbVehicle.VehicleIdentifyNumber,
VehicleCondition = tbVehicle.VehicleCondition,
StationUnitName = tbStationUnit.StationUnitName,
OwnerName = tbOwner.OwnerName,
Address = tbOwner.Address,
OwnerTelphone = tbOwner.OwnerTelphone,
ownerIDNum = tbOwner.ownerIDNum,
VehicleTypeID = tbVehicle.VehicleTypeID,
CheckTypeID = tbVehicle.CheckTypeID,
InsuranceID = tbVehicle.InsuranceID,
LicenseID = tbVehicle.LicenseID,
StationUnitID = tbStationUnit.StationUnitID,
OwnerID = tbOwner.OwnerID,
DriverDetail = (from tbDriverDetail in myModels.SYS_DriverDetail
where tbDriverDetail.VehicleID == tbVehicle.VehicleID
orderby tbDriverDetail.DriverDetailID
select tbDriverDetail).ToList(),
}).ToList();
//计算数据总条数
int totalRow = listVehicle.Count();
//分页数据
List< VehicleVo> dbVehicle = listVehicle
.Skip(layuiTablePage.GetStartIndex())
.Take(layuiTablePage.limit)
.ToList();
//实例化
LayuiTableData< VehicleVo> layuiTableData = new LayuiTableData< VehicleVo>();
layuiTableData.count = totalRow;
layuiTableData.data = dbVehicle;
return Json(layuiTableData, JsonRequestBehavior.AllowGet);
}
我们查询数据后返回到页面就会变成下面的样子了