|
0
|
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 }
|
