Linq to Sql ------------------------复习（二）

类中的代码
  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 
  6 namespace LinqTest
  7 {
  8 class LinqDemo_2
  9 {
 10 static void Main(string[] args)
 11 {
 12 StudentDBDataContext db = new StudentDBDataContext("server=.;uid=sa;pwd=123456;database=studentdb");
 13 #region Any/All/Contains操作符
 14 //返回没有参加考试的学生
 15 //Console.WriteLine("No	"+"Name	"+"Age");
 16 //foreach (var item in (from stu in db.stuInfo select stu))
 17 //{
 18 // Console.WriteLine(item.stuNo + "	" + item.stuName + "	" + item.stuAge);
 19 //}
 20 //Console.WriteLine();
 21 
 22 //var sql1 = from stu in db.stuInfo where !stu.stuMarks.Any() select stu;
 23 //Console.WriteLine(sql1);
 24 //foreach (var item in sql1)
 25 //{
 26 // Console.WriteLine(item.stuNo+"	"+item.stuName+"	"+item.stuAge);
 27 //}
 28 
 29 
 30 //Any:用于判断集合中是否有元素满足某一条件
 31 //var sql = from s in db.stuInfo
 32 // where !s.stuMarks.Any()
 33 // select s;
 34 //foreach (var s in sql)
 35 //{
 36 // Console.WriteLine("学生姓名:{0}", s.stuName);
 37 //}
 38 
 39 //ALL:用于判断集合中所有元素是否都满足某一条件
 40 
 41 Console.WriteLine("
");
 42 var sql2 = from stu in db.stuInfo where stu.stuMarks.All(sm => sm.stuNo != stu.stuNo) select stu;
 43 Console.WriteLine(sql2);
 44 foreach (var item in sql2)
 45 {
 46 Console.WriteLine(item.stuNo + "	" + item.stuName + "	" + item.stuAge);
 47 }
 48 
 49 Console.WriteLine();
 50 Console.WriteLine("no"+"	LabExam"+"	WrittenExam");
 51 foreach (var item in (from sm in db.stuMarks select sm))
 52 {
 53 Console.WriteLine(item.stuNo+"	"+item.LabExam+"	"+item.writtenExam);
 54 }
 55 //var sql = from s in db.stuInfo
 56 // where s.stuMarks.All(sc => sc.stuNo != s.stuNo)
 57 // select s;
 58 //foreach (var s in sql)
 59 //{
 60 // Console.WriteLine("学生姓名:{0}", s.stuName);
 61 //}
 62 
 63 // Contains子查询形式
 64 Console.WriteLine("Contains");
 65 var sql3 = from stu in db.stuInfo where !(from sm in db.stuMarks select sm.stuNo).Contains(stu.stuNo) select stu;
 66 Console.WriteLine("No	" + "Name	" + "Age");
 67 foreach (var item in sql3)
 68 {
 69 Console.WriteLine(item.stuNo + "	" + item.stuName + "	" + item.stuAge);
 70 }
 71 #endregion
 72 
 73 #region SelectMany 连接查询
 74 //SelectMany类型:我们在写查询语句时，如果被翻译成SelectMany需要满足2个条件。1：查询语句中没有join和into，2：必须出现EntitySet。在我们表关系中有一对一关系，一对多关系，多对多关系等
 75 //第一种形式:常规形式
 76 var sql4 = from stu in db.stuInfo
 77 from sm in db.stuMarks
 78 where stu.stuNo==sm.stuNo
 79 select new
 80 {
 81 姓名 = stu.stuName,
 82 笔试 = sm.writtenExam,
 83 机试 = sm.LabExam
 84 };
 85 Console.WriteLine("姓名	"+"笔试	"+"机试");
 86 foreach (var item in sql4)
 87 {
 88 Console.WriteLine(item.姓名+"	"+item.笔试+"	"+item.机试);
 89 }
 90 
 91 
 92 //第二种形式:SelectMany查询形式
 93 Console.WriteLine();
 94 var sql5 = from stu in db.stuInfo
 95 from sm in stu.stuMarks
 96 select new
 97 {
 98 姓名 = stu.stuName,
 99 笔试 = sm.writtenExam,
100 机试 = sm.LabExam
101 };
102 Console.WriteLine("姓名	" + "笔试	" + "机试");
103 foreach (var item in sql5)
104 {
105 Console.WriteLine(item.姓名 + "	" + item.笔试 + "	" + item.机试);
106 }
107 //var sql = from s in db.stuInfo
108 // from sc in s.stuMarks
109 // select new
110 // {
111 // 姓名 = s.stuName,
112 // 笔试 = sc.writtenExam,
113 // 机试 = sc.LabExam
114 // };
115 //Console.WriteLine(sql);
116 
117 //第三种:简化形式
118 Console.WriteLine();
119 var sql6 = from sm in db.stuMarks
120 select new
121 {
122 姓名 = sm.stuInfo.stuName,
123 笔试 = sm.writtenExam,
124 机试 = sm.LabExam,
125 班级= sm.stuInfo.classic.className
126 };
127 Console.WriteLine("姓名	" + "笔试	" + "机试");
128 foreach (var item in sql6)
129 {
130 Console.WriteLine(item.姓名 + "	" + item.笔试 + "	" + item.机试+"	"+item.班级);
131 }
132 //var sql = from s in db.stuInfo
133 // where s.classic.className == "T102"
134 // select s;
135 //Console.WriteLine(sql);
136 //foreach (var s in sql)
137 //{
138 // Console.WriteLine("学生姓名:{0},所在班级:{1}", s.stuName, s.classic.className);
139 //}
140 
141 //第四种:多对多的查询形式
142 //var sql = from s in db.stuInfo
143 // from sc in s.stuMarks
144 // select new
145 // {
146 // s.stuName,
147 // s.classic.className,
148 // sc.writtenExam,
149 // sc.LabExam
150 // };
151 //Console.WriteLine(sql);
152 //foreach (var s in sql)
153 //{
154 // Console.WriteLine("学生姓名:{0},所在班级:{1},笔试:{2},机试:{3}", s.stuName, s.className, s.writtenExam, s.LabExam);
155 //}
156 
157 Console.WriteLine("SelectMany");
158 var sql7 = db.stuInfo.SelectMany(s => s.stuMarks);
159 Console.WriteLine(sql7);
160 foreach (var item in sql7)
161 {
162 Console.WriteLine(item.stuNo+"	"+item.LabExam+"	"+item.writtenExam+"	");
163 }
164 #endregion
165 Console.WriteLine("aaaaaa");
166 //var sql8 = from stu in db.stuInfo
167 // join c in db.classic
168 // on stu.classID equals c.classID
169 // into s
170 // from ss in s.DefaultIfEmpty()
171 // select new
172 // {
173 // stu.stuNo,
174 // stu.stuName,
175 // stu.classID,
176 // count = s.Count(),
177 // ss.className
178 // };
179 //var sql8 = from c in db.classic
180 // join s in db.stuInfo
181 // on c.classID equals s.classID
182 // into cs
183 // from stu in cs.DefaultIfEmpty()
184 // select new
185 // {
186 
187 // stu.stuNo,
188 // stu.stuName,
189 // c.classID,
190 // c.className
191 // };
192 
193 //foreach (var item in sql8)
194 //{
195 // Console.WriteLine(item.stuNo + "	" + item.stuName + "	" + item.classID + "	" + item.className);
196 //}
197 #region GroupJoin连接查询
198 //像上面所说的，没有join和into，被翻译成SelectMany，同时有join和into时，那么就被翻译为GroupJoin,
199 //如果在连接查询中，需要使用聚合函数，可以采用GroupJoin进行分组连接查询
200 //只有join，没有into形式,类似于inner..join..on
201 //var sql = from c in db.classic
202 // join s in db.stuInfo
203 // on c.classID equals s.classID //注意采用equals替换==
204 // select new
205 // {
206 // c.className,
207 // s.stuName
208 // };
209 //Console.WriteLine(sql);
210 
211 //左外部联接(Left Outer Join):
212 //var sql = from s in db.stuInfo
213 // join sc in db.stuMarks
214 // on s.stuNo equals sc.stuNo
215 // into ss //many端重新命名
216 // from o in ss.DefaultIfEmpty() //左连接,DefaultIfEmpty再次遍历右边,没有的学生默认为null
217 // select new
218 // {
219 // s.stuName,
220 // writtenExam = o.writtenExam==null?0:o.writtenExam,
221 // labExam = o.LabExam==null?0:o.LabExam
222 
223 // };
224 //Console.WriteLine(sql);
225 //foreach (var s in sql)
226 //{
227 // Console.WriteLine("姓名:{0},笔试:{1},机试:{2}",s.stuName,s.writtenExam,s.labExam);
228 //}
229 
230 //三向连接
231 //var sql = from c in db.classic
232 // join s in db.stuInfo
233 // on c.classID equals s.classID
234 // join sc in db.stuMarks
235 // on s.stuNo equals sc.stuNo
236 // select new
237 // {
238 // c.className,
239 // s.stuName,
240 // sc.writtenExam,
241 // sc.LabExam
242 // };
243 //Console.WriteLine(sql);
244 //foreach (var s in sql)
245 //{
246 // Console.WriteLine("学生姓名:{0},所在班级:{1},笔试:{2},机试:{3}", s.className, s.stuName, s.writtenExam, s.LabExam);
247 //}
248 
249 //双向联接(Two way join)：
250 //var sql = from c in db.classic
251 // join s in db.stuInfo
252 // on c.classID equals s.classID
253 // into stu
254 // select new
255 // {
256 // className = c.className,
257 // count = stu.Count()
258 // };
259 //Console.WriteLine(sql);
260 //foreach (var s in sql)
261 //{
262 // Console.WriteLine("班级:{0},人数:{1}", s.className, s.count);
263 //}
264 #endregion
265 }
266 }
267 }
View Code
数据库中的代码和Linq to Sql ------------------------复习（一）是相同的