MVC学习笔记（四）---使用linq多表联查（SQL）

最新推荐文章于 2020-05-04 18:10:00 发布

weixin_30430169

最新推荐文章于 2020-05-04 18:10:00 发布

阅读量255

点赞数

CC 4.0 BY-SA版权

文章标签：测试 json 数据库

原文链接：http://www.cnblogs.com/jas0203/p/9670810.html

本文介绍如何使用C#和EF框架进行数据库查询，通过LINQ实现学生各科目成绩的关联查询，并展示具体代码实例。

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": "英语"
}]