|
0
|
1 using System;
|
|
|
2 using System.Linq;
|
|
|
3
|
|
|
4 using BLToolkit.Data.DataProvider;
|
|
|
5 using BLToolkit.Data.Linq;
|
|
|
6 using BLToolkit.DataAccess;
|
|
|
7 using BLToolkit.Mapping;
|
|
|
8
|
|
|
9 using NUnit.Framework;
|
|
|
10
|
|
|
11 namespace Data.Linq
|
|
|
12 {
|
|
|
13 using Model;
|
|
|
14
|
|
|
15 [TestFixture]
|
|
|
16 public class JoinTest : TestBase
|
|
|
17 {
|
|
|
18 [Test]
|
|
|
19 public void InnerJoin1()
|
|
|
20 {
|
|
|
21 TestJohn(db =>
|
|
|
22 from p1 in db.Person
|
|
|
23 join p2 in db.Person on p1.ID equals p2.ID
|
|
|
24 where p1.ID == 1
|
|
|
25 select new Person { ID = p1.ID, FirstName = p2.FirstName });
|
|
|
26 }
|
|
|
27
|
|
|
28 [Test]
|
|
|
29 public void InnerJoin2()
|
|
|
30 {
|
|
|
31 TestJohn(db =>
|
|
|
32 from p1 in db.Person
|
|
|
33 join p2 in db.Person on new { p1.ID, p1.FirstName } equals new { p2.ID, p2.FirstName }
|
|
|
34 where p1.ID == 1
|
|
|
35 select new Person { ID = p1.ID, FirstName = p2.FirstName });
|
|
|
36 }
|
|
|
37
|
|
|
38 [Test]
|
|
|
39 public void InnerJoin3()
|
|
|
40 {
|
|
|
41 TestJohn(db =>
|
|
|
42 from p1 in db.Person
|
|
|
43 join p2 in
|
|
|
44 from p2 in db.Person join p3 in db.Person on new { p2.ID, p2.LastName } equals new { p3.ID, p3.LastName } select new { p2, p3 }
|
|
|
45 on new { p1.ID, p1.FirstName } equals new { p2.p2.ID, p2.p2.FirstName }
|
|
|
46 where p1.ID == 1
|
|
|
47 select new Person { ID = p1.ID, FirstName = p2.p2.FirstName, LastName = p2.p3.LastName });
|
|
|
48 }
|
|
|
49
|
|
|
50 [Test]
|
|
|
51 public void InnerJoin4()
|
|
|
52 {
|
|
|
53 TestJohn(db =>
|
|
|
54 from p1 in db.Person
|
|
|
55 join p2 in db.Person on new { p1.ID, p1.FirstName } equals new { p2.ID, p2.FirstName }
|
|
|
56 join p3 in db.Person on new { p2.ID, p2.LastName } equals new { p3.ID, p3.LastName }
|
|
|
57 where p1.ID == 1
|
|
|
58 select new Person { ID = p1.ID, FirstName = p2.FirstName, LastName = p3.LastName });
|
|
|
59 }
|
|
|
60
|
|
|
61 [Test]
|
|
|
62 public void InnerJoin5()
|
|
|
63 {
|
|
|
64 TestJohn(db =>
|
|
|
65 from p1 in db.Person
|
|
|
66 join p2 in db.Person on new { p1.ID, p1.FirstName } equals new { p2.ID, p2.FirstName }
|
|
|
67 join p3 in db.Person on new { p1.ID, p2.LastName } equals new { p3.ID, p3.LastName }
|
|
|
68 where p1.ID == 1
|
|
|
69 select new Person { ID = p1.ID, FirstName = p2.FirstName, LastName = p3.LastName });
|
|
|
70 }
|
|
|
71
|
|
|
72 [Test]
|
|
|
73 public void InnerJoin6()
|
|
|
74 {
|
|
|
75 TestJohn(db =>
|
|
|
76 from p1 in db.Person
|
|
|
77 join p2 in from p3 in db.Person select new { ID = p3.ID + 1, p3.FirstName } on p1.ID equals p2.ID - 1
|
|
|
78 where p1.ID == 1
|
|
|
79 select new Person { ID = p1.ID, FirstName = p2.FirstName });
|
|
|
80 }
|
|
|
81
|
|
|
82 [Test]
|
|
|
83 public void InnerJoin7()
|
|
|
84 {
|
|
|
85 var expected =
|
|
|
86 from t in
|
|
|
87 from ch in Child
|
|
|
88 join p in Parent on ch.ParentID equals p.ParentID
|
|
|
89 select ch.ParentID + p.ParentID
|
|
|
90 where t > 2
|
|
|
91 select t;
|
|
|
92
|
|
|
93 ForEachProvider(db => AreEqual(expected,
|
|
|
94 from t in
|
|
|
95 from ch in db.Child
|
|
|
96 join p in db.Parent on ch.ParentID equals p.ParentID
|
|
|
97 select ch.ParentID + p.ParentID
|
|
|
98 where t > 2
|
|
|
99 select t));
|
|
|
100 }
|
|
|
101
|
|
|
102 [Test]
|
|
|
103 public void InnerJoin8()
|
|
|
104 {
|
|
|
105 ForEachProvider(db => AreEqual(
|
|
|
106 from t in
|
|
|
107 from ch in Child
|
|
|
108 join p in Parent on ch.ParentID equals p.ParentID
|
|
|
109 select new { ID = ch.ParentID + p.ParentID }
|
|
|
110 where t.ID > 2
|
|
|
111 select t,
|
|
|
112 from t in
|
|
|
113 from ch in db.Child
|
|
|
114 join p in db.Parent on ch.ParentID equals p.ParentID
|
|
|
115 select new { ID = ch.ParentID + p.ParentID }
|
|
|
116 where t.ID > 2
|
|
|
117 select t));
|
|
|
118 }
|
|
|
119
|
|
|
120 [Test]
|
|
|
121 public void InnerJoin9()
|
|
|
122 {
|
|
|
123 ForEachProvider(new[] { ProviderName.Access }, db => AreEqual(
|
|
|
124 from g in GrandChild
|
|
|
125 join p in Parent4 on g.Child.ParentID equals p.ParentID
|
|
|
126 where g.ParentID < 10 && p.Value1 == TypeValue.Value3
|
|
|
127 select g,
|
|
|
128 from g in db.GrandChild
|
|
|
129 join p in db.Parent4 on g.Child.ParentID equals p.ParentID
|
|
|
130 where g.ParentID < 10 && p.Value1 == TypeValue.Value3
|
|
|
131 select g));
|
|
|
132 }
|
|
|
133
|
|
|
134 [Test]
|
|
|
135 public void InnerJoin10()
|
|
|
136 {
|
|
|
137 ForEachProvider(db => AreEqual(
|
|
|
138 from p in Parent
|
|
|
139 join g in GrandChild on p.ParentID equals g.ParentID into q
|
|
|
140 from q1 in q
|
|
|
141 select new { p.ParentID, q1.GrandChildID },
|
|
|
142 from p in db.Parent
|
|
|
143 join g in db.GrandChild on p.ParentID equals g.ParentID into q
|
|
|
144 from q1 in q
|
|
|
145 select new { p.ParentID, q1.GrandChildID }));
|
|
|
146 }
|
|
|
147
|
|
|
148 [Test]
|
|
|
149 public void GroupJoin1()
|
|
|
150 {
|
|
|
151 ForEachProvider(db => AreEqual(
|
|
|
152 from p in Parent
|
|
|
153 join ch in Child on p.ParentID equals ch.ParentID into lj1
|
|
|
154 where p.ParentID == 1
|
|
|
155 select p,
|
|
|
156 from p in db.Parent
|
|
|
157 join ch in db.Child on p.ParentID equals ch.ParentID into lj1
|
|
|
158 where p.ParentID == 1
|
|
|
159 select p));
|
|
|
160 }
|
|
|
161
|
|
|
162 [Test]
|
|
|
163 public void GroupJoin2()
|
|
|
164 {
|
|
|
165 ForEachProvider(db =>
|
|
|
166 {
|
|
|
167 var q =
|
|
|
168 from p in db.Parent
|
|
|
169 join c in db.Child on p.ParentID equals c.ParentID into lj
|
|
|
170 where p.ParentID == 1
|
|
|
171 select new { p, lj };
|
|
|
172
|
|
|
173 var list = q.ToList();
|
|
|
174
|
|
|
175 Assert.AreEqual(1, list.Count);
|
|
|
176 Assert.AreEqual(1, list[0].p.ParentID);
|
|
|
177 Assert.AreEqual(1, list[0].lj.Count());
|
|
|
178
|
|
|
179 var ch = list[0].lj.ToList();
|
|
|
180
|
|
|
181 Assert.AreEqual( 1, ch[0].ParentID);
|
|
|
182 Assert.AreEqual(11, ch[0].ChildID);
|
|
|
183 });
|
|
|
184 }
|
|
|
185
|
|
|
186 [Test]
|
|
|
187 public void GroupJoin3()
|
|
|
188 {
|
|
|
189 var q1 = Parent
|
|
|
190 .GroupJoin(
|
|
|
191 Child,
|
|
|
192 p => p.ParentID,
|
|
|
193 ch => ch.ParentID,
|
|
|
194 (p, lj1) => new { p, lj1 = new { lj1 } }
|
|
|
195 )
|
|
|
196 .Where (t => t.p.ParentID == 2)
|
|
|
197 .Select(t => new { t.p, t.lj1 });
|
|
|
198
|
|
|
199 var list1 = q1.ToList();
|
|
|
200
|
|
|
201 ForEachProvider(db =>
|
|
|
202 {
|
|
|
203 var q2 = db.Parent
|
|
|
204 .GroupJoin(
|
|
|
205 db.Child,
|
|
|
206 p => p.ParentID,
|
|
|
207 ch => ch.ParentID,
|
|
|
208 (p, lj1) => new { p, lj1 = new { lj1 } }
|
|
|
209 )
|
|
|
210 .Where (t => t.p.ParentID == 2)
|
|
|
211 .Select(t => new { t.p, t.lj1 });
|
|
|
212
|
|
|
213 var list2 = q2.ToList();
|
|
|
214
|
|
|
215 Assert.AreEqual(list1.Count, list2.Count);
|
|
|
216 Assert.AreEqual(list1[0].p.ParentID, list2[0].p.ParentID);
|
|
|
217 Assert.AreEqual(list1[0].lj1.lj1.Count(), list2[0].lj1.lj1.Count());
|
|
|
218 });
|
|
|
219 }
|
|
|
220
|
|
|
221 [Test]
|
|
|
222 public void GroupJoin4()
|
|
|
223 {
|
|
|
224 var q1 =
|
|
|
225 from p in Parent
|
|
|
226 join ch in
|
|
|
227 from c in Child select new { c.ParentID, c.ChildID }
|
|
|
228 on p.ParentID equals ch.ParentID into lj1
|
|
|
229 where p.ParentID == 3
|
|
|
230 select new { p, lj1 };
|
|
|
231
|
|
|
232 var list1 = q1.ToList();
|
|
|
233
|
|
|
234 ForEachProvider(db =>
|
|
|
235 {
|
|
|
236 var q2 =
|
|
|
237 from p in db.Parent
|
|
|
238 join ch in
|
|
|
239 from c in db.Child select new { c.ParentID, c.ChildID }
|
|
|
240 on p.ParentID equals ch.ParentID into lj1
|
|
|
241 where p.ParentID == 3
|
|
|
242 select new { p, lj1 };
|
|
|
243
|
|
|
244 var list2 = q2.ToList();
|
|
|
245
|
|
|
246 Assert.AreEqual(list1.Count, list2.Count);
|
|
|
247 Assert.AreEqual(list1[0].p.ParentID, list2[0].p.ParentID);
|
|
|
248 Assert.AreEqual(list1[0].lj1.Count(), list2[0].lj1.Count());
|
|
|
249 });
|
|
|
250 }
|
|
|
251
|
|
|
252 [Test]
|
|
|
253 public void GroupJoin5()
|
|
|
254 {
|
|
|
255 ForEachProvider(db => AreEqual(
|
|
|
256 from p in Parent
|
|
|
257 join ch in Child on p.ParentID equals ch.ParentID into lj1
|
|
|
258 where p.ParentID == 1
|
|
|
259 select lj1.First(),
|
|
|
260 from p in db.Parent
|
|
|
261 join ch in db.Child on p.ParentID equals ch.ParentID into lj1
|
|
|
262 where p.ParentID == 1
|
|
|
263 select lj1.First()));
|
|
|
264 }
|
|
|
265
|
|
|
266 [Test]
|
|
|
267 public void GroupJoin51()
|
|
|
268 {
|
|
|
269 var expected =
|
|
|
270 (
|
|
|
271 from p in Parent
|
|
|
272 join ch in Child on p.ParentID equals ch.ParentID into lj1
|
|
|
273 where p.ParentID == 1
|
|
|
274 select new { p1 = lj1, p2 = lj1.First() }
|
|
|
275 ).ToList();
|
|
|
276
|
|
|
277 ForEachProvider(db =>
|
|
|
278 {
|
|
|
279 var result =
|
|
|
280 (
|
|
|
281 from p in db.Parent
|
|
|
282 join ch in db.Child on p.ParentID equals ch.ParentID into lj1
|
|
|
283 where p.ParentID == 1
|
|
|
284 select new { p1 = lj1, p2 = lj1.First() }
|
|
|
285 ).ToList();
|
|
|
286
|
|
|
287 Assert.AreEqual(expected.Count, result.Count);
|
|
|
288 AreEqual(expected[0].p1, result[0].p1);
|
|
|
289 });
|
|
|
290 }
|
|
|
291
|
|
|
292 [Test]
|
|
|
293 public void GroupJoin52()
|
|
|
294 {
|
|
|
295 ForEachProvider(db => AreEqual(
|
|
|
296 from p in Parent
|
|
|
297 join ch in Child on p.ParentID equals ch.ParentID into lj1
|
|
|
298 where p.ParentID == 1
|
|
|
299 select lj1.First().ParentID,
|
|
|
300 from p in db.Parent
|
|
|
301 join ch in db.Child on p.ParentID equals ch.ParentID into lj1
|
|
|
302 where p.ParentID == 1
|
|
|
303 select lj1.First().ParentID));
|
|
|
304 }
|
|
|
305
|
|
|
306 [Test]
|
|
|
307 public void GroupJoin53()
|
|
|
308 {
|
|
|
309 ForEachProvider(db => AreEqual(
|
|
|
310 from p in Parent
|
|
|
311 join ch in Child on p.ParentID equals ch.ParentID into lj1
|
|
|
312 where p.ParentID == 1
|
|
|
313 select lj1.Select(_ => _.ParentID).First(),
|
|
|
314 from p in db.Parent
|
|
|
315 join ch in db.Child on p.ParentID equals ch.ParentID into lj1
|
|
|
316 where p.ParentID == 1
|
|
|
317 select lj1.Select(_ => _.ParentID).First()));
|
|
|
318 }
|
|
|
319
|
|
|
320 [Test]
|
|
|
321 public void GroupJoin54()
|
|
|
322 {
|
|
|
323 ForEachProvider(db => AreEqual(
|
|
|
324 from p in Parent
|
|
|
325 join ch in Child on p.ParentID equals ch.ParentID into lj1
|
|
|
326 where p.ParentID == 1
|
|
|
327 select new { p1 = lj1.Count(), p2 = lj1.First() },
|
|
|
328 from p in db.Parent
|
|
|
329 join ch in db.Child on p.ParentID equals ch.ParentID into lj1
|
|
|
330 where p.ParentID == 1
|
|
|
331 select new { p1 = lj1.Count(), p2 = lj1.First() }));
|
|
|
332 }
|
|
|
333
|
|
|
334 [Test]
|
|
|
335 public void GroupJoin6()
|
|
|
336 {
|
|
|
337 var n = 1;
|
|
|
338
|
|
|
339 var q1 =
|
|
|
340 from p in Parent
|
|
|
341 join c in Child on p.ParentID + n equals c.ParentID into lj
|
|
|
342 where p.ParentID == 1
|
|
|
343 select new { p, lj };
|
|
|
344
|
|
|
345 var list1 = q1.ToList();
|
|
|
346 var ch1 = list1[0].lj.ToList();
|
|
|
347
|
|
|
348 ForEachProvider(db =>
|
|
|
349 {
|
|
|
350 var q2 =
|
|
|
351 from p in db.Parent
|
|
|
352 join c in db.Child on p.ParentID + n equals c.ParentID into lj
|
|
|
353 where p.ParentID == 1
|
|
|
354 select new { p, lj };
|
|
|
355
|
|
|
356 var list2 = q2.ToList();
|
|
|
357
|
|
|
358 Assert.AreEqual(list1.Count, list2.Count);
|
|
|
359 Assert.AreEqual(list1[0].p.ParentID, list2[0].p.ParentID);
|
|
|
360 Assert.AreEqual(list1[0].lj.Count(), list2[0].lj.Count());
|
|
|
361
|
|
|
362 var ch2 = list2[0].lj.ToList();
|
|
|
363
|
|
|
364 Assert.AreEqual(ch1[0].ParentID, ch2[0].ParentID);
|
|
|
365 Assert.AreEqual(ch1[0].ChildID, ch2[0].ChildID);
|
|
|
366 });
|
|
|
367 }
|
|
|
368
|
|
|
369 [Test]
|
|
|
370 public void GroupJoin7()
|
|
|
371 {
|
|
|
372 var n = 1;
|
|
|
373
|
|
|
374 var q1 =
|
|
|
375 from p in Parent
|
|
|
376 join c in Child on new { id = p.ParentID } equals new { id = c.ParentID - n } into j
|
|
|
377 where p.ParentID == 1
|
|
|
378 select new { p, j };
|
|
|
379
|
|
|
380 var list1 = q1.ToList();
|
|
|
381 var ch1 = list1[0].j.ToList();
|
|
|
382
|
|
|
383 ForEachProvider(
|
|
|
384 new[] { ProviderName.Firebird },
|
|
|
385 db =>
|
|
|
386 {
|
|
|
387 var q2 =
|
|
|
388 from p in db.Parent
|
|
|
389 join c in db.Child on new { id = p.ParentID } equals new { id = c.ParentID - n } into j
|
|
|
390 where p.ParentID == 1
|
|
|
391 select new { p, j };
|
|
|
392
|
|
|
393 var list2 = q2.ToList();
|
|
|
394
|
|
|
395 Assert.AreEqual(list1.Count, list2.Count);
|
|
|
396 Assert.AreEqual(list1[0].p.ParentID, list2[0].p.ParentID);
|
|
|
397 Assert.AreEqual(list1[0].j.Count(), list2[0].j.Count());
|
|
|
398
|
|
|
399 var ch2 = list2[0].j.ToList();
|
|
|
400
|
|
|
401 Assert.AreEqual(ch1[0].ParentID, ch2[0].ParentID);
|
|
|
402 Assert.AreEqual(ch1[0].ChildID, ch2[0].ChildID);
|
|
|
403 });
|
|
|
404 }
|
|
|
405
|
|
|
406 [Test]
|
|
|
407 public void GroupJoin8()
|
|
|
408 {
|
|
|
409 ForEachProvider(db => AreEqual(
|
|
|
410 from p in Parent
|
|
|
411 join c in Child on p.ParentID equals c.ParentID into g
|
|
|
412 select new
|
|
|
413 {
|
|
|
414 Child = g.FirstOrDefault()
|
|
|
415 },
|
|
|
416 from p in db.Parent
|
|
|
417 join c in db.Child on p.ParentID equals c.ParentID into g
|
|
|
418 select new
|
|
|
419 {
|
|
|
420 Child = g.FirstOrDefault()
|
|
|
421 }
|
|
|
422 ));
|
|
|
423 }
|
|
|
424
|
|
|
425 [Test]
|
|
|
426 public void GroupJoin9()
|
|
|
427 {
|
|
|
428 ForEachProvider(db => AreEqual(
|
|
|
429 Parent
|
|
|
430 .GroupJoin(
|
|
|
431 Parent,
|
|
|
432 x => new { Id = x.ParentID },
|
|
|
433 y => new { Id = y.ParentID },
|
|
|
434 (xid, yid) => new { xid, yid }
|
|
|
435 )
|
|
|
436 .SelectMany(
|
|
|
437 y => y.yid.DefaultIfEmpty(),
|
|
|
438 (x1, y) => new { x1.xid, y }
|
|
|
439 )
|
|
|
440 .GroupJoin(
|
|
|
441 Parent,
|
|
|
442 x => new { Id = x.xid.ParentID },
|
|
|
443 y => new { Id = y.ParentID },
|
|
|
444 (x2, y) => new { x2.xid, x2.y, h = y }
|
|
|
445 )
|
|
|
446 .SelectMany(
|
|
|
447 a => a.h.DefaultIfEmpty(),
|
|
|
448 (x3, a) => new { x3.xid, x3.y, a }
|
|
|
449 )
|
|
|
450 .GroupJoin(
|
|
|
451 Parent,
|
|
|
452 x => new { Id = x.xid.ParentID },
|
|
|
453 y => new { Id = y.ParentID },
|
|
|
454 (x4, y) => new { x4.xid, x4.y, x4.a, p = y }
|
|
|
455 )
|
|
|
456 .SelectMany(
|
|
|
457 z => z.p.DefaultIfEmpty(),
|
|
|
458 (x5, z) => new { x5.xid, z, x5.y, x5.a }
|
|
|
459 )
|
|
|
460 .GroupJoin(
|
|
|
461 Parent,
|
|
|
462 x => new { Id = x.xid.ParentID },
|
|
|
463 y => new { Id = y.Value1 ?? 1 },
|
|
|
464 (x6, y) => new { x6.xid, xy = x6.y, x6.a, x6.z, y }
|
|
|
465 )
|
|
|
466 .SelectMany(
|
|
|
467 z => z.y.DefaultIfEmpty(),
|
|
|
468 (x7, z) => new { x7.xid, z, x7.xy, x7.a, xz = x7.z }
|
|
|
469 )
|
|
|
470 .GroupJoin(
|
|
|
471 Parent,
|
|
|
472 x => new { Id = x.xid.ParentID },
|
|
|
473 y => new { Id = y.ParentID },
|
|
|
474 (x8, y) => new { x8.xid, x8.z, x8.xy, x8.a, x8.xz, y }
|
|
|
475 )
|
|
|
476 .SelectMany(
|
|
|
477 a => a.y.DefaultIfEmpty(),
|
|
|
478 (x9, a) => new { x9.xid, x9.z, x9.xy, xa = x9.a, x9.xz, a }
|
|
|
479 ),
|
|
|
480 db.Parent
|
|
|
481 .GroupJoin(
|
|
|
482 db.Parent,
|
|
|
483 x => new { Id = x.ParentID },
|
|
|
484 y => new { Id = y.ParentID },
|
|
|
485 (xid, yid) => new { xid, yid }
|
|
|
486 )
|
|
|
487 .SelectMany(
|
|
|
488 y => y.yid.DefaultIfEmpty(),
|
|
|
489 (x1, y) => new { x1.xid, y }
|
|
|
490 )
|
|
|
491 .GroupJoin(
|
|
|
492 db.Parent,
|
|
|
493 x => new { Id = x.xid.ParentID },
|
|
|
494 y => new { Id = y.ParentID },
|
|
|
495 (x2, y) => new { x2.xid, x2.y, h = y }
|
|
|
496 )
|
|
|
497 .SelectMany(
|
|
|
498 a => a.h.DefaultIfEmpty(),
|
|
|
499 (x3, a) => new { x3.xid, x3.y, a }
|
|
|
500 )
|
|
|
501 .GroupJoin(
|
|
|
502 db.Parent,
|
|
|
503 x => new { Id = x.xid.ParentID },
|
|
|
504 y => new { Id = y.ParentID },
|
|
|
505 (x4, y) => new { x4.xid, x4.y, x4.a, p = y }
|
|
|
506 )
|
|
|
507 .SelectMany(
|
|
|
508 z => z.p.DefaultIfEmpty(),
|
|
|
509 (x5, z) => new { x5.xid, z, x5.y, x5.a }
|
|
|
510 )
|
|
|
511 .GroupJoin(
|
|
|
512 db.Parent,
|
|
|
513 x => new { Id = x.xid.ParentID },
|
|
|
514 y => new { Id = y.Value1 ?? 1 },
|
|
|
515 (x6, y) => new { x6.xid, xy = x6.y, x6.a, x6.z, y }
|
|
|
516 )
|
|
|
517 .SelectMany(
|
|
|
518 z => z.y.DefaultIfEmpty(),
|
|
|
519 (x7, z) => new { x7.xid, z, x7.xy, x7.a, xz = x7.z }
|
|
|
520 )
|
|
|
521 .GroupJoin(
|
|
|
522 db.Parent,
|
|
|
523 x => new { Id = x.xid.ParentID },
|
|
|
524 y => new { Id = y.ParentID },
|
|
|
525 (x8, y) => new { x8.xid, x8.z, x8.xy, x8.a, x8.xz, y }
|
|
|
526 )
|
|
|
527 .SelectMany(
|
|
|
528 a => a.y.DefaultIfEmpty(),
|
|
|
529 (x9, a) => new { x9.xid, x9.z, x9.xy, xa = x9.a, x9.xz, a }
|
|
|
530 )));
|
|
|
531 }
|
|
|
532
|
|
|
533 [Test]
|
|
|
534 public void LeftJoin1()
|
|
|
535 {
|
|
|
536 var expected =
|
|
|
537 from p in Parent
|
|
|
538 join ch in Child on p.ParentID equals ch.ParentID into lj1
|
|
|
539 from ch in lj1.DefaultIfEmpty()
|
|
|
540 where p.ParentID >= 4
|
|
|
541 select new { p, ch };
|
|
|
542
|
|
|
543 ForEachProvider(db => AreEqual(expected,
|
|
|
544 from p in db.Parent
|
|
|
545 join ch in db.Child on p.ParentID equals ch.ParentID into lj1
|
|
|
546 from ch in lj1.DefaultIfEmpty()
|
|
|
547 where p.ParentID >= 4
|
|
|
548 select new { p, ch }));
|
|
|
549 }
|
|
|
550
|
|
|
551 [Test]
|
|
|
552 public void LeftJoin2()
|
|
|
553 {
|
|
|
554 ForEachProvider(db => AreEqual(
|
|
|
555 from p in Parent
|
|
|
556 join ch in Child on p.ParentID equals ch.ParentID into lj1
|
|
|
557 from ch in lj1.DefaultIfEmpty()
|
|
|
558 select new { p, ch },
|
|
|
559 from p in db.Parent
|
|
|
560 join ch in db.Child on p.ParentID equals ch.ParentID into lj1
|
|
|
561 from ch in lj1.DefaultIfEmpty()
|
|
|
562 select new { p, ch }));
|
|
|
563 }
|
|
|
564
|
|
|
565 [Test]
|
|
|
566 public void LeftJoin3()
|
|
|
567 {
|
|
|
568 ForEachProvider(db => AreEqual(
|
|
|
569 from c in Child select c.Parent,
|
|
|
570 from c in db.Child select c.Parent));
|
|
|
571 }
|
|
|
572
|
|
|
573 [Test]
|
|
|
574 public void LeftJoin4()
|
|
|
575 {
|
|
|
576 ForEachProvider(db => AreEqual(
|
|
|
577 Parent
|
|
|
578 .GroupJoin(Child,
|
|
|
579 x => new { x.ParentID, x.Value1 },
|
|
|
580 y => new { y.ParentID, Value1 = (int?)y.ParentID },
|
|
|
581 (x, y) => new { Parent = x, Child = y })
|
|
|
582 .SelectMany(
|
|
|
583 y => y.Child.DefaultIfEmpty(),
|
|
|
584 (x, y) => new { x.Parent, Child = x.Child.FirstOrDefault() })
|
|
|
585 .Where(x => x.Parent.ParentID == 1 && x.Parent.Value1 != null)
|
|
|
586 .OrderBy(x => x.Parent.ParentID),
|
|
|
587 db.Parent
|
|
|
588 .GroupJoin(db.Child,
|
|
|
589 x => new { x.ParentID, x.Value1 },
|
|
|
590 y => new { y.ParentID, Value1 = (int?)y.ParentID },
|
|
|
591 (x, y) => new { Parent = x, Child = y })
|
|
|
592 .SelectMany(
|
|
|
593 y => y.Child.DefaultIfEmpty(),
|
|
|
594 (x, y) => new { x.Parent, Child = x.Child.FirstOrDefault() })
|
|
|
595 .Where(x => x.Parent.ParentID == 1 && x.Parent.Value1 != null)
|
|
|
596 .OrderBy(x => x.Parent.ParentID)));
|
|
|
597 }
|
|
|
598
|
|
|
599 public enum EnumInt
|
|
|
600 {
|
|
|
601 [MapValue(1)] One
|
|
|
602 }
|
|
|
603
|
|
|
604 [TableName("Child")]
|
|
|
605 public class EnumChild
|
|
|
606 {
|
|
|
607 public int ParentID;
|
|
|
608 public EnumInt ChildID;
|
|
|
609 }
|
|
|
610
|
|
|
611 [Test]
|
|
|
612 public void LeftJoin5()
|
|
|
613 {
|
|
|
614 ForEachProvider(db =>
|
|
|
615 {
|
|
|
616 var q =
|
|
|
617 from p in db.Parent
|
|
|
618 join ch in new Table<EnumChild>(db) on p.ParentID equals ch.ParentID into lj1
|
|
|
619 from ch in lj1.DefaultIfEmpty()
|
|
|
620 where ch == null
|
|
|
621 select new { p, ch };
|
|
|
622
|
|
|
623 var list = q.ToList();
|
|
|
624 list.ToString();
|
|
|
625 });
|
|
|
626 }
|
|
|
627
|
|
|
628 [Test]
|
|
|
629 public void SubQueryJoin()
|
|
|
630 {
|
|
|
631 var expected =
|
|
|
632 from p in Parent
|
|
|
633 join ch in
|
|
|
634 from c in Child
|
|
|
635 where c.ParentID > 0
|
|
|
636 select new { c.ParentID, c.ChildID }
|
|
|
637 on p.ParentID equals ch.ParentID into lj1
|
|
|
638 from ch in lj1.DefaultIfEmpty()
|
|
|
639 select p;
|
|
|
640
|
|
|
641 ForEachProvider(db => AreEqual(expected,
|
|
|
642 from p in db.Parent
|
|
|
643 join ch in
|
|
|
644 from c in db.Child
|
|
|
645 where c.ParentID > 0
|
|
|
646 select new { c.ParentID, c.ChildID }
|
|
|
647 on p.ParentID equals ch.ParentID into lj1
|
|
|
648 from ch in lj1.DefaultIfEmpty()
|
|
|
649 select p));
|
|
|
650 }
|
|
|
651
|
|
|
652 [Test]
|
|
|
653 public void ReferenceJoin1()
|
|
|
654 {
|
|
|
655 ForEachProvider(new[] { ProviderName.Access }, db => AreEqual(
|
|
|
656 from c in Child join g in GrandChild on c equals g.Child select new { c.ParentID, g.GrandChildID },
|
|
|
657 from c in db.Child join g in db.GrandChild on c equals g.Child select new { c.ParentID, g.GrandChildID }));
|
|
|
658 }
|
|
|
659
|
|
|
660 [Test]
|
|
|
661 public void ReferenceJoin2()
|
|
|
662 {
|
|
|
663 ForEachProvider(new[] { ProviderName.Access }, db => AreEqual(
|
|
|
664 from g in GrandChild
|
|
|
665 join c in Child on g.Child equals c
|
|
|
666 select new { c.ParentID, g.GrandChildID },
|
|
|
667 from g in db.GrandChild
|
|
|
668 join c in db.Child on g.Child equals c
|
|
|
669 select new { c.ParentID, g.GrandChildID }));
|
|
|
670 }
|
|
|
671
|
|
|
672 [Test]
|
|
|
673 public void JoinByAnonymousTest()
|
|
|
674 {
|
|
|
675 ForEachProvider(new[] { ProviderName.Access }, db => AreEqual(
|
|
|
676 from p in Parent
|
|
|
677 join c in Child on new { Parent = p, p.ParentID } equals new { c.Parent, c.ParentID }
|
|
|
678 select new { p.ParentID, c.ChildID },
|
|
|
679 from p in db.Parent
|
|
|
680 join c in db.Child on new { Parent = p, p.ParentID } equals new { c.Parent, c.ParentID }
|
|
|
681 select new { p.ParentID, c.ChildID }));
|
|
|
682 }
|
|
|
683
|
|
|
684 [Test]
|
|
|
685 public void FourTableJoin()
|
|
|
686 {
|
|
|
687 ForEachProvider(db => AreEqual(
|
|
|
688 from p in Parent
|
|
|
689 join c1 in Child on p.ParentID equals c1.ParentID
|
|
|
690 join c2 in GrandChild on c1.ParentID equals c2.ParentID
|
|
|
691 join c3 in GrandChild on c2.ParentID equals c3.ParentID
|
|
|
692 select new { p, c1Key = c1.ChildID, c2Key = c2.GrandChildID, c3Key = c3.GrandChildID },
|
|
|
693 from p in db.Parent
|
|
|
694 join c1 in db.Child on p.ParentID equals c1.ParentID
|
|
|
695 join c2 in db.GrandChild on c1.ParentID equals c2.ParentID
|
|
|
696 join c3 in db.GrandChild on c2.ParentID equals c3.ParentID
|
|
|
697 select new { p, c1Key = c1.ChildID, c2Key = c2.GrandChildID, c3Key = c3.GrandChildID }));
|
|
|
698 }
|
|
|
699
|
|
|
700 [Test]
|
|
|
701 public void ProjectionTest1()
|
|
|
702 {
|
|
|
703 ForEachProvider(db => AreEqual(
|
|
|
704 from p1 in Person
|
|
|
705 join p2 in Person on p1.ID equals p2.ID
|
|
|
706 select new { ID1 = new { Value = p1.ID }, FirstName2 = p2.FirstName, } into p1
|
|
|
707 select p1.ID1.Value,
|
|
|
708 from p1 in db.Person
|
|
|
709 join p2 in db.Person on p1.ID equals p2.ID
|
|
|
710 select new { ID1 = new { Value = p1.ID }, FirstName2 = p2.FirstName, } into p1
|
|
|
711 select p1.ID1.Value));
|
|
|
712 }
|
|
|
713
|
|
|
714 [Test]
|
|
|
715 public void LeftJoinTest()
|
|
|
716 {
|
|
|
717 // Reproduces the problem described here: http://rsdn.ru/forum/prj.rfd/4221837.flat.aspx
|
|
|
718 ForEachProvider(
|
|
|
719 //Providers.Select(p => p.Name).Except(new[] { ProviderName.SQLite }).ToArray(),
|
|
|
720 db =>
|
|
|
721 {
|
|
|
722 var q =
|
|
|
723 from p1 in db.Person
|
|
|
724 join p2 in db.Person on p1.ID equals p2.ID into g
|
|
|
725 from p2 in g.DefaultIfEmpty() // yes I know the join will always succeed and it'll never be null, but just for test's sake :)
|
|
|
726 select new { p1, p2 };
|
|
|
727
|
|
|
728 var list = q.ToList(); // NotImplementedException? :(
|
|
|
729 Assert.That(list, Is.Not.Empty);
|
|
|
730 });
|
|
|
731 }
|
|
|
732
|
|
|
733 [Test]
|
|
|
734 public void LeftJoinTest2()
|
|
|
735 {
|
|
|
736 // THIS TEST MUST BE RUN IN RELEASE CONFIGURATION (BECAUSE IT PASSES UNDER DEBUG CONFIGURATION)
|
|
|
737 // Reproduces the problem described here: http://rsdn.ru/forum/prj.rfd/4221837.flat.aspx
|
|
|
738
|
|
|
739 ForEachProvider(
|
|
|
740 Providers.Select(p => p.Name).Except(new[] { ProviderName.SQLite }).ToArray(),
|
|
|
741 db =>
|
|
|
742 {
|
|
|
743 var q =
|
|
|
744 from p1 in db.Patient
|
|
|
745 join p2 in db.Patient on p1.Diagnosis equals p2.Diagnosis into g
|
|
|
746 from p2 in g.DefaultIfEmpty() // yes I know the join will always succeed and it'll never be null, but just for test's sake :)
|
|
|
747 join p3 in db.Person on p2.PersonID equals p3.ID
|
|
|
748 select new { p1, p2, p3 };
|
|
|
749
|
|
|
750 var arr = q.ToArray(); // NotImplementedException? :(
|
|
|
751 Assert.That(arr, Is.Not.Empty);
|
|
|
752 });
|
|
|
753 }
|
|
|
754
|
|
|
755 [Test]
|
|
|
756 public void StackOverflow([IncludeDataContexts("Sql2008", "Sql2012")] string context)
|
|
|
757 {
|
|
|
758 using (var db = new TestDbManager(context))
|
|
|
759 {
|
|
|
760 var q =
|
|
|
761 from c in db.Child
|
|
|
762 join p in db.Parent on c.ParentID equals p.ParentID
|
|
|
763 select new { p, c };
|
|
|
764
|
|
|
765 for (var i = 0; i < 100; i++)
|
|
|
766 {
|
|
|
767 q =
|
|
|
768 from c in q
|
|
|
769 join p in db.Parent on c.p.ParentID equals p.ParentID
|
|
|
770 select new { p, c.c };
|
|
|
771 }
|
|
|
772
|
|
|
773 var list = q.ToList();
|
|
|
774 }
|
|
|
775 }
|
|
|
776
|
|
|
777 [Test]
|
|
|
778 public void ApplyJoin([IncludeDataContexts("Sql2008")] string context)
|
|
|
779 {
|
|
|
780 using (var db = new TestDbManager(context))
|
|
|
781 {
|
|
|
782 var q =
|
|
|
783 from ch in db.Child
|
|
|
784 from p in new Model.Functions(db).GetParentByID(ch.Parent.ParentID)
|
|
|
785 select p;
|
|
|
786
|
|
|
787 q.ToList();
|
|
|
788 }
|
|
|
789 }
|
|
|
790
|
|
|
791 [Test]
|
|
|
792 public void Issue257([DataContexts] string context)
|
|
|
793 {
|
|
|
794 using (var db = GetDataContext(context))
|
|
|
795 {
|
|
|
796 var q =
|
|
|
797 from m in db.Types
|
|
|
798 join p in db.Parent on m.ID equals p.ParentID
|
|
|
799 group m by new
|
|
|
800 {
|
|
|
801 m.DateTimeValue.Date
|
|
|
802 }
|
|
|
803 into b
|
|
|
804 select new
|
|
|
805 {
|
|
|
806 QualiStatusByDate = b.Key,
|
|
|
807 Count = b.Count()
|
|
|
808 };
|
|
|
809
|
|
|
810 q.ToList();
|
|
|
811 }
|
|
|
812 }
|
|
|
813 }
|
|
|
814 }
|
