Mercurial > pub > bltoolkit

comparison UnitTests/Linq/CountTest.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 BLToolkit.Data.DataProvider;
5 using BLToolkit.Data.Linq;
6 using NUnit.Framework;
7
8 namespace Data.Linq
9 {
10 [TestFixture]
11 public class CountTest : TestBase
12 {
13 [Test]
14 public void Count1()
15 {
16 ForEachProvider(db => Assert.AreEqual(
17 Parent.Count(),
18 db.Parent.Count()));
19 }
20
21 [Test]
22 public void Count2()
23 {
24 ForEachProvider(db => Assert.AreEqual(
25 Parent.Count(p => p.ParentID > 2),
26 db.Parent.Count(p => p.ParentID > 2)));
27 }
28
29 [Test]
30 public void Count3()
31 {
32 ForEachProvider(db => AreEqual(
33 from p in Parent select p.Children.Count(),
34 from p in db.Parent select p.Children.Count()));
35 }
36
37 [Test]
38 public void Count4()
39 {
40 ForEachProvider(db => AreEqual(
41 from p in Parent select Child.Count(),
42 from p in db.Parent select db.Child.Count()));
43 }
44
45 [Test]
46 public void Count5()
47 {
48 ForEachProvider(db => Assert.AreEqual(
49 (from ch in Child group ch by ch.ParentID).Count(),
50 (from ch in db.Child group ch by ch.ParentID).Count()));
51 }
52
53 [Test]
54 public void Count6()
55 {
56 ForEachProvider(db => Assert.AreEqual(
57 (from ch in Child group ch by ch.ParentID).Count(g => g.Key > 2),
58 (from ch in db.Child group ch by ch.ParentID).Count(g => g.Key > 2)));
59 }
60
61 [Test]
62 public void Count7()
63 {
64 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
65 from p in Parent where p.Children.Count > 2 select p,
66 from p in db.Parent where p.Children.Count > 2 select p));
67 }
68
69 [Test]
70 public void SubQueryCount([IncludeDataContexts("Sql2008", "Sql2012")] string context)
71 {
72 using (var db = new TestDbManager(context))
73 {
74 AreEqual(
75 from p in Parent
76 select Parent.Where(t => t.ParentID == p.ParentID).Count()
77 ,
78 from p in db.Parent
79 //select Sql.AsSql(db.Parent.Count()));
80 select Sql.AsSql(db.GetParentByID(p.ParentID).Count()));
81 }
82 }
83
84 [Test]
85 public void GroupBy1()
86 {
87 var expected =
88 from ch in Child
89 group ch by ch.ParentID into g
90 select g.Count(ch => ch.ChildID > 20);
91
92 ForEachProvider(db => AreEqual(expected,
93 from ch in db.Child
94 group ch by ch.ParentID into g
95 select g.Count(ch => ch.ChildID > 20)));
96 }
97
98 [Test]
99 public void GroupBy101()
100 {
101 var expected =
102 from ch in Child
103 group ch by ch.ParentID into g
104 select g.Count();
105
106 ForEachProvider(db => AreEqual(expected,
107 from ch in db.Child
108 group ch by ch.ParentID into g
109 select g.Count()));
110 }
111
112 [Test]
113 public void GroupBy102()
114 {
115 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
116 from ch in Child
117 group ch by ch.ParentID into g
118 select new
119 {
120 ID1 = g.Max (ch => ch.ChildID),
121 ID2 = g.Count(ch => ch.ChildID > 20) + 1,
122 ID3 = g.Count(ch => ch.ChildID > 20),
123 ID4 = g.Count(ch => ch.ChildID > 10),
124 },
125 from ch in db.Child
126 group ch by ch.ParentID into g
127 select new
128 {
129 ID1 = g.Max (ch => ch.ChildID),
130 ID2 = g.Count(ch => ch.ChildID > 20) + 1,
131 ID3 = g.Count(ch => ch.ChildID > 20),
132 ID4 = g.Count(ch => ch.ChildID > 10),
133 }));
134 }
135
136 [Test]
137 public void GroupBy103()
138 {
139 var expected =
140 from ch in Child
141 group ch by new { Parent = ch.ParentID, ch.ChildID } into g
142 select g.Count(ch => ch.ChildID > 20);
143
144 ForEachProvider(db => AreEqual(expected,
145 from ch in db.Child
146 group ch by new { Parent = ch.ParentID, ch.ChildID } into g
147 select g.Count(ch => ch.ChildID > 20)));
148 }
149
150
151 [Test]
152 public void GroupBy21([DataContexts] string context)
153 {
154 var n = 1;
155
156 using (var db = GetDataContext(context))
157 AreEqual(
158 from ch in
159 from ch in Child select new { ParentID = ch.ParentID + 1, ch.ChildID }
160 where ch.ParentID + 1 > n
161 group ch by ch into g
162 select g.Count(p => p.ParentID < 3),
163 from ch in
164 from ch in db.Child select new { ParentID = ch.ParentID + 1, ch.ChildID }
165 where ch.ParentID + 1 > n
166 group ch by ch into g
167 select g.Count(p => p.ParentID < 3));
168 }
169
170 [Test]
171 public void GroupBy22()
172 {
173 var n = 1;
174
175 var expected =
176 from ch in
177 from ch in Child select new { ParentID = ch.ParentID + 1, ch.ChildID }
178 where ch.ParentID + 1 > n
179 group ch by new { ch.ParentID } into g
180 select g.Count(p => p.ParentID < 3);
181
182 ForEachProvider(db => AreEqual(expected,
183 from ch in
184 from ch in db.Child select new { ParentID = ch.ParentID + 1, ch.ChildID }
185 where ch.ParentID + 1 > n
186 group ch by new { ch.ParentID } into g
187 select g.Count(p => p.ParentID < 3)));
188 }
189
190 [Test]
191 public void GroupBy23()
192 {
193 ForEachProvider(new[] { ProviderName.SqlCe, "Oracle", "DevartOracle", "Sql2000", ProviderName.Sybase, ProviderName.Access }, db => AreEqual(
194 from p in
195 from p in Parent select new { ParentID = p.ParentID + 1, p.Value1 }
196 where p.ParentID + 1 > 1
197 group p by new { p.Value1 } into g
198 select g.Count(p => p.ParentID < 3),
199 from p in
200 from p in db.Parent select new { ParentID = p.ParentID + 1, p.Value1 }
201 where p.ParentID + 1 > 1
202 group p by new { p.Value1 } into g
203 select g.Count(p => p.ParentID < 3)));
204 }
205
206 [Test]
207 public void GroupBy3()
208 {
209 var expected =
210 from ch in
211 from ch in Child select new { ParentID = ch.ParentID + 1, ch.ChildID }
212 where ch.ParentID - 1 > 0
213 group ch by new { ch.ParentID } into g
214 select new
215 {
216 g.Key.ParentID,
217 ChildMin = g.Min(p => p.ChildID),
218 ChildCount = g.Count(p => p.ChildID > 25)
219 };
220
221 ForEachProvider(db => AreEqual(expected,
222 from ch in
223 from ch in db.Child select new { ParentID = ch.ParentID + 1, ch.ChildID }
224 where ch.ParentID - 1 > 0
225 group ch by new { ch.ParentID } into g
226 select new
227 {
228 g.Key.ParentID,
229 ChildMin = g.Min(p => p.ChildID),
230 ChildCount = g.Count(p => p.ChildID > 25)
231 }));
232 }
233
234 [Test]
235 public void GroupBy4()
236 {
237 var expected = Child.Count();
238
239 ForEachProvider(db =>
240 {
241 var result = db.Child.Count();
242 Assert.AreEqual(expected, result);
243 });
244 }
245
246 [Test]
247 public void GroupBy5()
248 {
249 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
250 from ch in Child
251 group ch by ch.ParentID into g
252 select new
253 {
254 ID1 = g.Max (ch => ch.ChildID),
255 ID2 = g.Count(ch => ch.ChildID > 20) + 1,
256 ID3 = g.Count(ch => ch.ChildID > 20),
257 ID4 = g.Count(ch => ch.ChildID > 10),
258 },
259 from ch in db.Child
260 group ch by ch.ParentID into g
261 select new
262 {
263 ID1 = g.Max (ch => ch.ChildID),
264 ID2 = g.Count(ch => ch.ChildID > 20) + 1,
265 ID3 = g.Count(ch => ch.ChildID > 20),
266 ID4 = g.Count(ch => ch.ChildID > 10),
267 }));
268 }
269
270 [Test]
271 public void GroupBy6()
272 {
273 ForEachProvider(db => Assert.AreEqual(
274 (from ch in Child group ch by ch.ParentID).Count(),
275 (from ch in db.Child group ch by ch.ParentID).Count()));
276 }
277
278 [Test]
279 public void GroupBy7()
280 {
281 ForEachProvider(db => AreEqual(
282 from ch in Child
283 group ch by ch.ParentID into g
284 select new
285 {
286 ID1 = g.Count(),
287 ID2 = g.Max (ch => ch.ChildID),
288 },
289 from ch in db.Child
290 group ch by ch.ParentID into g
291 select new
292 {
293 ID1 = g.Count(),
294 ID2 = g.Max (ch => ch.ChildID),
295 }));
296 }
297
298 [Test]
299 public void GroupByWhere()
300 {
301 var expected = Child.Count(ch => ch.ChildID > 20);
302 Assert.AreNotEqual(0, expected);
303
304 ForEachProvider(db =>
305 {
306 var result = db.Child.Count(ch => ch.ChildID > 20);
307 Assert.AreEqual(expected, result);
308 });
309 }
310
311 [Test]
312 public void GroupByWhere1()
313 {
314 ForEachProvider(db => AreEqual(
315 from ch in Child
316 group ch by ch.ParentID into g
317 where g.Key > 2
318 select g.Key,
319 from ch in db.Child
320 group ch by ch.ParentID into g
321 where g.Key > 2
322 select g.Key));
323 }
324
325 [Test]
326 public void GroupByWhere2()
327 {
328 ForEachProvider(db => AreEqual(
329 from ch in Child
330 group ch by ch.ParentID into g
331 where g.Count() > 2
332 select g.Key,
333 from ch in db.Child
334 group ch by ch.ParentID into g
335 where g.Count() > 2
336 select g.Key));
337 }
338
339 [Test]
340 public void GroupByWhere201()
341 {
342 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
343 from ch in Child
344 group ch by ch.ParentID into g
345 where g.Count(ch => ch.ChildID > 20) > 2
346 select g.Key,
347 from ch in db.Child
348 group ch by ch.ParentID into g
349 where g.Count(ch => ch.ChildID > 20) > 2
350 select g.Key));
351 }
352
353 [Test]
354 public void GroupByWhere202()
355 {
356 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
357 from ch in Child
358 group ch by ch.ParentID into g
359 where g.Count(ch => ch.ChildID > 20) > 2 || g.Count(ch => ch.ChildID == 20) > 2
360 select g.Key,
361 from ch in db.Child
362 group ch by ch.ParentID into g
363 where g.Count(ch => ch.ChildID > 20) > 2 || g.Count(ch => ch.ChildID == 20) > 2
364 select g.Key));
365 }
366
367 [Test]
368 public void GroupByWhere203()
369 {
370 ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
371 from ch in Child
372 group ch by ch.ParentID into g
373 where g.Count(ch => ch.ChildID > 20) > 2 || g.Key > 2
374 select g.Key,
375 from ch in db.Child
376 group ch by ch.ParentID into g
377 where g.Count(ch => ch.ChildID > 20) > 2 || g.Key > 2
378 select g.Key));
379 }
380
381 [Test]
382 public void GroupByWhere3()
383 {
384 ForEachProvider(db => AreEqual(
385 from ch in Child
386 group ch by ch.ParentID into g
387 where g.Count() > 2 && g.Key < 5
388 select g.Key,
389 from ch in db.Child
390 group ch by ch.ParentID into g
391 where g.Count() > 2 && g.Key < 5
392 select g.Key));
393 }
394
395 [Test]
396 public void GroupByWhere301()
397 {
398 ForEachProvider(db => AreEqual(
399 from ch in Child
400 group ch by ch.ParentID into g
401 where g.Count() > 3 || g.Key == 1
402 select g.Key,
403 from ch in db.Child
404 group ch by ch.ParentID into g
405 where g.Count() > 3 || g.Key == 1
406 select g.Key));
407 }
408
409 [Test]
410 public void GroupByWhere4()
411 {
412 ForEachProvider(db => AreEqual(
413 from ch in GrandChild1
414 group ch by ch.Parent into g
415 where g.Count() > 2
416 select g.Key.ParentID
417 ,
418 from ch in db.GrandChild1
419 group ch by ch.Parent into g
420 where g.Count() > 2
421 select g.Key.ParentID));
422 }
423
424 [Test]
425 public void SubQuery1()
426 {
427 ForEachProvider(db => AreEqual(
428 from p in Parent
429 where p.ParentID != 5
430 select new { p.ParentID, Count = p.Children.Where(c => c.ParentID == p.ParentID && c.ChildID != 0m).Count() },
431 from p in db.Parent
432 where p.ParentID != 5
433 select new { p.ParentID, Count = p.Children.Where(c => c.ParentID == p.ParentID && c.ChildID != 0m).Count() }));
434 }
435
436 [Test]
437 public void SubQuery2()
438 {
439 ForEachProvider(db => AreEqual(
440 from p in Parent
441 where p.ParentID != 5
442 select new { Count = p.Value1 == null ? p.Children.Count : p.Children.Count(c => c.ParentID == p.ParentID) },
443 from p in db.Parent
444 where p.ParentID != 5
445 select new { Count = p.Value1 == null ? p.Children.Count : p.Children.Count(c => c.ParentID == p.ParentID) }));
446 }
447
448 [Test]
449 public void SubQuery3()
450 {
451 ForEachProvider(db => AreEqual(
452 from p in Parent
453 where p.ParentID != 5
454 select new { Count = p.Value1 == null ? p.Children.Count() : p.Children.Count(c => c.ParentID == p.ParentID) },
455 from p in db.Parent
456 where p.ParentID != 5
457 select new { Count = p.Value1 == null ? p.Children.Count() : p.Children.Count(c => c.ParentID == p.ParentID) }));
458 }
459
460 [Test]
461 public void SubQuery4()
462 {
463 ForEachProvider(db => AreEqual(
464 from p in Parent select new { Count = Parent.Count(p1 => p1.ParentID == p.ParentID) },
465 from p in db.Parent select new { Count = db.Parent.Count(p1 => p1.ParentID == p.ParentID) }));
466 }
467
468 [Test]
469 public void SubQuery5()
470 {
471 ForEachProvider(db => AreEqual(
472 from p in Parent select new { Count = Parent.Where(p1 => p1.ParentID == p.ParentID).Count() },
473 from p in db.Parent select new { Count = db.Parent.Where(p1 => p1.ParentID == p.ParentID).Count() }));
474 }
475
476 [Test]
477 public void SubQuery6()
478 {
479 ForEachProvider(new[] { ProviderName.SqlCe, ProviderName.SQLite, ProviderName.Sybase }, db => AreEqual(
480 Parent.Take(5).OrderByDescending(p => p.ParentID).Select(p => p.Children.Count()),
481 db.Parent.Take(5).OrderByDescending(p => p.ParentID).Select(p => p.Children.Count())));
482 }
483
484 [Test]
485 public void SubQuery7()
486 {
487 ForEachProvider(
488 new[] { ProviderName.SqlCe, "Oracle", ProviderName.Sybase, ProviderName.Access }, ///// Fix It
489 db => AreEqual(
490 from p in Parent select Child.Count(c => c.Parent == p),
491 from p in db.Parent select db.Child.Count(c => c.Parent == p)));
492 }
493
494 [Test]
495 public void SubQueryMax1()
496 {
497 ForEachProvider(
498 db => Assert.AreEqual(
499 Parent.Max(p => Child.Count(c => c.Parent.ParentID == p.ParentID)),
500 db.Parent.Max(p => db.Child.Count(c => c.Parent.ParentID == p.ParentID))));
501 }
502
503 [Test]
504 public void SubQueryMax2()
505 {
506 ForEachProvider(
507 db => Assert.AreEqual(
508 Parent.Max(p => p.Children.Count()),
509 db.Parent.Max(p => p.Children.Count())));
510 }
511
512 [Test]
513 public void GroupJoin1()
514 {
515 ForEachProvider(db => AreEqual(
516 from p in Parent
517 join c in Child on p.ParentID equals c.ParentID into gc
518 join g in GrandChild on p.ParentID equals g.ParentID into gg
519 select new
520 {
521 Count1 = gc.Count(),
522 Count2 = gg.Count()
523 },
524 from p in db.Parent
525 join c in db.Child on p.ParentID equals c.ParentID into gc
526 join g in db.GrandChild on p.ParentID equals g.ParentID into gg
527 select new
528 {
529 Count1 = gc.Count(),
530 Count2 = gg.Count()
531 }));
532 }
533
534 [Test]
535 public void GroupJoin2()
536 {
537 ForEachProvider(db => AreEqual(
538 from p in Parent
539 join c in Child on p.ParentID equals c.ParentID into gc
540 join g in GrandChild on p.ParentID equals g.ParentID into gg
541 let gc1 = gc
542 let gg1 = gg
543 select new
544 {
545 Count1 = gc1.Count(),
546 Count2 = gg1.Count()
547 } ,
548 from p in db.Parent
549 join c in db.Child on p.ParentID equals c.ParentID into gc
550 join g in db.GrandChild on p.ParentID equals g.ParentID into gg
551 let gc1 = gc
552 let gg1 = gg
553 select new
554 {
555 Count1 = gc.Count(),
556 Count2 = gg.Count()
557 }));
558 }
559
560 [Test]
561 public void GroupJoin3()
562 {
563 ForEachProvider(db => AreEqual(
564 from p in Parent
565 join c in Child on p.ParentID equals c.ParentID into gc
566 select new
567 {
568 Count1 = gc.Count(),
569 },
570 from p in db.Parent
571 join c in db.Child on p.ParentID equals c.ParentID into gc
572 select new
573 {
574 Count1 = gc.Count(),
575 }));
576 }
577
578 [Test]
579 public void GroupJoin4()
580 {
581 ForEachProvider(db => AreEqual(
582 from p in Parent
583 join c in Child on p.ParentID equals c.ParentID into gc
584 select new
585 {
586 Count1 = gc.Count() + gc.Count(),
587 },
588 from p in db.Parent
589 join c in db.Child on p.ParentID equals c.ParentID into gc
590 select new
591 {
592 Count1 = gc.Count() + gc.Count(),
593 }));
594 }
595 }
596 }