5 // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne
\r
7 // Permission is hereby granted, free of charge, to any person obtaining a copy
\r
8 // of this software and associated documentation files (the "Software"), to deal
\r
9 // in the Software without restriction, including without limitation the rights
\r
10 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
\r
11 // copies of the Software, and to permit persons to whom the Software is
\r
12 // furnished to do so, subject to the following conditions:
\r
14 // The above copyright notice and this permission notice shall be included in
\r
15 // all copies or substantial portions of the Software.
\r
17 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
\r
18 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
\r
19 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
\r
20 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
\r
21 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
\r
22 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
\r
27 using System.Collections.Generic;
\r
28 using System.Data.Linq;
\r
31 using System.Linq.Expressions;
\r
32 using NUnit.Framework;
\r
38 using Id = System.Decimal;
\r
40 using Id = System.Int32;
\r
45 namespace Test_NUnit_MySql
\r
47 namespace Test_NUnit_OracleODP
\r
49 namespace Test_NUnit_Oracle
\r
51 namespace Test_NUnit_PostgreSql
\r
53 namespace Test_NUnit_Sqlite
\r
55 namespace Test_NUnit_Ingres
\r
56 #elif MSSQL && L2SQL
\r
57 namespace Test_NUnit_MsSql_Strict
\r
59 namespace Test_NUnit_MsSql
\r
61 namespace Test_NUnit_Firebird
\r
65 public class ReadTest_Complex : TestBase
\r
69 public ReadTest_Complex()
\r
75 #region 'D' tests exercise 'local object constants'
\r
77 public void D0_SelectPensByLocalProperty()
\r
79 //reported by Andrus.
\r
80 //http://groups.google.com/group/dblinq/browse_thread/thread/c25527cbed93d265
\r
82 Northwind db = CreateDB();
\r
84 Product localProduct = new Product { ProductName = "Chai" };
\r
85 var q = from p in db.Products where p.ProductName == localProduct.ProductName select p;
\r
87 List<Product> products = q.ToList();
\r
88 int productCount = products.Count;
\r
89 Assert.AreEqual(productCount, 1, "Expected one pen, got count=" + productCount);
\r
93 public void D1_SelectPensByLocalProperty()
\r
95 Northwind db = CreateDB();
\r
96 var pen = new { Name = "Chai" };
\r
97 var q = from p in db.Products where p.ProductName == pen.Name select p;
\r
99 List<Product> products = q.ToList();
\r
100 int productCount = products.Count;
\r
101 Assert.AreEqual(productCount, 1, "Expected one pen, got count=" + productCount);
\r
105 public void D2_SelectProductByLocalPropertyAndConstant()
\r
108 Northwind db = CreateDB();
\r
109 string product = "Carnarvon Tigers";
\r
110 var q = from p in db.Products
\r
111 where p.ProductName == product &&
\r
112 p.QuantityPerUnit.StartsWith("16")
\r
114 List<Product> products = q.ToList();
\r
115 int productCount = products.Count;
\r
116 Assert.AreEqual(1, productCount, "Expected one product, got count=" + productCount);
\r
120 public void D3_ArrayContains()
\r
122 Northwind db = CreateDB();
\r
124 var data = from p in db.Customers
\r
125 where new string[] { "ALFKI", "WARTH" }.Contains(p.CustomerID)
\r
126 select new { p.CustomerID, p.Country };
\r
128 var dataList = data.ToList();
\r
129 //Assert.AreEqual(productCount, 1, "Expected one pen, got count=" + productCount);
\r
133 #region Tests 'F' work on aggregation
\r
135 public void F1_ProductCount()
\r
137 var q = from p in db.Products select p;
\r
138 int productCount = q.Count();
\r
139 Assert.Greater(productCount, 0, "Expected non-zero product count");
\r
143 public void F2_ProductCount_Projected()
\r
145 var q = from p in db.Products select p.ProductID;
\r
146 int productCount = q.Count();
\r
147 Assert.Greater(productCount, 0, "Expected non-zero product count");
\r
148 Console.WriteLine();
\r
151 public void F2_ProductCount_Clause()
\r
153 var q = from p in db.Products select p.ProductID;
\r
154 int productCount = q.Count(i => i < 3);
\r
155 Assert.Greater(productCount, 0, "Expected non-zero product count");
\r
156 Assert.IsTrue(productCount < 4, "Expected product count < 3");
\r
160 public void F3_MaxProductId()
\r
162 var q = from p in db.Products select p.ProductID;
\r
163 var maxID = q.Max();
\r
164 Assert.Greater(maxID, 0, "Expected non-zero product count");
\r
168 public void F4_MinProductId()
\r
170 var q = from p in db.Products select p.ProductID;
\r
171 var minID = q.Min();
\r
172 Assert.Greater(minID, 0, "Expected non-zero product count");
\r
175 #if !ORACLE // picrap: this test causes an internal buffer overflow when marshaling with oracle win32 driver
\r
178 public void F5_AvgProductId()
\r
180 var q = from p in db.Products select p.ProductID;
\r
181 double avg = q.Average();
\r
182 Assert.Greater(avg, 0, "Expected non-zero productID average");
\r
188 public void F7_ExplicitJoin()
\r
190 //a nice and light nonsense join:
\r
191 //bring in rows such as {Chai,AIRBU}
\r
193 from p in db.Products
\r
194 join c in db.Categories on p.ProductID equals c.CategoryID
\r
195 select new { p.ProductName, c.CategoryName };
\r
198 foreach (var v in q)
\r
201 Assert.IsTrue(v.ProductName != null);
\r
202 Assert.IsTrue(v.CategoryName != null);
\r
204 Assert.IsTrue(rowCount > 2);
\r
208 public void F7b_ExplicitJoin()
\r
211 from c in db.Customers
\r
212 join o in db.Orders on c.CustomerID equals o.CustomerID
\r
213 where c.City == "London"
\r
217 #if INCLUDING_CLAUSE
\r
218 //Including() clause discontinued in Studio Orcas?
\r
220 public void F8_IncludingClause()
\r
223 from c in db.Customers
\r
224 where c.City == "London"
\r
226 .Including(c => c.Orders);
\r
230 public void F8_Including_Nested()
\r
233 from c in db.Customers
\r
234 where c.City == "London"
\r
236 .Including(c => c.Orders.Including(o => o.OrderDetails));
\r
241 public void F9_Project_AndContinue()
\r
244 from c in db.Customers
\r
245 where c.City == "London"
\r
246 select new { Name = c.ContactName, c.Phone } into x
\r
252 public void F10_DistinctCity()
\r
254 var q1 = from c in db.Customers select c.City;
\r
255 var q2 = q1.Distinct();
\r
258 foreach (string city in q2)
\r
260 if (city == "London") { numLondon++; }
\r
262 Assert.AreEqual(1, numLondon, "Expected to see London once");
\r
266 public void F11_ConcatString()
\r
268 var q4 = from p in db.Products select p.ProductName + p.ProductID;
\r
269 //var q4 = from p in db.Products select p.ProductID;
\r
270 var q5 = q4.ToList();
\r
271 Assert.Greater(q5.Count, 2, "Expected to see some concat strings");
\r
272 foreach (string s0 in q5)
\r
274 bool startWithLetter = Char.IsLetter(s0[0]);
\r
275 bool endsWithDigit = Char.IsDigit(s0[s0.Length - 1]);
\r
276 Assert.IsTrue(startWithLetter && endsWithDigit, "String must start with letter and end with digit");
\r
280 #if !DEBUG && POSTGRES
\r
284 public void F12_ConcatString_2()
\r
286 var q4 = from p in db.Products
\r
287 where (p.ProductName + p.ProductID).Contains("e")
\r
288 select p.ProductName+p.ProductID;
\r
289 //var q4 = from p in db.Products select p.ProductID;
\r
290 //var q5 = q4.ToList();
\r
291 Assert.Greater( q4.Count(), 2, "Expected to see some concat strings");
\r
292 foreach(string s0 in q4)
\r
294 bool startWithLetter = Char.IsLetter(s0[0]);
\r
295 bool endsWithDigit = Char.IsDigit(s0[s0.Length-1]);
\r
296 Assert.IsTrue(startWithLetter && endsWithDigit, "String must start with letter and end with digit");
\r
301 const string obsoleteError = @"Since beta2 in Linq2Sql to project a new entity (ie: select new Order(3)) is forbidden for coherence reasons, so this tests doesn't mimic the Linq2Sql behavior and it is obsolete and should be modified. If you apply such test cases to Linq2Sql you'll get Test_NUnit_MsSql_Strict.DynamicLinqTest.DL5_NestedObjectSelect:
\r
302 System.NotSupportedException : Explicit construction of entity type 'MsNorthwind.XX' in query is not allowed.\n\nMore Info in: http://linqinaction.net/blogs/roller/archive/2007/11/27/explicit-construction-of-entity-type-in-query-is-not-allowed.aspx";
\r
304 public void F13_NewCustomer()
\r
306 Assert.Ignore(obsoleteError);
\r
307 Northwind db = CreateDB();
\r
308 IQueryable<Customer> q = (from c in db.Customers
\r
312 CustomerID = c.CustomerID
\r
314 var list = q.ToList();
\r
315 Assert.Greater(list.Count(), 0, "Expected list");
\r
316 //Assert.Greater(list.Count(), 0, "Expected list");
\r
317 Assert.Ignore("test passed but: theoretically constructions of entity types are not allowed");
\r
321 public void F14_NewCustomer_Order()
\r
323 Assert.Ignore(obsoleteError);
\r
324 Northwind db = CreateDB();
\r
325 IQueryable<Customer> q = (from c in db.Customers
\r
329 CustomerID = c.CustomerID
\r
331 //this OrderBy clause messes up the SQL statement
\r
332 var q2 = q.OrderBy(c => c.CustomerID);
\r
333 var list = q2.ToList();
\r
334 Assert.Greater(list.Count(), 0, "Expected list");
\r
335 //Assert.Greater(list.Count(), 0, "Expected list");
\r
340 public void F15_OrderByCoalesce()
\r
342 Northwind db = CreateDB();
\r
343 var q = from c in db.Customers
\r
344 orderby c.ContactName ?? ""
\r
346 var list = q.ToList();
\r
347 Assert.Greater(list.Count(), 0, "Expected list");
\r
350 [Test(Description = "Non-dynamic version of DL5_NestedObjectSelect")]
\r
351 public void F16_NestedObjectSelect()
\r
353 Assert.Ignore(obsoleteError);
\r
354 Northwind db = CreateDB();
\r
355 var q = from o in db.Orders
\r
356 select new Order() { OrderID = o.OrderID, Customer = new Customer() { ContactName = o.Customer.ContactName } };
\r
357 var list = q.ToList();
\r
360 [Test(Description = "Non-dynamic version of DL5_NestedObjectSelect")]
\r
361 public void F17_NestedObjectSelect_Ver2()
\r
363 Assert.Ignore(obsoleteError);
\r
364 Northwind db = CreateDB();
\r
365 var query = from order in db.Orders
\r
368 OrderID = order.OrderID,
\r
369 Customer = new Customer
\r
371 ContactName = order.Customer.ContactName,
\r
372 ContactTitle = order.Customer.ContactTitle
\r
375 var list = query.ToList();
\r
376 Assert.IsTrue(list.Count > 0);
\r
379 #if !DEBUG && POSTGRES
\r
382 [Test(Description = "byte[] test")]
\r
383 public void F18_ByteArrayAssignmentTest()
\r
385 var db = CreateDB();
\r
387 var picture = new byte[] { 1, 2, 3, 4 };
\r
389 var nc = new Category { CategoryName = "test", Picture = picture };
\r
390 db.Categories.InsertOnSubmit(nc);
\r
391 db.SubmitChanges();
\r
393 var q = from c in db.Categories
\r
394 where c.CategoryName == "test"
\r
395 select new { c.Picture };
\r
396 var l = q.ToList();
\r
397 Assert.IsTrue(l.Count > 0);
\r
398 Assert.IsTrue(picture.SequenceEqual(l[0].Picture.ToArray()));
\r
400 db.Categories.DeleteOnSubmit(nc);
\r
401 db.SubmitChanges();
\r
406 public void F19_ExceptWithCount_ViaToList()
\r
408 var db = CreateDB();
\r
410 var toExclude = from t in db.GetTable<Territory>()
\r
411 where t.TerritoryDescription.StartsWith("A")
\r
413 var universe = from t in db.GetTable<Territory>() select t;
\r
414 var toTake = universe.Except(toExclude);
\r
416 int toListCount = toTake.ToList().Count;
\r
417 Assert.AreEqual(51, toListCount);
\r
421 public void F20_ExceptWithCount()
\r
423 var db = CreateDB();
\r
425 var toExclude = from t in db.GetTable<Territory>()
\r
426 where t.TerritoryDescription.StartsWith("A")
\r
428 var universe = from t in db.GetTable<Territory>() select t;
\r
429 var toTake = universe.Except(toExclude).Except(db.Territories.Where(terr => terr.TerritoryDescription.StartsWith("B")));
\r
431 int toTakeCount = toTake.Count();
\r
432 Assert.AreEqual(44, toTakeCount);
\r
435 #if !DEBUG && (SQLITE)
\r
439 public void F21_CountNestedExcepts()
\r
441 var db = CreateDB();
\r
443 var toExclude1 = from t in db.GetTable<Territory>()
\r
444 where t.TerritoryDescription.StartsWith("A")
\r
446 var toExclude2 = toExclude1.Except(db.GetTable<Territory>().Where(terr => terr.TerritoryDescription.Contains("i")));
\r
448 var universe = from t in db.GetTable<Territory>() select t;
\r
450 var toTake = universe.Except(toExclude2);
\r
452 int toTakeCount = toTake.Count();
\r
453 Assert.AreEqual(52, toTakeCount);
\r
456 #if !DEBUG && (SQLITE)
\r
460 public void F22_AnyNestedExcepts()
\r
462 var db = CreateDB();
\r
464 var toExclude1 = from t in db.GetTable<Territory>()
\r
465 where t.TerritoryDescription.StartsWith("A")
\r
467 var toExclude2 = toExclude1.Except(db.GetTable<Territory>().Where(terr => terr.TerritoryDescription.Contains("i")));
\r
469 var universe = from t in db.GetTable<Territory>() select t;
\r
471 var toTake = universe.Except(toExclude2);
\r
473 Assert.IsTrue(toTake.Any());
\r
476 #if !DEBUG && SQLITE
\r
480 public void F23_AnyNestedExcepts_WithParameter()
\r
482 var db = CreateDB();
\r
484 var toExclude1 = from t in db.GetTable<Territory>()
\r
485 where t.TerritoryDescription.StartsWith("A")
\r
487 var toExclude2 = toExclude1.Except(db.GetTable<Territory>().Where(terr => terr.TerritoryDescription.Contains("i")));
\r
489 var universe = from t in db.GetTable<Territory>() select t;
\r
491 var toTake = universe.Except(toExclude2);
\r
493 Assert.IsTrue(toTake.Any(t => t.TerritoryDescription.Contains("i")));
\r
496 #if !DEBUG && SQLITE
\r
500 public void F24_CountNestedExcepts_WithParameter()
\r
502 var db = CreateDB();
\r
504 var toExclude1 = from t in db.GetTable<Territory>()
\r
505 where t.TerritoryDescription.StartsWith("A")
\r
507 var toExclude2 = toExclude1.Except(db.GetTable<Territory>().Where(terr => terr.TerritoryDescription.Contains("i")));
\r
509 var universe = from t in db.GetTable<Territory>() select t;
\r
511 var toTake = universe.Except(toExclude2);
\r
513 int toTakeCount = toTake.Count(t => t.TerritoryDescription.Contains("o"));
\r
514 Assert.AreEqual(34, toTakeCount);
\r
518 public void F25_DistinctUnion()
\r
520 var db = CreateDB();
\r
522 var toInclude1 = from t in db.GetTable<Territory>()
\r
523 where t.TerritoryDescription.StartsWith("A")
\r
525 var toInclude2 = toInclude1.Concat(db.GetTable<Territory>().Where(terr => terr.TerritoryDescription.Contains("i")));
\r
527 var toTake = toInclude2.Distinct();
\r
529 int count = toTake.ToList().Count;
\r
531 Assert.AreEqual(27, count);
\r
535 public void F26_DistinctUnion_Count()
\r
537 var db = CreateDB();
\r
539 var toInclude1 = from t in db.GetTable<Territory>()
\r
540 where t.TerritoryDescription.StartsWith("A")
\r
542 var toInclude2 = toInclude1.Concat(db.GetTable<Territory>().Where(terr => terr.TerritoryDescription.Contains("i")));
\r
544 var toTake = toInclude2.Distinct();
\r
546 int count = toTake.Count();
\r
548 Assert.AreEqual(27, count);
\r
557 public void F27_SelectEmployee_Identifier()
\r
559 var db = CreateDB();
\r
560 var q = from e in db.GetTable<EmployeeWithStringIdentifier>() where e.Identifier == "7" select e;
\r
561 EmployeeWithStringIdentifier em = q.Single();
\r
563 Assert.AreEqual("King", em.LastName);
\r
569 /// the following three tests are from Jahmani's page
\r
570 /// LinqToSQL: Comprehensive Support for SQLite, MS Access, SQServer2000/2005
\r
571 /// http://www.codeproject.com/KB/linq/linqToSql_7.aspx?msg=2428251
\r
573 [Test(Description = "list of customers who have place orders that have all been shipped to the customers city.")]
\r
574 public void O1_OperatorAll()
\r
576 var q = from c in db.Customers
\r
577 where (from o in c.Orders
\r
578 select o).All(o => o.ShipCity == c.City)
\r
579 select new { c.CustomerID, c.ContactName };
\r
580 var list = q.ToList();
\r
583 [Test(Description = "list of customers who have placed no orders")]
\r
584 public void O2_OperatorAny()
\r
586 //SELECT t0.CustomerID, t0.ContactName
\r
587 //FROM Customers AS t0
\r
589 //( SELECT COUNT(*)
\r
590 // FROM Orders AS t1
\r
591 // WHERE (t1.CustomerID = t0.CustomerID)
\r
594 var q = from customer in db.Customers
\r
595 where !customer.Orders.Any()
\r
596 select new { customer.CustomerID, customer.ContactName };
\r
597 //var q = from customer in db.Customers
\r
598 // where customer.Orders.Count() == 0
\r
599 // select new { customer.CustomerID, customer.ContactName };
\r
600 var list = q.ToList();
\r
603 [Test(Description = "provide a list of customers and employees who live in London.")]
\r
604 public void O3_OperatorUnion()
\r
606 var q = (from c in db.Customers.Where(d => d.City == "London")
\r
607 select new { ContactName = c.ContactName })
\r
608 .Union(from e in db.Employees.Where(f => f.City == "London")
\r
609 select new { ContactName = e.LastName });
\r
610 var list = q.ToList();
\r
611 Assert.IsTrue(list.Count > 0, "Expected some customers and employees from London");
\r
615 public void O4_OperatorContains()
\r
617 var ids = new Id[] { 1, 2, 3 };
\r
618 Northwind db = CreateDB();
\r
620 //var q = from p in db.Products select p.ProductID;
\r
621 //int productCount = q.Count();
\r
623 var products = from p in db.Products
\r
624 where ids.Contains((Id) p.ProductID)
\r
627 Assert.AreEqual(3, products.Count());
\r