|
0
|
1 using System;
|
|
|
2 using System.Collections.Generic;
|
|
|
3 using System.Linq;
|
|
|
4 using System.Linq.Expressions;
|
|
|
5 using BLToolkit.Data.DataProvider;
|
|
|
6 using BLToolkit.Data.Linq;
|
|
|
7 using BLToolkit.Data.Sql.SqlProvider;
|
|
|
8 using BLToolkit.DataAccess;
|
|
|
9 using BLToolkit.Mapping;
|
|
|
10 using Data.Linq.Model;
|
|
|
11 using NUnit.Framework;
|
|
|
12
|
|
|
13 namespace Data.Linq
|
|
|
14 {
|
|
|
15 [TestFixture]
|
|
|
16 public partial class IdlTest : TestBase
|
|
|
17 {
|
|
|
18 class IdlProvidersAttribute : IncludeDataContextsAttribute
|
|
|
19 {
|
|
|
20 public IdlProvidersAttribute()
|
|
|
21 : base(ProviderName.SQLite, ProviderName.MySql, "Sql2005", "Sql2008")
|
|
|
22 {
|
|
|
23 }
|
|
|
24 }
|
|
|
25
|
|
|
26 private void ForProvider(string providerName, Action<ITestDataContext> func)
|
|
|
27 {
|
|
|
28 ForEachProvider(Providers.Select(p => p.Name).Except(new[] { providerName }).ToArray(), func);
|
|
|
29 }
|
|
|
30
|
|
|
31 private void ForMySqlProvider(Action<ITestDataContext> func)
|
|
|
32 {
|
|
|
33 ForEachProvider(Providers.Select(p => p.Name).Except(new[] { ProviderName.MySql }).ToArray(), func);
|
|
|
34 }
|
|
|
35
|
|
|
36 #region PersonWithId
|
|
|
37
|
|
|
38 public interface IHasID
|
|
|
39 {
|
|
|
40 int ID { get; set; }
|
|
|
41 }
|
|
|
42
|
|
|
43 [TableName(Name = "Person")]
|
|
|
44 public class PersonWithId : IHasID
|
|
|
45 {
|
|
|
46 public PersonWithId()
|
|
|
47 {
|
|
|
48 }
|
|
|
49
|
|
|
50 public PersonWithId(int id)
|
|
|
51 {
|
|
|
52 ID = id;
|
|
|
53 }
|
|
|
54
|
|
|
55 public PersonWithId(int id, string firstName)
|
|
|
56 {
|
|
|
57 ID = id;
|
|
|
58 FirstName = firstName;
|
|
|
59 }
|
|
|
60
|
|
|
61 [Identity, PrimaryKey]
|
|
|
62 [SequenceName("Firebird", "PersonID")]
|
|
|
63 [MapField("PersonID")]
|
|
|
64 public int ID { get; set; }
|
|
|
65 public string FirstName { get; set; }
|
|
|
66 public string LastName;
|
|
|
67 [Nullable] public string MiddleName;
|
|
|
68 public Gender Gender;
|
|
|
69
|
|
|
70 [MapIgnore]
|
|
|
71 public string Name
|
|
|
72 {
|
|
|
73 get { return FirstName + " " + LastName; }
|
|
|
74 }
|
|
|
75
|
|
|
76 public override bool Equals(object obj)
|
|
|
77 {
|
|
|
78 return Equals(obj as PersonWithId);
|
|
|
79 }
|
|
|
80
|
|
|
81 public bool Equals(PersonWithId other)
|
|
|
82 {
|
|
|
83 if (ReferenceEquals(null, other)) return false;
|
|
|
84 if (ReferenceEquals(this, other)) return true;
|
|
|
85 return
|
|
|
86 other.ID == ID &&
|
|
|
87 Equals(other.LastName, LastName) &&
|
|
|
88 Equals(other.MiddleName, MiddleName) &&
|
|
|
89 other.Gender == Gender &&
|
|
|
90 Equals(other.FirstName, FirstName);
|
|
|
91 }
|
|
|
92
|
|
|
93 public override int GetHashCode()
|
|
|
94 {
|
|
|
95 unchecked
|
|
|
96 {
|
|
|
97 var result = ID;
|
|
|
98 result = (result * 397) ^ (LastName != null ? LastName.GetHashCode() : 0);
|
|
|
99 result = (result * 397) ^ (MiddleName != null ? MiddleName.GetHashCode() : 0);
|
|
|
100 result = (result * 397) ^ Gender.GetHashCode();
|
|
|
101 result = (result * 397) ^ (FirstName != null ? FirstName.GetHashCode() : 0);
|
|
|
102 return result;
|
|
|
103 }
|
|
|
104 }
|
|
|
105 }
|
|
|
106
|
|
|
107 #endregion
|
|
|
108
|
|
|
109 #region ObjectId
|
|
|
110
|
|
|
111 public interface IHasObjectId2
|
|
|
112 {
|
|
|
113 ObjectId Id { get; set; }
|
|
|
114 }
|
|
|
115
|
|
|
116 public interface IHasObjectId1
|
|
|
117 {
|
|
|
118 ObjectId Id { get; set; }
|
|
|
119 }
|
|
|
120
|
|
|
121 public struct ObjectId
|
|
|
122 {
|
|
|
123 public ObjectId(int value)
|
|
|
124 {
|
|
|
125 m_value = value;
|
|
|
126 }
|
|
|
127
|
|
|
128 private int m_value;
|
|
|
129
|
|
|
130 public int Value
|
|
|
131 {
|
|
|
132 get { return m_value; }
|
|
|
133 set { m_value = value; }
|
|
|
134 }
|
|
|
135
|
|
|
136 public static implicit operator int(ObjectId val)
|
|
|
137 {
|
|
|
138 return val.m_value;
|
|
|
139 }
|
|
|
140 }
|
|
|
141
|
|
|
142 public class WithObjectIdBase : IHasObjectId1
|
|
|
143 {
|
|
|
144 public ObjectId Id { get; set; }
|
|
|
145 }
|
|
|
146
|
|
|
147 public class PersonWithObjectId : WithObjectIdBase, IHasObjectId2
|
|
|
148 {
|
|
|
149 public string FistName { get; set; }
|
|
|
150 }
|
|
|
151
|
|
|
152 public struct NullableObjectId
|
|
|
153 {
|
|
|
154 public NullableObjectId(int? value)
|
|
|
155 {
|
|
|
156 m_value = value;
|
|
|
157 }
|
|
|
158
|
|
|
159 private int? m_value;
|
|
|
160
|
|
|
161 public int? Value
|
|
|
162 {
|
|
|
163 get { return m_value; }
|
|
|
164 set { m_value = value; }
|
|
|
165 }
|
|
|
166
|
|
|
167 public static implicit operator int?(NullableObjectId val)
|
|
|
168 {
|
|
|
169 return val.m_value;
|
|
|
170 }
|
|
|
171 }
|
|
|
172
|
|
|
173 #endregion
|
|
|
174
|
|
|
175 [Test]
|
|
|
176 public void TestComplexExpression([IdlProviders] string providerName)
|
|
|
177 {
|
|
|
178 // failed with BLToolkit.Data.Linq.LinqException : 'new StationObjectId() {Value = ConvertNullable(child.ChildID)}'
|
|
|
179 // cannot be converted to SQL.
|
|
|
180 ForProvider(
|
|
|
181 providerName,
|
|
|
182 db =>
|
|
|
183 {
|
|
|
184 var source = from child in db.GrandChild
|
|
|
185 select
|
|
|
186 new
|
|
|
187 {
|
|
|
188 NullableId = new NullableObjectId { Value = child.ChildID }
|
|
|
189 };
|
|
|
190
|
|
|
191 var query = from e in source where e.NullableId == 1 select e;
|
|
|
192
|
|
|
193 var result = query.ToArray();
|
|
|
194 Assert.That(result, Is.Not.Null);
|
|
|
195 });
|
|
|
196 }
|
|
|
197
|
|
|
198 [Test]
|
|
|
199 public void TestJoin([IdlProviders] string providerName)
|
|
|
200 {
|
|
|
201 // failed with System.ArgumentOutOfRangeException : Index was out of range. Must be non-negative and less than
|
|
|
202 // the size of the collection.
|
|
|
203 // Parameter name: index
|
|
|
204 ForProvider(
|
|
|
205 providerName,
|
|
|
206 db =>
|
|
|
207 {
|
|
|
208 var source = from p1 in db.Person
|
|
|
209 join p2 in db.Person on p1.ID equals p2.ID
|
|
|
210 select
|
|
|
211 new { ID1 = new ObjectId { Value = p1.ID }, FirstName2 = p2.FirstName, };
|
|
|
212
|
|
|
213 var query = from p1 in source select p1.ID1.Value;
|
|
|
214
|
|
|
215 var result = query.ToArray();
|
|
|
216 Assert.That(result, Is.Not.Null);
|
|
|
217 });
|
|
|
218 }
|
|
|
219
|
|
|
220 [Test]
|
|
|
221 public void TestNullableExpression([IdlProviders] string providerName)
|
|
|
222 {
|
|
|
223 // failed with System.NullReferenceException : Object reference not set to an instance of an object.
|
|
|
224 ForProvider(
|
|
|
225 providerName,
|
|
|
226 db =>
|
|
|
227 {
|
|
|
228 var source = from obj in db.Person select new { Id = obj.ID, };
|
|
|
229
|
|
|
230 // fails for bool?, double?, int32?, int64?, string
|
|
|
231 // works for byte?, int16?, DateTime?
|
|
|
232 double? @p1 = null;
|
|
|
233
|
|
|
234 var r = from c in source where @p1 != null select c;
|
|
|
235
|
|
|
236 Assert.That(r.ToArray(), Is.Not.Null);
|
|
|
237 });
|
|
|
238 }
|
|
|
239
|
|
|
240 [Test]
|
|
|
241 public void TestLookupWithInterfaceProperty([IdlProviders] string providerName)
|
|
|
242 {
|
|
|
243 ForProvider(
|
|
|
244 providerName,
|
|
|
245 db =>
|
|
|
246 {
|
|
|
247 var r = GetById<PersonWithId>(db, 1).SingleOrDefault();
|
|
|
248 Assert.That(r, Is.Not.Null);
|
|
|
249 });
|
|
|
250 }
|
|
|
251
|
|
|
252 #region ObjectExt
|
|
|
253
|
|
|
254 public abstract class ObjectWithId
|
|
|
255 {
|
|
|
256 public ObjectId Id;
|
|
|
257 }
|
|
|
258
|
|
|
259 public class ParentEx : ObjectWithId
|
|
|
260 {
|
|
|
261 public int? Value1;
|
|
|
262 }
|
|
|
263
|
|
|
264 #endregion
|
|
|
265
|
|
|
266 [Test]
|
|
|
267 public void TestForObjectExt([IdlProviders] string providerName)
|
|
|
268 {
|
|
|
269 ForProvider(
|
|
|
270 providerName,
|
|
|
271 db =>
|
|
|
272 {
|
|
|
273 var r = from p in db.Parent
|
|
|
274 select new ParentEx
|
|
|
275 {
|
|
|
276 Id = new ObjectId { Value = p.ParentID },
|
|
|
277 Value1 = p.Value1,
|
|
|
278 };
|
|
|
279 Assert.That(r.ToArray(), Is.Not.Null);
|
|
|
280 });
|
|
|
281 }
|
|
|
282
|
|
|
283 private void getData(ITestDataContext db, IEnumerable<int?> d, IEnumerable<int?> compareWith)
|
|
|
284 {
|
|
|
285 var r1 = db.GrandChild
|
|
|
286 .Where(x => d.Contains(x.ParentID))
|
|
|
287 .GroupBy(x => x.ChildID, x => x.GrandChildID)
|
|
|
288 .ToList();
|
|
|
289 foreach (var group in r1)
|
|
|
290 {
|
|
|
291 Assert.That(compareWith.Any(x => group.Contains(x)), Is.True);
|
|
|
292 }
|
|
|
293 }
|
|
|
294
|
|
|
295 [Test]
|
|
|
296 public void TestForGroupBy([IdlProviders] string providerName)
|
|
|
297 {
|
|
|
298 ForProvider(
|
|
|
299 providerName,
|
|
|
300 db =>
|
|
|
301 {
|
|
|
302 /* no error in first call */
|
|
|
303 getData(db, new List<int?> { 2 }, new List<int?> { 211, 212, 221, 222 });
|
|
|
304
|
|
|
305 /* error in second and more calls */
|
|
|
306 /*
|
|
|
307 * GROUP BY select clause is correct
|
|
|
308 SELECT x.ChildID FROM GrandChild x WHERE x.ParentID IN (3) GROUP BY x.ChildID
|
|
|
309
|
|
|
310 * But next SELECT clause contains "x.ParentID IN (2)" instead "x.ParentID IN (3)"
|
|
|
311 -- DECLARE ?p1 Int32
|
|
|
312 -- SET ?p1 = 31
|
|
|
313 SELECT x.GrandChildID FROM GrandChild x WHERE x.ParentID IN (2) AND x.ChildID = ?p1
|
|
|
314 */
|
|
|
315 getData(db, new List<int?> { 3 }, new List<int?> { 311, 312, 313, 321, 333 });
|
|
|
316 });
|
|
|
317 }
|
|
|
318
|
|
|
319 [Test]
|
|
|
320 public void TestLinqMax([IdlProviders] string providerName)
|
|
|
321 {
|
|
|
322 ForProvider(
|
|
|
323 providerName,
|
|
|
324 db =>
|
|
|
325 {
|
|
|
326 Assert.That(db.Patient.Where(x => x.PersonID < 0).Select(x => (int?)x.PersonID).Max(), Is.Null);
|
|
|
327 Assert.That(db.Patient.Where(x => x.PersonID < 0).Max(x => (int?)x.PersonID), Is.Null);
|
|
|
328 Assert.Catch<InvalidOperationException>(
|
|
|
329 () => db.Patient.Where(x => x.PersonID < 0).Select(x => x.PersonID).Max());
|
|
|
330 Assert.Catch<InvalidOperationException>(
|
|
|
331 () => db.Patient.Where(x => x.PersonID < 0).Max(x => x.PersonID));
|
|
|
332 });
|
|
|
333 }
|
|
|
334
|
|
|
335 [Test]
|
|
|
336 public void TestConvertFunction([IdlProviders] string providerName)
|
|
|
337 {
|
|
|
338 ForProvider(
|
|
|
339 providerName,
|
|
|
340 db =>
|
|
|
341 {
|
|
|
342 var ds = new IdlPatientSource(db);
|
|
|
343 var r1 = ds.Patients().ToList();
|
|
|
344 var r2 = ds.Persons().ToList();
|
|
|
345
|
|
|
346 Assert.That(r1, Is.Not.Empty);
|
|
|
347 Assert.That(r2, Is.Not.Empty);
|
|
|
348
|
|
|
349 var r3 = ds.Patients().ToIdlPatientEx(ds);
|
|
|
350 var r4 = r3.ToList();
|
|
|
351 Assert.That(r4, Is.Not.Empty);
|
|
|
352 });
|
|
|
353 }
|
|
|
354
|
|
|
355 [Test]
|
|
|
356 public void TestJoinOrder([IdlProviders] string providerName)
|
|
|
357 {
|
|
|
358 ForProvider(
|
|
|
359 providerName,
|
|
|
360 db =>
|
|
|
361 {
|
|
|
362 var source = new IdlPatientSource(db);
|
|
|
363
|
|
|
364 // Success when use result from second JOIN
|
|
|
365 var query1 = from p1 in source.GrandChilds()
|
|
|
366 join p2 in source.Persons() on p1.ParentID equals p2.Id
|
|
|
367 join p3 in source.Persons() on p1.ChildID equals p3.Id
|
|
|
368 select
|
|
|
369 new
|
|
|
370 {
|
|
|
371 p1.ChildID,
|
|
|
372 p1.ParentID,
|
|
|
373 //Parent = p2,
|
|
|
374 Child = p3,
|
|
|
375 };
|
|
|
376 var data1 = query1.ToList();
|
|
|
377
|
|
|
378 // Fail when use result from first JOIN
|
|
|
379 var query2 = from p1 in source.GrandChilds()
|
|
|
380 join p2 in source.Persons() on p1.ParentID equals p2.Id
|
|
|
381 join p3 in source.Persons() on p1.ChildID equals p3.Id
|
|
|
382 select
|
|
|
383 new
|
|
|
384 {
|
|
|
385 p1.ChildID,
|
|
|
386 p1.ParentID,
|
|
|
387 Parent = p2,
|
|
|
388 //Child = p3,
|
|
|
389 };
|
|
|
390 var data2 = query2.ToList();
|
|
|
391 });
|
|
|
392 }
|
|
|
393
|
|
|
394 [Test]
|
|
|
395 public void TestDistinctWithGroupBy([IdlProviders] string providerName)
|
|
|
396 {
|
|
|
397 ForProvider(
|
|
|
398 providerName,
|
|
|
399 db =>
|
|
|
400 {
|
|
|
401 const int parentId = 10000;
|
|
|
402 db.Parent.Insert(() => new Parent { ParentID = parentId, Value1 = 1 });
|
|
|
403 db.Parent.Insert(() => new Parent { ParentID = parentId, Value1 = 1 });
|
|
|
404
|
|
|
405 try
|
|
|
406 {
|
|
|
407 var source = db.Parent.ToList();
|
|
|
408
|
|
|
409 // Success when query is executed in memory
|
|
|
410 TestDistinctWithGroupBy(source.AsQueryable());
|
|
|
411
|
|
|
412 // Failed when query is executed on sql server
|
|
|
413 TestDistinctWithGroupBy(db.Parent);
|
|
|
414 }
|
|
|
415 finally
|
|
|
416 {
|
|
|
417 db.Parent.Delete(x => x.ParentID == parentId);
|
|
|
418 }
|
|
|
419 });
|
|
|
420 }
|
|
|
421
|
|
|
422 private static void TestDistinctWithGroupBy(IQueryable<Parent> source)
|
|
|
423 {
|
|
|
424 const int score = 4;
|
|
|
425 var q = source.Select(x => new { Key = x.Value1, MatchScore = score })
|
|
|
426 .Distinct();
|
|
|
427 var qq = q.GroupBy(
|
|
|
428 x => x.Key,
|
|
|
429 (key, x) => new { Id = key, MatchScore = x.Sum(y => y.MatchScore) })
|
|
|
430 .Select(x => new { x.Id, x.MatchScore });
|
|
|
431
|
|
|
432 var result = qq.ToList();
|
|
|
433 Assert.That(result.Select(x => x.MatchScore), Is.All.EqualTo(score));
|
|
|
434 }
|
|
|
435
|
|
|
436 private static IQueryable<T> GetById<T>(ITestDataContext db, int id) where T : class, IHasID
|
|
|
437 {
|
|
|
438 return db.GetTable<T>().Where(obj => obj.ID == id);
|
|
|
439 }
|
|
|
440
|
|
|
441 [Test]
|
|
|
442 public void ImplicitCastTest([IdlProviders] string providerName)
|
|
|
443 {
|
|
|
444 ForProvider(
|
|
|
445 providerName,
|
|
|
446 db =>
|
|
|
447 {
|
|
|
448 var people =
|
|
|
449 from p in db.Person
|
|
|
450 select new IdlPerson
|
|
|
451 {
|
|
|
452 Id = new ObjectId { Value = p.ID },
|
|
|
453 Name = p.FirstName
|
|
|
454 };
|
|
|
455
|
|
|
456 var sql1 = (from p in people where p.Id == 1 select p).ToString();
|
|
|
457 var sql2 = (from p in people where p.Id.Value == 1 select p).ToString();
|
|
|
458
|
|
|
459 Assert.That(sql1, Is.EqualTo(sql2));
|
|
|
460 });
|
|
|
461 }
|
|
|
462
|
|
|
463 [Test]
|
|
|
464 public void ListvsArrayTest([IdlProviders] string providerName)
|
|
|
465 {
|
|
|
466 ForProvider(
|
|
|
467 providerName,
|
|
|
468 db =>
|
|
|
469 {
|
|
|
470 var st = "John";
|
|
|
471
|
|
|
472 //SQL - x.FirstName IN ('John')
|
|
|
473 var queryList = from x in db.Person
|
|
|
474 where new List<string> { st }.Contains(x.FirstName)
|
|
|
475 select x.ID;
|
|
|
476
|
|
|
477 //SQL - x.FirstName IN ('J', 'o', 'h', 'n')
|
|
|
478 var queryArray = from x in db.Person
|
|
|
479 where new[] { st }.Contains(x.FirstName)
|
|
|
480 select x.ID;
|
|
|
481
|
|
|
482 Assert.That(queryList.ToList(), Is.EqualTo(queryArray.ToList()));
|
|
|
483 });
|
|
|
484 }
|
|
|
485
|
|
|
486 [Test]
|
|
|
487 public void ConcatJoinOrderByTest([IdlProviders] string providerName)
|
|
|
488 {
|
|
|
489 ForProvider(
|
|
|
490 providerName,
|
|
|
491 db =>
|
|
|
492 {
|
|
|
493 var query = from y in
|
|
|
494 ((from pat in db.Patient
|
|
|
495 where pat.Diagnosis == "a"
|
|
|
496 select pat)
|
|
|
497 .Concat
|
|
|
498 (
|
|
|
499 from pat in db.Patient
|
|
|
500 where pat.Diagnosis == "b"
|
|
|
501 select pat))
|
|
|
502 join person in db.Person on y.PersonID equals person.ID
|
|
|
503 orderby person.ID
|
|
|
504 select new { Id = person.ID, Id2 = y.PersonID };
|
|
|
505
|
|
|
506 Assert.That(query.ToList(), Is.Not.Null);
|
|
|
507 });
|
|
|
508 }
|
|
|
509
|
|
|
510 [Test]
|
|
|
511 public void TestIsContainedInArrayOfEnumValues([IdlProviders] string providerName)
|
|
|
512 {
|
|
|
513 var types2 = new[] { TypeValue.Value2, TypeValue.Value3, TypeValue.Value4 };
|
|
|
514
|
|
|
515 ForProvider(
|
|
|
516 providerName,
|
|
|
517 db =>
|
|
|
518 {
|
|
|
519 var result = (from x in db.Parent4 where types2.Contains(x.Value1) select x)
|
|
|
520 .ToList();
|
|
|
521
|
|
|
522 Assert.That(result, Is.Not.Null);
|
|
|
523 });
|
|
|
524 }
|
|
|
525
|
|
|
526 [Test]
|
|
|
527 public void TestQueryWithInterface([IdlProviders] string providerName)
|
|
|
528 {
|
|
|
529 ForProvider(
|
|
|
530 providerName,
|
|
|
531 db =>
|
|
|
532 {
|
|
|
533 var persons =
|
|
|
534 from x in db.Person
|
|
|
535 select new PersonWithObjectId
|
|
|
536 {
|
|
|
537 Id = new ObjectId { Value = x.ID },
|
|
|
538 FistName = x.FirstName,
|
|
|
539 };
|
|
|
540
|
|
|
541 // this works
|
|
|
542 var r1 = FilterSourceByIdDefinedInBaseClass(persons, 5).ToArray();
|
|
|
543 Assert.That(r1, Is.Not.Null);
|
|
|
544
|
|
|
545 // and this works
|
|
|
546 var r2 = FilterSourceByIdDefinedInInterface1(persons, 5).ToArray();
|
|
|
547 Assert.That(r2, Is.Not.Null);
|
|
|
548
|
|
|
549 // but this fails
|
|
|
550 var r3 = FilterSourceByIdDefinedInInterface2(persons, 5).ToArray();
|
|
|
551 Assert.That(r3, Is.Not.Null);
|
|
|
552 });
|
|
|
553 }
|
|
|
554
|
|
|
555 [Test]
|
|
|
556 public void TestBugCountWithOrderBy([IdlProviders] string providerName)
|
|
|
557 {
|
|
|
558 ForProvider(
|
|
|
559 providerName,
|
|
|
560 db =>
|
|
|
561 {
|
|
|
562 var q1 = db.Person.OrderBy(x => x.ID);
|
|
|
563
|
|
|
564 var q2 = from p in q1
|
|
|
565 join p2 in db.Person on p.ID equals p2.ID
|
|
|
566 select p2;
|
|
|
567
|
|
|
568 Assert.DoesNotThrow(() => q2.Max(x => x.ID));
|
|
|
569 Assert.DoesNotThrow(() => q2.Count());
|
|
|
570 });
|
|
|
571 }
|
|
|
572
|
|
|
573 [Test]
|
|
|
574 public void TestUpdateWithTargetByAssociationProperty([IdlProviders] string providerName)
|
|
|
575 {
|
|
|
576 TestUpdateByAssociationProperty(providerName, true);
|
|
|
577 }
|
|
|
578
|
|
|
579 [Test]
|
|
|
580 public void TestSetUpdateWithoutTargetByAssociationProperty([IdlProviders] string providerName)
|
|
|
581 {
|
|
|
582 TestUpdateByAssociationProperty(providerName, false);
|
|
|
583 }
|
|
|
584
|
|
|
585 private void TestUpdateByAssociationProperty(string providerName, bool useUpdateWithTarget)
|
|
|
586 {
|
|
|
587 ForProvider(
|
|
|
588 providerName,
|
|
|
589 db =>
|
|
|
590 {
|
|
|
591 const int childId = 10000;
|
|
|
592 const int parentId = 20000;
|
|
|
593
|
|
|
594 try
|
|
|
595 {
|
|
|
596 db.Parent.Insert(() => new Parent { ParentID = parentId });
|
|
|
597 db.Child.Insert(() => new Child { ChildID = childId, ParentID = parentId });
|
|
|
598
|
|
|
599 var parents = from child in db.Child
|
|
|
600 where child.ChildID == childId
|
|
|
601 select child.Parent;
|
|
|
602
|
|
|
603 if (useUpdateWithTarget)
|
|
|
604 {
|
|
|
605 // this failed for MySql and SQLite but works with MS SQL
|
|
|
606 Assert.DoesNotThrow(() => parents.Update(db.Parent, x => new Parent { Value1 = 5 }));
|
|
|
607 }
|
|
|
608 else
|
|
|
609 {
|
|
|
610 // this works with MySql but failed for SQLite and MS SQL
|
|
|
611 Assert.DoesNotThrow(() => parents.Set(x => x.Value1, 5).Update());
|
|
|
612 }
|
|
|
613 }
|
|
|
614 finally
|
|
|
615 {
|
|
|
616 db.Child.Delete(x => x.ChildID == childId);
|
|
|
617 db.Parent.Delete(x => x.ParentID == parentId);
|
|
|
618 }
|
|
|
619 });
|
|
|
620 }
|
|
|
621
|
|
|
622
|
|
|
623 private IQueryable<T> FilterSourceByIdDefinedInBaseClass<T>(IQueryable<T> source, int id)
|
|
|
624 where T : WithObjectIdBase
|
|
|
625 {
|
|
|
626 return from x in source where x.Id == id select x;
|
|
|
627 }
|
|
|
628
|
|
|
629 private IQueryable<T> FilterSourceByIdDefinedInInterface1<T>(IQueryable<T> source, int id)
|
|
|
630 where T : IHasObjectId1
|
|
|
631 {
|
|
|
632 return from x in source where x.Id == id select x;
|
|
|
633 }
|
|
|
634
|
|
|
635 private IQueryable<T> FilterSourceByIdDefinedInInterface2<T>(IQueryable<T> source, int id)
|
|
|
636 where T : IHasObjectId2
|
|
|
637 {
|
|
|
638 return from x in source where x.Id == id select x;
|
|
|
639 }
|
|
|
640
|
|
|
641 [Test]
|
|
|
642 public void TestComparePropertyOfEnumTypeToVaribleInSubquery([IdlProviders] string providerName)
|
|
|
643 {
|
|
|
644 ForProvider(
|
|
|
645 providerName,
|
|
|
646 db =>
|
|
|
647 {
|
|
|
648 var gender = Gender.Other;
|
|
|
649 var q = from x in db.Patient
|
|
|
650 join y in db.Person.Where(x => x.Gender == gender) on x.PersonID equals y.ID
|
|
|
651 select x;
|
|
|
652
|
|
|
653 var r = q.ToList();
|
|
|
654 Assert.That(r, Is.Not.Null);
|
|
|
655 });
|
|
|
656 }
|
|
|
657
|
|
|
658 [Test]
|
|
|
659 public void ConcatOrderByTest([IdlProviders] string providerName)
|
|
|
660 {
|
|
|
661 ForProvider(
|
|
|
662 providerName,
|
|
|
663 db =>
|
|
|
664 {
|
|
|
665 var q = from p in db.Person
|
|
|
666 where p.ID < 0
|
|
|
667 select new { Rank = 0, FirstName = (string)null, LastName = (string)null };
|
|
|
668 var q2 =
|
|
|
669 q.Concat(
|
|
|
670 from p in db.Person
|
|
|
671 select new { Rank = p.ID, p.FirstName, p.LastName });
|
|
|
672
|
|
|
673 var resultquery = (from x in q2 orderby x.Rank, x.FirstName, x.LastName select x).ToString();
|
|
|
674
|
|
|
675 var rqr = resultquery.LastIndexOf(
|
|
|
676 "ORDER BY", System.StringComparison.InvariantCultureIgnoreCase);
|
|
|
677 var rqp =
|
|
|
678 (resultquery.Substring(rqr + "ORDER BY".Length).Split(',')).Select(p => p.Trim()).ToArray();
|
|
|
679
|
|
|
680 Assert.That(rqp.Count(), Is.EqualTo(3));
|
|
|
681 });
|
|
|
682 }
|
|
|
683
|
|
|
684 [Test]
|
|
|
685 public void TestContainsForNullableDateTimeWithOnlyNullValue1([IdlProviders] string providerName)
|
|
|
686 {
|
|
|
687 ForProvider(
|
|
|
688 providerName,
|
|
|
689 db =>
|
|
|
690 {
|
|
|
691 var dates = new DateTime?[] { null };
|
|
|
692
|
|
|
693 // Ensures that the query works properly in memory
|
|
|
694 // ReSharper disable RemoveToList.2
|
|
|
695 var resultCount = db.Types2.ToList().Count(x => dates.Contains(x.DateTimeValue2));
|
|
|
696 // ReSharper restore RemoveToList.2
|
|
|
697 Assert.That(resultCount, Is.GreaterThan(0));
|
|
|
698
|
|
|
699 var result = db.Types2.Count(x => dates.Contains(x.DateTimeValue2));
|
|
|
700 Assert.That(result, Is.EqualTo(resultCount));
|
|
|
701 });
|
|
|
702 }
|
|
|
703
|
|
|
704 [Test]
|
|
|
705 public void TestContainsForNullableDateTimeWithOnlyNullValue2([IdlProviders] string providerName)
|
|
|
706 {
|
|
|
707 ForProvider(
|
|
|
708 providerName,
|
|
|
709 db =>
|
|
|
710 {
|
|
|
711 // Ensures that the query works properly in memory
|
|
|
712 // ReSharper disable RemoveToList.2
|
|
|
713 var resultCount = db.Types2.ToList().Count(x => new DateTime?[] { null }.Contains(x.DateTimeValue2));
|
|
|
714 // ReSharper restore RemoveToList.2
|
|
|
715 Assert.That(resultCount, Is.GreaterThan(0));
|
|
|
716
|
|
|
717 var result = db.Types2.Count(x => new DateTime?[] { null }.Contains(x.DateTimeValue2));
|
|
|
718 Assert.That(result, Is.EqualTo(resultCount));
|
|
|
719 });
|
|
|
720 }
|
|
|
721
|
|
|
722 [Test]
|
|
|
723 public void TestContainsForNullableDateTimeWithNullAndNotNullValues1([IdlProviders] string providerName)
|
|
|
724 {
|
|
|
725 ForProvider(
|
|
|
726 providerName,
|
|
|
727 db =>
|
|
|
728 {
|
|
|
729 var date = new DateTime(2009, 9, 24, 9, 19, 29, 90);
|
|
|
730 var dates = new DateTime?[] { null, date };
|
|
|
731
|
|
|
732 // Ensures that the query works properly in memory
|
|
|
733 // ReSharper disable RemoveToList.2
|
|
|
734 var resultCount = db.Types2.ToList().Count(x => dates.Contains(x.DateTimeValue2));
|
|
|
735 // ReSharper restore RemoveToList.2
|
|
|
736 Assert.That(resultCount, Is.GreaterThan(0));
|
|
|
737
|
|
|
738 var result = db.Types2.Count(x => dates.Contains(x.DateTimeValue2));
|
|
|
739 Assert.That(result, Is.EqualTo(resultCount));
|
|
|
740 });
|
|
|
741 }
|
|
|
742
|
|
|
743 [Test]
|
|
|
744 public void TestContainsForNullableDateTimeWithNullAndNotNullValues2([IdlProviders] string providerName)
|
|
|
745 {
|
|
|
746 ForProvider(
|
|
|
747 providerName,
|
|
|
748 db =>
|
|
|
749 {
|
|
|
750 // Ensures that the query works properly in memory
|
|
|
751 // ReSharper disable RemoveToList.2
|
|
|
752 var resultCount = db.Types2.ToList().Count(x => new DateTime?[] { null, new DateTime(2009, 9, 24, 9, 19, 29, 90) }.Contains(x.DateTimeValue2));
|
|
|
753 // ReSharper restore RemoveToList.2
|
|
|
754 Assert.That(resultCount, Is.GreaterThan(0));
|
|
|
755
|
|
|
756 var result = db.Types2.Count(x => new DateTime?[] { null, new DateTime(2009, 9, 24, 9, 19, 29, 90) }.Contains(x.DateTimeValue2));
|
|
|
757 Assert.That(result, Is.EqualTo(resultCount));
|
|
|
758 });
|
|
|
759 }
|
|
|
760
|
|
|
761 #region GenericQuery classes
|
|
|
762
|
|
|
763 public abstract partial class GenericQueryBase
|
|
|
764 {
|
|
|
765 private readonly IdlPatientSource m_ds;
|
|
|
766
|
|
|
767 protected GenericQueryBase(ITestDataContext ds)
|
|
|
768 {
|
|
|
769 m_ds = new IdlPatientSource(ds);
|
|
|
770 }
|
|
|
771
|
|
|
772 #region Object sources
|
|
|
773
|
|
|
774 protected IQueryable<IdlPerson> AllPersons
|
|
|
775 {
|
|
|
776 get { return m_ds.Persons(); }
|
|
|
777 }
|
|
|
778
|
|
|
779 protected IQueryable<IdlPatient> AllPatients
|
|
|
780 {
|
|
|
781 get { return m_ds.Patients(); }
|
|
|
782 }
|
|
|
783
|
|
|
784 protected IQueryable<IdlGrandChild> AllGrandChilds
|
|
|
785 {
|
|
|
786 get { return m_ds.GrandChilds(); }
|
|
|
787 }
|
|
|
788
|
|
|
789 #endregion
|
|
|
790
|
|
|
791 public abstract IEnumerable<object> Query();
|
|
|
792 }
|
|
|
793
|
|
|
794 public class GenericConcatQuery : GenericQueryBase
|
|
|
795 {
|
|
|
796 private String @p1;
|
|
|
797 private Int32 @p2;
|
|
|
798
|
|
|
799 public GenericConcatQuery(ITestDataContext ds, object[] args)
|
|
|
800 : base(ds)
|
|
|
801 {
|
|
|
802 @p1 = (String)args[0];
|
|
|
803 @p2 = (Int32)args[1];
|
|
|
804 }
|
|
|
805
|
|
|
806 public override IEnumerable<object> Query()
|
|
|
807 {
|
|
|
808 return (from y in AllPersons
|
|
|
809 select y.Name)
|
|
|
810 .Concat(
|
|
|
811 from x in AllPersons
|
|
|
812 from z in AllPatients
|
|
|
813 where (x.Name == @p1 || z.Id == new ObjectId { Value = @p2 })
|
|
|
814 select x.Name
|
|
|
815 );
|
|
|
816 }
|
|
|
817 }
|
|
|
818
|
|
|
819 public class GenericConcatJoinOrderQuery : GenericQueryBase
|
|
|
820 {
|
|
|
821 private String @p1;
|
|
|
822 private Int32 @p2;
|
|
|
823
|
|
|
824 public GenericConcatJoinOrderQuery(ITestDataContext ds, object[] args)
|
|
|
825 : base(ds)
|
|
|
826 {
|
|
|
827 @p1 = (String)args[0];
|
|
|
828 @p2 = (Int32)args[1];
|
|
|
829 }
|
|
|
830
|
|
|
831 public override IEnumerable<object> Query()
|
|
|
832 {
|
|
|
833 return (from j in
|
|
|
834 (from y in AllPersons
|
|
|
835 select new { FirstName = y.Name })
|
|
|
836 .Concat(
|
|
|
837 from x in AllPersons
|
|
|
838 from z in AllPatients
|
|
|
839 where (x.Name == @p1 || z.Id == new ObjectId { Value = @p2 })
|
|
|
840 select new { FirstName = x.Name }
|
|
|
841 )
|
|
|
842 join g in AllGrandChilds on j.FirstName equals @p1
|
|
|
843 orderby g.ParentID.Value
|
|
|
844 select new { FirstName = g.ParentID.Value.ToString() });
|
|
|
845 }
|
|
|
846 }
|
|
|
847
|
|
|
848 #endregion
|
|
|
849
|
|
|
850 [Test]
|
|
|
851 public void TestMono01()
|
|
|
852 {
|
|
|
853 ForMySqlProvider(
|
|
|
854 db =>
|
|
|
855 {
|
|
|
856 var ds = new IdlPatientSource(db);
|
|
|
857 var t = "A";
|
|
|
858 var query =
|
|
|
859 (from y in ds.Persons()
|
|
|
860 select y.Name)
|
|
|
861 .Concat(
|
|
|
862 from x in ds.Persons()
|
|
|
863 where x.Name == t
|
|
|
864 select x.Name
|
|
|
865 );
|
|
|
866
|
|
|
867 Assert.That(query.ToList(), Is.Not.Null);
|
|
|
868 });
|
|
|
869 }
|
|
|
870
|
|
|
871 [Test]
|
|
|
872 public void TestMono03()
|
|
|
873 {
|
|
|
874 ForMySqlProvider(
|
|
|
875 db => Assert.That(new GenericConcatQuery(db, new object[] { "A", 1 }).Query().ToList(), Is.Not.Null));
|
|
|
876 }
|
|
|
877
|
|
|
878 [Test]
|
|
|
879 public void TestMono04()
|
|
|
880 {
|
|
|
881 ForMySqlProvider(
|
|
|
882 db =>
|
|
|
883 Assert.That(
|
|
|
884 new GenericConcatJoinOrderQuery(db, new object[] { "A", 1 }).Query().ToList(), Is.Not.Null));
|
|
|
885 }
|
|
|
886
|
|
|
887 public static IQueryable<TSource> Concat2<TSource>(IQueryable<TSource> source1, IEnumerable<TSource> source2)
|
|
|
888 {
|
|
|
889 return source1.Provider.CreateQuery<TSource>(
|
|
|
890 Expression.Call(
|
|
|
891 null,
|
|
|
892 typeof (Queryable).GetMethod("Concat").MakeGenericMethod(typeof (TSource)),
|
|
|
893 new[] { source1.Expression, Expression.Constant(source2, typeof (IEnumerable<TSource>)) }));
|
|
|
894 }
|
|
|
895
|
|
|
896 [Test]
|
|
|
897 public void TestMonoConcat()
|
|
|
898 {
|
|
|
899 ForMySqlProvider(
|
|
|
900 db =>
|
|
|
901 {
|
|
|
902 var ds = new IdlPatientSource(db);
|
|
|
903 var t = "A";
|
|
|
904 var query = Concat2(
|
|
|
905 from y in ds.Persons() select y.Name,
|
|
|
906 from x in ds.Persons() where x.Name == t select x.Name);
|
|
|
907
|
|
|
908 Assert.That(query.ToList(), Is.Not.Null);
|
|
|
909 });
|
|
|
910 }
|
|
|
911
|
|
|
912 [Test]
|
|
|
913 public void TestMonoConcat2()
|
|
|
914 {
|
|
|
915 ForMySqlProvider(
|
|
|
916 db =>
|
|
|
917 {
|
|
|
918 var ds = new IdlPatientSource(db);
|
|
|
919 var t = "A";
|
|
|
920 var query1 = Concat2(
|
|
|
921 from y in ds.Persons() select y.Name,
|
|
|
922 from x in ds.Persons() where x.Name == t select x.Name);
|
|
|
923
|
|
|
924 Assert.That(query1.ToList(), Is.Not.Null);
|
|
|
925 });
|
|
|
926
|
|
|
927 ForMySqlProvider(
|
|
|
928 db =>
|
|
|
929 {
|
|
|
930 var ds = new IdlPatientSource(db);
|
|
|
931 var t = "A";
|
|
|
932 var query2 = Concat2(
|
|
|
933 from y in ds.Persons() select y.Name,
|
|
|
934 from x in ds.Persons() where x.Name == t select x.Name);
|
|
|
935
|
|
|
936 Assert.That(query2.ToList(), Is.Not.Null);
|
|
|
937 });
|
|
|
938 }
|
|
|
939 }
|
|
|
940
|
|
|
941 #region TestConvertFunction classes
|
|
|
942
|
|
|
943 public class IdlPatient
|
|
|
944 {
|
|
|
945 public IdlTest.ObjectId Id { get; set; }
|
|
|
946 }
|
|
|
947
|
|
|
948 public class IdlPerson
|
|
|
949 {
|
|
|
950 public IdlTest.ObjectId Id { get; set; }
|
|
|
951 public string Name { get; set; }
|
|
|
952 }
|
|
|
953
|
|
|
954 public class IdlGrandChild
|
|
|
955 {
|
|
|
956 public IdlTest.ObjectId ParentID { get; set; }
|
|
|
957 public IdlTest.ObjectId ChildID { get; set; }
|
|
|
958 public IdlTest.ObjectId GrandChildID { get; set; }
|
|
|
959 }
|
|
|
960
|
|
|
961 public class IdlPatientEx : IdlPatient
|
|
|
962 {
|
|
|
963 public IdlPerson Person { get; set; }
|
|
|
964 }
|
|
|
965
|
|
|
966 public class IdlPatientSource
|
|
|
967 {
|
|
|
968 private readonly ITestDataContext m_dc;
|
|
|
969
|
|
|
970 public IdlPatientSource(ITestDataContext dc)
|
|
|
971 {
|
|
|
972 m_dc = dc;
|
|
|
973 }
|
|
|
974
|
|
|
975 public IQueryable<IdlGrandChild> GrandChilds()
|
|
|
976 {
|
|
|
977 return m_dc.GrandChild.Select(
|
|
|
978 x => new IdlGrandChild
|
|
|
979 {
|
|
|
980 ChildID = new IdlTest.ObjectId { Value = x.ChildID.Value },
|
|
|
981 GrandChildID = new IdlTest.ObjectId { Value = x.GrandChildID.Value },
|
|
|
982 ParentID = new IdlTest.ObjectId { Value = x.ParentID.Value }
|
|
|
983 });
|
|
|
984 }
|
|
|
985
|
|
|
986 public IQueryable<IdlPatient> Patients()
|
|
|
987 {
|
|
|
988 return m_dc.Patient.Select(x => new IdlPatient { Id = new IdlTest.ObjectId { Value = x.PersonID }, });
|
|
|
989 }
|
|
|
990
|
|
|
991 public IQueryable<IdlPerson> Persons()
|
|
|
992 {
|
|
|
993 return
|
|
|
994 m_dc.Person.Select(
|
|
|
995 x => new IdlPerson { Id = new IdlTest.ObjectId { Value = x.ID }, Name = x.FirstName, });
|
|
|
996 }
|
|
|
997 }
|
|
|
998
|
|
|
999 public static class IdlPersonConverterExtensions
|
|
|
1000 {
|
|
|
1001 public static IEnumerable<IdlPatientEx> ToIdlPatientEx(
|
|
|
1002 this IQueryable<IdlPatient> list, IdlPatientSource source)
|
|
|
1003 {
|
|
|
1004 return from x in list
|
|
|
1005 join person in source.Persons() on x.Id.Value equals person.Id.Value
|
|
|
1006 select new IdlPatientEx
|
|
|
1007 {
|
|
|
1008 Id = x.Id,
|
|
|
1009 Person =
|
|
|
1010 new IdlPerson { Id = new IdlTest.ObjectId { Value = person.Id }, Name = person.Name, },
|
|
|
1011 };
|
|
|
1012 }
|
|
|
1013 }
|
|
|
1014
|
|
|
1015 #endregion
|
|
|
1016 }
|
