/**************join 练习*******************/
//对于1对多关系
var list =from c in ctx.T_Student
from s in c.T_Score
where c.stuName=="黄阳"
select s;
//也可以间接的通过表关联其它表，这个结果和上边的查询结果相同
var list1 = from s in ctx.T_Score
where s.T_Student.stuName=="黄阳"
select s;
//多对多关系,他们之间的关系是1：M:1
var list2 = from s in ctx.T_Cource
from c in s.T_Score
select new {
s.courceName,
c.score,
c.T_Student.stuName
};
//如果表里边有隶属关系，可以使用自联结，由于表的设计不是很好，所以没有包括这种情况
//双向链接
var list3 = from s in ctx.T_Student
join t in ctx.T_Score
on s.stuNumber
equals t.stuNumber
into g
select new {
stuName = s.stuName,
score = g.Sum(k=>k.score)
};
//三向链接
var list4 = from s in ctx.T_Score
join c in ctx.T_Cource
on s.courceNumber
equals c.courceNumber into temp1
join t in ctx.T_Student
on s.stuNumber
equals t.stuNumber into temp2
select new {
Score = s.score,
Name = temp1.Count()
};
//左外部链接
var list5 = from s in ctx.T_Student
join c in ctx.T_Score
on s.stuNumber
equals c.stuNumber
into temp1
from k in temp1.DefaultIfEmpty()
select new
{
stuName = s.stuName,
sumScore = k.score
};
//投影的let操作
var list6 = from s in ctx.T_Student
join c in ctx.T_Score
on s.stuNumber
equals c.stuNumber into temp1
let studentInfo = s.stuName +s.stuSex
from k in temp1
select new {
stuNumber=s.stuNumber,
CoreOfStudents = k.score,
studentInfomation = studentInfo
};
//使用组合健来表示多表之间的关系 ,这个由于表中设计时候，基本只有一个主键，没有用到联合主键
//一侧可为空另一侧不能为空的链接
var list7 = from s in ctx.T_Student
join c in ctx.T_Score
on Convert.ToInt32(s.stuNumber)
equals (int ?)Convert.ToInt32(c.stuNumber) into temp1
select new {
stuName = s.stuNumber,
sumCore = temp1.Sum(f=>f.score)
};