5 // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne
\r
7 // Permission is hereby granted, free of charge, to any person obtaining a copy
\r
8 // of this software and associated documentation files (the "Software"), to deal
\r
9 // in the Software without restriction, including without limitation the rights
\r
10 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
\r
11 // copies of the Software, and to permit persons to whom the Software is
\r
12 // furnished to do so, subject to the following conditions:
\r
14 // The above copyright notice and this permission notice shall be included in
\r
15 // all copies or substantial portions of the Software.
\r
17 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
\r
18 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
\r
19 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
\r
20 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
\r
21 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
\r
22 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
\r
27 using System.Globalization;
\r
28 using System.Collections.Generic;
\r
31 using System.Linq.Expressions;
\r
32 using NUnit.Framework;
\r
40 namespace Test_NUnit_MySql
\r
42 namespace Test_NUnit_OracleODP
\r
44 namespace Test_NUnit_Oracle
\r
46 namespace Test_NUnit_PostgreSql
\r
48 namespace Test_NUnit_Sqlite
\r
50 namespace Test_NUnit_Ingres
\r
51 #elif MSSQL && MONO_STRICT
\r
52 namespace Test_NUnit_MsSql_Strict
\r
54 namespace Test_NUnit_MsSql
\r
56 namespace Test_NUnit_Firebird
\r
60 public class ReadTests_StringFunctions : TestBase
\r
63 public void Insert01()
\r
65 Northwind db = CreateDB();
\r
67 var q = from e in db.Employees
\r
68 select e.LastName.Insert(3, ":");
\r
71 var list = q.ToList();
\r
72 Assert.IsTrue(list.All(lastname => lastname.Contains(":")));
\r
76 public void Insert02()
\r
78 Northwind db = CreateDB();
\r
80 var q = from e in db.Employees
\r
81 where e.LastName.Insert(3, ":").Contains(":")
\r
82 select e.LastName.Insert(3, ":");
\r
85 var list = q.ToList();
\r
86 Assert.IsTrue(list.All(lastname => lastname.Contains(":")));
\r
90 public void Replace01()
\r
92 Northwind db = CreateDB();
\r
94 var q = from e in db.Employees
\r
95 where " .".Replace('.', 'a') == " a"
\r
98 var list = q.ToList();
\r
99 Assert.AreEqual(list.Count, db.Employees.Count());
\r
104 public void Replace02()
\r
106 Northwind db = CreateDB();
\r
108 var q = from e in db.Employees
\r
109 where e.LastName.Replace('A', 'B').Contains("B")
\r
112 var list = q.ToList();
\r
113 Assert.IsTrue(list.Count > 0);
\r
118 public void Replace03()
\r
120 //white-box test: Testing preevalutation of the where predicate (SpecialExpression.Execute method) before of building the sql query
\r
121 Northwind db = CreateDB();
\r
123 var q = from e in db.Employees
\r
124 where " .".Replace(" ", "f") == "f."
\r
127 var list = q.ToList();
\r
128 Assert.IsTrue(list.Count > 0);
\r
132 public void Replace04()
\r
134 //white-box test: Testing the select's projection field execution in clr.
\r
135 Northwind db = CreateDB();
\r
137 var q = from e in db.Employees
\r
138 select e.LastName + " .".Replace('.', 'a');
\r
140 var list = q.ToList();
\r
141 Assert.AreEqual(list.Count, db.Employees.Count());
\r
145 public void Replace05()
\r
147 Northwind db = CreateDB();
\r
149 var q = from e in db.Employees
\r
150 select " .".Replace(" ", "f");
\r
151 var list = q.ToList();
\r
152 Assert.AreEqual(list.Count, db.Employees.Count());
\r
156 public void IndexOf01()
\r
158 Northwind db = CreateDB();
\r
160 var q = from e in db.Employees
\r
161 where " fu".IndexOf("fu") == 1
\r
164 var list = q.ToList();
\r
165 Assert.IsTrue(list.Count > 0);
\r
170 public void IndexOf02()
\r
172 Northwind db = CreateDB();
\r
174 var q = from e in db.Employees
\r
175 where e.LastName.IndexOf("Fu") == 0
\r
178 var list = q.ToList();
\r
179 Assert.IsTrue(list.Count > 0);
\r
183 public void IndexOf03()
\r
185 Northwind db = CreateDB();
\r
187 var q = from e in db.Employees
\r
188 where " fu".IndexOf('f') == 1
\r
191 var list = q.ToList();
\r
192 Assert.IsTrue(list.Count > 0);
\r
197 public void IndexOf04()
\r
199 Northwind db = CreateDB();
\r
201 var q = from e in db.Employees
\r
202 where e.LastName.IndexOf('F') == 0
\r
205 var list = q.ToList();
\r
206 Assert.IsTrue(list.Count > 0);
\r
212 public void IndexOf05()
\r
214 Northwind db = CreateDB();
\r
216 var q = from e in db.Employees
\r
217 where e.LastName.IndexOf("u", 1) == 1
\r
220 var list = q.ToList();
\r
221 Assert.IsTrue(list.Count > 0);
\r
227 public void IndexOf06()
\r
229 Northwind db = CreateDB();
\r
231 var q = from e in db.Employees
\r
232 where e.LastName.IndexOf('u', 1, 1) == 1
\r
235 var list = q.ToList();
\r
236 Assert.IsTrue(list.Count > 0);
\r
240 public void IndexOf08()
\r
242 Northwind db = CreateDB();
\r
244 var q = from e in db.Employees
\r
245 where e.LastName.IndexOf("u", 1, 1) == 1
\r
248 var list = q.ToList();
\r
249 Assert.IsTrue(list.Count > 0);
\r
254 public void IndexOf09()
\r
256 Northwind db = CreateDB();
\r
258 var q = from e in db.Employees
\r
259 select e.LastName.IndexOf("Fu") == 0;
\r
261 var list = q.ToList();
\r
262 Assert.AreEqual(list.Count, db.Employees.Count());
\r
266 public void IndexOf10()
\r
268 Northwind db = CreateDB();
\r
270 var q = from e in db.Employees
\r
271 select " fu".IndexOf('f') == 1;
\r
274 var list = q.ToList();
\r
275 Assert.AreEqual(list.Count, db.Employees.Count());
\r
279 public void IndexOf11()
\r
281 Northwind db = CreateDB();
\r
283 var q = from e in db.Employees
\r
284 select e.LastName.IndexOf('F') == 0;
\r
286 var list = q.ToList();
\r
287 Assert.AreEqual(list.Count, db.Employees.Count());
\r
293 public void IndexOf12()
\r
295 Northwind db = CreateDB();
\r
297 var q = from e in db.Employees
\r
298 select e.LastName.IndexOf("u", 1) == 1;
\r
300 var list = q.ToList();
\r
301 Assert.AreEqual(list.Count, db.Employees.Count());
\r
307 public void IndexOf13()
\r
309 Northwind db = CreateDB();
\r
311 var q = from e in db.Employees
\r
312 select e.LastName.IndexOf('u', 1, 1) == 1;
\r
314 var list = q.ToList();
\r
315 Assert.AreEqual(list.Count, db.Employees.Count());
\r
319 public void IndexOf14()
\r
321 Northwind db = CreateDB();
\r
323 var q = from e in db.Employees
\r
324 select e.LastName.IndexOf("u", 1, 1) == 1;
\r
326 var list = q.ToList();
\r
327 Assert.AreEqual(list.Count, db.Employees.Count());
\r
332 public void Remove01()
\r
334 Northwind db = CreateDB();
\r
336 var q = from e in db.Employees
\r
337 where " .".Remove(1) == " "
\r
340 var list = q.ToList();
\r
341 Assert.IsTrue(list.Count > 0);
\r
346 public void Remove02()
\r
348 Northwind db = CreateDB();
\r
350 var q = from e in db.Employees
\r
351 where e.LastName.Remove(1).Length > 0
\r
354 var list = q.ToList();
\r
355 Assert.IsTrue(list.Count > 0);
\r
360 public void Remove03()
\r
362 Northwind db = CreateDB();
\r
364 var q = from e in db.Employees
\r
365 where " ..".Remove(1, 2) == " "
\r
368 var list = q.ToList();
\r
369 Assert.IsTrue(list.Count > 0);
\r
373 public void Remove04()
\r
375 Northwind db = CreateDB();
\r
377 var q = from e in db.Employees
\r
378 where e.LastName.Remove(1, 2).Length > 0
\r
381 var list = q.ToList();
\r
382 Assert.IsTrue(list.Count > 0);
\r
386 public void Remove05()
\r
388 Northwind db = CreateDB();
\r
390 var q = from e in db.Employees
\r
391 select " .".Remove(1) == " ";
\r
393 var list = q.ToList();
\r
394 Assert.AreEqual(list.Count, db.Employees.Count());
\r
398 public void Remove06()
\r
400 Northwind db = CreateDB();
\r
402 var q = from e in db.Employees
\r
403 select e.LastName.Remove(1).Length > 0;
\r
405 var list = q.ToList();
\r
406 Assert.AreEqual(list.Count, db.Employees.Count());
\r
411 public void Remove07()
\r
413 Northwind db = CreateDB();
\r
415 var q = from e in db.Employees
\r
416 select " ..".Remove(1, 2) == " ";
\r
418 var list = q.ToList();
\r
419 Assert.AreEqual(list.Count, db.Employees.Count());
\r
423 public void Remove08()
\r
425 Northwind db = CreateDB();
\r
427 var q = from e in db.Employees
\r
428 select e.LastName.Remove(1, 2).Length > 0;
\r
430 var list = q.ToList();
\r
431 Assert.AreEqual(list.Count, db.Employees.Count());
\r
435 public void StartsWith01()
\r
437 Northwind db = CreateDB();
\r
439 var q = from c in db.Customers
\r
440 where c.CustomerID.StartsWith("ALF")
\r
441 select c.CustomerID;
\r
443 string custID = q.Single();
\r
444 Assert.IsTrue(custID == "ALFKI");
\r
448 public void StartsWith02()
\r
450 Northwind db = CreateDB();
\r
452 var q = from c in db.Customers
\r
453 where c.CustomerID == "ALFKI"
\r
454 select c.CustomerID.StartsWith("ALF");
\r
456 bool matchStart = q.Single();
\r
457 Assert.IsTrue(matchStart);
\r
461 public void EndsWith01()
\r
463 Northwind db = CreateDB();
\r
465 var q = from c in db.Customers
\r
466 where c.CustomerID.EndsWith("LFKI")
\r
467 select c.CustomerID;
\r
469 string custID = q.Single();
\r
470 Assert.IsTrue(custID == "ALFKI");
\r
474 public void EndsWith02()
\r
476 string param = "LFKI";
\r
477 Northwind db = CreateDB();
\r
479 var q = from c in db.Customers
\r
480 where c.CustomerID.EndsWith(param)
\r
481 select c.CustomerID;
\r
483 string custID = q.Single();
\r
484 Assert.IsTrue(custID == "ALFKI");
\r
488 public void EndsWith03()
\r
490 Northwind db = CreateDB();
\r
492 var q = from c in db.Customers
\r
493 where "ALFKI".EndsWith("LFKI")
\r
494 select c.CustomerID;
\r
496 string custID = q.Single();
\r
497 Assert.IsTrue(custID == "ALFKI");
\r
501 public void EndsWith04()
\r
503 Northwind db = CreateDB();
\r
505 var q = from c in db.Customers
\r
506 select c.CustomerID.EndsWith("LFKI");
\r
508 Assert.IsTrue(q.Any(r => r == true));
\r
512 public void StartsWithPercent01()
\r
514 string param = "%";
\r
515 Northwind db = CreateDB();
\r
517 var q = from c in db.Customers
\r
518 where c.CustomerID.StartsWith(param)
\r
519 select c.CustomerID;
\r
521 int cnt = q.Count();
\r
522 Assert.AreEqual(0, cnt);
\r
526 public void LTrim01()
\r
528 Northwind db = CreateDB();
\r
530 var q = from e in db.Employees
\r
531 where !((e.LastName)).TrimStart().Contains(" ")
\r
534 var list = q.ToList();
\r
535 Assert.IsTrue(list.Count > 0);
\r
539 public void LTrim02()
\r
541 Northwind db = CreateDB();
\r
543 var q = from e in db.Employees
\r
544 select !((e.LastName)).TrimStart().Contains(" ");
\r
546 var list = q.ToList();
\r
547 Assert.IsTrue(list.Count > 0);
\r
551 public void RTrim01()
\r
553 Northwind db = CreateDB();
\r
555 var q = from e in db.Employees
\r
556 where !((e.LastName)).TrimEnd().Contains(" ")
\r
559 var list = q.ToList();
\r
560 Assert.IsTrue(list.Count > 0);
\r
564 public void RTrim02()
\r
566 Northwind db = CreateDB();
\r
568 var q = from e in db.Employees
\r
569 select !((e.LastName)).TrimEnd().Contains(" ");
\r
571 var list = q.ToList();
\r
572 Assert.IsTrue(list.Count > 0);
\r
576 public void Substring01()
\r
578 Northwind db = CreateDB();
\r
581 var q = (from e in db.Customers
\r
582 where e.CustomerID == "WARTH"
\r
583 select new { name = e.CustomerID.Substring(index) }).First();
\r
585 Assert.AreEqual(q.name, "WARTH".Substring(index));
\r
589 public void Substring02()
\r
591 Northwind db = CreateDB();
\r
593 var q = (from e in db.Customers
\r
594 where e.CustomerID.Substring(2) == "RTH"
\r
597 Assert.IsTrue(q.Any());
\r
601 public void Substring03()
\r
603 Northwind db = CreateDB();
\r
605 var q = from e in db.Employees
\r
606 where e.HomePhone.Substring(4, 1) == ")"
\r
607 select new { A = e.HomePhone.Remove(0, 6), B = e.HomePhone.Substring(4, 1) };
\r
609 var list = q.ToList();
\r
610 Assert.IsTrue(list.Count > 0);
\r
612 var Employees = db.Employees.ToArray();
\r
614 var q2 = (from e in Employees
\r
615 where e.HomePhone != null && e.HomePhone.Substring(4, 1) == ")"
\r
616 select new { A = e.HomePhone.Remove(0, 6), B = e.HomePhone.Substring(4, 1) }).ToArray();
\r
618 Assert.AreEqual(list.Count, q2.Count());
\r
620 for (int i = 0; i < list.Count; i++)
\r
621 Assert.AreEqual(list[i], q2[i]);
\r