|
0
|
1 using System;
|
|
|
2 using System.Collections.Generic;
|
|
|
3 using System.Linq;
|
|
|
4
|
|
|
5 using BLToolkit.Data.DataProvider;
|
|
|
6 using BLToolkit.Mapping;
|
|
|
7
|
|
|
8 using NUnit.Framework;
|
|
|
9
|
|
|
10 namespace Data.Linq
|
|
|
11 {
|
|
|
12 using Model;
|
|
|
13 using BLToolkit.Data.Linq;
|
|
|
14
|
|
|
15 [TestFixture]
|
|
|
16 public class WhereTest : TestBase
|
|
|
17 {
|
|
|
18 [Test]
|
|
|
19 public void MakeSubQuery()
|
|
|
20 {
|
|
|
21 TestOneJohn(db =>
|
|
|
22 from p in db.Person
|
|
|
23 select new { PersonID = p.ID + 1, p.FirstName } into p
|
|
|
24 where p.PersonID == 2
|
|
|
25 select new Person(p.PersonID - 1) { FirstName = p.FirstName });
|
|
|
26 }
|
|
|
27
|
|
|
28 [Test]
|
|
|
29 public void MakeSubQueryWithParam()
|
|
|
30 {
|
|
|
31 var n = 1;
|
|
|
32
|
|
|
33 TestOneJohn(new[] { "Fdp" }, db =>
|
|
|
34 from p in db.Person
|
|
|
35 select new { PersonID = p.ID + n, p.FirstName } into p
|
|
|
36 where p.PersonID == 2
|
|
|
37 select new Person(p.PersonID - 1) { FirstName = p.FirstName });
|
|
|
38 }
|
|
|
39
|
|
|
40 [Test]
|
|
|
41 public void DoNotMakeSubQuery()
|
|
|
42 {
|
|
|
43 TestOneJohn(db =>
|
|
|
44 from p1 in db.Person
|
|
|
45 select new { p1.ID, Name = p1.FirstName + "\r\r\r" } into p2
|
|
|
46 where p2.ID == 1
|
|
|
47 select new Person(p2.ID) { FirstName = p2.Name.TrimEnd('\r') });
|
|
|
48 }
|
|
|
49
|
|
|
50 [Test]
|
|
|
51 public void EqualsConst()
|
|
|
52 {
|
|
|
53 TestOneJohn(db => from p in db.Person where p.ID == 1 select p);
|
|
|
54 }
|
|
|
55
|
|
|
56 [Test]
|
|
|
57 public void EqualsConsts()
|
|
|
58 {
|
|
|
59 TestOneJohn(db => from p in db.Person where p.ID == 1 && p.FirstName == "John" select p);
|
|
|
60 }
|
|
|
61
|
|
|
62 [Test]
|
|
|
63 public void EqualsConsts2()
|
|
|
64 {
|
|
|
65 TestOneJohn(db =>
|
|
|
66 from p in db.Person
|
|
|
67 where (p.FirstName == "John" || p.FirstName == "John's") && p.ID > 0 && p.ID < 2 && p.LastName != "123"
|
|
|
68 select p);
|
|
|
69 }
|
|
|
70
|
|
|
71 [Test]
|
|
|
72 public void EqualsParam()
|
|
|
73 {
|
|
|
74 var id = 1;
|
|
|
75 TestOneJohn(db => from p in db.Person where p.ID == id select p);
|
|
|
76 }
|
|
|
77
|
|
|
78 [Test]
|
|
|
79 public void EqualsParams()
|
|
|
80 {
|
|
|
81 var id = 1;
|
|
|
82 var name = "John";
|
|
|
83 TestOneJohn(db => from p in db.Person where p.ID == id && p.FirstName == name select p);
|
|
|
84 }
|
|
|
85
|
|
|
86 [Test]
|
|
|
87 public void NullParam1()
|
|
|
88 {
|
|
|
89 var id = 1;
|
|
|
90 string name = null;
|
|
|
91 TestOneJohn(db => from p in db.Person where p.ID == id && p.MiddleName == name select p);
|
|
|
92 }
|
|
|
93
|
|
|
94 [Test]
|
|
|
95 public void NullParam2()
|
|
|
96 {
|
|
|
97 var id = 1;
|
|
|
98 string name = null;
|
|
|
99 TestOneJohn(db =>
|
|
|
100 {
|
|
|
101 (from p in db.Person where p.ID == id && p.MiddleName == name select p).ToList();
|
|
|
102 return from p in db.Person where p.ID == id && p.MiddleName == name select p;
|
|
|
103 });
|
|
|
104 }
|
|
|
105
|
|
|
106 int TestMethod()
|
|
|
107 {
|
|
|
108 return 1;
|
|
|
109 }
|
|
|
110
|
|
|
111 [Test]
|
|
|
112 public void MethodParam()
|
|
|
113 {
|
|
|
114 TestOneJohn(db => from p in db.Person where p.ID == TestMethod() select p);
|
|
|
115 }
|
|
|
116
|
|
|
117 static int StaticTestMethod()
|
|
|
118 {
|
|
|
119 return 1;
|
|
|
120 }
|
|
|
121
|
|
|
122 [Test]
|
|
|
123 public void StaticMethodParam()
|
|
|
124 {
|
|
|
125 TestOneJohn(db => from p in db.Person where p.ID == StaticTestMethod() select p);
|
|
|
126 }
|
|
|
127
|
|
|
128 class TestMethodClass
|
|
|
129 {
|
|
|
130 private readonly int _n;
|
|
|
131
|
|
|
132 public TestMethodClass(int n)
|
|
|
133 {
|
|
|
134 _n = n;
|
|
|
135 }
|
|
|
136
|
|
|
137 public int TestMethod()
|
|
|
138 {
|
|
|
139 return _n;
|
|
|
140 }
|
|
|
141 }
|
|
|
142
|
|
|
143 public void MethodParam(int n)
|
|
|
144 {
|
|
|
145 var t = new TestMethodClass(n);
|
|
|
146
|
|
|
147 ForEachProvider(db =>
|
|
|
148 {
|
|
|
149 var id = (from p in db.Person where p.ID == t.TestMethod() select new { p.ID }).ToList().First();
|
|
|
150 Assert.AreEqual(n, id.ID);
|
|
|
151 });
|
|
|
152 }
|
|
|
153
|
|
|
154 [Test]
|
|
|
155 public void MethodParam2()
|
|
|
156 {
|
|
|
157 MethodParam(1);
|
|
|
158 MethodParam(2);
|
|
|
159 }
|
|
|
160
|
|
|
161 static IQueryable<Person> TestDirectParam(ITestDataContext db, int id)
|
|
|
162 {
|
|
|
163 var name = "John";
|
|
|
164 return from p in db.Person where p.ID == id && p.FirstName == name select p;
|
|
|
165 }
|
|
|
166
|
|
|
167 [Test]
|
|
|
168 public void DirectParams()
|
|
|
169 {
|
|
|
170 TestOneJohn(db => TestDirectParam(db, 1));
|
|
|
171 }
|
|
|
172
|
|
|
173 [Test]
|
|
|
174 public void BinaryAdd()
|
|
|
175 {
|
|
|
176 TestOneJohn(db => from p in db.Person where p.ID + 1 == 2 select p);
|
|
|
177 }
|
|
|
178
|
|
|
179 [Test]
|
|
|
180 public void BinaryDivide()
|
|
|
181 {
|
|
|
182 TestOneJohn(db => from p in db.Person where (p.ID + 9) / 10 == 1 && p.ID == 1 select p);
|
|
|
183 }
|
|
|
184
|
|
|
185 [Test]
|
|
|
186 public void BinaryModulo()
|
|
|
187 {
|
|
|
188 TestOneJohn(db => from p in db.Person where p.ID % 2 == 1 && p.ID == 1 select p);
|
|
|
189 }
|
|
|
190
|
|
|
191 [Test]
|
|
|
192 public void BinaryMultiply()
|
|
|
193 {
|
|
|
194 TestOneJohn(db => from p in db.Person where p.ID * 10 - 9 == 1 select p);
|
|
|
195 }
|
|
|
196
|
|
|
197 [Test]
|
|
|
198 public void BinaryXor()
|
|
|
199 {
|
|
|
200 TestOneJohn(new[] { ProviderName.Access }, db => from p in db.Person where (p.ID ^ 2) == 3 select p);
|
|
|
201 }
|
|
|
202
|
|
|
203 [Test]
|
|
|
204 public void BinaryAnd()
|
|
|
205 {
|
|
|
206 TestOneJohn(new[] { ProviderName.Access }, db => from p in db.Person where (p.ID & 3) == 1 select p);
|
|
|
207 }
|
|
|
208
|
|
|
209 [Test]
|
|
|
210 public void BinaryOr()
|
|
|
211 {
|
|
|
212 TestOneJohn(new[] { ProviderName.Access }, db => from p in db.Person where (p.ID | 2) == 3 select p);
|
|
|
213 }
|
|
|
214
|
|
|
215 [Test]
|
|
|
216 public void BinarySubtract()
|
|
|
217 {
|
|
|
218 TestOneJohn(db => from p in db.Person where p.ID - 1 == 0 select p);
|
|
|
219 }
|
|
|
220
|
|
|
221 [Test]
|
|
|
222 public void EqualsNull()
|
|
|
223 {
|
|
|
224 TestOneJohn(db => from p in db.Person where p.ID == 1 && p.MiddleName == null select p);
|
|
|
225 }
|
|
|
226
|
|
|
227 [Test]
|
|
|
228 public void EqualsNull2()
|
|
|
229 {
|
|
|
230 TestOneJohn(db => from p in db.Person where p.ID == 1 && null == p.MiddleName select p);
|
|
|
231 }
|
|
|
232
|
|
|
233 [Test]
|
|
|
234 public void NotEqualNull()
|
|
|
235 {
|
|
|
236 TestOneJohn(db => from p in db.Person where p.ID == 1 && p.FirstName != null select p);
|
|
|
237 }
|
|
|
238
|
|
|
239 [Test]
|
|
|
240 public void NotEqualNull2()
|
|
|
241 {
|
|
|
242 TestOneJohn(db => from p in db.Person where p.ID == 1 && null != p.FirstName select p);
|
|
|
243 }
|
|
|
244
|
|
|
245 [Test]
|
|
|
246 public void NotTest()
|
|
|
247 {
|
|
|
248 TestOneJohn(db => from p in db.Person where p.ID == 1 && !(p.MiddleName != null) select p);
|
|
|
249 }
|
|
|
250
|
|
|
251 [Test]
|
|
|
252 public void NotTest2()
|
|
|
253 {
|
|
|
254 int n = 2;
|
|
|
255 TestOneJohn(db => from p in db.Person where p.ID == 1 && !(p.MiddleName != null && p.ID == n) select p);
|
|
|
256 }
|
|
|
257
|
|
|
258 [Test]
|
|
|
259 public void Coalesce1()
|
|
|
260 {
|
|
|
261 TestOneJohn(db =>
|
|
|
262
|
|
|
263 from p in db.Person
|
|
|
264 where
|
|
|
265 p.ID == 1 &&
|
|
|
266 (p.MiddleName ?? "None") == "None" &&
|
|
|
267 (p.FirstName ?? "None") == "John"
|
|
|
268 select p
|
|
|
269
|
|
|
270 );
|
|
|
271 }
|
|
|
272
|
|
|
273 [Test]
|
|
|
274 public void Coalesce2()
|
|
|
275 {
|
|
|
276 ForEachProvider(db => Assert.AreEqual(1, (from p in db.Parent where p.ParentID == 1 ? true : false select p).ToList().Count));
|
|
|
277 }
|
|
|
278
|
|
|
279 [Test]
|
|
|
280 public void Coalesce3()
|
|
|
281 {
|
|
|
282 ForEachProvider(db => Assert.AreEqual(1, (from p in db.Parent where p.ParentID != 1 ? false: true select p).ToList().Count));
|
|
|
283 }
|
|
|
284
|
|
|
285 [Test]
|
|
|
286 public void Coalesce4()
|
|
|
287 {
|
|
|
288 ForEachProvider(db => AreEqual(
|
|
|
289 from p in Parent where p.ParentID == 1 ? false: true select p,
|
|
|
290 from p in db.Parent where p.ParentID == 1 ? false: true select p));
|
|
|
291 }
|
|
|
292
|
|
|
293 [Test]
|
|
|
294 public void Coalesce5()
|
|
|
295 {
|
|
|
296 ForEachProvider(db => Assert.AreEqual(2,
|
|
|
297 (from p in db.Parent where (p.Value1 == 1 ? 10 : 20) == 10 select p).ToList().Count));
|
|
|
298 }
|
|
|
299
|
|
|
300 [Test]
|
|
|
301 public void Coalesce6()
|
|
|
302 {
|
|
|
303 ForEachProvider(db => AreEqual(
|
|
|
304 from p in Parent where (p.Value1 == 1 ? 10 : 20) == 20 select p,
|
|
|
305 from p in db.Parent where (p.Value1 == 1 ? 10 : 20) == 20 select p));
|
|
|
306 }
|
|
|
307
|
|
|
308 [Test]
|
|
|
309 public void Coalesce7()
|
|
|
310 {
|
|
|
311 ForEachProvider(db => AreEqual(
|
|
|
312 from p in Parent where (p.ParentID == 1 ? 10 : 20) == 20 select p,
|
|
|
313 from p in db.Parent where (p.ParentID == 1 ? 10 : 20) == 20 select p));
|
|
|
314 }
|
|
|
315
|
|
|
316 [Test]
|
|
|
317 public void Conditional()
|
|
|
318 {
|
|
|
319 TestOneJohn(db =>
|
|
|
320
|
|
|
321 from p in db.Person
|
|
|
322 where
|
|
|
323 p.ID == 1 &&
|
|
|
324 (p.MiddleName == null ? 1 : 2) == 1 &&
|
|
|
325 (p.FirstName != null ? 1 : 2) == 1
|
|
|
326 select p
|
|
|
327
|
|
|
328 );
|
|
|
329 }
|
|
|
330
|
|
|
331 [Test]
|
|
|
332 public void Conditional2()
|
|
|
333 {
|
|
|
334 TestOneJohn(db =>
|
|
|
335
|
|
|
336 from p in db.Person
|
|
|
337 where
|
|
|
338 p.ID == 1 &&
|
|
|
339 (p.MiddleName != null ? 3 : p.MiddleName == null? 1 : 2) == 1 &&
|
|
|
340 (p.FirstName == null ? 3 : p.FirstName != null? 1 : 2) == 1
|
|
|
341 select p
|
|
|
342
|
|
|
343 );
|
|
|
344 }
|
|
|
345
|
|
|
346 [Test]
|
|
|
347 public void Conditional3()
|
|
|
348 {
|
|
|
349 TestOneJohn(db =>
|
|
|
350
|
|
|
351 from p in db.Person
|
|
|
352 where
|
|
|
353 p.ID == 1 &&
|
|
|
354 (p.MiddleName != null ? 3 : p.ID == 2 ? 2 : p.MiddleName != null ? 0 : 1) == 1 &&
|
|
|
355 (p.FirstName == null ? 3 : p.ID == 2 ? 2 : p.FirstName == null ? 0 : 1) == 1
|
|
|
356 select p
|
|
|
357
|
|
|
358 );
|
|
|
359 }
|
|
|
360
|
|
|
361 [Test]
|
|
|
362 public void MultipleQuery1()
|
|
|
363 {
|
|
|
364 ForEachProvider(db =>
|
|
|
365 {
|
|
|
366 var id = 1;
|
|
|
367 var q = from p in db.Person where p.ID == id select p;
|
|
|
368
|
|
|
369 var list = q.ToList();
|
|
|
370 Assert.AreEqual(1, list[0].ID);
|
|
|
371
|
|
|
372 id = 2;
|
|
|
373 list = q.ToList();
|
|
|
374 Assert.AreEqual(2, list[0].ID);
|
|
|
375 });
|
|
|
376 }
|
|
|
377
|
|
|
378 [Test]
|
|
|
379 public void MultipleQuery2()
|
|
|
380 {
|
|
|
381 ForEachProvider(db =>
|
|
|
382 {
|
|
|
383 string str = null;
|
|
|
384 var q = from p in db.Person where p.MiddleName == str select p;
|
|
|
385
|
|
|
386 var list = q.ToList();
|
|
|
387 Assert.AreNotEqual(0, list.Count);
|
|
|
388
|
|
|
389 str = "123";
|
|
|
390 list = q.ToList();
|
|
|
391 Assert.AreEqual(0, list.Count);
|
|
|
392 });
|
|
|
393 }
|
|
|
394
|
|
|
395 [Test]
|
|
|
396 public void HasValue1()
|
|
|
397 {
|
|
|
398 var expected = from p in Parent where p.Value1.HasValue select p;
|
|
|
399 ForEachProvider(db => AreEqual(expected, from p in db.Parent where p.Value1.HasValue select p));
|
|
|
400 }
|
|
|
401
|
|
|
402 [Test]
|
|
|
403 public void HasValue2()
|
|
|
404 {
|
|
|
405 ForEachProvider(db => Assert.AreEqual(2, (from p in db.Parent where !p.Value1.HasValue select p).ToList().Count));
|
|
|
406 }
|
|
|
407
|
|
|
408 [Test]
|
|
|
409 public void Value()
|
|
|
410 {
|
|
|
411 ForEachProvider(db => Assert.AreEqual(2, (from p in db.Parent where p.Value1.Value == 1 select p).ToList().Count));
|
|
|
412 }
|
|
|
413
|
|
|
414 [Test]
|
|
|
415 public void CompareNullable1()
|
|
|
416 {
|
|
|
417 ForEachProvider(db => Assert.AreEqual(2, (from p in db.Parent where p.Value1 == 1 select p).ToList().Count));
|
|
|
418 }
|
|
|
419
|
|
|
420 [Test]
|
|
|
421 public void CompareNullable2()
|
|
|
422 {
|
|
|
423 ForEachProvider(db => Assert.AreEqual(1, (from p in db.Parent where p.ParentID == p.Value1 && p.Value1 == 1 select p).ToList().Count));
|
|
|
424 }
|
|
|
425
|
|
|
426 [Test]
|
|
|
427 public void CompareNullable3()
|
|
|
428 {
|
|
|
429 ForEachProvider(db => Assert.AreEqual(1, (from p in db.Parent where p.Value1 == p.ParentID && p.Value1 == 1 select p).ToList().Count));
|
|
|
430 }
|
|
|
431
|
|
|
432 [Test]
|
|
|
433 public void SubQuery()
|
|
|
434 {
|
|
|
435 var expected =
|
|
|
436 from t in
|
|
|
437 from ch in Child
|
|
|
438 select ch.ParentID * 1000
|
|
|
439 where t > 2000
|
|
|
440 select t / 1000;
|
|
|
441
|
|
|
442 ForEachProvider(db => AreEqual(expected,
|
|
|
443 from t in
|
|
|
444 from ch in db.Child
|
|
|
445 select ch.ParentID * 1000
|
|
|
446 where t > 2000
|
|
|
447 select t / 1000));
|
|
|
448 }
|
|
|
449
|
|
|
450 [Test]
|
|
|
451 public void AnonymousEqual1()
|
|
|
452 {
|
|
|
453 var child = new { ParentID = 2, ChildID = 21 };
|
|
|
454 var expected =
|
|
|
455 from ch in Child
|
|
|
456 where ch.ParentID == child.ParentID && ch.ChildID == child.ChildID
|
|
|
457 select ch;
|
|
|
458
|
|
|
459 ForEachProvider(db => AreEqual(expected,
|
|
|
460 from ch in db.Child
|
|
|
461 where new { ch.ParentID, ch.ChildID } == child
|
|
|
462 select ch));
|
|
|
463 }
|
|
|
464
|
|
|
465 [Test]
|
|
|
466 public void AnonymousEqual2()
|
|
|
467 {
|
|
|
468 var child = new { ParentID = 2, ChildID = 21 };
|
|
|
469 var expected =
|
|
|
470 from ch in Child
|
|
|
471 where !(ch.ParentID == child.ParentID && ch.ChildID == child.ChildID) && ch.ParentID > 0
|
|
|
472 select ch;
|
|
|
473
|
|
|
474 ForEachProvider(db => AreEqual(expected,
|
|
|
475 from ch in db.Child
|
|
|
476 where child != new { ch.ParentID, ch.ChildID } && ch.ParentID > 0
|
|
|
477 select ch));
|
|
|
478 }
|
|
|
479
|
|
|
480 [Test]
|
|
|
481 public void AnonymousEqual3()
|
|
|
482 {
|
|
|
483 var expected =
|
|
|
484 from ch in Child
|
|
|
485 where ch.ParentID == 2 && ch.ChildID == 21
|
|
|
486 select ch;
|
|
|
487
|
|
|
488 ForEachProvider(db => AreEqual(expected,
|
|
|
489 from ch in db.Child
|
|
|
490 where new { ch.ParentID, ch.ChildID } == new { ParentID = 2, ChildID = 21 }
|
|
|
491 select ch));
|
|
|
492 }
|
|
|
493
|
|
|
494 [Test]
|
|
|
495 public void AnonymousEqual4()
|
|
|
496 {
|
|
|
497 var parent = new { ParentID = 2, Value1 = (int?)null };
|
|
|
498 var expected =
|
|
|
499 from p in Parent
|
|
|
500 where p.ParentID == parent.ParentID && p.Value1 == parent.Value1
|
|
|
501 select p;
|
|
|
502
|
|
|
503 ForEachProvider(db => AreEqual(expected,
|
|
|
504 from p in db.Parent
|
|
|
505 where new { p.ParentID, p.Value1 } == parent
|
|
|
506 select p));
|
|
|
507 }
|
|
|
508
|
|
|
509 [Test]
|
|
|
510 public void AnonymousEqual5()
|
|
|
511 {
|
|
|
512 var parent = new { ParentID = 3, Value1 = (int?)3 };
|
|
|
513 var expected =
|
|
|
514 from p in Parent
|
|
|
515 where p.ParentID == parent.ParentID && p.Value1 == parent.Value1
|
|
|
516 select p;
|
|
|
517
|
|
|
518 ForEachProvider(db => AreEqual(expected,
|
|
|
519 from p in db.Parent
|
|
|
520 where new { p.ParentID, p.Value1 } == parent
|
|
|
521 select p));
|
|
|
522 }
|
|
|
523
|
|
|
524 [Test]
|
|
|
525 public void CheckLeftJoin1()
|
|
|
526 {
|
|
|
527 var expected =
|
|
|
528 from p in Parent
|
|
|
529 join ch in Child on p.ParentID equals ch.ParentID into lj1
|
|
|
530 from ch in lj1.DefaultIfEmpty()
|
|
|
531 where ch == null
|
|
|
532 select p;
|
|
|
533
|
|
|
534 ForEachProvider(db => AreEqual(expected,
|
|
|
535 from p in db.Parent
|
|
|
536 join ch in db.Child on p.ParentID equals ch.ParentID into lj1
|
|
|
537 from ch in lj1.DefaultIfEmpty()
|
|
|
538 where ch == null
|
|
|
539 select p));
|
|
|
540 }
|
|
|
541
|
|
|
542 [Test]
|
|
|
543 public void CheckLeftJoin2()
|
|
|
544 {
|
|
|
545 var expected =
|
|
|
546 from p in Parent
|
|
|
547 join ch in Child on p.ParentID equals ch.ParentID into lj1
|
|
|
548 from ch in lj1.DefaultIfEmpty()
|
|
|
549 where ch != null
|
|
|
550 select p;
|
|
|
551
|
|
|
552 ForEachProvider(data => AreEqual(expected, CompiledQuery.Compile<ITestDataContext,IQueryable<Parent>>(db =>
|
|
|
553 from p in db.Parent
|
|
|
554 join ch in db.Child on p.ParentID equals ch.ParentID into lj1
|
|
|
555 from ch in lj1.DefaultIfEmpty()
|
|
|
556 where null != ch
|
|
|
557 select p)(data)));
|
|
|
558 }
|
|
|
559
|
|
|
560 [Test]
|
|
|
561 public void CheckLeftJoin3()
|
|
|
562 {
|
|
|
563 var expected =
|
|
|
564 from p in Parent
|
|
|
565 join ch in
|
|
|
566 from c in GrandChild
|
|
|
567 where c.ParentID > 0
|
|
|
568 select new { ParentID = 1 + c.ParentID, c.ChildID }
|
|
|
569 on p.ParentID equals ch.ParentID into lj1
|
|
|
570 from ch in lj1.DefaultIfEmpty()
|
|
|
571 where ch == null && ch == null
|
|
|
572 select p;
|
|
|
573
|
|
|
574 ForEachProvider(new[] { ProviderName.Firebird, ProviderName.Sybase, ProviderName.Access }, db => AreEqual(expected,
|
|
|
575 from p in db.Parent
|
|
|
576 join ch in
|
|
|
577 from c in db.GrandChild
|
|
|
578 where c.ParentID > 0
|
|
|
579 select new { ParentID = 1 + c.ParentID, c.ChildID }
|
|
|
580 on p.ParentID equals ch.ParentID into lj1
|
|
|
581 from ch in lj1.DefaultIfEmpty()
|
|
|
582 where ch == null && ch == null
|
|
|
583 select p));
|
|
|
584 }
|
|
|
585
|
|
|
586 [Test]
|
|
|
587 public void CheckLeftJoin4()
|
|
|
588 {
|
|
|
589 var expected =
|
|
|
590 from p in Parent
|
|
|
591 join ch in
|
|
|
592 from c in Child
|
|
|
593 where c.ParentID > 0
|
|
|
594 select new { c.ParentID, c.ChildID }
|
|
|
595 on p.ParentID equals ch.ParentID into lj1
|
|
|
596 from ch in lj1.DefaultIfEmpty()
|
|
|
597 where ch == null
|
|
|
598 select p;
|
|
|
599
|
|
|
600 ForEachProvider(db => AreEqual(expected,
|
|
|
601 from p in db.Parent
|
|
|
602 join ch in
|
|
|
603 from c in db.Child
|
|
|
604 where c.ParentID > 0
|
|
|
605 select new { c.ParentID, c.ChildID }
|
|
|
606 on p.ParentID equals ch.ParentID into lj1
|
|
|
607 from ch in lj1.DefaultIfEmpty()
|
|
|
608 where ch == null
|
|
|
609 select p));
|
|
|
610 }
|
|
|
611
|
|
|
612 [Test]
|
|
|
613 public void CheckNull1()
|
|
|
614 {
|
|
|
615 ForEachProvider(db => AreEqual(
|
|
|
616 from p in Parent where p != null select p,
|
|
|
617 from p in db.Parent where p != null select p));
|
|
|
618 }
|
|
|
619
|
|
|
620 [Test]
|
|
|
621 public void CheckNull2()
|
|
|
622 {
|
|
|
623 int? n = null;
|
|
|
624
|
|
|
625 ForEachProvider(db => AreEqual(
|
|
|
626 from p in Parent where n != null || p.ParentID > 1 select p,
|
|
|
627 from p in db.Parent where n != null || p.ParentID > 1 select p));
|
|
|
628 }
|
|
|
629
|
|
|
630 [Test]
|
|
|
631 public void CheckNull3()
|
|
|
632 {
|
|
|
633 int? n = 1;
|
|
|
634
|
|
|
635 ForEachProvider(new[] { ProviderName.SqlCe, ProviderName.Firebird }, db => AreEqual(
|
|
|
636 from p in Parent where n != null || p.ParentID > 1 select p,
|
|
|
637 from p in db.Parent where n != null || p.ParentID > 1 select p));
|
|
|
638 }
|
|
|
639
|
|
|
640 [Test]
|
|
|
641 public void CheckCondition1()
|
|
|
642 {
|
|
|
643 var expected =
|
|
|
644 from p in Parent
|
|
|
645 where p.ParentID == 1 && p.Value1 == 1 || p.ParentID == 2 && p.Value1.HasValue
|
|
|
646 select p;
|
|
|
647
|
|
|
648 ForEachProvider(db => AreEqual(expected,
|
|
|
649 from p in db.Parent
|
|
|
650 where p.ParentID == 1 && p.Value1 == 1 || p.ParentID == 2 && p.Value1.HasValue
|
|
|
651 select p));
|
|
|
652 }
|
|
|
653
|
|
|
654 [Test]
|
|
|
655 public void CheckCondition2()
|
|
|
656 {
|
|
|
657 var expected =
|
|
|
658 from p in Parent
|
|
|
659 where p.ParentID == 1 && p.Value1 == 1 || p.ParentID == 2 && (p.ParentID != 3 || p.ParentID == 4) && p.Value1.HasValue
|
|
|
660 select p;
|
|
|
661
|
|
|
662 ForEachProvider(db => AreEqual(expected,
|
|
|
663 from p in db.Parent
|
|
|
664 where p.ParentID == 1 && p.Value1 == 1 || p.ParentID == 2 && (p.ParentID != 3 || p.ParentID == 4) && p.Value1.HasValue
|
|
|
665 select p));
|
|
|
666 }
|
|
|
667
|
|
|
668 [Test]
|
|
|
669 public void CompareObject1()
|
|
|
670 {
|
|
|
671 var child = (from ch in Child where ch.ParentID == 2 select ch).First();
|
|
|
672 var expected = from ch in Child where ch == child select ch;
|
|
|
673
|
|
|
674 ForEachProvider(db => AreEqual(expected, from ch in db.Child where ch == child select ch));
|
|
|
675 }
|
|
|
676
|
|
|
677 [Test]
|
|
|
678 public void CompareObject2()
|
|
|
679 {
|
|
|
680 var parent = (from p in Parent where p.ParentID == 2 select p).First();
|
|
|
681 var expected = from p in Parent where parent == p select p;
|
|
|
682
|
|
|
683 ForEachProvider(db => AreEqual(expected, from p in db.Parent where parent == p select p));
|
|
|
684 }
|
|
|
685
|
|
|
686 [Test]
|
|
|
687 public void CompareObject3()
|
|
|
688 {
|
|
|
689 var child = (from ch in Child where ch.ParentID == 2 select ch).First();
|
|
|
690 var expected = from ch in Child where ch != child select ch;
|
|
|
691
|
|
|
692 ForEachProvider(db => AreEqual(expected, from ch in db.Child where ch != child select ch));
|
|
|
693 }
|
|
|
694
|
|
|
695 [Test]
|
|
|
696 public void OrAnd()
|
|
|
697 {
|
|
|
698 var expected =
|
|
|
699 from c in Child
|
|
|
700 where (c.ParentID == 2 || c.ParentID == 3) && c.ChildID != 21
|
|
|
701 select c;
|
|
|
702
|
|
|
703 ForEachProvider(db => AreEqual(expected,
|
|
|
704 from c in db.Child
|
|
|
705 where (c.ParentID == 2 || c.ParentID == 3) && c.ChildID != 21
|
|
|
706 select c));
|
|
|
707 }
|
|
|
708
|
|
|
709 [Test]
|
|
|
710 public void NotOrAnd()
|
|
|
711 {
|
|
|
712 var expected =
|
|
|
713 from c in Child
|
|
|
714 where !(c.ParentID == 2 || c.ParentID == 3) && c.ChildID != 44
|
|
|
715 select c;
|
|
|
716
|
|
|
717 ForEachProvider(db => AreEqual(expected,
|
|
|
718 from c in db.Child
|
|
|
719 where !(c.ParentID == 2 || c.ParentID == 3) && c.ChildID != 44
|
|
|
720 select c));
|
|
|
721 }
|
|
|
722
|
|
|
723 [Test]
|
|
|
724 public void AndOr()
|
|
|
725 {
|
|
|
726 ForEachProvider(db => AreEqual(
|
|
|
727 from p in Parent
|
|
|
728 where p.ParentID == 1 || (p.ParentID == 2 || p.ParentID == 3) && (p.ParentID == 3 || p.ParentID == 1)
|
|
|
729 select p,
|
|
|
730 from p in db.Parent
|
|
|
731 where p.ParentID == 1 || (p.ParentID == 2 || p.ParentID == 3) && (p.ParentID == 3 || p.ParentID == 1)
|
|
|
732 select p));
|
|
|
733 }
|
|
|
734
|
|
|
735 [Test]
|
|
|
736 public void Contains1()
|
|
|
737 {
|
|
|
738 var words = new [] { "John", "Pupkin" };
|
|
|
739
|
|
|
740 var expected =
|
|
|
741 from p in Person
|
|
|
742 where words.Contains(p.FirstName) || words.Contains(p.LastName)
|
|
|
743 select p;
|
|
|
744
|
|
|
745 ForEachProvider(db => AreEqual(expected,
|
|
|
746 from p in db.Person
|
|
|
747 where words.Contains(p.FirstName) || words.Contains(p.LastName)
|
|
|
748 select p));
|
|
|
749 }
|
|
|
750
|
|
|
751 [Test]
|
|
|
752 public void Contains2()
|
|
|
753 {
|
|
|
754 IEnumerable<int> ids = new [] { 2, 3 };
|
|
|
755
|
|
|
756 ForEachProvider(db => AreEqual(
|
|
|
757 from p in Parent where ids.Contains(p.ParentID) select p,
|
|
|
758 from p in db.Parent where ids.Contains(p.ParentID) select p));
|
|
|
759 }
|
|
|
760
|
|
|
761 static IEnumerable<int> GetIds()
|
|
|
762 {
|
|
|
763 yield return 1;
|
|
|
764 yield return 2;
|
|
|
765 }
|
|
|
766
|
|
|
767 [Test]
|
|
|
768 public void Contains3()
|
|
|
769 {
|
|
|
770 ForEachProvider(db => AreEqual(
|
|
|
771 from p in Parent where GetIds().Contains(p.ParentID) select p,
|
|
|
772 from p in db.Parent where GetIds().Contains(p.ParentID) select p));
|
|
|
773 }
|
|
|
774
|
|
|
775 static IEnumerable<int> GetIds(int start, int n)
|
|
|
776 {
|
|
|
777 for (int i = 0; i < n; i++)
|
|
|
778 yield return start + i;
|
|
|
779 }
|
|
|
780
|
|
|
781 [Test]
|
|
|
782 public void Contains4()
|
|
|
783 {
|
|
|
784 ForEachProvider(db => AreEqual(
|
|
|
785 from p in Parent where GetIds(1, 2).Contains(p.ParentID) || GetIds(3, 0).Contains(p.ParentID) select p,
|
|
|
786 from p in db.Parent where GetIds(1, 2).Contains(p.ParentID) || GetIds(3, 0).Contains(p.ParentID) select p));
|
|
|
787 }
|
|
|
788
|
|
|
789 [Test]
|
|
|
790 public void Contains5()
|
|
|
791 {
|
|
|
792 IEnumerable<int> ids = new int[0];
|
|
|
793
|
|
|
794 ForEachProvider(db => AreEqual(
|
|
|
795 from p in Parent where !ids.Contains(p.ParentID) select p,
|
|
|
796 from p in db.Parent where !ids.Contains(p.ParentID) select p));
|
|
|
797 }
|
|
|
798
|
|
|
799 [Test]
|
|
|
800 public void AliasTest1()
|
|
|
801 {
|
|
|
802 int user = 3;
|
|
|
803
|
|
|
804 ForEachProvider(db => AreEqual(
|
|
|
805 from p in Parent where p.ParentID == user select p,
|
|
|
806 from p in db.Parent where p.ParentID == user select p));
|
|
|
807 }
|
|
|
808
|
|
|
809 [Test]
|
|
|
810 public void AliasTest2()
|
|
|
811 {
|
|
|
812 ForEachProvider(db => AreEqual(
|
|
|
813 Parent.Where(_ => _.ParentID == 3),
|
|
|
814 db.Parent.Where(_ => _.ParentID == 3)));
|
|
|
815 }
|
|
|
816
|
|
|
817 [Test]
|
|
|
818 public void AliasTest3()
|
|
|
819 {
|
|
|
820 ForEachProvider(db => AreEqual(
|
|
|
821 Parent.Where(_p => _p.ParentID == 3),
|
|
|
822 db.Parent.Where(_p => _p.ParentID == 3)));
|
|
|
823 }
|
|
|
824
|
|
|
825 [Test]
|
|
|
826 public void AliasTest4()
|
|
|
827 {
|
|
|
828 ForEachProvider(db => AreEqual(
|
|
|
829 Parent.Where(тбл => тбл.ParentID == 3),
|
|
|
830 db.Parent.Where(тбл => тбл.ParentID == 3)));
|
|
|
831 }
|
|
|
832
|
|
|
833 [Test]
|
|
|
834 public void AliasTest5()
|
|
|
835 {
|
|
|
836 ForEachProvider(db => AreEqual(
|
|
|
837 Parent.Where(p_ => p_.ParentID == 3),
|
|
|
838 db.Parent.Where(p_ => p_.ParentID == 3)));
|
|
|
839 }
|
|
|
840
|
|
|
841 [Test]
|
|
|
842 public void SelectNestedCalculatedTest([IncludeDataContexts("Northwind")] string context)
|
|
|
843 {
|
|
|
844 using (var db = new NorthwindDB())
|
|
|
845 AreEqual(
|
|
|
846 from r in from o in Order select o.Freight * 1000 where r > 100000 select r / 1000,
|
|
|
847 from r in from o in db.Order select o.Freight * 1000 where r > 100000 select r / 1000);
|
|
|
848 }
|
|
|
849
|
|
|
850 [Test]
|
|
|
851 public void CheckField1()
|
|
|
852 {
|
|
|
853 ForEachProvider(db => AreEqual(
|
|
|
854 from p in Parent
|
|
|
855 select new { p } into p
|
|
|
856 where p.p.ParentID == 1
|
|
|
857 select p.p,
|
|
|
858 from p in db.Parent
|
|
|
859 select new { p } into p
|
|
|
860 where p.p.ParentID == 1
|
|
|
861 select p.p));
|
|
|
862 }
|
|
|
863
|
|
|
864 [Test]
|
|
|
865 public void CheckField2()
|
|
|
866 {
|
|
|
867 ForEachProvider(db => AreEqual(
|
|
|
868 from p in Parent
|
|
|
869 select new { p } into p
|
|
|
870 where p.p.ParentID == 1
|
|
|
871 select new { p.p.Value1, p },
|
|
|
872 from p in db.Parent
|
|
|
873 select new { p } into p
|
|
|
874 where p.p.ParentID == 1
|
|
|
875 select new { p.p.Value1, p }));
|
|
|
876 }
|
|
|
877
|
|
|
878 [Test]
|
|
|
879 public void CheckField3()
|
|
|
880 {
|
|
|
881 ForEachProvider(db => AreEqual(
|
|
|
882 from p in Parent
|
|
|
883 select new { p } into p
|
|
|
884 where p.p.ParentID == 1
|
|
|
885 select new { p.p.Value1, p.p },
|
|
|
886 from p in db.Parent
|
|
|
887 select new { p } into p
|
|
|
888 where p.p.ParentID == 1
|
|
|
889 select new { p.p.Value1, p.p }));
|
|
|
890 }
|
|
|
891
|
|
|
892 [Test]
|
|
|
893 public void CheckField4()
|
|
|
894 {
|
|
|
895 ForEachProvider(db => AreEqual(
|
|
|
896 Parent.Select(p => new { p }).Where(p => p.p.ParentID == 1),
|
|
|
897 db.Parent.Select(p => new { p }).Where(p => p.p.ParentID == 1)));
|
|
|
898 }
|
|
|
899
|
|
|
900 [Test]
|
|
|
901 public void CheckField5()
|
|
|
902 {
|
|
|
903 ForEachProvider(db => AreEqual(
|
|
|
904 Parent.Select(p => new { Value = p.Value1 + 1, p }).Where(p => p.Value == 2 && p.p.ParentID == 1),
|
|
|
905 db.Parent.Select(p => new { Value = p.Value1 + 1, p }).Where(p => p.Value == 2 && p.p.ParentID == 1)));
|
|
|
906 }
|
|
|
907
|
|
|
908 [Test]
|
|
|
909 public void CheckField6()
|
|
|
910 {
|
|
|
911 ForEachProvider(db => AreEqual(
|
|
|
912 from p1 in Parent
|
|
|
913 select new { p1, Value = p1.Value1 * 100 } into p
|
|
|
914 where p.p1.ParentID == 1 && p.Value > 0
|
|
|
915 select new { p, p.p1.Value1, p.Value, p.p1 },
|
|
|
916
|
|
|
917 from p1 in db.Parent
|
|
|
918 select new { p1, Value = p1.Value1 * 100 } into p
|
|
|
919 where p.p1.ParentID == 1 && p.Value > 0
|
|
|
920 select new { p, p.p1.Value1, p.Value, p.p1 }));
|
|
|
921 }
|
|
|
922
|
|
|
923 [Test]
|
|
|
924 public void SubQuery1()
|
|
|
925 {
|
|
|
926 ForEachProvider(db => AreEqual(
|
|
|
927 from p in Types
|
|
|
928 select new { Value = Math.Round(p.MoneyValue, 2) } into pp
|
|
|
929 where pp.Value != 0 && pp.Value != 7
|
|
|
930 select pp.Value,
|
|
|
931 from p in db.Types
|
|
|
932 select new { Value = Math.Round(p.MoneyValue, 2) } into pp
|
|
|
933 where pp.Value != 0 && pp.Value != 7
|
|
|
934 select pp.Value));
|
|
|
935 }
|
|
|
936
|
|
|
937 [Test]
|
|
|
938 public void SearchCondition1()
|
|
|
939 {
|
|
|
940 ForEachProvider(db => AreEqual(
|
|
|
941 from t in Types
|
|
|
942 where !t.BoolValue && t.MoneyValue > 1 && (t.SmallIntValue == 5 || t.SmallIntValue == 7 || t.SmallIntValue == 8)
|
|
|
943 select t,
|
|
|
944 from t in db.Types
|
|
|
945 where !t.BoolValue && t.MoneyValue > 1 && (t.SmallIntValue == 5 || t.SmallIntValue == 7 || t.SmallIntValue == 8)
|
|
|
946 select t));
|
|
|
947 }
|
|
|
948 }
|
|
|
949 }
|
