LINQ多表查询

View Code

#region Group,Join

//只有join，没有into，内联(inner join)

//var sql = from c in sdb.Classic

// join s in sdb.StuInfo

// on c.classID equals s.classID //注意不能用==

// select new // { // c.className, // s.stuName // };

//Console.WriteLine(sql);

//var sql = from s in sdb.StuInfo

// join m in sdb.StuMarks

// on s.stuNo equals m.stuNo

// into temp

//many端重新命名

// from t in temp.DefaultIfEmpty() //左连接，DefaultIfEmpty再次遍历右边，缺考人员的成绩默认为null

// select new

// { // s.stuName, // we = t.writtenExam == null ? 0 : t.writtenExam, // le = t.LabExam == null ? 0 : t.LabExam // };

//Console.WriteLine(sql);

//foreach (var item in sql)

//{ // Console.WriteLine("{0}\t{1}\t{2}",item.stuName,item.we,item.le); //}

//三向联接

//var sql = from c in sdb.Classic

// join s in sdb.StuInfo

// on c.classID equals s.classID

// join m in sdb.StuMarks

// on s.stuNo equals m.stuNo

// select new

// { // c.className,

// s.stuName,

// we = m.writtenExam == null ? 0 : m.writtenExam,

// m.LabExam // };

//Console.WriteLine(sql);

//foreach (var item in sql)

//{

// Console.WriteLine("{0}\t{1}\t{2}\t{3}", item.className, item.stuName, item.we, item.LabExam);

//}

//双向连接 var sql = from c in sdb.Classic join s in sdb.StuInfo on c.classID equals s.classID into stu select new { className = c.className, list=stu.ToList<StuInfo>(), count = stu.Count() }; Console.WriteLine(sql); foreach (var item in sql) { Console.WriteLine("班级：{0},人数：{1}",item.className,item.count); foreach (var s in item.list) { Console.WriteLine(s.stuName); } }

#endregion