linq to sql 学习（9）linq的多表关联汇总

来源：互联网发布：淘宝店铺宝贝素材图片编辑：程序博客网时间：2024/06/14 03:57

①，我们准备两张数据表：

学生资料表：StudentData

if exists (select * from sysobjects where id = OBJECT_ID('[StudentData]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)

DROP TABLE [StudentData]

CREATE TABLE [StudentData] (

[Sid] [int] NOT NULL,

[Student] [varchar] (50) NULL)

ALTER TABLE [StudentData] WITH NOCHECK ADD CONSTRAINT [PK_StudentData] PRIMARY KEY NONCLUSTERED ( [Sid] )

INSERT [StudentData] ([Sid],[Student]) VALUES ( 1,'学生A')

INSERT [StudentData] ([Sid],[Student]) VALUES ( 2,'学生B')

INSERT [StudentData] ([Sid],[Student]) VALUES ( 3,'学生C')

INSERT [StudentData] ([Sid],[Student]) VALUES ( 4,'学生D')

INSERT [StudentData] ([Sid],[Student]) VALUES ( 5,'学生E')

INSERT [StudentData] ([Sid],[Student]) VALUES ( 6,'学生F')

INSERT [StudentData] ([Sid],[Student]) VALUES ( 7,'学生G')

INSERT [StudentData] ([Sid],[Student]) VALUES ( 8,'学生H')

SidStudent1学生A2学生B3学生C4学生D5学生E6学生F7学生G8学生H

学生成绩表StudentsScore

if exists (select * from sysobjects where id = OBJECT_ID('[StudentsScore]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)

DROP TABLE [StudentsScore]

CREATE TABLE [StudentsScore] (

[id] [int] NOT NULL,

[sid] [int] NULL,

[Student] [varchar] (50) NULL,

[Subject] [varchar] (50) NULL,

[Score] [int] NULL)

ALTER TABLE [StudentsScore] WITH NOCHECK ADD CONSTRAINT [PK_StudentsScore] PRIMARY KEY NONCLUSTERED ( [id] )

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 1,1,'学生A','中文',80)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 2,1,'学生A','数学',78)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 3,1,'学生A','英语',92)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 4,2,'学生B','中文',89)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 5,2,'学生B','数学',87)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 6,2,'学生B','英语',75)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 7,3,'学生C','中文',92)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 8,3,'学生C','数学',74)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 9,3,'学生C','英语',65)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 10,4,'学生D','中文',79)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 11,4,'学生D','数学',83)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 12,4,'学生D','英语',81)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 13,5,'学生E','中文',73)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 14,5,'学生E','数学',84)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 15,5,'学生E','英语',93)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 16,6,'学生F','中文',79)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 17,6,'学生F','数学',86)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 18,6,'学生F','英语',84)

INSERT [StudentsScore] ([id],[sid],[Student],[Subject],[Score]) VALUES ( 19,7,'学生G','数学',90)

idsidStudentSubjectScore11学生A中文8021学生A数学7831学生A英语9242学生B中文8952学生B数学8762学生B英语7573学生C中文9283学生C数学7493学生C英语65104学生D中文79114学生D数学83124学生D英语81135学生E中文73145学生E数学84155学生E英语93166学生F中文79176学生F数学86186学生F英语84197学生G数学90

②，我们新建一个LINQ TO SQL类，名称为Student.dbml ，并将这两张表加到改窗体上。

③，我们新建一个页面，Student.aspx,我们这里面只放一个GridView视图控件，用来绑定数据。

接下来我们编写在Student.aspx.cs后台的代码，如下：

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Collections.Generic;

namespace LinqDemo

{

public partial class Student : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

bind();

}

public void bind()

{

StudentDataContext db = new StudentDataContext();

//学生资料表

var pre1 = PredicateBuilder.True<StudentData>();

List<StudentData> studentdataQuery = db.StudentData.Where(pre1).ToList();

//学生成绩表

var pre2 = PredicateBuilder.True<StudentsScore>();

List<StudentsScore> studentsScoreQuery = db.StudentsScore.Where(pre2).ToList();

//先算出学生成绩表中各学生的总分汇总

var ssGroupByStu = from p in studentsScoreQuery

group p by new { p.sid, p.Student } into g

select

new

{

Sid = g.Key.sid,

Student = g.Key.Student,

总分 = g == null ? 0 : g.Sum(a => a.Score)

};

//在左连接学生表

var query = from sd in studentdataQuery

join

ss in ssGroupByStu

on sd.Sid equals ss.Sid into g

from j in g.DefaultIfEmpty()

select new

{

学号 = sd.Sid,

姓名 = sd.Student,

总分 = j == null ? 0 : j.总分,

评价 = j == null ? "暂无数据" : GetEvaluation(Convert.ToInt32(j.总分))

};

//最后按照总分降序排序

query = query.OrderByDescending(a => a.总分);

gd.DataSource = query;

gd.DataBind();

}

//对总分的判断

private string GetEvaluation(int score)

{

if (score < 200)

{

return "太差了！";

}

else if (score > 200 && score<240)

{

return "还可以！";

}

else if (score >= 240 && score < 300)

{

return "还不错！";

}

else

{

return "";

}

我们运行这个页面后，就得到如下结果。

学号姓名总分评价2学生B251还不错！1学生A250还不错！5学生E250还不错！6学生F249还不错！4学生D243还不错！3学生C231还可以！7学生G90太差了！8学生H0暂无数据