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": ,
"Subject": "语文"
}, {
"ID": "5eaaff20-f806-4116-a387-39b44c4c76e0",
"Name": "李四",
"School": "厦门大学",
"Score": ,
"Subject": "数学"
}, {
"ID": "5eaaff20-f806-4116-a387-39b44c4c76e0",
"Name": "李四",
"School": "厦门大学",
"Score": ,
"Subject": "英语"
}, {
"ID": "662bf2f7-3680-490c-8ff8-9208e4e5f362",
"Name": "张三",
"School": "北京大学",
"Score": ,
"Subject": "语文"
}, {
"ID": "662bf2f7-3680-490c-8ff8-9208e4e5f362",
"Name": "张三",
"School": "北京大学",
"Score": ,
"Subject": "数学"
}, {
"ID": "662bf2f7-3680-490c-8ff8-9208e4e5f362",
"Name": "张三",
"School": "北京大学",
"Score": ,
"Subject": "英语"
}, {
"ID": "5eaaff20-f806-4116-a387-39b44c4c76e0",
"Name": "小明",
"School": "中山大学",
"Score": ,
"Subject": "语文"
}, {
"ID": "4321592e-863f-411a-b044-9478952c29d1",
"Name": "小明",
"School": "中山大学",
"Score": ,
"Subject": "数学"
}, {
"ID": "4321592e-863f-411a-b044-9478952c29d1",
"Name": "小明",
"School": "中山大学",
"Score": ,
"Subject": "英语"
}]
linq中多条件的写法 用&&来连接
4.在实际过程中可能会遇到格式化字符串等等，按照下面方式(加上AsEnumerable())就可以了。
public ActionResult Index()
{
try
{
EFContextDB db = new EFContextDB();
List<StudentScoreView> scoreList = new List<StudentScoreView>();
scoreList = (from s in db.Students.AsEnumerable()
join
c in db.Scores.AsEnumerable() on s.ID equals c.StudentID
select new StudentScoreView
{
ID = s.ID,
Name = s.Name,
Subject = c.Subject,
Score = c.StudentScore,
School = s.School,
ApplyTime = i.CreateTime.ToString("yyyy-MM-dd HH:mm:ss")
}).ToList();
return Json(scoreList, JsonRequestBehavior.AllowGet);
}
catch(Exception ex)
{
return Json("err", JsonRequestBehavior.AllowGet);
}
}
小插曲：中间在编译的过程中，遇到了“未能找到查询的实现”，解决方法是引用命名空间：using System.Linq
写在后面的话：不积跬步无以至千里，我喜欢用文字记录下自己学习的过程，虽然每一篇都是小知识点，每一篇都很简单，但我相信量变会引起质变，加油！