学习一下Linq,关于多表的查询方式有很多种,今天练习了一下使用join连接查询:就是将多个表所需要的数据聚合在一张大表中;
上代码:
//定义三种数据类型UserTable、ClassTable、StudentGradeTable
public class UserTable//用户表
{
public string UserId{ get; set; }
public string Name { get; set; }
public string Gender { get; set; }
public string IdCard { get; set; }
public DateTime Birthday { get; set; }
}
public class ClassTable//班级表
{
public string ClassId { get; set; }
public string Name { get; set; }
public string Year { get; set; }
public int Number { get; set; }
public string TeacherUserId { get; set; }
}
public class StudentGradeTable//学生成绩表
{
public string UserId { get; set; }
public string ClassId { get; set; }
public float TotalScore { get; set; }
}
//首先建立了三个列表
List<UserTable> userTableList = new List<UserTable>();
List<ClassTable> classTableList = new List<ClassTable>();
List<StudentGradeTable> studentGradeTableList = new List<StudentGradeTable>();
//然后塞一些数据进去用于调试
///LINQ查询
//查询一年级成绩前n的学生信息,学生信息包括:姓名、学号、性别、班级、总分;按总分由大到小排序
List<StudentMessageResult> studentMessageResultList = new List<StudentMessageResult>();
//一年级所有班级以及成绩
var gradeFirstList = classTableList.Join(studentGradeTableList, c => c.ClassId, s => s.ClassId, (c, s) => new { c, s })
.Where(x => x.c.Year == "一");
var list = gradeFirstList
.SelectMany(g => userTableList, (grade, student) => new { grade, student })
.Where(y => y.grade.s.UserId == y.student.UserId)
.OrderByDescending(z => z.grade.s.TotalScore)
.Take(n);
我感觉这个方法看起来有点笨,但是简单粗暴@^@;
由于测试数据建的少,所以代码可能还存在一些问题,仅供参考!欢迎大佬指正!