Mercurial > pub > bltoolkit

comparison UnitTests/Linq/StringFunctions.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.Data.Linq.SqlClient;
3 using System.Linq;
4
5 using NUnit.Framework;
6
7 using BLToolkit.Data.DataProvider;
8 using BLToolkit.Data.Linq;
9 using BLToolkit.DataAccess;
10
11 namespace Data.Linq
12 {
13 using Model;
14
15 [TestFixture]
16 public class StringFunctions : TestBase
17 {
18 [Test]
19 public void Length()
20 {
21 ForEachProvider(db =>
22 {
23 var q = from p in db.Person where p.FirstName.Length == "John".Length && p.ID == 1 select p;
24 Assert.AreEqual(1, q.ToList().First().ID);
25 });
26 }
27
28 [Test]
29 public void ContainsConstant()
30 {
31 ForEachProvider(db =>
32 {
33 var q = from p in db.Person where p.FirstName.Contains("oh") && p.ID == 1 select p;
34 Assert.AreEqual(1, q.ToList().First().ID);
35 });
36 }
37
38 [Test]
39 public void ContainsConstant2()
40 {
41 ForEachProvider(db =>
42 {
43 var q = from p in db.Person where !p.FirstName.Contains("o%h") && p.ID == 1 select p;
44 Assert.AreEqual(1, q.ToList().First().ID);
45 });
46 }
47
48 [Test]
49 public void ContainsConstant3([DataContexts] string context)
50 {
51 using (var db = GetDataContext(context))
52 {
53 var arr = new[] { "oh", "oh'", "oh\\" };
54
55 var q = from p in db.Person where arr.Contains(p.FirstName) select p;
56 Assert.AreEqual(0, q.Count());
57 }
58 }
59
60 [Test]
61 public void ContainsParameter1()
62 {
63 var str = "oh";
64
65 ForEachProvider(db =>
66 {
67 var q = from p in db.Person where p.FirstName.Contains(str) && p.ID == 1 select new { p, str };
68 var r = q.ToList().First();
69 Assert.AreEqual(1, r.p.ID);
70 Assert.AreEqual(str, r.str);
71 });
72 }
73
74 [Test]
75 public void ContainsParameter2()
76 {
77 var str = "o%h";
78
79 ForEachProvider(db =>
80 {
81 var q = from p in db.Person where !p.FirstName.Contains(str) && p.ID == 1 select p;
82 Assert.AreEqual(1, q.ToList().First().ID);
83 });
84 }
85
86 [Test]
87 public void ContainsParameter3()
88 {
89 var str = "o";
90
91 using (var db = new TestDbManager())
92 {
93 var q =
94 from d in db.Doctor
95 join p in db.Person.Where(p => p.FirstName.Contains(str))
96 on d.PersonID equals p.ID
97 select p;
98
99 Assert.AreEqual(1, q.ToList().First().ID);
100 }
101 }
102
103 [Test]
104 public void ContainsParameter4()
105 {
106 ForEachProvider(db =>
107 AreEqual(
108 from p in Person
109 select new
110 {
111 p,
112 Field1 = p.FirstName.Contains("Jo")
113 } into p
114 where p.Field1
115 orderby p.Field1
116 select p,
117 from p in db.Person
118 select new
119 {
120 p,
121 Field1 = p.FirstName.Contains("Jo")
122 } into p
123 where p.Field1
124 orderby p.Field1
125 select p));
126 }
127
128 [Test]
129 public void StartsWith1()
130 {
131 ForEachProvider(db =>
132 {
133 var q = from p in db.Person where p.FirstName.StartsWith("Jo") && p.ID == 1 select p;
134 Assert.AreEqual(1, q.ToList().First().ID);
135 });
136 }
137
138 [Test]
139 public void StartsWith2()
140 {
141 ForEachProvider(
142 new[] { ProviderName.DB2, ProviderName.Access },
143 db => AreEqual(
144 from p in Person where "John123".StartsWith(p.FirstName) select p,
145 from p in db.Person where "John123".StartsWith(p.FirstName) select p));
146 }
147
148 [Test]
149 public void StartsWith3()
150 {
151 var str = "John123";
152
153 ForEachProvider(
154 new[] { ProviderName.DB2, ProviderName.Access },
155 db => AreEqual(
156 from p in Person where str.StartsWith(p.FirstName) select p,
157 from p in db.Person where str.StartsWith(p.FirstName) select p));
158 }
159
160 [Test]
161 public void StartsWith4()
162 {
163 ForEachProvider(
164 new[] { ProviderName.DB2, ProviderName.Access },
165 db => AreEqual(
166 from p1 in Person
167 from p2 in Person
168 where p1.ID == p2.ID && p1.FirstName.StartsWith(p2.FirstName)
169 select p1,
170 from p1 in db.Person
171 from p2 in db.Person
172 where p1.ID == p2.ID &&
173 Sql.Like(p1.FirstName, p2.FirstName.Replace("%", "~%"), '~')
174 select p1));
175 }
176
177 [Test]
178 public void StartsWith5()
179 {
180 ForEachProvider(
181 new[] { ProviderName.DB2, ProviderName.Access },
182 db => AreEqual(
183 from p1 in Person
184 from p2 in Person
185 where p1.ID == p2.ID && p1.FirstName.Replace("J", "%").StartsWith(p2.FirstName.Replace("J", "%"))
186 select p1,
187 from p1 in db.Person
188 from p2 in db.Person
189 where p1.ID == p2.ID && p1.FirstName.Replace("J", "%").StartsWith(p2.FirstName.Replace("J", "%"))
190 select p1));
191 }
192
193 [Test]
194 public void EndsWith()
195 {
196 ForEachProvider(db =>
197 {
198 var q = from p in db.Person where p.FirstName.EndsWith("hn") && p.ID == 1 select p;
199 Assert.AreEqual(1, q.ToList().First().ID);
200 });
201 }
202
203 [Test]
204 public void Like11()
205 {
206 ForEachProvider(db =>
207 {
208 var q = from p in db.Person where SqlMethods.Like(p.FirstName, "%hn%") && p.ID == 1 select p;
209 Assert.AreEqual(1, q.ToList().First().ID);
210 });
211 }
212
213 [Test]
214 public void Like12()
215 {
216 ForEachProvider(db =>
217 {
218 var q = from p in db.Person where !SqlMethods.Like(p.FirstName, @"%h~%n%", '~') && p.ID == 1 select p;
219 Assert.AreEqual(1, q.ToList().First().ID);
220 });
221 }
222
223 [Test]
224 public void Like21()
225 {
226 ForEachProvider(db =>
227 {
228 var q = from p in db.Person where Sql.Like(p.FirstName, "%hn%") && p.ID == 1 select p;
229 Assert.AreEqual(1, q.ToList().First().ID);
230 });
231 }
232
233 [Test]
234 public void Like22()
235 {
236 ForEachProvider(db =>
237 {
238 var q = from p in db.Person where !Sql.Like(p.FirstName, @"%h~%n%", '~') && p.ID == 1 select p;
239 Assert.AreEqual(1, q.ToList().First().ID);
240 });
241 }
242
243 [Test]
244 public void IndexOf11()
245 {
246 ForEachProvider(new[] { ProviderName.Firebird, ProviderName.Informix }, db =>
247 {
248 var q = from p in db.Person where p.FirstName.IndexOf("oh") == 1 && p.ID == 1 select p;
249 Assert.AreEqual(1, q.ToList().First().ID);
250 });
251 }
252
253 [Test]
254 public void IndexOf12()
255 {
256 ForEachProvider(new[] { ProviderName.Firebird, ProviderName.Informix }, db =>
257 {
258 var q = from p in db.Person where p.FirstName.IndexOf("") == 0 && p.ID == 1 select p;
259 Assert.AreEqual(1, q.ToList().First().ID);
260 });
261 }
262
263 [Test]
264 public void IndexOf2()
265 {
266 ForEachProvider(new[] { ProviderName.Firebird, ProviderName.Informix }, db =>
267 {
268 var q = from p in db.Person where p.LastName.IndexOf("e", 2) == 4 && p.ID == 2 select p;
269 Assert.AreEqual(2, q.ToList().First().ID);
270 });
271 }
272
273 [Test]
274 public void IndexOf3([DataContexts(
275 ProviderName.DB2, ProviderName.Firebird, ProviderName.Informix, ProviderName.SqlCe, ProviderName.Sybase, ProviderName.Access)] string context)
276 {
277 var s = "e";
278 var n1 = 2;
279 var n2 = 5;
280
281 using (var db = GetDataContext(context))
282 {
283 var q = from p in db.Person where p.LastName.IndexOf(s, n1, n2) == 1 && p.ID == 2 select p;
284 Assert.AreEqual(2, q.ToList().First().ID);
285 }
286 }
287
288 static readonly string[] _lastIndexExcludeList = new[]
289 {
290 ProviderName.DB2, ProviderName.Firebird, ProviderName.Informix, ProviderName.SqlCe, ProviderName.Access
291 };
292
293 [Test]
294 public void LastIndexOf1()
295 {
296 ForEachProvider(_lastIndexExcludeList, db =>
297 {
298 var q = from p in db.Person where p.LastName.LastIndexOf("p") == 2 && p.ID == 1 select p;
299 Assert.AreEqual(1, q.ToList().First().ID);
300 });
301 }
302
303 [Test]
304 public void LastIndexOf2()
305 {
306 ForEachProvider(_lastIndexExcludeList, db =>
307 {
308 var q = from p in db.Person where p.ID == 1 select new { p.ID, FirstName = "123" + p.FirstName + "012345" };
309 q = q.Where(p => p.FirstName.LastIndexOf("123", 5) == 8);
310 Assert.AreEqual(1, q.ToList().First().ID);
311 });
312 }
313
314 [Test]
315 public void LastIndexOf3()
316 {
317 ForEachProvider(_lastIndexExcludeList, db =>
318 {
319 var q = from p in db.Person where p.ID == 1 select new { p.ID, FirstName = "123" + p.FirstName + "0123451234" };
320 q = q.Where(p => p.FirstName.LastIndexOf("123", 5, 6) == 8);
321 Assert.AreEqual(1, q.ToList().First().ID);
322 });
323 }
324
325 [Test]
326 public void CharIndex1()
327 {
328 ForEachProvider(new[] { ProviderName.Firebird, ProviderName.Informix }, db =>
329 {
330 var q = from p in db.Person where Sql.CharIndex("oh", p.FirstName) == 2 && p.ID == 1 select p;
331 Assert.AreEqual(1, q.ToList().First().ID);
332 });
333 }
334
335 [Test]
336 public void CharIndex2()
337 {
338 ForEachProvider(new[] { ProviderName.Firebird, ProviderName.Informix }, db =>
339 {
340 var q = from p in db.Person where Sql.CharIndex("p", p.LastName, 2) == 3 && p.ID == 1 select p;
341 Assert.AreEqual(1, q.ToList().First().ID);
342 });
343 }
344
345 [Test]
346 public void Left()
347 {
348 ForEachProvider(db =>
349 {
350 var q = from p in db.Person where Sql.Left(p.FirstName, 2) == "Jo" && p.ID == 1 select p;
351 Assert.AreEqual(1, q.ToList().First().ID);
352 });
353 }
354
355 [Test]
356 public void Right()
357 {
358 ForEachProvider(db =>
359 {
360 var q = from p in db.Person where Sql.Right(p.FirstName, 3) == "ohn" && p.ID == 1 select p;
361 Assert.AreEqual(1, q.ToList().First().ID);
362 });
363 }
364
365 [Test]
366 public void Substring1()
367 {
368 ForEachProvider(db =>
369 {
370 var q = from p in db.Person where p.FirstName.Substring(1) == "ohn" && p.ID == 1 select p;
371 Assert.AreEqual(1, q.ToList().First().ID);
372 });
373 }
374
375 [Test]
376 public void Substring2()
377 {
378 ForEachProvider(db =>
379 {
380 var q = from p in db.Person where p.FirstName.Substring(1, 2) == "oh" && p.ID == 1 select p;
381 Assert.AreEqual(1, q.ToList().First().ID);
382 });
383 }
384
385 [Test]
386 public void Reverse()
387 {
388 ForEachProvider(new[] { ProviderName.DB2, ProviderName.Informix, ProviderName.SqlCe, ProviderName.Access }, db =>
389 {
390 var q = from p in db.Person where Sql.Reverse(p.FirstName) == "nhoJ" && p.ID == 1 select p;
391 Assert.AreEqual(1, q.ToList().First().ID);
392 });
393 }
394
395 [Test]
396 public void Stuff()
397 {
398 ForEachProvider(db =>
399 {
400 var q = from p in db.Person where Sql.Stuff(p.FirstName, 3, 1, "123") == "Jo123n" && p.ID == 1 select p;
401 Assert.AreEqual(1, q.ToList().First().ID);
402 });
403 }
404
405 [Test]
406 public void Insert()
407 {
408 ForEachProvider(db =>
409 {
410 var q = from p in db.Person where p.FirstName.Insert(2, "123") == "Jo123hn" && p.ID == 1 select p;
411 Assert.AreEqual(1, q.ToList().First().ID);
412 });
413 }
414
415 [Test]
416 public void Remove1()
417 {
418 ForEachProvider(db =>
419 {
420 var q = from p in db.Person where p.FirstName.Remove(2) == "Jo" && p.ID == 1 select p;
421 Assert.AreEqual(1, q.ToList().First().ID);
422 });
423 }
424
425 [Test]
426 public void Remove2()
427 {
428 ForEachProvider(db =>
429 {
430 var q = from p in db.Person where p.FirstName.Remove(1, 2) == "Jn" && p.ID == 1 select p;
431 Assert.AreEqual(1, q.ToList().First().ID);
432 });
433 }
434
435 [Test]
436 public void Space()
437 {
438 ForEachProvider(db =>
439 {
440 var q = from p in db.Person where p.FirstName + Sql.Space(p.ID + 1) + "123" == "John 123" && p.ID == 1 select p;
441 Assert.AreEqual(1, q.ToList().First().ID);
442 });
443 }
444
445 [Test]
446 public void PadRight()
447 {
448 ForEachProvider(db =>
449 {
450 var q = from p in db.Person where Sql.PadRight(p.FirstName, 6, ' ') + "123" == "John 123" && p.ID == 1 select p;
451 Assert.AreEqual(1, q.ToList().First().ID);
452 });
453 }
454
455 [Test]
456 public void PadRight1()
457 {
458 ForEachProvider(db =>
459 {
460 var q = from p in db.Person where p.FirstName.PadRight(6) + "123" == "John 123" && p.ID == 1 select p;
461 Assert.AreEqual(1, q.ToList().First().ID);
462 });
463 }
464
465 [Test]
466 public void PadRight2()
467 {
468 ForEachProvider(db =>
469 {
470 var q = from p in db.Person where p.FirstName.PadRight(6, '*') + "123" == "John**123" && p.ID == 1 select p;
471 Assert.AreEqual(1, q.ToList().First().ID);
472 });
473 }
474
475 [Test]
476 public void PadLeft()
477 {
478 ForEachProvider(db =>
479 {
480 var q = from p in db.Person where "123" + Sql.PadLeft(p.FirstName, 6, ' ') == "123 John" && p.ID == 1 select p;
481 Assert.AreEqual(1, q.ToList().First().ID);
482 });
483 }
484
485 [Test]
486 public void PadLeft1()
487 {
488 ForEachProvider(db =>
489 {
490 var q = from p in db.Person where "123" + p.FirstName.PadLeft(6) == "123 John" && p.ID == 1 select p;
491 Assert.AreEqual(1, q.ToList().First().ID);
492 });
493 }
494
495 [Test]
496 public void PadLeft2()
497 {
498 ForEachProvider(db =>
499 {
500 var q = from p in db.Person where "123" + p.FirstName.PadLeft(6, '*') == "123**John" && p.ID == 1 select p;
501 Assert.AreEqual(1, q.ToList().First().ID);
502 });
503 }
504
505 [Test]
506 public void Replace()
507 {
508 ForEachProvider(new[] { ProviderName.Access }, db =>
509 {
510 var q = from p in db.Person where p.FirstName.Replace("hn", "lie") == "Jolie" && p.ID == 1 select p;
511 Assert.AreEqual(1, q.ToList().First().ID);
512 });
513 }
514
515 [Test]
516 public void Trim()
517 {
518 ForEachProvider(db =>
519 {
520 var q =
521 from p in db.Person where p.ID == 1 select new { p.ID, Name = " " + p.FirstName + " " } into pp
522 where pp.Name.Trim() == "John" select pp;
523 Assert.AreEqual(1, q.ToList().First().ID);
524 });
525 }
526
527 [Test]
528 public void TrimLeft()
529 {
530 ForEachProvider(db =>
531 {
532 var q =
533 from p in db.Person where p.ID == 1 select new { p.ID, Name = " " + p.FirstName + " " } into pp
534 where pp.Name.TrimStart() == "John " select pp;
535 Assert.AreEqual(1, q.ToList().First().ID);
536 });
537 }
538
539 [Test]
540 public void TrimRight()
541 {
542 ForEachProvider(db =>
543 {
544 var q =
545 from p in db.Person where p.ID == 1 select new { p.ID, Name = " " + p.FirstName + " " } into pp
546 where pp.Name.TrimEnd() == " John" select pp;
547 Assert.AreEqual(1, q.ToList().First().ID);
548 });
549 }
550
551 [Test]
552 public void ToLower()
553 {
554 ForEachProvider(db =>
555 {
556 var q = from p in db.Person where p.FirstName.ToLower() == "john" && p.ID == 1 select p;
557 Assert.AreEqual(1, q.ToList().First().ID);
558 });
559 }
560
561 [Test]
562 public void ToUpper()
563 {
564 ForEachProvider(db =>
565 {
566 var q = from p in db.Person where p.FirstName.ToUpper() == "JOHN" && p.ID == 1 select p;
567 Assert.AreEqual(1, q.ToList().First().ID);
568 });
569 }
570
571 [Test]
572 public void CompareTo()
573 {
574 ForEachProvider(db =>
575 {
576 var q = from p in db.Person where p.FirstName.CompareTo("John") == 0 && p.ID == 1 select p;
577 Assert.AreEqual(1, q.ToList().First().ID);
578 });
579 }
580
581 [Test]
582 public void CompareToNotEqual1()
583 {
584 ForEachProvider(db =>
585 {
586 var q = from p in db.Person where p.FirstName.CompareTo("Jo") != 0 && p.ID == 1 select p;
587 Assert.AreEqual(1, q.ToList().First().ID);
588 });
589 }
590
591 [Test]
592 public void CompareToNotEqual2()
593 {
594 ForEachProvider(db =>
595 {
596 var q = from p in db.Person where 0 != p.FirstName.CompareTo("Jo") && p.ID == 1 select p;
597 Assert.AreEqual(1, q.ToList().First().ID);
598 });
599 }
600
601 [Test]
602 public void CompareTo1()
603 {
604 ForEachProvider(db =>
605 {
606 var q = from p in db.Person where p.FirstName.CompareTo("Joh") > 0 && p.ID == 1 select p;
607 Assert.AreEqual(1, q.ToList().First().ID);
608 });
609 }
610
611 [Test]
612 public void CompareTo2()
613 {
614 ForEachProvider(db =>
615 {
616 var q = from p in db.Person where p.FirstName.CompareTo("Johnn") < 0 && p.ID == 1 select p;
617 Assert.AreEqual(1, q.ToList().First().ID);
618 });
619 }
620
621 [Test]
622 public void CompareTo21()
623 {
624 ForEachProvider(db =>
625 {
626 var q = from p in db.Person where p.FirstName.CompareTo("Johnn") <= 0 && p.ID == 1 select p;
627 Assert.AreEqual(1, q.ToList().First().ID);
628 });
629 }
630
631 [Test]
632 public void CompareTo22()
633 {
634 ForEachProvider(db =>
635 {
636 var q = from p in db.Person where 0 >= p.FirstName.CompareTo("Johnn") && p.ID == 1 select p;
637 Assert.AreEqual(1, q.ToList().First().ID);
638 });
639 }
640
641 [Test]
642 public void CompareTo3()
643 {
644 ForEachProvider(db =>
645 {
646 var q = from p in db.Person where p.FirstName.CompareTo(55) > 0 && p.ID == 1 select p;
647 Assert.AreEqual(1, q.ToList().First().ID);
648 });
649 }
650
651 [Test]
652 public void CompareTo31()
653 {
654 ForEachProvider(db =>
655 {
656 var q = from p in db.Person where p.FirstName.CompareTo(55) >= 0 && p.ID == 1 select p;
657 Assert.AreEqual(1, q.ToList().First().ID);
658 });
659 }
660
661 [Test]
662 public void CompareTo32()
663 {
664 ForEachProvider(db =>
665 {
666 var q = from p in db.Person where 0 <= p.FirstName.CompareTo(55) && p.ID == 1 select p;
667 Assert.AreEqual(1, q.ToList().First().ID);
668 });
669 }
670
671 [Test]
672 public void CompareOrdinal1()
673 {
674 ForEachProvider(db =>
675 {
676 var q = from p in db.Person where string.CompareOrdinal(p.FirstName, "Joh") > 0 && p.ID == 1 select p;
677 Assert.AreEqual(1, q.ToList().First().ID);
678 });
679 }
680
681 [Test]
682 public void CompareOrdinal2()
683 {
684 ForEachProvider(db =>
685 {
686 var q = from p in db.Person where string.CompareOrdinal(p.FirstName, 1, "Joh", 1, 2) == 0 && p.ID == 1 select p;
687 Assert.AreEqual(1, q.ToList().First().ID);
688 });
689 }
690
691 [Test]
692 public void Compare1()
693 {
694 ForEachProvider(db =>
695 {
696 var q = from p in db.Person where string.Compare(p.FirstName, "Joh") > 0 && p.ID == 1 select p;
697 Assert.AreEqual(1, q.ToList().First().ID);
698 });
699 }
700
701 [Test]
702 public void Compare2()
703 {
704 ForEachProvider(db =>
705 {
706 var q = from p in db.Person where string.Compare(p.FirstName, "joh", true) > 0 && p.ID == 1 select p;
707 Assert.AreEqual(1, q.ToList().First().ID);
708 });
709 }
710
711 [Test]
712 public void Compare3()
713 {
714 ForEachProvider(db =>
715 {
716 var q = from p in db.Person where string.Compare(p.FirstName, 1, "Joh", 1, 2) == 0 && p.ID == 1 select p;
717 Assert.AreEqual(1, q.ToList().First().ID);
718 });
719 }
720
721 [Test]
722 public void Compare4()
723 {
724 ForEachProvider(db =>
725 {
726 var q = from p in db.Person where string.Compare(p.FirstName, 1, "Joh", 1, 2, true) == 0 && p.ID == 1 select p;
727 Assert.AreEqual(1, q.ToList().First().ID);
728 });
729 }
730
731 [Test]
732 public void IsNullOrEmpty1()
733 {
734 ForEachProvider(db =>
735 {
736 var q = from p in db.Person where !string.IsNullOrEmpty(p.FirstName) && p.ID == 1 select p;
737 Assert.AreEqual(1, q.ToList().First().ID);
738 });
739 }
740
741 [Test]
742 public void IsNullOrEmpty2()
743 {
744 ForEachProvider(db =>
745 {
746 var q = from p in db.Person where p.ID == 1 select string.IsNullOrEmpty(p.FirstName);
747 Assert.AreEqual(false, q.ToList().First());
748 });
749 }
750
751 //[Test]
752 public void Test()
753 {
754 using (var db = new TestDbManager(ProviderName.Firebird))
755 {
756 var p = db
757 .SetCommand(@"
758 SELECT
759 t1.ParentID,
760 t1.Value1
761 FROM
762 Parent t1
763 LEFT JOIN (
764 SELECT
765 t3.ParentID as ParentID1,
766 Coalesce(t3.ParentID, 1) as c1
767 FROM
768 Child t3
769 ) t2 ON t1.ParentID = t2.ParentID1
770 WHERE
771 t2.c1 IS NULL")
772 .ExecuteList<Parent>();
773
774 var p1 = p.First();
775 Assert.AreEqual(1, p1.ParentID);
776
777
778 var da = new SqlQuery();
779 var pr = (Person)da.SelectByKey(typeof(Person), 1);
780
781 Assert.AreEqual("Pupkin", pr.LastName);
782
783
784 //Assert.AreEqual(1, p.ID);
785 }
786 }
787 }
788 }