|
0
|
1 using System;
|
|
|
2 using System.Collections;
|
|
|
3 using System.Collections.Generic;
|
|
|
4 using System.Linq;
|
|
|
5 using System.Linq.Expressions;
|
|
|
6
|
|
|
7 using BLToolkit.Data.DataProvider;
|
|
|
8 using BLToolkit.DataAccess;
|
|
|
9 using BLToolkit.Mapping;
|
|
|
10
|
|
|
11 using NUnit.Framework;
|
|
|
12
|
|
|
13 namespace Data.Linq
|
|
|
14 {
|
|
|
15 using Model;
|
|
|
16
|
|
|
17 [TestFixture]
|
|
|
18 public class ComplexTest : TestBase
|
|
|
19 {
|
|
|
20 [Test]
|
|
|
21 public void Contains1()
|
|
|
22 {
|
|
|
23 var q1 =
|
|
|
24 from gc1 in GrandChild
|
|
|
25 join max in
|
|
|
26 from gch in GrandChild
|
|
|
27 group gch by gch.ChildID into g
|
|
|
28 select g.Max(c => c.GrandChildID)
|
|
|
29 on gc1.GrandChildID equals max
|
|
|
30 select gc1;
|
|
|
31
|
|
|
32 var expected =
|
|
|
33 from ch in Child
|
|
|
34 join p in Parent on ch.ParentID equals p.ParentID
|
|
|
35 join gc2 in q1 on p.ParentID equals gc2.ParentID into g
|
|
|
36 from gc3 in g.DefaultIfEmpty()
|
|
|
37 where gc3 == null || !new[] { 111, 222 }.Contains(gc3.GrandChildID.Value)
|
|
|
38 select new { p.ParentID, gc3 };
|
|
|
39
|
|
|
40 ForEachProvider(new [] { ProviderName.Firebird, ProviderName.Access }, db =>
|
|
|
41 {
|
|
|
42 var q2 =
|
|
|
43 from gc1 in db.GrandChild
|
|
|
44 join max in
|
|
|
45 from gch in db.GrandChild
|
|
|
46 group gch by gch.ChildID into g
|
|
|
47 select g.Max(c => c.GrandChildID)
|
|
|
48 on gc1.GrandChildID equals max
|
|
|
49 select gc1;
|
|
|
50
|
|
|
51 var result =
|
|
|
52 from ch in db.Child
|
|
|
53 join p in db.Parent on ch.ParentID equals p.ParentID
|
|
|
54 join gc2 in q2 on p.ParentID equals gc2.ParentID into g
|
|
|
55 from gc3 in g.DefaultIfEmpty()
|
|
|
56 where gc3 == null || !new[] { 111, 222 }.Contains(gc3.GrandChildID.Value)
|
|
|
57 select new { p.ParentID, gc3 };
|
|
|
58
|
|
|
59 AreEqual(expected, result);
|
|
|
60 });
|
|
|
61 }
|
|
|
62
|
|
|
63 [Test]
|
|
|
64 public void Contains2()
|
|
|
65 {
|
|
|
66 var q1 =
|
|
|
67 from gc in GrandChild
|
|
|
68 join max in
|
|
|
69 from gch in GrandChild
|
|
|
70 group gch by gch.ChildID into g
|
|
|
71 select g.Max(c => c.GrandChildID)
|
|
|
72 on gc.GrandChildID equals max
|
|
|
73 select gc;
|
|
|
74
|
|
|
75 var expected =
|
|
|
76 from ch in Child
|
|
|
77 join p in Parent on ch.ParentID equals p.ParentID
|
|
|
78 join gc in q1 on p.ParentID equals gc.ParentID into g
|
|
|
79 from gc in g.DefaultIfEmpty()
|
|
|
80 where gc == null || gc.GrandChildID != 111 && gc.GrandChildID != 222
|
|
|
81 select new
|
|
|
82 {
|
|
|
83 Parent = p,
|
|
|
84 GrandChildID = gc,
|
|
|
85 Value = GetValue(gc != null ? gc.ChildID : int.MaxValue)
|
|
|
86 };
|
|
|
87
|
|
|
88 ForEachProvider(new [] { ProviderName.Firebird, ProviderName.Access }, db =>
|
|
|
89 {
|
|
|
90 var q2 =
|
|
|
91 from gc in db.GrandChild
|
|
|
92 join max in
|
|
|
93 from gch in db.GrandChild
|
|
|
94 group gch by gch.ChildID into g
|
|
|
95 select g.Max(c => c.GrandChildID)
|
|
|
96 on gc.GrandChildID equals max
|
|
|
97 select gc;
|
|
|
98
|
|
|
99 var result =
|
|
|
100 from ch in db.Child
|
|
|
101 join p in db.Parent on ch.ParentID equals p.ParentID
|
|
|
102 join gc in q2 on p.ParentID equals gc.ParentID into g
|
|
|
103 from gc in g.DefaultIfEmpty()
|
|
|
104 where gc == null || gc.GrandChildID != 111 && gc.GrandChildID != 222
|
|
|
105 select new
|
|
|
106 {
|
|
|
107 Parent = p,
|
|
|
108 GrandChildID = gc,
|
|
|
109 Value = GetValue(gc != null ? gc.ChildID : int.MaxValue)
|
|
|
110 };
|
|
|
111
|
|
|
112 AreEqual(expected, result);
|
|
|
113 });
|
|
|
114 }
|
|
|
115
|
|
|
116 static int GetValue(int? value)
|
|
|
117 {
|
|
|
118 return value ?? 777;
|
|
|
119 }
|
|
|
120
|
|
|
121 [Test]
|
|
|
122 public void Contains3()
|
|
|
123 {
|
|
|
124 var q1 =
|
|
|
125 from gc in GrandChild1
|
|
|
126 join max in
|
|
|
127 from gch in GrandChild1
|
|
|
128 group gch by gch.ChildID into g
|
|
|
129 select g.Max(c => c.GrandChildID)
|
|
|
130 on gc.GrandChildID equals max
|
|
|
131 select gc;
|
|
|
132
|
|
|
133 var expected =
|
|
|
134 from ch in Child
|
|
|
135 join p in Parent on ch.ParentID equals p.ParentID
|
|
|
136 join gc in q1 on p.ParentID equals gc.ParentID into g
|
|
|
137 from gc in g.DefaultIfEmpty()
|
|
|
138 where gc == null || !new[] { 111, 222 }.Contains(gc.GrandChildID.Value)
|
|
|
139 select new { p.ParentID, gc };
|
|
|
140
|
|
|
141 ForEachProvider(new [] { ProviderName.SQLite, ProviderName.Access }, db =>
|
|
|
142 {
|
|
|
143 var q2 =
|
|
|
144 from gc in db.GrandChild1
|
|
|
145 join max in
|
|
|
146 from gch in db.GrandChild1
|
|
|
147 group gch by gch.ChildID into g
|
|
|
148 select g.Max(c => c.GrandChildID)
|
|
|
149 on gc.GrandChildID equals max
|
|
|
150 select gc;
|
|
|
151
|
|
|
152 var result =
|
|
|
153 from ch in db.Child
|
|
|
154 join p in db.Parent on ch.ParentID equals p.ParentID
|
|
|
155 join gc in q2 on p.ParentID equals gc.ParentID into g
|
|
|
156 from gc in g.DefaultIfEmpty()
|
|
|
157 where gc == null || !new[] { 111, 222 }.Contains(gc.GrandChildID.Value)
|
|
|
158 select new { p.ParentID, gc };
|
|
|
159
|
|
|
160 AreEqual(expected, result);
|
|
|
161 });
|
|
|
162 }
|
|
|
163
|
|
|
164 [Test]
|
|
|
165 public void Contains4()
|
|
|
166 {
|
|
|
167 var q1 =
|
|
|
168 from gc in GrandChild1
|
|
|
169 join max in
|
|
|
170 from gch in GrandChild1
|
|
|
171 group gch by gch.ChildID into g
|
|
|
172 select g.Max(c => c.GrandChildID)
|
|
|
173 on gc.GrandChildID equals max
|
|
|
174 select gc;
|
|
|
175
|
|
|
176 var expected =
|
|
|
177 from ch in Child
|
|
|
178 join gc in q1 on ch.Parent.ParentID equals gc.ParentID into g
|
|
|
179 from gc in g.DefaultIfEmpty()
|
|
|
180 where gc == null || !new[] { 111, 222 }.Contains(gc.GrandChildID.Value)
|
|
|
181 select new { ch.Parent, gc };
|
|
|
182
|
|
|
183 ForEachProvider(new [] { ProviderName.SQLite, ProviderName.Access }, db =>
|
|
|
184 {
|
|
|
185 var q2 =
|
|
|
186 from gc in db.GrandChild1
|
|
|
187 join max in
|
|
|
188 from gch in db.GrandChild1
|
|
|
189 group gch by gch.ChildID into g
|
|
|
190 select g.Max(c => c.GrandChildID)
|
|
|
191 on gc.GrandChildID equals max
|
|
|
192 select gc;
|
|
|
193
|
|
|
194 var result =
|
|
|
195 from ch in db.Child
|
|
|
196 join gc in q2 on ch.Parent.ParentID equals gc.ParentID into g
|
|
|
197 from gc in g.DefaultIfEmpty()
|
|
|
198 where gc == null || !new[] { 111, 222 }.Contains(gc.GrandChildID.Value)
|
|
|
199 select new { ch.Parent, gc };
|
|
|
200
|
|
|
201 AreEqual(expected, result);
|
|
|
202 });
|
|
|
203 }
|
|
|
204
|
|
|
205 [Test]
|
|
|
206 public void Join1()
|
|
|
207 {
|
|
|
208 var q1 =
|
|
|
209 from p in Parent
|
|
|
210 join c in Child on p.ParentID equals c.ParentID
|
|
|
211 join g in GrandChild on p.ParentID equals g.ParentID
|
|
|
212 select new { p, c, g };
|
|
|
213
|
|
|
214 var expected =
|
|
|
215 from x in q1
|
|
|
216 where
|
|
|
217 (
|
|
|
218 (x.c.ParentID == 2 || x.c.ParentID == 3) && x.g.ChildID != 21 && x.g.ChildID != 33
|
|
|
219 ) || (
|
|
|
220 x.g.ParentID == 3 && x.g.ChildID == 32
|
|
|
221 ) || (
|
|
|
222 x.g.ChildID == 11
|
|
|
223 )
|
|
|
224 select x;
|
|
|
225
|
|
|
226 ForEachProvider(db =>
|
|
|
227 {
|
|
|
228 var q2 =
|
|
|
229 from p in db.Parent
|
|
|
230 join c in db.Child on p.ParentID equals c.ParentID
|
|
|
231 join g in db.GrandChild on p.ParentID equals g.ParentID
|
|
|
232 select new { p, c, g };
|
|
|
233
|
|
|
234 var result =
|
|
|
235 from x in q2
|
|
|
236 where
|
|
|
237 (
|
|
|
238 (x.c.ParentID == 2 || x.c.ParentID == 3) && x.g.ChildID != 21 && x.g.ChildID != 33
|
|
|
239 ) || (
|
|
|
240 x.g.ParentID == 3 && x.g.ChildID == 32
|
|
|
241 ) || (
|
|
|
242 x.g.ChildID == 11
|
|
|
243 )
|
|
|
244 select x;
|
|
|
245
|
|
|
246 AreEqual(expected, result);
|
|
|
247 });
|
|
|
248 }
|
|
|
249
|
|
|
250 public class MyObject
|
|
|
251 {
|
|
|
252 public Parent Parent;
|
|
|
253 public Child Child;
|
|
|
254 }
|
|
|
255
|
|
|
256 IQueryable<MyObject> GetData(ITestDataContext db, int id)
|
|
|
257 {
|
|
|
258 var q =
|
|
|
259 from p in db.Parent
|
|
|
260 join c in db.Child on p.ParentID equals c.ChildID
|
|
|
261 where p.ParentID == id && c.ChildID > 0
|
|
|
262 select new MyObject { Parent = p, Child = c };
|
|
|
263
|
|
|
264 return q;
|
|
|
265 }
|
|
|
266
|
|
|
267 [Test]
|
|
|
268 public void Join2()
|
|
|
269 {
|
|
|
270 ForEachProvider(db =>
|
|
|
271 {
|
|
|
272 var q =
|
|
|
273 from o in GetData(db, 1)
|
|
|
274 from g in o.Parent.GrandChildren
|
|
|
275 select new { o, g };
|
|
|
276
|
|
|
277 var list = q.ToList();
|
|
|
278 });
|
|
|
279 }
|
|
|
280
|
|
|
281 [Test]
|
|
|
282 public void ExpressionTest1([IncludeDataContexts("Northwind")] string context)
|
|
|
283 {
|
|
|
284 Expression<Func<Northwind.Customer,bool>> pred1 = cust=>cust.Country=="UK";
|
|
|
285 Expression<Func<Northwind.Customer,bool>> pred2 = cust=>cust.Country=="France";
|
|
|
286
|
|
|
287 var param = Expression.Parameter(typeof(Northwind.Customer), "x");
|
|
|
288 var final = Expression.Lambda<Func<Northwind.Customer,bool>>(
|
|
|
289 Expression.OrElse(
|
|
|
290 Expression.Invoke(pred1, param),
|
|
|
291 Expression.Invoke(pred2, param)
|
|
|
292 ), param);
|
|
|
293
|
|
|
294 using (var db = new NorthwindDB())
|
|
|
295 {
|
|
|
296 var count = db.Customer.Count(final);
|
|
|
297 }
|
|
|
298 }
|
|
|
299
|
|
|
300 [Test]
|
|
|
301 public void ExpressionTest2()
|
|
|
302 {
|
|
|
303 Expression<Func<Parent,bool>> pred1 = _=>_.ParentID == 1;
|
|
|
304 Expression<Func<Parent,bool>> pred2 = _=>_.Value1 == 1 || _.Value1 == null;
|
|
|
305
|
|
|
306 var param = Expression.Parameter(typeof(Parent), "x");
|
|
|
307 var final = Expression.Lambda<Func<Parent,bool>>(
|
|
|
308 Expression.AndAlso(
|
|
|
309 Expression.Invoke(pred1, param),
|
|
|
310 Expression.Invoke(pred2, param)
|
|
|
311 ), param);
|
|
|
312
|
|
|
313 using (var db = new TestDbManager())
|
|
|
314 {
|
|
|
315 Assert.AreEqual(1, db.Parent.Count(final));
|
|
|
316 }
|
|
|
317 }
|
|
|
318
|
|
|
319 #region IEnumerableTest
|
|
|
320
|
|
|
321 public class Entity
|
|
|
322 {
|
|
|
323 public int Id { get; set; }
|
|
|
324 }
|
|
|
325
|
|
|
326 public enum TestEntityType : byte { Type1, Type2 }
|
|
|
327
|
|
|
328 [TableName("GrandChild")]
|
|
|
329 [MapField("GrandChildID", "Id")]
|
|
|
330 [MapField("ChildID", "InnerEnity.Id")]
|
|
|
331 [MapField("ParentID", "InnerEntityType")]
|
|
|
332 public class LookupEntity : Entity
|
|
|
333 {
|
|
|
334 public Entity InnerEnity { get; set; }
|
|
|
335 public TestEntityType InnerEntityType { get; set; }
|
|
|
336 }
|
|
|
337
|
|
|
338 [TableName("GrandChild")]
|
|
|
339 [MapField("GrandChildID", "Id")]
|
|
|
340 [MapField("ChildID", "Owner.Id")]
|
|
|
341 [MapField("ParentID", "EntityType")]
|
|
|
342 public class TestEntityBase : Entity
|
|
|
343 {
|
|
|
344 public TestEntityType EntityType { get; set; }
|
|
|
345 public SuperAccount Owner { get; set; }
|
|
|
346 }
|
|
|
347
|
|
|
348 public class TestEntity : TestEntityBase, IEnumerable<object>
|
|
|
349 {
|
|
|
350 #region IEnumerable<object> Members
|
|
|
351
|
|
|
352 public IEnumerator<object> GetEnumerator()
|
|
|
353 {
|
|
|
354 throw new NotImplementedException();
|
|
|
355 }
|
|
|
356
|
|
|
357 #endregion
|
|
|
358
|
|
|
359 #region IEnumerable Members
|
|
|
360
|
|
|
361 IEnumerator IEnumerable.GetEnumerator()
|
|
|
362 {
|
|
|
363 throw new NotImplementedException();
|
|
|
364 }
|
|
|
365
|
|
|
366 #endregion
|
|
|
367 }
|
|
|
368
|
|
|
369 public class TestEntity2 : TestEntityBase
|
|
|
370 {
|
|
|
371 }
|
|
|
372
|
|
|
373 public enum SuperAccountType { Client, Organization }
|
|
|
374
|
|
|
375 [TableName("GrandChild")]
|
|
|
376 [MapField("GrandChildID", "Id")]
|
|
|
377 [MapField("ParentID", "Type")]
|
|
|
378 public class SuperAccount : Entity, IEnumerable<object>
|
|
|
379 {
|
|
|
380 public List<Entity> InnerAccounts { get; set; }
|
|
|
381 public SuperAccountType Type { get; set; }
|
|
|
382
|
|
|
383
|
|
|
384 #region IEnumerable<object> Members
|
|
|
385
|
|
|
386 public IEnumerator<object> GetEnumerator()
|
|
|
387 {
|
|
|
388 throw new NotImplementedException();
|
|
|
389 }
|
|
|
390
|
|
|
391 #endregion
|
|
|
392
|
|
|
393 #region IEnumerable Members
|
|
|
394
|
|
|
395 IEnumerator IEnumerable.GetEnumerator()
|
|
|
396 {
|
|
|
397 throw new NotImplementedException();
|
|
|
398 }
|
|
|
399
|
|
|
400 #endregion
|
|
|
401 }
|
|
|
402
|
|
|
403 [Test]
|
|
|
404 public void IEnumerableTest1()
|
|
|
405 {
|
|
|
406 using (var db = new TestDbManager())
|
|
|
407 {
|
|
|
408 var res =
|
|
|
409 from rc in db.GetTable<TestEntity>()
|
|
|
410 join li in db.GetTable<LookupEntity>() on rc.Id equals li.InnerEnity.Id
|
|
|
411 where rc.EntityType == TestEntityType.Type1
|
|
|
412 select rc;
|
|
|
413
|
|
|
414 res.ToList();
|
|
|
415 }
|
|
|
416 }
|
|
|
417
|
|
|
418 [Test]
|
|
|
419 public void IEnumerableTest2()
|
|
|
420 {
|
|
|
421 using (var db = new TestDbManager())
|
|
|
422 {
|
|
|
423 var zones =
|
|
|
424 from z in db.GetTable<TestEntity2>()
|
|
|
425 join o in db.GetTable<SuperAccount>() on z.Owner.Id equals o.Id
|
|
|
426 select z;
|
|
|
427
|
|
|
428 zones.ToList();
|
|
|
429 }
|
|
|
430 }
|
|
|
431
|
|
|
432 #endregion
|
|
|
433 }
|
|
|
434 }
|
