|
0
|
1 using System;
|
|
|
2 using System.Collections.Generic;
|
|
|
3 using System.Linq;
|
|
|
4 using System.Linq.Expressions;
|
|
|
5
|
|
|
6 using BLToolkit.Data.Linq;
|
|
|
7
|
|
|
8 using NUnit.Framework;
|
|
|
9
|
|
|
10 namespace Data.Linq
|
|
|
11 {
|
|
|
12 using Model;
|
|
|
13
|
|
|
14 [TestFixture]
|
|
|
15 public class SetTest : TestBase
|
|
|
16 {
|
|
|
17 [Test]
|
|
|
18 public void Except1()
|
|
|
19 {
|
|
|
20 ForEachProvider(db => AreEqual(
|
|
|
21 Child.Except( Child.Where(p => p.ParentID == 3)),
|
|
|
22 db.Child.Except(db.Child.Where(p => p.ParentID == 3))));
|
|
|
23 }
|
|
|
24
|
|
|
25 //[Test]
|
|
|
26 public void Except2()
|
|
|
27 {
|
|
|
28 var ids = new[] { 1, 2 };
|
|
|
29
|
|
|
30 ForEachProvider(db => Assert.AreEqual(
|
|
|
31 Child.Where(c => c.GrandChildren.Select(_ => _.ParentID ?? 0).Except(ids).Count() == 0),
|
|
|
32 db.Child.Where(c => c.GrandChildren.Select(_ => _.ParentID ?? 0).Except(ids).Count() == 0)));
|
|
|
33 }
|
|
|
34
|
|
|
35 [Test]
|
|
|
36 public void Intersect()
|
|
|
37 {
|
|
|
38 ForEachProvider(db => AreEqual(
|
|
|
39 Child.Intersect( Child.Where(p => p.ParentID == 3)),
|
|
|
40 db.Child.Intersect(db.Child.Where(p => p.ParentID == 3))));
|
|
|
41 }
|
|
|
42
|
|
|
43 [Test]
|
|
|
44 public void Any1()
|
|
|
45 {
|
|
|
46 ForEachProvider(db => AreEqual(
|
|
|
47 Parent.Where(p => Child.Where(c => c.ParentID == p.ParentID).Any(c => c.ParentID > 3)),
|
|
|
48 db.Parent.Where(p => db.Child.Where(c => c.ParentID == p.ParentID).Any(c => c.ParentID > 3))));
|
|
|
49 }
|
|
|
50
|
|
|
51 [Test]
|
|
|
52 public void Any2()
|
|
|
53 {
|
|
|
54 ForEachProvider(db => AreEqual(
|
|
|
55 Parent.Where(p => Child.Where(c => c.ParentID == p.ParentID).Any()),
|
|
|
56 db.Parent.Where(p => db.Child.Where(c => c.ParentID == p.ParentID).Any())));
|
|
|
57 }
|
|
|
58
|
|
|
59 [Test]
|
|
|
60 public void Any3()
|
|
|
61 {
|
|
|
62 ForEachProvider(db => AreEqual(
|
|
|
63 Parent.Where(p => p.Children.Any(c => c.ParentID > 3)),
|
|
|
64 db.Parent.Where(p => p.Children.Any(c => c.ParentID > 3))));
|
|
|
65 }
|
|
|
66
|
|
|
67 [Test]
|
|
|
68 public void Any31()
|
|
|
69 {
|
|
|
70 ForEachProvider(db => AreEqual(
|
|
|
71 Parent.Where(p => p.ParentID > 0 && p.Children.Any(c => c.ParentID > 0 && c.ParentID > 3)),
|
|
|
72 db.Parent.Where(p => p.ParentID > 0 && p.Children.Any(c => c.ParentID > 0 && c.ParentID > 3))));
|
|
|
73 }
|
|
|
74
|
|
|
75 [MethodExpression("SelectAnyExpression")]
|
|
|
76 static bool SelectAny(Parent p)
|
|
|
77 {
|
|
|
78 return p.Children.Any(c => c.ParentID > 0 && c.ParentID > 3);
|
|
|
79 }
|
|
|
80
|
|
|
81 static Expression<Func<Parent,bool>> SelectAnyExpression()
|
|
|
82 {
|
|
|
83 return p => p.Children.Any(c => c.ParentID > 0 && c.ParentID > 3);
|
|
|
84 }
|
|
|
85
|
|
|
86 [Test]
|
|
|
87 public void Any32()
|
|
|
88 {
|
|
|
89 ForEachProvider(db => AreEqual(
|
|
|
90 Parent.Where(p => p.ParentID > 0 && SelectAny(p)),
|
|
|
91 db.Parent.Where(p => p.ParentID > 0 && SelectAny(p))));
|
|
|
92 }
|
|
|
93
|
|
|
94 [Test]
|
|
|
95 public void Any4()
|
|
|
96 {
|
|
|
97 ForEachProvider(db => AreEqual(
|
|
|
98 Parent.Where(p => p.Children.Any()),
|
|
|
99 db.Parent.Where(p => p.Children.Any())));
|
|
|
100 }
|
|
|
101
|
|
|
102 [Test]
|
|
|
103 public void Any5()
|
|
|
104 {
|
|
|
105 ForEachProvider(db => AreEqual(
|
|
|
106 Parent.Where(p => p.Children.Any(c => c.GrandChildren.Any(g => g.ParentID > 3))),
|
|
|
107 db.Parent.Where(p => p.Children.Any(c => c.GrandChildren.Any(g => g.ParentID > 3)))));
|
|
|
108 }
|
|
|
109
|
|
|
110 [Test]
|
|
|
111 public void Any6()
|
|
|
112 {
|
|
|
113 ForEachProvider(db => Assert.AreEqual(
|
|
|
114 Child.Any(c => c.ParentID > 3),
|
|
|
115 db.Child.Any(c => c.ParentID > 3)));
|
|
|
116 }
|
|
|
117
|
|
|
118 [Test]
|
|
|
119 public void Any61([DataContexts] string context)
|
|
|
120 {
|
|
|
121 using (var db = GetDataContext(context))
|
|
|
122 Assert.AreEqual(
|
|
|
123 Child. Any(c => c.ParentID > 3),
|
|
|
124 db.GetTable<Child>().Any(c => c.ParentID > 3));
|
|
|
125 }
|
|
|
126
|
|
|
127 [Test]
|
|
|
128 public void Any7()
|
|
|
129 {
|
|
|
130 ForEachProvider(db => Assert.AreEqual(Child.Any(), db.Child.Any()));
|
|
|
131 }
|
|
|
132
|
|
|
133 [Test]
|
|
|
134 public void Any8()
|
|
|
135 {
|
|
|
136 ForEachProvider(db => AreEqual(
|
|
|
137 from p in Parent select Child.Select(c => c.Parent).Any(c => c == p),
|
|
|
138 from p in db.Parent select db.Child.Select(c => c.Parent).Any(c => c == p)));
|
|
|
139 }
|
|
|
140
|
|
|
141 [Test]
|
|
|
142 public void Any9()
|
|
|
143 {
|
|
|
144 ForEachProvider(db => AreEqual(
|
|
|
145 from p in
|
|
|
146 from p in Parent
|
|
|
147 from g in p.GrandChildren
|
|
|
148 join c in Child on g.ChildID equals c.ChildID
|
|
|
149 select c
|
|
|
150 where !p.GrandChildren.Any(x => x.ParentID < 0)
|
|
|
151 select p,
|
|
|
152 from p in
|
|
|
153 from p in db.Parent
|
|
|
154 from g in p.GrandChildren
|
|
|
155 join c in db.Child on g.ChildID equals c.ChildID
|
|
|
156 select c
|
|
|
157 where !p.GrandChildren.Any(x => x.ParentID < 0)
|
|
|
158 select p));
|
|
|
159 }
|
|
|
160
|
|
|
161 [Test]
|
|
|
162 public void Any10()
|
|
|
163 {
|
|
|
164 ForEachProvider(db => AreEqual(
|
|
|
165 from p in
|
|
|
166 from p in Parent
|
|
|
167 from g in p.GrandChildren
|
|
|
168 join c in Child on g.ChildID equals c.ChildID
|
|
|
169 select p
|
|
|
170 where !p.GrandChildren.Any(x => x.ParentID < 0)
|
|
|
171 select p,
|
|
|
172 from p in
|
|
|
173 from p in db.Parent
|
|
|
174 from g in p.GrandChildren
|
|
|
175 join c in db.Child on g.ChildID equals c.ChildID
|
|
|
176 select p
|
|
|
177 where !p.GrandChildren.Any(x => x.ParentID < 0)
|
|
|
178 select p));
|
|
|
179 }
|
|
|
180
|
|
|
181 [Test]
|
|
|
182 public void Any11()
|
|
|
183 {
|
|
|
184 ForEachProvider(db => AreEqual(
|
|
|
185 from p in
|
|
|
186 from p in Parent
|
|
|
187 from g in p.GrandChildren
|
|
|
188 join c in Child on g.ChildID equals c.ChildID
|
|
|
189 join t in Types on c.ParentID equals t.ID
|
|
|
190 select c
|
|
|
191 where !p.GrandChildren.Any(x => x.ParentID < 0)
|
|
|
192 select p,
|
|
|
193 from p in
|
|
|
194 from p in db.Parent
|
|
|
195 from g in p.GrandChildren
|
|
|
196 join c in db.Child on g.ChildID equals c.ChildID
|
|
|
197 join t in db.Types on c.ParentID equals t.ID
|
|
|
198 select c
|
|
|
199 where !p.GrandChildren.Any(x => x.ParentID < 0)
|
|
|
200 select p));
|
|
|
201 }
|
|
|
202
|
|
|
203 [Test]
|
|
|
204 public void Any12()
|
|
|
205 {
|
|
|
206 ForEachProvider(db => AreEqual(
|
|
|
207 from p in Parent where Child. Any(c => p.ParentID == c.ParentID && c.ChildID > 3) select p,
|
|
|
208 from p in db.GetTable<Parent>() where db.GetTable<Child>().Any(c => p.ParentID == c.ParentID && c.ChildID > 3) select p));
|
|
|
209 }
|
|
|
210
|
|
|
211 [Test]
|
|
|
212 public void All1()
|
|
|
213 {
|
|
|
214 ForEachProvider(db => AreEqual(
|
|
|
215 Parent.Where(p => Child.Where(c => c.ParentID == p.ParentID).All(c => c.ParentID > 3)),
|
|
|
216 db.Parent.Where(p => db.Child.Where(c => c.ParentID == p.ParentID).All(c => c.ParentID > 3))));
|
|
|
217 }
|
|
|
218
|
|
|
219 [Test]
|
|
|
220 public void All2()
|
|
|
221 {
|
|
|
222 ForEachProvider(db => AreEqual(
|
|
|
223 Parent.Where(p => p.Children.All(c => c.ParentID > 3)),
|
|
|
224 db.Parent.Where(p => p.Children.All(c => c.ParentID > 3))));
|
|
|
225 }
|
|
|
226
|
|
|
227 [Test]
|
|
|
228 public void All3()
|
|
|
229 {
|
|
|
230 ForEachProvider(db => AreEqual(
|
|
|
231 Parent.Where(p => p.Children.All(c => c.GrandChildren.All(g => g.ParentID > 3))),
|
|
|
232 db.Parent.Where(p => p.Children.All(c => c.GrandChildren.All(g => g.ParentID > 3)))));
|
|
|
233 }
|
|
|
234
|
|
|
235 [Test]
|
|
|
236 public void All4()
|
|
|
237 {
|
|
|
238 ForEachProvider(db => Assert.AreEqual(
|
|
|
239 Child.All(c => c.ParentID > 3),
|
|
|
240 db.Child.All(c => c.ParentID > 3)));
|
|
|
241 }
|
|
|
242
|
|
|
243 [Test]
|
|
|
244 public void All5()
|
|
|
245 {
|
|
|
246 int n = 3;
|
|
|
247
|
|
|
248 ForEachProvider(db => Assert.AreEqual(
|
|
|
249 Child.All(c => c.ParentID > n),
|
|
|
250 db.Child.All(c => c.ParentID > n)));
|
|
|
251 }
|
|
|
252
|
|
|
253 [Test]
|
|
|
254 public void SubQueryAllAny()
|
|
|
255 {
|
|
|
256 ForEachProvider(db => AreEqual(
|
|
|
257 from c in Parent
|
|
|
258 where Child.Where(o => o.Parent == c).All(o => Child.Where(e => o == e).Any(e => e.ChildID > 10))
|
|
|
259 select c,
|
|
|
260 from c in db.Parent
|
|
|
261 where db.Child.Where(o => o.Parent == c).All(o => db.Child.Where(e => o == e).Any(e => e.ChildID > 10))
|
|
|
262 select c));
|
|
|
263 }
|
|
|
264
|
|
|
265 [Test]
|
|
|
266 public void AllNestedTest([IncludeDataContexts("Northwind")] string context)
|
|
|
267 {
|
|
|
268 using (var db = new NorthwindDB())
|
|
|
269 AreEqual(
|
|
|
270 from c in Customer
|
|
|
271 where Order.Where(o => o.Customer == c).All(o => Employee.Where(e => o.Employee == e).Any(e => e.FirstName.StartsWith("A")))
|
|
|
272 select c,
|
|
|
273 from c in db.Customer
|
|
|
274 where db.Order.Where(o => o.Customer == c).All(o => db.Employee.Where(e => o.Employee == e).Any(e => e.FirstName.StartsWith("A")))
|
|
|
275 select c);
|
|
|
276 }
|
|
|
277
|
|
|
278 [Test]
|
|
|
279 public void ComplexAllTest([IncludeDataContexts("Northwind")] string context)
|
|
|
280 {
|
|
|
281 using (var db = new NorthwindDB())
|
|
|
282 AreEqual(
|
|
|
283 from o in Order
|
|
|
284 where
|
|
|
285 Customer.Where(c => c == o.Customer).All(c => c.CompanyName.StartsWith("A")) ||
|
|
|
286 Employee.Where(e => e == o.Employee).All(e => e.FirstName.EndsWith("t"))
|
|
|
287 select o,
|
|
|
288 from o in db.Order
|
|
|
289 where
|
|
|
290 db.Customer.Where(c => c == o.Customer).All(c => c.CompanyName.StartsWith("A")) ||
|
|
|
291 db.Employee.Where(e => e == o.Employee).All(e => e.FirstName.EndsWith("t"))
|
|
|
292 select o);
|
|
|
293 }
|
|
|
294
|
|
|
295 [Test]
|
|
|
296 public void Contains1()
|
|
|
297 {
|
|
|
298 ForEachProvider(db => AreEqual(
|
|
|
299 from p in Parent select Child.Select(c => c.Parent).Contains(p),
|
|
|
300 from p in db.Parent select db.Child.Select(c => c.Parent).Contains(p)));
|
|
|
301 }
|
|
|
302
|
|
|
303 [Test]
|
|
|
304 public void Contains2()
|
|
|
305 {
|
|
|
306 ForEachProvider(db => AreEqual(
|
|
|
307 from p in Parent select Child.Select(c => c.ParentID).Contains(p.ParentID),
|
|
|
308 from p in db.Parent select db.Child.Select(c => c.ParentID).Contains(p.ParentID)));
|
|
|
309 }
|
|
|
310
|
|
|
311 [Test]
|
|
|
312 public void Contains201()
|
|
|
313 {
|
|
|
314 ForEachProvider(db => AreEqual(
|
|
|
315 from p in Parent select Child.Select(c => c.ParentID).Contains(p.ParentID - 1),
|
|
|
316 from p in db.Parent select db.Child.Select(c => c.ParentID).Contains(p.ParentID - 1)));
|
|
|
317 }
|
|
|
318
|
|
|
319 [Test]
|
|
|
320 public void Contains3()
|
|
|
321 {
|
|
|
322 ForEachProvider(db => AreEqual(
|
|
|
323 from p in Parent where Child.Select(c => c.Parent).Contains(p) select p,
|
|
|
324 from p in db.Parent where db.Child.Select(c => c.Parent).Contains(p) select p));
|
|
|
325 }
|
|
|
326
|
|
|
327 [Test]
|
|
|
328 public void Contains4()
|
|
|
329 {
|
|
|
330 ForEachProvider(db => AreEqual(
|
|
|
331 from p in Parent where Child.Select(c => c.ParentID).Contains(p.ParentID) select p,
|
|
|
332 from p in db.Parent where db.Child.Select(c => c.ParentID).Contains(p.ParentID) select p));
|
|
|
333 }
|
|
|
334
|
|
|
335 [Test]
|
|
|
336 public void Contains5()
|
|
|
337 {
|
|
|
338 ForEachProvider(db => AreEqual(
|
|
|
339 from p in Parent where Child.Select(c => c.ParentID).Contains(p.ParentID + 1) select p,
|
|
|
340 from p in db.Parent where db.Child.Select(c => c.ParentID).Contains(p.ParentID + 1) select p));
|
|
|
341 }
|
|
|
342
|
|
|
343 [Test]
|
|
|
344 public void Contains6()
|
|
|
345 {
|
|
|
346 var n = 1;
|
|
|
347
|
|
|
348 ForEachProvider(db => AreEqual(
|
|
|
349 from p in Parent where Child.Select(c => c.ParentID).Contains(p.ParentID + n) select p,
|
|
|
350 from p in db.Parent where db.Child.Select(c => c.ParentID).Contains(p.ParentID + n) select p));
|
|
|
351 }
|
|
|
352
|
|
|
353 [Test]
|
|
|
354 public void Contains7()
|
|
|
355 {
|
|
|
356 ForEachProvider(db => Assert.AreEqual(
|
|
|
357 Child.Select(c => c.ParentID).Contains(11),
|
|
|
358 db.Child.Select(c => c.ParentID).Contains(11)));
|
|
|
359 }
|
|
|
360
|
|
|
361 [Test]
|
|
|
362 public void Contains701()
|
|
|
363 {
|
|
|
364 ForEachProvider(db => Assert.AreEqual(
|
|
|
365 Child.Select(c => c.Parent).Contains(new Parent { ParentID = 11, Value1 = 11}),
|
|
|
366 db.Child.Select(c => c.Parent).Contains(new Parent { ParentID = 11, Value1 = 11})));
|
|
|
367 }
|
|
|
368
|
|
|
369 [Test]
|
|
|
370 public void Contains8()
|
|
|
371 {
|
|
|
372 var arr = new[] { GrandChild.ElementAt(0), GrandChild.ElementAt(1) };
|
|
|
373
|
|
|
374 ForEachProvider(db => AreEqual(
|
|
|
375 from p in Parent
|
|
|
376 join ch in Child on p.ParentID equals ch.ParentID
|
|
|
377 join gc in GrandChild on ch.ChildID equals gc.ChildID
|
|
|
378 where arr.Contains(gc)
|
|
|
379 select p,
|
|
|
380 from p in db.Parent
|
|
|
381 join ch in db.Child on p.ParentID equals ch.ParentID
|
|
|
382 join gc in db.GrandChild on ch.ChildID equals gc.ChildID
|
|
|
383 where arr.Contains(gc)
|
|
|
384 select p));
|
|
|
385 }
|
|
|
386
|
|
|
387 [Test]
|
|
|
388 public void Contains801()
|
|
|
389 {
|
|
|
390 var arr = new[] { GrandChild.ElementAt(0), GrandChild.ElementAt(1) };
|
|
|
391
|
|
|
392 ForEachProvider(db => AreEqual(
|
|
|
393 from p in Parent
|
|
|
394 join ch in Child on p.ParentID equals ch.ParentID
|
|
|
395 join gc in GrandChild on ch.ChildID equals gc.ChildID
|
|
|
396 select new GrandChild { ParentID = 2, ChildID = ch.ChildID, GrandChildID = gc.GrandChildID } into gc
|
|
|
397 where arr.Contains(gc)
|
|
|
398 select gc,
|
|
|
399 from p in db.Parent
|
|
|
400 join ch in db.Child on p.ParentID equals ch.ParentID
|
|
|
401 join gc in db.GrandChild on ch.ChildID equals gc.ChildID
|
|
|
402 select new GrandChild { ParentID = 2, ChildID = ch.ChildID, GrandChildID = gc.GrandChildID } into gc
|
|
|
403 where arr.Contains(gc)
|
|
|
404 select gc));
|
|
|
405 }
|
|
|
406
|
|
|
407 [Test]
|
|
|
408 public void Contains802()
|
|
|
409 {
|
|
|
410 var arr = new[] { GrandChild.ElementAt(0), GrandChild.ElementAt(1) };
|
|
|
411
|
|
|
412 ForEachProvider(db => AreEqual(
|
|
|
413 from p in Parent
|
|
|
414 join ch in Child on p.ParentID equals ch.ParentID
|
|
|
415 join gc in GrandChild on ch.ChildID equals gc.ChildID
|
|
|
416 where arr.Contains(new GrandChild { ParentID = p.ParentID, ChildID = ch.ChildID, GrandChildID = gc.GrandChildID })
|
|
|
417 select p,
|
|
|
418 from p in db.Parent
|
|
|
419 join ch in db.Child on p.ParentID equals ch.ParentID
|
|
|
420 join gc in db.GrandChild on ch.ChildID equals gc.ChildID
|
|
|
421 where arr.Contains(new GrandChild { ParentID = p.ParentID, ChildID = ch.ChildID, GrandChildID = gc.GrandChildID })
|
|
|
422 select p));
|
|
|
423 }
|
|
|
424
|
|
|
425 [Test]
|
|
|
426 public void Contains803()
|
|
|
427 {
|
|
|
428 var arr = new[] { GrandChild.ElementAt(0), GrandChild.ElementAt(1) };
|
|
|
429
|
|
|
430 ForEachProvider(db => AreEqual(
|
|
|
431 from p in Parent
|
|
|
432 join ch in Child on p.ParentID equals ch.ParentID
|
|
|
433 join gc in GrandChild on ch.ChildID equals gc.ChildID
|
|
|
434 where arr.Contains(new GrandChild { ParentID = 1, ChildID = ch.ChildID, GrandChildID = gc.GrandChildID })
|
|
|
435 select p,
|
|
|
436 from p in db.Parent
|
|
|
437 join ch in db.Child on p.ParentID equals ch.ParentID
|
|
|
438 join gc in db.GrandChild on ch.ChildID equals gc.ChildID
|
|
|
439 where arr.Contains(new GrandChild { ParentID = 1, ChildID = ch.ChildID, GrandChildID = gc.GrandChildID })
|
|
|
440 select p));
|
|
|
441 }
|
|
|
442
|
|
|
443 [Test]
|
|
|
444 public void Contains9()
|
|
|
445 {
|
|
|
446 var arr = Parent1.Take(2).ToArray();
|
|
|
447
|
|
|
448 ForEachProvider(db => AreEqual(
|
|
|
449 from p in Parent1 where arr.Contains(p) select p,
|
|
|
450 from p in db.Parent1 where arr.Contains(p) select p));
|
|
|
451 }
|
|
|
452
|
|
|
453 [Test]
|
|
|
454 public void Contains10([IncludeDataContexts("Northwind")] string context)
|
|
|
455 {
|
|
|
456 using (var db = new NorthwindDB())
|
|
|
457 {
|
|
|
458 var arr = new[]
|
|
|
459 {
|
|
|
460 new Northwind.Order { OrderID = 11000 },
|
|
|
461 new Northwind.Order { OrderID = 11001 },
|
|
|
462 new Northwind.Order { OrderID = 11002 }
|
|
|
463 };
|
|
|
464
|
|
|
465 var q =
|
|
|
466 from e in db.Employee
|
|
|
467 from o in e.Orders
|
|
|
468 where arr.Contains(o)
|
|
|
469 select new
|
|
|
470 {
|
|
|
471 e.FirstName,
|
|
|
472 o.OrderID,
|
|
|
473 };
|
|
|
474
|
|
|
475 q.ToList();
|
|
|
476 }
|
|
|
477 }
|
|
|
478
|
|
|
479 [Test]
|
|
|
480 public void Contains11([IncludeDataContexts("Northwind")] string context)
|
|
|
481 {
|
|
|
482 using (var db = new NorthwindDB())
|
|
|
483 {
|
|
|
484 var q =
|
|
|
485 from e in db.EmployeeTerritory
|
|
|
486 group e by e.Employee into g
|
|
|
487 where g.Key.EmployeeTerritories.Count() > 1
|
|
|
488 select new
|
|
|
489 {
|
|
|
490 g.Key.LastName,
|
|
|
491 cnt = g.Where(t => t.Employee.FirstName.Contains("an")).Count(),
|
|
|
492 };
|
|
|
493
|
|
|
494 q.ToList();
|
|
|
495 }
|
|
|
496 }
|
|
|
497
|
|
|
498 [Test]
|
|
|
499 public void Contains12([IncludeDataContexts("Northwind")] string context)
|
|
|
500 {
|
|
|
501 using (var db = new NorthwindDB())
|
|
|
502 {
|
|
|
503 var q =
|
|
|
504 from e in db.EmployeeTerritory
|
|
|
505 group e by e.Employee into g
|
|
|
506 where g.Key.EmployeeTerritories.Count() > 1 && g.Count() > 2
|
|
|
507 select new
|
|
|
508 {
|
|
|
509 g.Key.LastName,
|
|
|
510 //cnt = g.Where(t => t.Employee.FirstName.Contains("an")).Count(),
|
|
|
511 };
|
|
|
512
|
|
|
513 q.ToList();
|
|
|
514 }
|
|
|
515 }
|
|
|
516
|
|
|
517 [Test]
|
|
|
518 public void Contains13([IncludeDataContexts("Northwind")] string context)
|
|
|
519 {
|
|
|
520 using (var db = new NorthwindDB())
|
|
|
521 {
|
|
|
522 var arr = new[]
|
|
|
523 {
|
|
|
524 new Northwind.EmployeeTerritory { EmployeeID = 1, TerritoryID = "01581" },
|
|
|
525 new Northwind.EmployeeTerritory { EmployeeID = 1, TerritoryID = "02116" },
|
|
|
526 new Northwind.EmployeeTerritory { EmployeeID = 1, TerritoryID = "31406" }
|
|
|
527 };
|
|
|
528
|
|
|
529 var q =
|
|
|
530 from e in db.EmployeeTerritory
|
|
|
531 group e by e.EmployeeID into g
|
|
|
532 select new
|
|
|
533 {
|
|
|
534 g.Key,
|
|
|
535 cnt = g.Count(t => arr.Contains(t)),
|
|
|
536 };
|
|
|
537
|
|
|
538 q.ToList();
|
|
|
539 }
|
|
|
540 }
|
|
|
541
|
|
|
542 void TestContains(ITestDataContext db, Parent1 parent)
|
|
|
543 {
|
|
|
544 Assert.AreEqual(
|
|
|
545 Parent1.Where(p => p.ParentID == 1).Contains(parent),
|
|
|
546 db.Parent1.Where(p => p.ParentID == 1).Contains(parent));
|
|
|
547 }
|
|
|
548
|
|
|
549 [Test]
|
|
|
550 public void Contains14()
|
|
|
551 {
|
|
|
552 var ps = Parent1.OrderBy(p => p.ParentID).Take(2).ToArray();
|
|
|
553
|
|
|
554 ForEachProvider(db => Array.ForEach(ps, p => TestContains(db, p)));
|
|
|
555 }
|
|
|
556
|
|
|
557 static void GetData(ITestDataContext db, IEnumerable<int?> d)
|
|
|
558 {
|
|
|
559 var r1 = db.GrandChild
|
|
|
560 .Where(x => d.Contains(x.ParentID))
|
|
|
561 .ToList();
|
|
|
562
|
|
|
563 foreach (var g in r1)
|
|
|
564 {
|
|
|
565 Assert.AreEqual(d.First().Value, g.ParentID);
|
|
|
566 }
|
|
|
567 }
|
|
|
568
|
|
|
569 [Test]
|
|
|
570 public void TestForGroupBy()
|
|
|
571 {
|
|
|
572 ForEachProvider(db =>
|
|
|
573 {
|
|
|
574 GetData(db, new List<int?> { 2 });
|
|
|
575 GetData(db, new List<int?> { 3 });
|
|
|
576 });
|
|
|
577 }
|
|
|
578 }
|
|
|
579 }
|
