多表查询

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

开发工具与关键技术：VS、

作者：#33

撰写时间：撰写时间：2019年06月27日

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

MVC项目引入数据库后，实例化数据库及顶部using数据库和类：

、Models.NBAKUEntities myModel = new Models.NBAKUEntities();

、using NBAKU.Models;、using NBAKU.EntityClass;

学习linq的多表查询：数据图

数据表格的渲染：

tabnba = layuitable.render({   elem: "#tabnba".url: '/Main/Hoopmaninfor'.   cols: [[{ title: ''. type: 'checkbox' }.         { title: '序号'. type: 'numbers' }.         { title: '球员姓名'. field: 'HoopmanName'. align: 'left' }.         { title: '年龄'. field: 'Age'. align: 'center' }.         { title: '身高（CM）'. field: 'Stature'. align: 'center'. sort: true }.         { title: '体重(KG)'. field: 'Weight'. align: 'center'. sort: true }.         { title: '性别'. field: 'Sex'. align: 'center' }.         { title: '球衣号码'. field: 'HoopmanNum'. align: 'center' }.         { title: '球队'. field: 'BasketballTeamName'. align: 'center' }.         { title: '场上位置'. field: 'Positionl'. align: 'center' }.         { title: '国家'. field: 'Country'. align: 'center' }. ]].   page: {limit: 8. limits: [8. 10. 15. 20. 25.].}})

查询球员的信息，涉及到球员表（Hoopman）、国籍表（Nationality）、场上位置表（Position）、球队表（BasketballTeam）。球员表中分别引用其他三张表的主键（球员表的外键）作为查询的条件。多表查询（基本公式）：

var list表 =（from tb001 in myModel.001表 join tb002 in myModel.002表 on tb001.002ID equals tb002. 002ID join tb003 in myModel.003表 on tb001.003ID equals tb003. 003ID …… select new 001表infor（定义的类用于查询球员其他表中的字段） {                   赋值要查询的字段 }）

定义的类需要查询其他用到其他表的字段：如球队表中所属的队名、国籍表中的国名、场上位置表中的位置名。

public class HoopmanInfor:Models.Hoopman{ public string BasketballTeamName { get; set; }//球队       public string Country { get; set; }//国家       public string Positionl { get; set; }//位置 }

控制器查询方法：

public ActionResult Hoopmaninfor(LayuiTablePage layuiTablePage) {     var listHoopman = (from tbHoopman in myModel.Hoopman join tbBasketballTeam in myModel.BasketballTeam on tbHoopman.BasketballTeamID equals tbBasketballTeam.BasketballTeamID                       join tbNationality in myModel.Nationality on tbHoopman.NationalityID equals tbNationality.NationalityID                       join tbPosition in myModel.Position on tbHoopman.PositionID equals tbPosition.PositionID                   select new HoopmanInfor {//赋值要查询的字段       HoopmanID = tbHoopman.HoopmanID. HoopmanName = tbHoopman.HoopmanName.       HoopmanNum = tbHoopman.HoopmanNum. Age = tbHoopman.Age. Sex = tbHoopman.Sex. Stature = tbHoopman.Stature. Weight = tbHoopman.Weight.  NationalityID = tbNationality.NationalityID. Country = tbNationality.Country. BasketballTeamID = tbBasketballTeam.BasketballTeamID.      BasketballTeamName = tbBasketballTeam.BasketballTeamName.       PositionID = tbPosition.PositionID.  Positionl = tbPosition.Position1                         }).ToList(); int totalRows = listHoopman.Count();//总条数     List<HoopmanInfor> list = listHoopman.OrderByDescending(m => m.HoopmanID)         .Skip(layuiTablePage.GetStartIndex()).Take(layuiTablePage.limit).ToList();     LayuiTableData<HoopmanInfor> layuiTableData = new LayuiTableData<HoopmanInfor>();     layuiTableData.count = totalRows; layuiTableData.data = list;     return Json(layuiTableData. JsonRequestBehavior.AllowGet); }