|
0
|
1 using System;
|
|
|
2 using System.Linq;
|
|
|
3
|
|
|
4 using NUnit.Framework;
|
|
|
5
|
|
|
6 using BLToolkit.Data.DataProvider;
|
|
|
7
|
|
|
8 namespace Data.Linq
|
|
|
9 {
|
|
|
10 using Model;
|
|
|
11
|
|
|
12 [TestFixture]
|
|
|
13 public class GroupByTest : TestBase
|
|
|
14 {
|
|
|
15 [Test]
|
|
|
16 public void Simple1()
|
|
|
17 {
|
|
|
18 BLToolkit.Common.Configuration.Linq.PreloadGroups = true;
|
|
|
19
|
|
|
20 ForEachProvider(db =>
|
|
|
21 {
|
|
|
22 var q =
|
|
|
23 from ch in db.Child
|
|
|
24 group ch by ch.ParentID;
|
|
|
25
|
|
|
26 var list = q.ToList().Where(n => n.Key < 6).OrderBy(n => n.Key).ToList();
|
|
|
27
|
|
|
28 Assert.AreEqual(4, list.Count);
|
|
|
29
|
|
|
30 for (var i = 0; i < list.Count; i++)
|
|
|
31 {
|
|
|
32 var values = list[i].OrderBy(c => c.ChildID).ToList();
|
|
|
33
|
|
|
34 Assert.AreEqual(i + 1, list[i].Key);
|
|
|
35 Assert.AreEqual(i + 1, values.Count);
|
|
|
36
|
|
|
37 for (var j = 0; j < values.Count; j++)
|
|
|
38 Assert.AreEqual((i + 1) * 10 + j + 1, values[j].ChildID);
|
|
|
39 }
|
|
|
40 });
|
|
|
41 }
|
|
|
42
|
|
|
43 [Test]
|
|
|
44 public void Simple2()
|
|
|
45 {
|
|
|
46 BLToolkit.Common.Configuration.Linq.PreloadGroups = false;
|
|
|
47
|
|
|
48 ForEachProvider(db =>
|
|
|
49 {
|
|
|
50 var q =
|
|
|
51 from ch in db.GrandChild
|
|
|
52 group ch by new { ch.ParentID, ch.ChildID };
|
|
|
53
|
|
|
54 var list = q.ToList();
|
|
|
55
|
|
|
56 Assert.AreEqual (8, list.Count);
|
|
|
57 Assert.AreNotEqual(0, list.OrderBy(c => c.Key.ParentID).First().ToList().Count);
|
|
|
58 });
|
|
|
59 }
|
|
|
60
|
|
|
61 [Test]
|
|
|
62 public void Simple3()
|
|
|
63 {
|
|
|
64 ForEachProvider(db =>
|
|
|
65 {
|
|
|
66 var q =
|
|
|
67 from ch in db.Child
|
|
|
68 group ch by ch.ParentID into g
|
|
|
69 select g.Key;
|
|
|
70
|
|
|
71 var list = q.ToList().Where(n => n < 6).OrderBy(n => n).ToList();
|
|
|
72
|
|
|
73 Assert.AreEqual(4, list.Count);
|
|
|
74 for (var i = 0; i < list.Count; i++) Assert.AreEqual(i + 1, list[i]);
|
|
|
75 });
|
|
|
76 }
|
|
|
77
|
|
|
78 [Test]
|
|
|
79 public void Simple4()
|
|
|
80 {
|
|
|
81 ForEachProvider(db =>
|
|
|
82 {
|
|
|
83 var q =
|
|
|
84 from ch in db.Child
|
|
|
85 group ch by ch.ParentID into g
|
|
|
86 orderby g.Key
|
|
|
87 select g.Key;
|
|
|
88
|
|
|
89 var list = q.ToList().Where(n => n < 6).ToList();
|
|
|
90
|
|
|
91 Assert.AreEqual(4, list.Count);
|
|
|
92 for (var i = 0; i < list.Count; i++) Assert.AreEqual(i + 1, list[i]);
|
|
|
93 });
|
|
|
94 }
|
|
|
95
|
|
|
96 [Test]
|
|
|
97 public void Simple5()
|
|
|
98 {
|
|
|
99 var expected =
|
|
|
100 from ch in GrandChild
|
|
|
101 group ch by new { ch.ParentID, ch.ChildID } into g
|
|
|
102 group g by new { g.Key.ParentID } into g
|
|
|
103 select g.Key;
|
|
|
104
|
|
|
105 ForEachProvider(db => AreEqual(expected,
|
|
|
106 from ch in db.GrandChild
|
|
|
107 group ch by new { ch.ParentID, ch.ChildID } into g
|
|
|
108 group g by new { g.Key.ParentID } into g
|
|
|
109 select g.Key));
|
|
|
110 }
|
|
|
111
|
|
|
112 [Test]
|
|
|
113 public void Simple6()
|
|
|
114 {
|
|
|
115 ForEachProvider(db =>
|
|
|
116 {
|
|
|
117 var q = db.GrandChild.GroupBy(ch => new { ch.ParentID, ch.ChildID }, ch => ch.GrandChildID);
|
|
|
118 var list = q.ToList();
|
|
|
119
|
|
|
120 Assert.AreNotEqual(0, list[0].Count());
|
|
|
121 Assert.AreEqual (8, list.Count);
|
|
|
122 });
|
|
|
123 }
|
|
|
124
|
|
|
125 [Test]
|
|
|
126 public void Simple7()
|
|
|
127 {
|
|
|
128 ForEachProvider(db =>
|
|
|
129 {
|
|
|
130 var q = db.GrandChild
|
|
|
131 .GroupBy(ch => new { ch.ParentID, ch.ChildID }, ch => ch.GrandChildID)
|
|
|
132 .Select (gr => new { gr.Key.ParentID, gr.Key.ChildID });
|
|
|
133
|
|
|
134 var list = q.ToList();
|
|
|
135 Assert.AreEqual(8, list.Count);
|
|
|
136 });
|
|
|
137 }
|
|
|
138
|
|
|
139 [Test]
|
|
|
140 public void Simple8()
|
|
|
141 {
|
|
|
142 ForEachProvider(db =>
|
|
|
143 {
|
|
|
144 var q = db.GrandChild.GroupBy(ch => new { ch.ParentID, ch.ChildID }, (g,ch) => g.ChildID);
|
|
|
145
|
|
|
146 var list = q.ToList();
|
|
|
147 Assert.AreEqual(8, list.Count);
|
|
|
148 });
|
|
|
149 }
|
|
|
150
|
|
|
151 [Test]
|
|
|
152 public void Simple9()
|
|
|
153 {
|
|
|
154 ForEachProvider(db =>
|
|
|
155 {
|
|
|
156 var q = db.GrandChild.GroupBy(ch => new { ch.ParentID, ch.ChildID }, ch => ch.GrandChildID, (g,ch) => g.ChildID);
|
|
|
157 var list = q.ToList();
|
|
|
158
|
|
|
159 Assert.AreEqual(8, list.Count);
|
|
|
160 });
|
|
|
161 }
|
|
|
162
|
|
|
163 [Test]
|
|
|
164 public void Simple10()
|
|
|
165 {
|
|
|
166 var expected = (from ch in Child group ch by ch.ParentID into g select g).ToList().OrderBy(p => p.Key).ToList();
|
|
|
167
|
|
|
168 ForEachProvider(db =>
|
|
|
169 {
|
|
|
170 var result = (from ch in db.Child group ch by ch.ParentID into g select g).ToList().OrderBy(p => p.Key).ToList();
|
|
|
171
|
|
|
172 AreEqual(expected[0], result[0]);
|
|
|
173 AreEqual(expected.Select(p => p.Key), result.Select(p => p.Key));
|
|
|
174 AreEqual(expected[0].ToList(), result[0].ToList());
|
|
|
175 });
|
|
|
176 }
|
|
|
177
|
|
|
178 [Test]
|
|
|
179 public void Simple11()
|
|
|
180 {
|
|
|
181 ForEachProvider(db =>
|
|
|
182 {
|
|
|
183 var q1 = GrandChild
|
|
|
184 .GroupBy(ch => new { ParentID = ch.ParentID + 1, ch.ChildID }, ch => ch.ChildID);
|
|
|
185
|
|
|
186 var q2 = db.GrandChild
|
|
|
187 .GroupBy(ch => new { ParentID = ch.ParentID + 1, ch.ChildID }, ch => ch.ChildID);
|
|
|
188
|
|
|
189 var list1 = q1.AsEnumerable().OrderBy(_ => _.Key.ChildID).ToList();
|
|
|
190 var list2 = q2.AsEnumerable().OrderBy(_ => _.Key.ChildID).ToList();
|
|
|
191
|
|
|
192 Assert.AreEqual(list1.Count, list2.Count);
|
|
|
193 Assert.AreEqual(list1[0].ToList(), list2[0].ToList());
|
|
|
194 });
|
|
|
195 }
|
|
|
196
|
|
|
197 [Test]
|
|
|
198 public void Simple12()
|
|
|
199 {
|
|
|
200 ForEachProvider(db =>
|
|
|
201 {
|
|
|
202 var q = db.GrandChild
|
|
|
203 .GroupBy(ch => new { ParentID = ch.ParentID + 1, ch.ChildID }, (g,ch) => g.ChildID);
|
|
|
204
|
|
|
205 var list = q.ToList();
|
|
|
206 Assert.AreEqual(8, list.Count);
|
|
|
207 });
|
|
|
208 }
|
|
|
209
|
|
|
210 [Test]
|
|
|
211 public void Simple13()
|
|
|
212 {
|
|
|
213 ForEachProvider(db =>
|
|
|
214 {
|
|
|
215 var q = db.GrandChild
|
|
|
216 .GroupBy(ch => new { ParentID = ch.ParentID + 1, ch.ChildID }, ch => ch.ChildID, (g,ch) => g.ChildID);
|
|
|
217
|
|
|
218 var list = q.ToList();
|
|
|
219 Assert.AreEqual(8, list.Count);
|
|
|
220 });
|
|
|
221 }
|
|
|
222
|
|
|
223 //[Test]
|
|
|
224 public void Simple14()
|
|
|
225 {
|
|
|
226 ForEachProvider(db => AreEqual(
|
|
|
227 from p in Parent
|
|
|
228 select
|
|
|
229 from c in p.Children
|
|
|
230 group c by c.ParentID into g
|
|
|
231 select g.Key,
|
|
|
232 from p in db.Parent
|
|
|
233 select
|
|
|
234 from c in p.Children
|
|
|
235 group c by c.ParentID into g
|
|
|
236 select g.Key));
|
|
|
237 }
|
|
|
238
|
|
|
239 [Test]
|
|
|
240 public void MemberInit1()
|
|
|
241 {
|
|
|
242 ForEachProvider(db => AreEqual(
|
|
|
243 from ch in Child
|
|
|
244 group ch by new Child { ParentID = ch.ParentID } into g
|
|
|
245 select g.Key,
|
|
|
246 from ch in db.Child
|
|
|
247 group ch by new Child { ParentID = ch.ParentID } into g
|
|
|
248 select g.Key));
|
|
|
249 }
|
|
|
250
|
|
|
251 class GroupByInfo
|
|
|
252 {
|
|
|
253 public GroupByInfo Prev;
|
|
|
254 public object Field;
|
|
|
255
|
|
|
256 public override bool Equals(object obj)
|
|
|
257 {
|
|
|
258 return Equals(obj as GroupByInfo);
|
|
|
259 }
|
|
|
260
|
|
|
261 public bool Equals(GroupByInfo other)
|
|
|
262 {
|
|
|
263 if (ReferenceEquals(null, other)) return false;
|
|
|
264 if (ReferenceEquals(this, other)) return true;
|
|
|
265 return Equals(other.Prev, Prev) && Equals(other.Field, Field);
|
|
|
266 }
|
|
|
267
|
|
|
268 public override int GetHashCode()
|
|
|
269 {
|
|
|
270 unchecked
|
|
|
271 {
|
|
|
272 return ((Prev != null ? Prev.GetHashCode() : 0) * 397) ^ (Field != null ? Field.GetHashCode() : 0);
|
|
|
273 }
|
|
|
274 }
|
|
|
275 }
|
|
|
276
|
|
|
277 [Test]
|
|
|
278 public void MemberInit2()
|
|
|
279 {
|
|
|
280 ForEachProvider(db => AreEqual(
|
|
|
281 from ch in Child
|
|
|
282 group ch by new GroupByInfo { Prev = new GroupByInfo { Field = ch.ParentID }, Field = ch.ChildID } into g
|
|
|
283 select g.Key,
|
|
|
284 from ch in db.Child
|
|
|
285 group ch by new GroupByInfo { Prev = new GroupByInfo { Field = ch.ParentID }, Field = ch.ChildID } into g
|
|
|
286 select g.Key));
|
|
|
287 }
|
|
|
288
|
|
|
289 [Test]
|
|
|
290 public void MemberInit3()
|
|
|
291 {
|
|
|
292 ForEachProvider(db => AreEqual(
|
|
|
293 from ch in Child
|
|
|
294 group ch by new { Prev = new { Field = ch.ParentID }, Field = ch.ChildID } into g
|
|
|
295 select g.Key,
|
|
|
296 from ch in db.Child
|
|
|
297 group ch by new { Prev = new { Field = ch.ParentID }, Field = ch.ChildID } into g
|
|
|
298 select g.Key));
|
|
|
299 }
|
|
|
300
|
|
|
301 [Test]
|
|
|
302 public void SubQuery1()
|
|
|
303 {
|
|
|
304 var n = 1;
|
|
|
305
|
|
|
306 var expected =
|
|
|
307 from ch in
|
|
|
308 from ch in Child select ch.ParentID + 1
|
|
|
309 where ch + 1 > n
|
|
|
310 group ch by ch into g
|
|
|
311 select g.Key;
|
|
|
312
|
|
|
313 ForEachProvider(db => AreEqual(expected,
|
|
|
314 from ch in
|
|
|
315 from ch in db.Child select ch.ParentID + 1
|
|
|
316 where ch > n
|
|
|
317 group ch by ch into g
|
|
|
318 select g.Key));
|
|
|
319 }
|
|
|
320
|
|
|
321 [Test]
|
|
|
322 public void SubQuery2()
|
|
|
323 {
|
|
|
324 var n = 1;
|
|
|
325
|
|
|
326 var expected =
|
|
|
327 from ch in Child select new { ParentID = ch.ParentID + 1 } into ch
|
|
|
328 where ch.ParentID > n
|
|
|
329 group ch by ch into g
|
|
|
330 select g.Key;
|
|
|
331
|
|
|
332 ForEachProvider(db => AreEqual(expected,
|
|
|
333 from ch in db.Child select new { ParentID = ch.ParentID + 1 } into ch
|
|
|
334 where ch.ParentID > n
|
|
|
335 group ch by ch into g
|
|
|
336 select g.Key));
|
|
|
337 }
|
|
|
338
|
|
|
339 [Test]
|
|
|
340 public void SubQuery3()
|
|
|
341 {
|
|
|
342 ForEachProvider(db => AreEqual(
|
|
|
343 from ch in
|
|
|
344 from ch in Child
|
|
|
345 select new { ch, n = ch.ChildID + 1 }
|
|
|
346 group ch by ch.n into g
|
|
|
347 select new
|
|
|
348 {
|
|
|
349 g.Key,
|
|
|
350 Sum = g.Sum(_ => _.ch.ParentID)
|
|
|
351 },
|
|
|
352 from ch in
|
|
|
353 from ch in db.Child
|
|
|
354 select new { ch, n = ch.ChildID + 1 }
|
|
|
355 group ch by ch.n into g
|
|
|
356 select new
|
|
|
357 {
|
|
|
358 g.Key,
|
|
|
359 Sum = g.Sum(_ => _.ch.ParentID)
|
|
|
360 }));
|
|
|
361 }
|
|
|
362
|
|
|
363 [Test]
|
|
|
364 public void SubQuery31()
|
|
|
365 {
|
|
|
366 ForEachProvider(db => AreEqual(
|
|
|
367 from ch in
|
|
|
368 from ch in Child
|
|
|
369 select new { ch, n = ch.ChildID + 1 }
|
|
|
370 group ch.ch by ch.n into g
|
|
|
371 select new
|
|
|
372 {
|
|
|
373 g.Key,
|
|
|
374 Sum = g.Sum(_ => _.ParentID)
|
|
|
375 },
|
|
|
376 from ch in
|
|
|
377 from ch in db.Child
|
|
|
378 select new { ch, n = ch.ChildID + 1 }
|
|
|
379 group ch.ch by ch.n into g
|
|
|
380 select new
|
|
|
381 {
|
|
|
382 g.Key,
|
|
|
383 Sum = g.Sum(_ => _.ParentID)
|
|
|
384 }));
|
|
|
385 }
|
|
|
386
|
|
|
387 [Test]
|
|
|
388 public void SubQuery32()
|
|
|
389 {
|
|
|
390 ForEachProvider(db => AreEqual(
|
|
|
391 from ch in
|
|
|
392 from ch in Child
|
|
|
393 select new { ch, n = ch.ChildID + 1 }
|
|
|
394 group ch.ch.ParentID by ch.n into g
|
|
|
395 select new
|
|
|
396 {
|
|
|
397 g.Key,
|
|
|
398 Sum = g.Sum(_ => _)
|
|
|
399 },
|
|
|
400 from ch in
|
|
|
401 from ch in db.Child
|
|
|
402 select new { ch, n = ch.ChildID + 1 }
|
|
|
403 group ch.ch.ParentID by ch.n into g
|
|
|
404 select new
|
|
|
405 {
|
|
|
406 g.Key,
|
|
|
407 Sum = g.Sum(_ => _)
|
|
|
408 }));
|
|
|
409 }
|
|
|
410
|
|
|
411 [Test]
|
|
|
412 public void SubQuery4()
|
|
|
413 {
|
|
|
414 ForEachProvider(db => AreEqual(
|
|
|
415 from ch in Child
|
|
|
416 group ch by new { n = ch.ChildID + 1 } into g
|
|
|
417 select new
|
|
|
418 {
|
|
|
419 g.Key,
|
|
|
420 Sum = g.Sum(_ => _.ParentID)
|
|
|
421 },
|
|
|
422 from ch in db.Child
|
|
|
423 group ch by new { n = ch.ChildID + 1 } into g
|
|
|
424 select new
|
|
|
425 {
|
|
|
426 g.Key,
|
|
|
427 Sum = g.Sum(_ => _.ParentID)
|
|
|
428 }));
|
|
|
429 }
|
|
|
430
|
|
|
431 [Test]
|
|
|
432 public void SubQuery5()
|
|
|
433 {
|
|
|
434 ForEachProvider(db => AreEqual(
|
|
|
435 from ch in Child
|
|
|
436 join p in Parent on ch.ParentID equals p.ParentID into pg
|
|
|
437 from p in pg.DefaultIfEmpty()
|
|
|
438 group ch by ch.ChildID into g
|
|
|
439 select g.Sum(_ => _.ParentID),
|
|
|
440 from ch in db.Child
|
|
|
441 join p in db.Parent on ch.ParentID equals p.ParentID into pg
|
|
|
442 from p in pg.DefaultIfEmpty()
|
|
|
443 group ch by ch.ChildID into g
|
|
|
444 select g.Sum(_ => _.ParentID)));
|
|
|
445 }
|
|
|
446
|
|
|
447 [Test]
|
|
|
448 public void SubQuery6()
|
|
|
449 {
|
|
|
450 var expected =
|
|
|
451 from ch in Child select new { ParentID = ch.ParentID + 1 } into ch
|
|
|
452 group ch.ParentID by ch into g
|
|
|
453 select g.Key;
|
|
|
454
|
|
|
455 ForEachProvider(db => AreEqual(expected,
|
|
|
456 from ch in db.Child select new { ParentID = ch.ParentID + 1 } into ch
|
|
|
457 group ch.ParentID by ch into g
|
|
|
458 select g.Key));
|
|
|
459 }
|
|
|
460
|
|
|
461 [Test]
|
|
|
462 public void SubQuery7()
|
|
|
463 {
|
|
|
464 ForEachProvider(db => AreEqual(
|
|
|
465 from p in Parent
|
|
|
466 join c in
|
|
|
467 from c in Child
|
|
|
468 where c.ParentID == 1
|
|
|
469 select c
|
|
|
470 on p.ParentID equals c.ParentID into g
|
|
|
471 from c in g.DefaultIfEmpty()
|
|
|
472 group p by c == null ? 0 : c.ChildID into gg
|
|
|
473 select new { gg.Key },
|
|
|
474 from p in db.Parent
|
|
|
475 join c in
|
|
|
476 from c in db.Child
|
|
|
477 where c.ParentID == 1
|
|
|
478 select c
|
|
|
479 on p.ParentID equals c.ParentID into g
|
|
|
480 from c in g.DefaultIfEmpty()
|
|
|
481 group p by c.ChildID into gg
|
|
|
482 select new { gg.Key }));
|
|
|
483 }
|
|
|
484
|
|
|
485 [Test]
|
|
|
486 public void Calculated1()
|
|
|
487 {
|
|
|
488 var expected =
|
|
|
489 (
|
|
|
490 from ch in Child
|
|
|
491 group ch by ch.ParentID > 2 ? ch.ParentID > 3 ? "1" : "2" : "3"
|
|
|
492 into g select g
|
|
|
493 ).ToList().OrderBy(p => p.Key).ToList();
|
|
|
494
|
|
|
495 ForEachProvider(db =>
|
|
|
496 {
|
|
|
497 var result =
|
|
|
498 (
|
|
|
499 from ch in db.Child
|
|
|
500 group ch by ch.ParentID > 2 ? ch.ParentID > 3 ? "1" : "2" : "3"
|
|
|
501 into g select g
|
|
|
502 ).ToList().OrderBy(p => p.Key).ToList();
|
|
|
503
|
|
|
504 AreEqual(expected[0], result[0]);
|
|
|
505 AreEqual(expected.Select(p => p.Key), result.Select(p => p.Key));
|
|
|
506 });
|
|
|
507 }
|
|
|
508
|
|
|
509 [Test]
|
|
|
510 public void Calculated2()
|
|
|
511 {
|
|
|
512 var expected =
|
|
|
513 from p in
|
|
|
514 from ch in
|
|
|
515 from ch in Child
|
|
|
516 group ch by ch.ParentID > 2 ? ch.ParentID > 3 ? "1" : "2" : "3"
|
|
|
517 into g select g
|
|
|
518 select ch.Key + "2"
|
|
|
519 where p == "22"
|
|
|
520 select p;
|
|
|
521
|
|
|
522 ForEachProvider(db => AreEqual(expected,
|
|
|
523 from p in
|
|
|
524 from ch in
|
|
|
525 from ch in db.Child
|
|
|
526 group ch by ch.ParentID > 2 ? ch.ParentID > 3 ? "1" : "2" : "3"
|
|
|
527 into g select g
|
|
|
528 select ch.Key + "2"
|
|
|
529 where p == "22"
|
|
|
530 select p));
|
|
|
531 }
|
|
|
532
|
|
|
533 [Test]
|
|
|
534 public void GroupBy1()
|
|
|
535 {
|
|
|
536 ForEachProvider(db => AreEqual(
|
|
|
537 Child.GroupBy(ch => ch.ParentID).GroupBy(ch => ch).GroupBy(ch => ch).Select(p => p.Key.Key.Key),
|
|
|
538 db.Child.GroupBy(ch => ch.ParentID).GroupBy(ch => ch).GroupBy(ch => ch).Select(p => p.Key.Key.Key)));
|
|
|
539 }
|
|
|
540
|
|
|
541 [Test]
|
|
|
542 public void GroupBy2()
|
|
|
543 {
|
|
|
544 ForEachProvider(db => AreEqual(
|
|
|
545 from p in Parent
|
|
|
546 join c in Child on p.ParentID equals c.ParentID
|
|
|
547 group p by new
|
|
|
548 {
|
|
|
549 ID = p.Value1 ?? c.ChildID
|
|
|
550 } into gr
|
|
|
551 select new
|
|
|
552 {
|
|
|
553 gr.Key.ID,
|
|
|
554 ID1 = gr.Key.ID + 1,
|
|
|
555 },
|
|
|
556 from p in db.Parent
|
|
|
557 join c in db.Child on p.ParentID equals c.ParentID
|
|
|
558 group p by new
|
|
|
559 {
|
|
|
560 ID = p.Value1 ?? c.ChildID
|
|
|
561 } into gr
|
|
|
562 select new
|
|
|
563 {
|
|
|
564 gr.Key.ID,
|
|
|
565 ID1 = gr.Key.ID + 1,
|
|
|
566 }));
|
|
|
567 }
|
|
|
568
|
|
|
569 [Test]
|
|
|
570 public void GroupBy3()
|
|
|
571 {
|
|
|
572 ForEachProvider(db => AreEqual(
|
|
|
573 from p in Parent
|
|
|
574 join c in Child on p.ParentID equals c.ParentID
|
|
|
575 group p by p.Value1 ?? c.ChildID into gr
|
|
|
576 select new
|
|
|
577 {
|
|
|
578 gr.Key
|
|
|
579 },
|
|
|
580 from p in db.Parent
|
|
|
581 join c in db.Child on p.ParentID equals c.ParentID
|
|
|
582 group p by p.Value1 ?? c.ChildID into gr
|
|
|
583 select new
|
|
|
584 {
|
|
|
585 gr.Key
|
|
|
586 }));
|
|
|
587 }
|
|
|
588
|
|
|
589 [Test]
|
|
|
590 public void Sum1()
|
|
|
591 {
|
|
|
592 var expected =
|
|
|
593 from ch in Child
|
|
|
594 group ch by ch.ParentID into g
|
|
|
595 select g.Sum(p => p.ChildID);
|
|
|
596
|
|
|
597 ForEachProvider(db => AreEqual(expected,
|
|
|
598 from ch in db.Child
|
|
|
599 group ch by ch.ParentID into g
|
|
|
600 select g.Sum(p => p.ChildID)));
|
|
|
601 }
|
|
|
602
|
|
|
603 [Test]
|
|
|
604 public void Sum2()
|
|
|
605 {
|
|
|
606 var expected =
|
|
|
607 from ch in Child
|
|
|
608 group ch by ch.ParentID into g
|
|
|
609 select new { Sum = g.Sum(p => p.ChildID) };
|
|
|
610
|
|
|
611 ForEachProvider(db => AreEqual(expected,
|
|
|
612 from ch in db.Child
|
|
|
613 group ch by ch.ParentID into g
|
|
|
614 select new { Sum = g.Sum(p => p.ChildID) }));
|
|
|
615 }
|
|
|
616
|
|
|
617 [Test]
|
|
|
618 public void Sum3()
|
|
|
619 {
|
|
|
620 ForEachProvider(
|
|
|
621 new[] { ProviderName.SqlCe },
|
|
|
622 db => AreEqual(
|
|
|
623 from ch in Child
|
|
|
624 group ch by ch.Parent into g
|
|
|
625 select g.Key.Children.Sum(p => p.ChildID),
|
|
|
626 from ch in db.Child
|
|
|
627 group ch by ch.Parent into g
|
|
|
628 select g.Key.Children.Sum(p => p.ChildID)));
|
|
|
629 }
|
|
|
630
|
|
|
631 [Test]
|
|
|
632 public void SumSubQuery1()
|
|
|
633 {
|
|
|
634 var n = 1;
|
|
|
635
|
|
|
636 var expected =
|
|
|
637 from ch in
|
|
|
638 from ch in Child select new { ParentID = ch.ParentID + 1, ch.ChildID }
|
|
|
639 where ch.ParentID + 1 > n group ch by ch into g
|
|
|
640 select g.Sum(p => p.ParentID - 3);
|
|
|
641
|
|
|
642 ForEachProvider(db => AreEqual(expected,
|
|
|
643 from ch in
|
|
|
644 from ch in db.Child select new { ParentID = ch.ParentID + 1, ch.ChildID }
|
|
|
645 where ch.ParentID + 1 > n group ch by ch into g
|
|
|
646 select g.Sum(p => p.ParentID - 3)));
|
|
|
647 }
|
|
|
648
|
|
|
649 [Test]
|
|
|
650 public void GroupByMax()
|
|
|
651 {
|
|
|
652 ForEachProvider(db => AreEqual(
|
|
|
653 from ch in Child group ch.ParentID by ch.ChildID into g select new { Max = g.Max() },
|
|
|
654 from ch in db.Child group ch.ParentID by ch.ChildID into g select new { Max = g.Max() }));
|
|
|
655 }
|
|
|
656
|
|
|
657 [Test]
|
|
|
658 public void Aggregates1()
|
|
|
659 {
|
|
|
660 ForEachProvider(db => AreEqual(
|
|
|
661 from ch in Child
|
|
|
662 group ch by ch.ParentID into g
|
|
|
663 select new
|
|
|
664 {
|
|
|
665 Cnt = g.Count(),
|
|
|
666 Sum = g.Sum(c => c.ChildID),
|
|
|
667 Min = g.Min(c => c.ChildID),
|
|
|
668 Max = g.Max(c => c.ChildID),
|
|
|
669 Avg = (int)g.Average(c => c.ChildID),
|
|
|
670 },
|
|
|
671 from ch in db.Child
|
|
|
672 group ch by ch.ParentID into g
|
|
|
673 select new
|
|
|
674 {
|
|
|
675 Cnt = g.Count(),
|
|
|
676 Sum = g.Sum(c => c.ChildID),
|
|
|
677 Min = g.Min(c => c.ChildID),
|
|
|
678 Max = g.Max(c => c.ChildID),
|
|
|
679 Avg = (int)g.Average(c => c.ChildID),
|
|
|
680 }));
|
|
|
681 }
|
|
|
682
|
|
|
683 [Test]
|
|
|
684 public void Aggregates2()
|
|
|
685 {
|
|
|
686 ForEachProvider(db => AreEqual(
|
|
|
687 from ch in Child
|
|
|
688 group ch by ch.ParentID into g
|
|
|
689 select new
|
|
|
690 {
|
|
|
691 Sum = g.Select(c => c.ChildID).Sum(),
|
|
|
692 Min = g.Select(c => c.ChildID).Min(),
|
|
|
693 Max = g.Select(c => c.ChildID).Max(),
|
|
|
694 Avg = (int)g.Select(c => c.ChildID).Average(),
|
|
|
695 Cnt = g.Count()
|
|
|
696 },
|
|
|
697 from ch in db.Child
|
|
|
698 group ch by ch.ParentID into g
|
|
|
699 select new
|
|
|
700 {
|
|
|
701 Sum = g.Select(c => c.ChildID).Sum(),
|
|
|
702 Min = g.Select(c => c.ChildID).Min(),
|
|
|
703 Max = g.Select(c => c.ChildID).Max(),
|
|
|
704 Avg = (int)g.Select(c => c.ChildID).Average(),
|
|
|
705 Cnt = g.Count()
|
|
|
706 }));
|
|
|
707 }
|
|
|
708
|
|
|
709 [Test]
|
|
|
710 public void Aggregates3()
|
|
|
711 {
|
|
|
712 ForEachProvider(
|
|
|
713 new[] { ProviderName.SqlCe },
|
|
|
714 db => AreEqual(
|
|
|
715 from ch in Child
|
|
|
716 where ch.ChildID > 30
|
|
|
717 group ch by ch.ParentID into g
|
|
|
718 select new
|
|
|
719 {
|
|
|
720 Sum = g.Select(c => c.ChildID).Where(_ => _ > 30).Sum(),
|
|
|
721 Min = g.Select(c => c.ChildID).Where(_ => _ > 30).Min(),
|
|
|
722 Max = g.Select(c => c.ChildID).Where(_ => _ > 30).Max(),
|
|
|
723 Avg = (int)g.Select(c => c.ChildID).Where(_ => _ > 30).Average(),
|
|
|
724 },
|
|
|
725 from ch in db.Child
|
|
|
726 where ch.ChildID > 30
|
|
|
727 group ch by ch.ParentID into g
|
|
|
728 select new
|
|
|
729 {
|
|
|
730 Sum = g.Select(c => c.ChildID).Where(_ => _ > 30).Sum(),
|
|
|
731 Min = g.Select(c => c.ChildID).Where(_ => _ > 30).Min(),
|
|
|
732 Max = g.Select(c => c.ChildID).Where(_ => _ > 30).Max(),
|
|
|
733 Avg = (int)g.Select(c => c.ChildID).Where(_ => _ > 30).Average(),
|
|
|
734 }));
|
|
|
735 }
|
|
|
736
|
|
|
737 [Test]
|
|
|
738 public void Aggregates4()
|
|
|
739 {
|
|
|
740 ForEachProvider(
|
|
|
741 new[] { ProviderName.SqlCe },
|
|
|
742 db => AreEqual(
|
|
|
743 from ch in Child
|
|
|
744 group ch by ch.ParentID into g
|
|
|
745 select new
|
|
|
746 {
|
|
|
747 Count = g.Count(_ => _.ChildID > 30),
|
|
|
748 Sum = g.Where(_ => _.ChildID > 30).Sum(c => c.ChildID),
|
|
|
749 },
|
|
|
750 from ch in db.Child
|
|
|
751 group ch by ch.ParentID into g
|
|
|
752 select new
|
|
|
753 {
|
|
|
754 Count = g.Count(_ => _.ChildID > 30),
|
|
|
755 Sum = g.Where(_ => _.ChildID > 30).Sum(c => c.ChildID),
|
|
|
756 }));
|
|
|
757 }
|
|
|
758
|
|
|
759 [Test]
|
|
|
760 public void SelectMax()
|
|
|
761 {
|
|
|
762 var expected =
|
|
|
763 from ch in Child
|
|
|
764 group ch by ch.ParentID into g
|
|
|
765 select g.Max(c => c.ChildID);
|
|
|
766
|
|
|
767 ForEachProvider(db => AreEqual(expected,
|
|
|
768 from ch in db.Child
|
|
|
769 group ch by ch.ParentID into g
|
|
|
770 select g.Max(c => c.ChildID)));
|
|
|
771 }
|
|
|
772
|
|
|
773 [Test]
|
|
|
774 public void JoinMax()
|
|
|
775 {
|
|
|
776 var expected =
|
|
|
777 from ch in Child
|
|
|
778 join max in
|
|
|
779 from ch in Child
|
|
|
780 group ch by ch.ParentID into g
|
|
|
781 select g.Max(c => c.ChildID)
|
|
|
782 on ch.ChildID equals max
|
|
|
783 select ch;
|
|
|
784
|
|
|
785 ForEachProvider(db => AreEqual(expected,
|
|
|
786 from ch in db.Child
|
|
|
787 join max in
|
|
|
788 from ch in db.Child
|
|
|
789 group ch by ch.ParentID into g
|
|
|
790 select g.Max(c => c.ChildID)
|
|
|
791 on ch.ChildID equals max
|
|
|
792 select ch));
|
|
|
793 }
|
|
|
794
|
|
|
795 [Test]
|
|
|
796 public void Min1()
|
|
|
797 {
|
|
|
798 var expected = Child.Min(c => c.ChildID);
|
|
|
799 ForEachProvider(db => Assert.AreEqual(expected, db.Child.Min(c => c.ChildID)));
|
|
|
800 }
|
|
|
801
|
|
|
802 [Test]
|
|
|
803 public void Min2()
|
|
|
804 {
|
|
|
805 var expected = Child.Select(c => c.ChildID).Min();
|
|
|
806 ForEachProvider(db => Assert.AreEqual(expected, db.Child.Select(c => c.ChildID).Min()));
|
|
|
807 }
|
|
|
808
|
|
|
809 [Test]
|
|
|
810 public void Max1()
|
|
|
811 {
|
|
|
812 var expected = Child.Max(c => c.ChildID);
|
|
|
813 Assert.AreNotEqual(0, expected);
|
|
|
814 ForEachProvider(db => Assert.AreEqual(expected, db.Child.Max(c => c.ChildID)));
|
|
|
815 }
|
|
|
816
|
|
|
817 [Test]
|
|
|
818 public void Max11()
|
|
|
819 {
|
|
|
820 ForEachProvider(db => Assert.AreEqual(
|
|
|
821 Child.Max(c => c.ChildID > 20),
|
|
|
822 db.Child.Max(c => c.ChildID > 20)));
|
|
|
823 }
|
|
|
824
|
|
|
825 [Test]
|
|
|
826 public void Max12()
|
|
|
827 {
|
|
|
828 ForEachProvider(db => Assert.AreEqual(
|
|
|
829 Child.Max(c => (bool?)(c.ChildID > 20)),
|
|
|
830 db.Child.Max(c => (bool?)(c.ChildID > 20))));
|
|
|
831 }
|
|
|
832
|
|
|
833 [Test]
|
|
|
834 public void Max2()
|
|
|
835 {
|
|
|
836 var expected =
|
|
|
837 from p in Parent
|
|
|
838 join c in Child on p.ParentID equals c.ParentID
|
|
|
839 where c.ChildID > 20
|
|
|
840 select p;
|
|
|
841
|
|
|
842 ForEachProvider(db =>
|
|
|
843 {
|
|
|
844 var result =
|
|
|
845 from p in db.Parent
|
|
|
846 join c in db.Child on p.ParentID equals c.ParentID
|
|
|
847 where c.ChildID > 20
|
|
|
848 select p;
|
|
|
849
|
|
|
850 Assert.AreEqual(expected.Max(p => p.ParentID), result.Max(p => p.ParentID));
|
|
|
851 });
|
|
|
852 }
|
|
|
853
|
|
|
854 [Test]
|
|
|
855 public void Max3()
|
|
|
856 {
|
|
|
857 ForEachProvider(db => Assert.AreEqual(
|
|
|
858 Child.Select(c => c.ChildID).Max(),
|
|
|
859 db.Child.Select(c => c.ChildID).Max()));
|
|
|
860 }
|
|
|
861
|
|
|
862 [Test]
|
|
|
863 public void Max4()
|
|
|
864 {
|
|
|
865 ForEachProvider(db => Assert.AreEqual(
|
|
|
866 from t1 in Types
|
|
|
867 join t2 in
|
|
|
868 from sub in Types
|
|
|
869 where
|
|
|
870 sub.ID == 1 &&
|
|
|
871 sub.DateTimeValue <= DateTime.Today
|
|
|
872 group sub by new
|
|
|
873 {
|
|
|
874 sub.ID
|
|
|
875 } into g
|
|
|
876 select new
|
|
|
877 {
|
|
|
878 g.Key.ID,
|
|
|
879 DateTimeValue = g.Max( p => p.DateTimeValue )
|
|
|
880 }
|
|
|
881 on new { t1.ID, t1.DateTimeValue } equals new { t2.ID, t2.DateTimeValue }
|
|
|
882 select t1.MoneyValue,
|
|
|
883 from t1 in db.Types
|
|
|
884 join t2 in
|
|
|
885 from sub in db.Types
|
|
|
886 where
|
|
|
887 sub.ID == 1 &&
|
|
|
888 sub.DateTimeValue <= DateTime.Today
|
|
|
889 group sub by new
|
|
|
890 {
|
|
|
891 sub.ID
|
|
|
892 } into g
|
|
|
893 select new
|
|
|
894 {
|
|
|
895 g.Key.ID,
|
|
|
896 DateTimeValue = g.Max( p => p.DateTimeValue )
|
|
|
897 }
|
|
|
898 on new { t1.ID, t1.DateTimeValue } equals new { t2.ID, t2.DateTimeValue }
|
|
|
899 select t1.MoneyValue
|
|
|
900 ));
|
|
|
901 }
|
|
|
902
|
|
|
903 [Test]
|
|
|
904 public void Average1()
|
|
|
905 {
|
|
|
906 ForEachProvider(db => Assert.AreEqual(
|
|
|
907 (int)db.Child.Average(c => c.ChildID),
|
|
|
908 (int) Child.Average(c => c.ChildID)));
|
|
|
909 }
|
|
|
910
|
|
|
911 [Test]
|
|
|
912 public void Average2()
|
|
|
913 {
|
|
|
914 var expected = Child.Select(c => c.ChildID).Average();
|
|
|
915 ForEachProvider(db => Assert.AreEqual((int)expected, (int)db.Child.Select(c => c.ChildID).Average()));
|
|
|
916 }
|
|
|
917
|
|
|
918 [Test]
|
|
|
919 public void GrooupByAssociation1()
|
|
|
920 {
|
|
|
921 ForEachProvider(db => AreEqual(
|
|
|
922 from ch in GrandChild1
|
|
|
923 group ch by ch.Parent into g
|
|
|
924 where g.Count() > 2
|
|
|
925 select g.Key.Value1
|
|
|
926 ,
|
|
|
927 from ch in db.GrandChild1
|
|
|
928 group ch by ch.Parent into g
|
|
|
929 where g.Count() > 2
|
|
|
930 select g.Key.Value1));
|
|
|
931 }
|
|
|
932
|
|
|
933 [Test]
|
|
|
934 public void GrooupByAssociation101()
|
|
|
935 {
|
|
|
936 ForEachProvider(db => AreEqual(
|
|
|
937 from ch in GrandChild1
|
|
|
938 group ch by ch.Parent into g
|
|
|
939 where g.Max(_ => _.ParentID) > 2
|
|
|
940 select g.Key.Value1
|
|
|
941 ,
|
|
|
942 from ch in db.GrandChild1
|
|
|
943 group ch by ch.Parent into g
|
|
|
944 where g.Max(_ => _.ParentID) > 2
|
|
|
945 select g.Key.Value1));
|
|
|
946 }
|
|
|
947
|
|
|
948 [Test]
|
|
|
949 public void GrooupByAssociation102()
|
|
|
950 {
|
|
|
951 ForEachProvider(
|
|
|
952 new[] { ProviderName.Informix },
|
|
|
953 db => AreEqual(
|
|
|
954 from ch in GrandChild1
|
|
|
955 group ch by ch.Parent into g
|
|
|
956 where g.Count(_ => _.ChildID >= 20) > 2
|
|
|
957 select g.Key.Value1
|
|
|
958 ,
|
|
|
959 from ch in db.GrandChild1
|
|
|
960 group ch by ch.Parent into g
|
|
|
961 where g.Count(_ => _.ChildID >= 20) > 2
|
|
|
962 select g.Key.Value1));
|
|
|
963 }
|
|
|
964
|
|
|
965 [Test]
|
|
|
966 public void GrooupByAssociation1022()
|
|
|
967 {
|
|
|
968 ForEachProvider(
|
|
|
969 new[] { ProviderName.SqlCe, ProviderName.Access, ProviderName.Informix }, // Can be fixed.
|
|
|
970 db => AreEqual(
|
|
|
971 from ch in GrandChild1
|
|
|
972 group ch by ch.Parent into g
|
|
|
973 where g.Count(_ => _.ChildID >= 20) > 2 && g.Where(_ => _.ChildID >= 19).Sum(p => p.ParentID) > 0
|
|
|
974 select g.Key.Value1
|
|
|
975 ,
|
|
|
976 from ch in db.GrandChild1
|
|
|
977 group ch by ch.Parent into g
|
|
|
978 where g.Count(_ => _.ChildID >= 20) > 2 && g.Where(_ => _.ChildID >= 19).Sum(p => p.ParentID) > 0
|
|
|
979 select g.Key.Value1));
|
|
|
980 }
|
|
|
981
|
|
|
982 [Test]
|
|
|
983 public void GrooupByAssociation1023()
|
|
|
984 {
|
|
|
985 ForEachProvider(
|
|
|
986 new[] { ProviderName.SqlCe, ProviderName.Access, ProviderName.Informix }, // Can be fixed.
|
|
|
987 db => AreEqual(
|
|
|
988 from ch in GrandChild1
|
|
|
989 group ch by ch.Parent into g
|
|
|
990 where
|
|
|
991 g.Count(_ => _.ChildID >= 20) > 2 &&
|
|
|
992 g.Where(_ => _.ChildID >= 19).Sum(p => p.ParentID) > 0 &&
|
|
|
993 g.Where(_ => _.ChildID >= 19).Max(p => p.ParentID) > 0
|
|
|
994 select g.Key.Value1
|
|
|
995 ,
|
|
|
996 from ch in db.GrandChild1
|
|
|
997 group ch by ch.Parent into g
|
|
|
998 where
|
|
|
999 g.Count(_ => _.ChildID >= 20) > 2 &&
|
|
|
1000 g.Where(_ => _.ChildID >= 19).Sum(p => p.ParentID) > 0 &&
|
|
|
1001 g.Where(_ => _.ChildID >= 19).Max(p => p.ParentID) > 0
|
|
|
1002 select g.Key.Value1));
|
|
|
1003 }
|
|
|
1004
|
|
|
1005 [Test]
|
|
|
1006 public void GrooupByAssociation1024()
|
|
|
1007 {
|
|
|
1008 ForEachProvider(
|
|
|
1009 new[] { ProviderName.SqlCe, ProviderName.Access, ProviderName.Informix }, // Can be fixed.
|
|
|
1010 db => AreEqual(
|
|
|
1011 from ch in GrandChild1
|
|
|
1012 group ch by ch.Parent into g
|
|
|
1013 where
|
|
|
1014 g.Count(_ => _.ChildID >= 20) > 2 &&
|
|
|
1015 g.Where(_ => _.ChildID >= 19).Sum(p => p.ParentID) > 0 &&
|
|
|
1016 g.Where(_ => _.ChildID >= 19).Max(p => p.ParentID) > 0 &&
|
|
|
1017 g.Where(_ => _.ChildID >= 18).Max(p => p.ParentID) > 0
|
|
|
1018 select g.Key.Value1
|
|
|
1019 ,
|
|
|
1020 from ch in db.GrandChild1
|
|
|
1021 group ch by ch.Parent into g
|
|
|
1022 where
|
|
|
1023 g.Count(_ => _.ChildID >= 20) > 2 &&
|
|
|
1024 g.Where(_ => _.ChildID >= 19).Sum(p => p.ParentID) > 0 &&
|
|
|
1025 g.Where(_ => _.ChildID >= 19).Max(p => p.ParentID) > 0 &&
|
|
|
1026 g.Where(_ => _.ChildID >= 18).Max(p => p.ParentID) > 0
|
|
|
1027 select g.Key.Value1));
|
|
|
1028 }
|
|
|
1029
|
|
|
1030 [Test]
|
|
|
1031 public void GrooupByAssociation2()
|
|
|
1032 {
|
|
|
1033 ForEachProvider(db => AreEqual(
|
|
|
1034 from ch in GrandChild1
|
|
|
1035 group ch by ch.Parent into g
|
|
|
1036 where g.Count() > 2 && g.Key.ParentID != 1
|
|
|
1037 select g.Key.Value1
|
|
|
1038 ,
|
|
|
1039 from ch in db.GrandChild1
|
|
|
1040 group ch by ch.Parent into g
|
|
|
1041 where g.Count() > 2 && g.Key.ParentID != 1
|
|
|
1042 select g.Key.Value1));
|
|
|
1043 }
|
|
|
1044
|
|
|
1045 [Test]
|
|
|
1046 public void GrooupByAssociation3([IncludeDataContexts("Northwind")] string context)
|
|
|
1047 {
|
|
|
1048 using (var db = new NorthwindDB())
|
|
|
1049 {
|
|
|
1050 var result =
|
|
|
1051 from p in db.Product
|
|
|
1052 group p by p.Category into g
|
|
|
1053 where g.Count() == 12
|
|
|
1054 select g.Key.CategoryName;
|
|
|
1055
|
|
|
1056 var list = result.ToList();
|
|
|
1057 Assert.AreEqual(3, list.Count);
|
|
|
1058 }
|
|
|
1059 }
|
|
|
1060
|
|
|
1061 [Test]
|
|
|
1062 public void GrooupByAssociation4([IncludeDataContexts("Northwind")] string context)
|
|
|
1063 {
|
|
|
1064 using (var db = new NorthwindDB())
|
|
|
1065 {
|
|
|
1066 var result =
|
|
|
1067 from p in db.Product
|
|
|
1068 group p by p.Category into g
|
|
|
1069 where g.Count() == 12
|
|
|
1070 select g.Key.CategoryID;
|
|
|
1071
|
|
|
1072 var list = result.ToList();
|
|
|
1073 Assert.AreEqual(3, list.Count);
|
|
|
1074 }
|
|
|
1075 }
|
|
|
1076
|
|
|
1077 [Test]
|
|
|
1078 public void GroupByAggregate1()
|
|
|
1079 {
|
|
|
1080 var expected =
|
|
|
1081 from p in Parent
|
|
|
1082 group p by p.Children.Count > 0 && p.Children.Average(c => c.ParentID) > 3 into g
|
|
|
1083 select g.Key;
|
|
|
1084
|
|
|
1085 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(expected,
|
|
|
1086 from p in db.Parent
|
|
|
1087 group p by p.Children.Average(c => c.ParentID) > 3 into g
|
|
|
1088 select g.Key));
|
|
|
1089 }
|
|
|
1090
|
|
|
1091 [Test]
|
|
|
1092 public void GroupByAggregate11()
|
|
|
1093 {
|
|
|
1094 var expected =
|
|
|
1095 from p in Parent
|
|
|
1096 where p.Children.Count > 0
|
|
|
1097 group p by p.Children.Average(c => c.ParentID) > 3 into g
|
|
|
1098 select g.Key;
|
|
|
1099
|
|
|
1100 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(expected,
|
|
|
1101 from p in db.Parent
|
|
|
1102 where p.Children.Count > 0
|
|
|
1103 group p by p.Children.Average(c => c.ParentID) > 3 into g
|
|
|
1104 select g.Key));
|
|
|
1105 }
|
|
|
1106
|
|
|
1107 [Test]
|
|
|
1108 public void GroupByAggregate12()
|
|
|
1109 {
|
|
|
1110 var expected =
|
|
|
1111 from p in Parent
|
|
|
1112 group p by p.Children.Count > 0 && p.Children.Average(c => c.ParentID) > 3 into g
|
|
|
1113 select g.Key;
|
|
|
1114
|
|
|
1115 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(expected,
|
|
|
1116 from p in db.Parent
|
|
|
1117 group p by p.Children.Count > 0 && p.Children.Average(c => c.ParentID) > 3 into g
|
|
|
1118 select g.Key));
|
|
|
1119 }
|
|
|
1120
|
|
|
1121 [Test]
|
|
|
1122 public void GroupByAggregate2([IncludeDataContexts("Northwind")] string context)
|
|
|
1123 {
|
|
|
1124 using (var db = new NorthwindDB())
|
|
|
1125 AreEqual(
|
|
|
1126 (
|
|
|
1127 from c in Customer
|
|
|
1128 group c by c.Orders.Count > 0 && c.Orders.Average(o => o.Freight) >= 80
|
|
|
1129 ).ToList().Select(k => k.Key),
|
|
|
1130 (
|
|
|
1131 from c in db.Customer
|
|
|
1132 group c by c.Orders.Average(o => o.Freight) >= 80
|
|
|
1133 ).ToList().Select(k => k.Key));
|
|
|
1134 }
|
|
|
1135
|
|
|
1136 [Test]
|
|
|
1137 public void GroupByAggregate3()
|
|
|
1138 {
|
|
|
1139 var expected =
|
|
|
1140 (
|
|
|
1141 from p in Parent
|
|
|
1142 group p by p.Children.Count > 0 && p.Children.Average(c => c.ParentID) > 3
|
|
|
1143 ).ToList().First(g => !g.Key);
|
|
|
1144
|
|
|
1145 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(expected,
|
|
|
1146 (
|
|
|
1147 from p in db.Parent
|
|
|
1148 group p by p.Children.Average(c => c.ParentID) > 3
|
|
|
1149 ).ToList().First(g => !g.Key)));
|
|
|
1150 }
|
|
|
1151
|
|
|
1152 [Test]
|
|
|
1153 public void ByJoin()
|
|
|
1154 {
|
|
|
1155 ForEachProvider(db => AreEqual(
|
|
|
1156 from c1 in Child
|
|
|
1157 join c2 in Child on c1.ChildID equals c2.ChildID + 1
|
|
|
1158 group c2 by c1.ParentID into g
|
|
|
1159 select g.Sum(_ => _.ChildID),
|
|
|
1160 from c1 in db.Child
|
|
|
1161 join c2 in db.Child on c1.ChildID equals c2.ChildID + 1
|
|
|
1162 group c2 by c1.ParentID into g
|
|
|
1163 select g.Sum(_ => _.ChildID)));
|
|
|
1164 }
|
|
|
1165
|
|
|
1166 [Test]
|
|
|
1167 public void SelectMany()
|
|
|
1168 {
|
|
|
1169 ForEachProvider(db => AreEqual(
|
|
|
1170 Child.GroupBy(ch => ch.ParentID).SelectMany(g => g),
|
|
|
1171 db.Child.GroupBy(ch => ch.ParentID).SelectMany(g => g)));
|
|
|
1172 }
|
|
|
1173
|
|
|
1174 [Test]
|
|
|
1175 public void Scalar1()
|
|
|
1176 {
|
|
|
1177 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
|
|
|
1178 (from ch in Child
|
|
|
1179 group ch by ch.ParentID into g
|
|
|
1180 select g.Select(ch => ch.ChildID).Max()),
|
|
|
1181 (from ch in db.Child
|
|
|
1182 group ch by ch.ParentID into g
|
|
|
1183 select g.Select(ch => ch.ChildID).Max())));
|
|
|
1184 }
|
|
|
1185
|
|
|
1186 [Test]
|
|
|
1187 public void Scalar101()
|
|
|
1188 {
|
|
|
1189 ForEachProvider(db => AreEqual(
|
|
|
1190 (from ch in Child
|
|
|
1191 select ch.ChildID into id
|
|
|
1192 group id by id into g
|
|
|
1193 select g.Max()),
|
|
|
1194 (from ch in db.Child
|
|
|
1195 select ch.ChildID into id
|
|
|
1196 group id by id into g
|
|
|
1197 select g.Max())));
|
|
|
1198 }
|
|
|
1199
|
|
|
1200 [Test]
|
|
|
1201 public void Scalar2()
|
|
|
1202 {
|
|
|
1203 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
|
|
|
1204 (from ch in Child
|
|
|
1205 group ch by ch.ParentID into g
|
|
|
1206 select new
|
|
|
1207 {
|
|
|
1208 Max1 = g.Select(ch => ch.ChildID ).Max(),
|
|
|
1209 Max2 = g.Select(ch => ch.ChildID + ch.ParentID).Max()
|
|
|
1210 }),
|
|
|
1211 (from ch in db.Child
|
|
|
1212 group ch by ch.ParentID into g
|
|
|
1213 select new
|
|
|
1214 {
|
|
|
1215 Max1 = g.Select(ch => ch.ChildID ).Max(),
|
|
|
1216 Max2 = g.Select(ch => ch.ChildID + ch.ParentID).Max()
|
|
|
1217 })));
|
|
|
1218 }
|
|
|
1219
|
|
|
1220 [Test]
|
|
|
1221 public void Scalar3()
|
|
|
1222 {
|
|
|
1223 ForEachProvider(
|
|
|
1224 new[] { ProviderName.SqlCe },
|
|
|
1225 db => AreEqual(
|
|
|
1226 (from ch in Child
|
|
|
1227 group ch by ch.ParentID into g
|
|
|
1228 select g.Select(ch => ch.ChildID).Where(id => id > 0).Max()),
|
|
|
1229 (from ch in db.Child
|
|
|
1230 group ch by ch.ParentID into g
|
|
|
1231 select g.Select(ch => ch.ChildID).Where(id => id > 0).Max())));
|
|
|
1232 }
|
|
|
1233
|
|
|
1234 [Test]
|
|
|
1235 public void Scalar4()
|
|
|
1236 {
|
|
|
1237 ForEachProvider(
|
|
|
1238 new[] { ProviderName.SqlCe, ProviderName.Access, ProviderName.Informix },
|
|
|
1239 db => AreEqual(
|
|
|
1240 from ch in Child
|
|
|
1241 group ch by ch.ParentID into g
|
|
|
1242 where g.Where(ch => ch.ParentID > 2).Select(ch => (int?)ch.ChildID).Min() != null
|
|
|
1243 select g.Where(ch => ch.ParentID > 2).Select(ch => ch.ChildID).Min()
|
|
|
1244 ,
|
|
|
1245 from ch in db.Child
|
|
|
1246 group ch by ch.ParentID into g
|
|
|
1247 where g.Where(ch => ch.ParentID > 2).Select(ch => (int?)ch.ChildID).Min() != null
|
|
|
1248 select g.Where(ch => ch.ParentID > 2).Select(ch => ch.ChildID).Min()));
|
|
|
1249 }
|
|
|
1250
|
|
|
1251 [Test]
|
|
|
1252 public void Scalar41()
|
|
|
1253 {
|
|
|
1254 ForEachProvider(
|
|
|
1255 new[] { ProviderName.SqlCe, ProviderName.Access, ProviderName.Informix },
|
|
|
1256 db => AreEqual(
|
|
|
1257 from ch in Child
|
|
|
1258 group ch by ch.ParentID into g
|
|
|
1259 select new { g } into g
|
|
|
1260 where g.g.Where(ch => ch.ParentID > 2).Select(ch => (int?)ch.ChildID).Min() != null
|
|
|
1261 select g.g.Where(ch => ch.ParentID > 2).Select(ch => ch.ChildID).Min()
|
|
|
1262 ,
|
|
|
1263 from ch in db.Child
|
|
|
1264 group ch by ch.ParentID into g
|
|
|
1265 select new { g } into g
|
|
|
1266 where g.g.Where(ch => ch.ParentID > 2).Select(ch => (int?)ch.ChildID).Min() != null
|
|
|
1267 select g.g.Where(ch => ch.ParentID > 2).Select(ch => ch.ChildID).Min()));
|
|
|
1268 }
|
|
|
1269
|
|
|
1270 [Test]
|
|
|
1271 public void Scalar5()
|
|
|
1272 {
|
|
|
1273 ForEachProvider(db => AreEqual(
|
|
|
1274 from ch in Child
|
|
|
1275 select ch.ParentID into id
|
|
|
1276 group id by id into g
|
|
|
1277 select g.Max()
|
|
|
1278 ,
|
|
|
1279 from ch in db.Child
|
|
|
1280 select ch.ParentID into id
|
|
|
1281 group id by id into g
|
|
|
1282 select g.Max()));
|
|
|
1283 }
|
|
|
1284
|
|
|
1285 //[Test]
|
|
|
1286 public void Scalar51()
|
|
|
1287 {
|
|
|
1288 ForEachProvider(db => AreEqual(
|
|
|
1289 from ch in Child
|
|
|
1290 group ch by ch.ParentID into g
|
|
|
1291 select g.Max()
|
|
|
1292 ,
|
|
|
1293 from ch in db.Child
|
|
|
1294 group ch by ch.ParentID into g
|
|
|
1295 select g.Max()));
|
|
|
1296 }
|
|
|
1297
|
|
|
1298 [Test]
|
|
|
1299 public void Scalar6()
|
|
|
1300 {
|
|
|
1301 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
|
|
|
1302 (from ch in Child
|
|
|
1303 where ch.ParentID < 3
|
|
|
1304 group ch by ch.ParentID into g
|
|
|
1305 select g.Where(ch => ch.ParentID < 3).Max(ch => ch.ChildID)),
|
|
|
1306 (from ch in db.Child
|
|
|
1307 where ch.ParentID < 3
|
|
|
1308 group ch by ch.ParentID into g
|
|
|
1309 select g.Where(ch => ch.ParentID < 3).Max(ch => ch.ChildID))));
|
|
|
1310 }
|
|
|
1311
|
|
|
1312 [Test]
|
|
|
1313 public void Scalar7()
|
|
|
1314 {
|
|
|
1315 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
|
|
|
1316 (from ch in Child
|
|
|
1317 group ch by ch.ParentID into g
|
|
|
1318 select new { max = g.Select(ch => ch.ChildID).Max()}).Select(id => id.max),
|
|
|
1319 (from ch in db.Child
|
|
|
1320 group ch by ch.ParentID into g
|
|
|
1321 select new { max = g.Select(ch => ch.ChildID).Max()}).Select(id => id.max)));
|
|
|
1322 }
|
|
|
1323
|
|
|
1324 [Test]
|
|
|
1325 public void Scalar8()
|
|
|
1326 {
|
|
|
1327 ForEachProvider(db => AreEqual(
|
|
|
1328 (from ch in Child
|
|
|
1329 group ch by ch.ParentID into g
|
|
|
1330 select new { max = g.Max(ch => ch.ChildID)}).Select(id => id.max),
|
|
|
1331 (from ch in db.Child
|
|
|
1332 group ch by ch.ParentID into g
|
|
|
1333 select new { max = g.Max(ch => ch.ChildID)}).Select(id => id.max)));
|
|
|
1334 }
|
|
|
1335
|
|
|
1336 [Test]
|
|
|
1337 public void Scalar9()
|
|
|
1338 {
|
|
|
1339 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
|
|
|
1340 (from ch in Child
|
|
|
1341 group ch by ch.ParentID into g
|
|
|
1342 select g.Select(ch => ch.ChildID).Where(id => id < 30).Count()),
|
|
|
1343 (from ch in db.Child
|
|
|
1344 group ch by ch.ParentID into g
|
|
|
1345 select g.Select(ch => ch.ChildID).Where(id => id < 30).Count())));
|
|
|
1346 }
|
|
|
1347
|
|
|
1348 [Test]
|
|
|
1349 public void Scalar10()
|
|
|
1350 {
|
|
|
1351 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
|
|
|
1352 (from ch in Child
|
|
|
1353 group ch by ch.ParentID into g
|
|
|
1354 select g.Select(ch => ch.ChildID).Where(id => id < 30).Count(id => id >= 20)),
|
|
|
1355 (from ch in db.Child
|
|
|
1356 group ch by ch.ParentID into g
|
|
|
1357 select g.Select(ch => ch.ChildID).Where(id => id < 30).Count(id => id >= 20))));
|
|
|
1358 }
|
|
|
1359
|
|
|
1360 [Test, Category("MySql")]
|
|
|
1361 public void GroupByExtraFieldBugTest([IncludeDataContexts(ProviderName.MySql)] string context)
|
|
|
1362 {
|
|
|
1363 // https://github.com/igor-tkachev/bltoolkit/issues/42
|
|
|
1364 // extra field is generated in the GROUP BY clause, for example:
|
|
|
1365 // GROUP BY p.LastName, p.LastName <--- the second one is redundant
|
|
|
1366
|
|
|
1367 using (var db = new TestDbManager(context))
|
|
|
1368 {
|
|
|
1369 var q =
|
|
|
1370 from d in db.Doctor
|
|
|
1371 join p in db.Person on d.PersonID equals p.ID
|
|
|
1372 group d by p.LastName into g
|
|
|
1373 select g.Key;
|
|
|
1374
|
|
|
1375 q.ToList();
|
|
|
1376
|
|
|
1377 const string fieldName = "LastName";
|
|
|
1378
|
|
|
1379 var lastQuery = db.LastQuery;
|
|
|
1380 var groupByPos = lastQuery.IndexOf("GROUP BY");
|
|
|
1381 var fieldPos = lastQuery.IndexOf(fieldName, groupByPos);
|
|
|
1382
|
|
|
1383 // check that our field does not present in the GROUP BY clause second time
|
|
|
1384 Assert.AreEqual(-1, lastQuery.IndexOf(fieldName, fieldPos + 1));
|
|
|
1385 }
|
|
|
1386 }
|
|
|
1387
|
|
|
1388 [Test]
|
|
|
1389 public void DoubleGroupBy1()
|
|
|
1390 {
|
|
|
1391 ForEachProvider(
|
|
|
1392 db => AreEqual(
|
|
|
1393 from t in
|
|
|
1394 from p in Parent
|
|
|
1395 where p.Value1 != null
|
|
|
1396 group p by p.ParentID into g
|
|
|
1397 select new
|
|
|
1398 {
|
|
|
1399 ID = g.Key,
|
|
|
1400 Max = g.Max(t => t.Value1)
|
|
|
1401 }
|
|
|
1402 group t by t.ID into g
|
|
|
1403 select new
|
|
|
1404 {
|
|
|
1405 g.Key,
|
|
|
1406 Sum = g.Sum(t => t.Max)
|
|
|
1407 },
|
|
|
1408 from t in
|
|
|
1409 from p in db.Parent
|
|
|
1410 where p.Value1 != null
|
|
|
1411 group p by p.ParentID into g
|
|
|
1412 select new
|
|
|
1413 {
|
|
|
1414 ID = g.Key,
|
|
|
1415 Max = g.Max(t => t.Value1)
|
|
|
1416 }
|
|
|
1417 group t by t.ID into g
|
|
|
1418 select new
|
|
|
1419 {
|
|
|
1420 g.Key,
|
|
|
1421 Sum = g.Sum(t => t.Max)
|
|
|
1422 }));
|
|
|
1423
|
|
|
1424 }
|
|
|
1425
|
|
|
1426 [Test]
|
|
|
1427 public void DoubleGroupBy2()
|
|
|
1428 {
|
|
|
1429 ForEachProvider(
|
|
|
1430 db => AreEqual(
|
|
|
1431 from p in Parent
|
|
|
1432 where p.Value1 != null
|
|
|
1433 group p by p.ParentID into g
|
|
|
1434 select new
|
|
|
1435 {
|
|
|
1436 ID = g.Key,
|
|
|
1437 Max = g.Max(t => t.Value1)
|
|
|
1438 } into t
|
|
|
1439 group t by t.ID into g
|
|
|
1440 select new
|
|
|
1441 {
|
|
|
1442 g.Key,
|
|
|
1443 Sum = g.Sum(t => t.Max)
|
|
|
1444 },
|
|
|
1445 from p in db.Parent
|
|
|
1446 where p.Value1 != null
|
|
|
1447 group p by p.ParentID into g
|
|
|
1448 select new
|
|
|
1449 {
|
|
|
1450 ID = g.Key,
|
|
|
1451 Max = g.Max(t => t.Value1)
|
|
|
1452 } into t
|
|
|
1453 group t by t.ID into g
|
|
|
1454 select new
|
|
|
1455 {
|
|
|
1456 g.Key,
|
|
|
1457 Sum = g.Sum(t => t.Max)
|
|
|
1458 }));
|
|
|
1459
|
|
|
1460 }
|
|
|
1461
|
|
|
1462 [Test]
|
|
|
1463 public void InnerQuery([DataContexts(ProviderName.SqlCe)] string context)
|
|
|
1464 {
|
|
|
1465 using (var db = GetDataContext(context))
|
|
|
1466 {
|
|
|
1467 AreEqual(
|
|
|
1468 Doctor.GroupBy(s => s.PersonID).Select(s => s.Select(d => d.Taxonomy).First()),
|
|
|
1469 db.Doctor.GroupBy(s => s.PersonID).Select(s => s.Select(d => d.Taxonomy).First()));
|
|
|
1470 }
|
|
|
1471 }
|
|
|
1472
|
|
|
1473 [Test]
|
|
|
1474 public void CalcMember([DataContexts] string context)
|
|
|
1475 {
|
|
|
1476 using (var db = GetDataContext(context))
|
|
|
1477 {
|
|
|
1478 AreEqual(
|
|
|
1479 from parent in Parent
|
|
|
1480 from child in Person
|
|
|
1481 where child.ID == parent.ParentID
|
|
|
1482 let data = new
|
|
|
1483 {
|
|
|
1484 parent.Value1,
|
|
|
1485 Value = child.FirstName == "John" ? child.FirstName : "a"
|
|
|
1486 }
|
|
|
1487 group data by data.Value into groupedData
|
|
|
1488 select new
|
|
|
1489 {
|
|
|
1490 groupedData.Key,
|
|
|
1491 Count = groupedData.Count()
|
|
|
1492 },
|
|
|
1493 from parent in db.Parent
|
|
|
1494 from child in db.Person
|
|
|
1495 where child.ID == parent.ParentID
|
|
|
1496 let data = new
|
|
|
1497 {
|
|
|
1498 parent.Value1,
|
|
|
1499 Value = child.FirstName == "John" ? child.FirstName : "a"
|
|
|
1500 }
|
|
|
1501 group data by data.Value into groupedData
|
|
|
1502 select new
|
|
|
1503 {
|
|
|
1504 groupedData.Key,
|
|
|
1505 Count = groupedData.Count()
|
|
|
1506 });
|
|
|
1507 }
|
|
|
1508 }
|
|
|
1509
|
|
|
1510 [Test]
|
|
|
1511 public void GroupByDate([DataContexts] string context)
|
|
|
1512 {
|
|
|
1513 using (var db = GetDataContext(context))
|
|
|
1514 {
|
|
|
1515 AreEqual(
|
|
|
1516 from t in Types2
|
|
|
1517 group t by new { t.DateTimeValue.Value.Month, t.DateTimeValue.Value.Year } into grp
|
|
|
1518 select new
|
|
|
1519 {
|
|
|
1520 Total = grp.Sum(_ => _.MoneyValue),
|
|
|
1521 year = grp.Key.Year,
|
|
|
1522 month = grp.Key.Month
|
|
|
1523 },
|
|
|
1524 from t in db.Types2
|
|
|
1525 group t by new { t.DateTimeValue.Value.Month, t.DateTimeValue.Value.Year } into grp
|
|
|
1526 select new
|
|
|
1527 {
|
|
|
1528 Total = grp.Sum(_ => _.MoneyValue),
|
|
|
1529 year = grp.Key.Year,
|
|
|
1530 month = grp.Key.Month
|
|
|
1531 });
|
|
|
1532 }
|
|
|
1533 }
|
|
|
1534 }
|
|
|
1535 }
|
