1.数据库原型(Students表中的ID和Scores表中的StudentID是对应的)
2.实现效果:查询出每个学生各个科目的成绩(用的是MVC学习笔记(三)—用EF向数据库中添加数据的架构)
C#代码
public ActionResult Index() { try { EFContextDB db = new EFContextDB(); List<StudentScoreView> scoreList = new List<StudentScoreView>(); scoreList = (from s in db.Students join c in db.Scores on s.ID equals c.StudentID select new StudentScoreView { ID = s.ID, Name = s.Name, Subject = c.Subject, Score = c.StudentScore, School = s.School }).ToList(); return Json(scoreList, JsonRequestBehavior.AllowGet); } catch(Exception ex) { return Json("err", JsonRequestBehavior.AllowGet); } }
加了一个显示类studentScoreView
public class StudentScoreView { /// <summary> /// 主键 /// </summary> public Guid ID { get; set; } /// <summary> /// 姓名 /// </summary> public string Name { get; set; } /// <summary> /// 学校 /// </summary> public string School { get; set; } /// <summary> /// 分数 /// </summary> public int Score { get; set; } /// <summary> /// 科目 /// </summary> public string Subject { get; set; } }
3.最后返回json结果
[{ "ID": "5eaaff20-f806-4116-a387-39b44c4c76e0", "Name": "李四", "School": "厦门大学", "Score": 98, "Subject": "语文" }, { "ID": "5eaaff20-f806-4116-a387-39b44c4c76e0", "Name": "李四", "School": "厦门大学", "Score": 93, "Subject": "数学" }, { "ID": "5eaaff20-f806-4116-a387-39b44c4c76e0", "Name": "李四", "School": "厦门大学", "Score": 28, "Subject": "英语" }, { "ID": "662bf2f7-3680-490c-8ff8-9208e4e5f362", "Name": "张三", "School": "北京大学", "Score": 88, "Subject": "语文" }, { "ID": "662bf2f7-3680-490c-8ff8-9208e4e5f362", "Name": "张三", "School": "北京大学", "Score": 90, "Subject": "数学" }, { "ID": "662bf2f7-3680-490c-8ff8-9208e4e5f362", "Name": "张三", "School": "北京大学", "Score": 60, "Subject": "英语" }, { "ID": "5eaaff20-f806-4116-a387-39b44c4c76e0", "Name": "小明", "School": "中山大学", "Score": 91, "Subject": "语文" }, { "ID": "4321592e-863f-411a-b044-9478952c29d1", "Name": "小明", "School": "中山大学", "Score": 100, "Subject": "数学" }, { "ID": "4321592e-863f-411a-b044-9478952c29d1", "Name": "小明", "School": "中山大学", "Score": 92, "Subject": "英语" }]
linq中多条件的写法 用&&来连接
4.在实际过程中可能会遇到格式化字符串等等,按照下面方式(加上AsEnumerable())就可以了。
1 public ActionResult Index() 2 { 3 try 4 { 5 EFContextDB db = new EFContextDB(); 6 List<StudentScoreView> scoreList = new List<StudentScoreView>(); 7 scoreList = (from s in db.Students.AsEnumerable() 8 join 9 c in db.Scores.AsEnumerable() on s.ID equals c.StudentID 10 select new StudentScoreView 11 { 12 ID = s.ID, 13 Name = s.Name, 14 Subject = c.Subject, 15 Score = c.StudentScore, 16 School = s.School, 17 ApplyTime = i.CreateTime.ToString("yyyy-MM-dd HH:mm:ss") 18 }).ToList(); 19 return Json(scoreList, JsonRequestBehavior.AllowGet); 20 } 21 catch(Exception ex) 22 { 23 return Json("err", JsonRequestBehavior.AllowGet); 24 } 25 }
小插曲:中间在编译的过程中,遇到了“未能找到查询的实现”,解决方法是引用命名空间:using System.Linq
写在后面的话:不积跬步无以至千里,我喜欢用文字记录下自己学习的过程,虽然每一篇都是小知识点,每一篇都很简单,但我相信量变会引起质变,加油!