Mercurial > pub > bltoolkit

comparison UnitTests/Linq/GroupByTest.cs @ 0:f990fcb411a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Копия текущей версии из github
author cin
date Thu, 27 Mar 2014 21:46:09 +0400
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:f990fcb411a9
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 }