Mercurial > pub > bltoolkit

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