mcs/class/System.Data.Linq/src/DbLinq/Test/Providers/ReadTest.cs

   1 #region MIT license\r
   2 // \r
   3 // MIT license\r
   4 //\r
   5 // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne\r
   6 // \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
  13 // \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
  16 // \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
  23 // THE SOFTWARE.\r
  24 // \r
  25 #endregion\r
  26 using System;\r
  27 using System.Collections.Generic;\r
  28 using System.Text;\r
  29 using System.Linq;\r
  30 using NUnit.Framework;\r
  31 using Test_NUnit;\r
  32 using System.Data.Linq.Mapping;\r
  33 \r
  34 using nwind;\r
  35 \r
  36 #if MONO_STRICT\r
  37 using DataLinq = System.Data.Linq;\r
  38 #else\r
  39 using DataLinq = DbLinq.Data.Linq;\r
  40 #endif\r
  41 \r
  42 // test ns \r
  43 #if MYSQL\r
  44     namespace Test_NUnit_MySql\r
  45 #elif ORACLE && ODP\r
  46     namespace Test_NUnit_OracleODP\r
  47 #elif ORACLE\r
  48     namespace Test_NUnit_Oracle\r
  49 #elif POSTGRES\r
  50     namespace Test_NUnit_PostgreSql\r
  51 #elif SQLITE\r
  52     namespace Test_NUnit_Sqlite\r
  53 #elif INGRES\r
  54     namespace Test_NUnit_Ingres\r
  55 #elif MSSQL && L2SQL\r
  56     namespace Test_NUnit_MsSql_Strict\r
  57 #elif MSSQL\r
  58     namespace Test_NUnit_MsSql\r
  59 #elif FIREBIRD\r
  60     namespace Test_NUnit_Firebird\r
  61 #endif\r
  62 {\r
  63     [TestFixture]\r
  64     public class ReadTest : TestBase\r
  65     {\r
  66         #region Tests 'A' check for DB being ready\r
  67 \r
  68 \r
  69         /// <summary>\r
  70         /// in NUnit, tests are executed in alpha order.\r
  71         /// We want to start by checking access to DB.\r
  72         /// </summary>\r
  73         [Test]\r
  74         public void A1_PingDatabase()\r
  75         {\r
  76             Northwind db = CreateDB();\r
  77             bool pingOK = db.DatabaseExists();\r
  78             //bool pingOK = Conn.Ping(); //Schildkroete - Ping throws NullRef if conn is not open\r
  79             Assert.IsTrue(pingOK, "Pinging database");\r
  80         }\r
  81 \r
  82 \r
  83 \r
  84 #if !DEBUG && (MSSQL && L2SQL)\r
  85         // L2SQL doesn't support 'SELECT' queries in DataContext.ExecuteCommand().\r
  86         [Explicit]\r
  87 #endif\r
  88         [Test]\r
  89         public void A3_ProductsTableHasPen()\r
  90         {\r
  91             Northwind db = CreateDB();\r
  92             //string sql = @"SELECT count(*) FROM linqtestdb.Products WHERE ProductName='Chai'";\r
  93             string sql = @"SELECT count(*) FROM [Products] WHERE [ProductName]='Chai'";\r
  94             long iResult = db.ExecuteCommand(sql);\r
  95             //long iResult = base.ExecuteScalar(sql);\r
  96             Assert.AreEqual(iResult, 1L, "Expecting one Chai in Products table, got:" + iResult + " (SQL:" + sql + ")");\r
  97         }\r
  98 \r
  99         [Test]\r
 100         public void A4_SelectSingleCustomer()\r
 101         {\r
 102             Northwind db = CreateDB();\r
 103 \r
 104             // Query for a specific customer\r
 105             var cust = db.Customers.Single(c => c.CompanyName == "Around the Horn");\r
 106             Assert.IsNotNull(cust, "Expected one customer 'Around the Horn'.");\r
 107             var id = 1;\r
 108             var prod = db.Products.Single(p => p.ProductID == id);\r
 109             Assert.AreEqual("Chai", prod.ProductName);\r
 110             id = 2;\r
 111             prod = db.Products.Single(p => p.ProductID == id);\r
 112             Assert.AreEqual("Chang", prod.ProductName);\r
 113         }\r
 114 \r
 115         [Test]\r
 116         public void A5_SelectSingleOrDefault()\r
 117         {\r
 118             Northwind db = CreateDB();\r
 119 \r
 120             // Query for a specific customer\r
 121             var cust = db.Customers.SingleOrDefault(c => c.CompanyName == "Around the Horn");\r
 122             Assert.IsNotNull(cust, "Expected one customer 'Around the Horn'.");\r
 123 \r
 124 #if false\r
 125             var id = "ALFKI";\r
 126             cust = db.Customers.SingleOrDefault(c => c.CustomerID == id);\r
 127             Assert.AreEqual("ALFKI", cust.CustomerID);\r
 128             id = "BLAUS";\r
 129             cust = db.Customers.SingleOrDefault(c => c.CustomerID == id);\r
 130             Assert.AreEqual("BLAUS", cust.CustomerID);\r
 131             id = "DNE";\r
 132             cust = db.Customers.SingleOrDefault(c => c.CustomerID == id); // Does Not Exist\r
 133             Assert.IsNull(cust);\r
 134 \r
 135             id = "ALFKI";\r
 136             cust = db.Customers.SingleOrDefault(c => c.CustomerID == id);\r
 137             Assert.AreEqual("ALFKI", cust.CustomerID);\r
 138             id = "BLAUS";\r
 139             cust = db.Customers.SingleOrDefault(c => c.CustomerID == id);\r
 140 #endif\r
 141             cust = GetCustomerById(db, "ALFKI");\r
 142             Assert.AreEqual("ALFKI", cust.CustomerID);\r
 143 \r
 144             cust = GetCustomerById(db, "BLAUS");\r
 145             Assert.AreEqual("BLAUS", cust.CustomerID);\r
 146 \r
 147             cust = GetCustomerById(db, "DNE");\r
 148             Assert.IsNull(cust);\r
 149 \r
 150             cust = GetCustomerById(db, "ALFKI");\r
 151             Assert.AreEqual("ALFKI", cust.CustomerID);\r
 152 \r
 153             cust = GetCustomerById(db, "BLAUS");\r
 154             Assert.AreEqual("BLAUS", cust.CustomerID);\r
 155         }\r
 156 \r
 157 \r
 158         private static Customer GetCustomerById(Northwind db, string id)\r
 159         {\r
 160             return db.Customers.SingleOrDefault(c => c.CustomerID == id);\r
 161         }\r
 162 \r
 163 \r
 164         [Test]\r
 165         public void A6_ConnectionOpenTest()\r
 166         {\r
 167             Northwind db = CreateDB(System.Data.ConnectionState.Open);\r
 168             Product p1 = db.Products.Single(p => p.ProductID == 1);\r
 169             Assert.IsTrue(p1.ProductID == 1);\r
 170         }\r
 171 \r
 172         [Test]\r
 173         public void A7_ConnectionClosedTest()\r
 174         {\r
 175             Northwind db = CreateDB(System.Data.ConnectionState.Closed);\r
 176             Product p1 = db.Products.Single(p => p.ProductID == 1);\r
 177             Assert.IsTrue(p1.ProductID == 1);\r
 178         }\r
 179 \r
 180         public void A8_SelectSingleOrDefault_QueryCacheDisabled()\r
 181         {\r
 182             Northwind db = CreateDB();\r
 183 #if !MONO_STRICT\r
 184             db.QueryCacheEnabled = false;\r
 185 #endif\r
 186 \r
 187             // Query for a specific customer\r
 188             var cust = db.Customers.SingleOrDefault(c => c.CompanyName == "Around the Horn");\r
 189             Assert.IsNotNull(cust, "Expected one customer 'Around the Horn'.");\r
 190 \r
 191             cust = GetCustomerById(db, "ALFKI");\r
 192             Assert.AreEqual("ALFKI", cust.CustomerID);\r
 193 \r
 194             cust = GetCustomerById(db, "BLAUS");\r
 195             Assert.AreEqual("BLAUS", cust.CustomerID);\r
 196 \r
 197             cust = GetCustomerById(db, "DNE");\r
 198             Assert.IsNull(cust);\r
 199 \r
 200             cust = GetCustomerById(db, "ALFKI");\r
 201             Assert.AreEqual("ALFKI", cust.CustomerID);\r
 202 \r
 203             cust = GetCustomerById(db, "BLAUS");\r
 204             Assert.AreEqual("BLAUS", cust.CustomerID);\r
 205         }\r
 206 \r
 207         #endregion\r
 208 \r
 209         //TODO: group B, which checks AllTypes\r
 210 \r
 211         #region Tests 'C' do plain select - no aggregation\r
 212         [Test]\r
 213         public void C1_SelectProducts()\r
 214         {\r
 215             Northwind db = CreateDB();\r
 216 \r
 217             var q = from p in db.Products select p;\r
 218             List<Product> products = q.ToList();\r
 219             int productCount = products.Count;\r
 220             Assert.Greater(productCount, 0, "Expected some products, got none");\r
 221         }\r
 222 \r
 223 #if !DEBUG && SQLITE\r
 224         [Explicit]\r
 225 #endif\r
 226         [Test]\r
 227         public void C2_SelectPenId()\r
 228         {\r
 229             Northwind db = CreateDB();\r
 230 \r
 231             var q = from p in db.Products where p.ProductName == "Chai" select p.ProductID;\r
 232             var productIDs = q.ToList();\r
 233             int productCount = productIDs.Count;\r
 234             Assert.AreEqual(productCount, 1, "Expected one pen, got count=" + productCount);\r
 235         }\r
 236 \r
 237         [Test]\r
 238         public void C2b_SelectPenId()\r
 239         {\r
 240             Northwind db = CreateDB();\r
 241 \r
 242             var pen = "Chai";\r
 243             var q = from p in db.Products where p.ProductName == pen select p.ProductID;\r
 244             var productIDs = q.ToList();\r
 245             int productCount = productIDs.Count;\r
 246             Assert.AreEqual(productCount, 1, "Expected one pen, got count=" + productCount);\r
 247         }\r
 248 \r
 249         [Test]\r
 250         public void C3_SelectPenIdName()\r
 251         {\r
 252             Northwind db = CreateDB();\r
 253 \r
 254             var q = from p in db.Products\r
 255                     where p.ProductName == "Chai"\r
 256                     select new { ProductId = p.ProductID, Name = p.ProductName };\r
 257             int count = 0;\r
 258             //string penName;\r
 259             foreach (var v in q)\r
 260             {\r
 261                 Assert.AreEqual(v.Name, "Chai", "Expected ProductName='Chai'");\r
 262                 count++;\r
 263             }\r
 264             Assert.AreEqual(count, 1, "Expected one pen, got count=" + count);\r
 265         }\r
 266 \r
 267         [Test]\r
 268         public void C4_CountWithOrderBy()\r
 269         {\r
 270             Northwind db = CreateDB();\r
 271             var q = (from p in db.Products\r
 272                      orderby p.ProductID\r
 273                      select p).Count();\r
 274             Assert.IsTrue(q > 0);\r
 275         }\r
 276 \r
 277         [Test]\r
 278         public void C5_ConstantProperty()\r
 279         {\r
 280             Northwind db = CreateDB();\r
 281             var res = from o in db.Orders\r
 282                       select new { test = 1 };\r
 283             var list = res.ToList();\r
 284             Assert.AreEqual(db.Orders.Count(), list.Count);\r
 285         }\r
 286 \r
 287 \r
 288         [Test]\r
 289         public void C6_NullParentEmplyee()\r
 290         {\r
 291             //this should generate a LEFT JOIN statement, but currently does not.\r
 292             Northwind db = CreateDB();\r
 293 \r
 294             var query = from e in db.Employees\r
 295                         select new\r
 296                         {\r
 297                             Name = e.FirstName,\r
 298                             ReportsTo = e.ReportsToEmployee.FirstName\r
 299                         };\r
 300 \r
 301             var list = query.ToList();\r
 302             // PC patch: I get 4 results...\r
 303             Assert.IsTrue(list.Count >= 3);\r
 304         }\r
 305 \r
 306 \r
 307 \r
 308         [Test]\r
 309         public void C7_CaseInsensitiveSubstringSearch()\r
 310         {\r
 311             Northwind db = CreateDB();\r
 312 \r
 313             string search = "HERKKU";\r
 314             var query = db.Customers.Where(d => d.CompanyName.ToUpper()\r
 315               .Contains(search));\r
 316 \r
 317             var list = query.ToList();\r
 318             Assert.AreEqual(1, list.Count);\r
 319         }\r
 320 \r
 321 \r
 322         /// <summary>\r
 323         /// from http://www.agilior.pt/blogs/pedro.rainho/archive/2008/04/11/4271.aspx\r
 324         /// </summary>\r
 325 #if !DEBUG && (SQLITE || POSTGRES || (MSSQL && !L2SQL))\r
 326         [Explicit]\r
 327 #endif\r
 328         [Test(Description = "Using LIKE operator from linq query")]\r
 329         public void C7B_LikeOperator()\r
 330         {\r
 331             Northwind db = CreateDB();\r
 332 \r
 333             //this used to read "Like(HU%F)" but I don't think we have that company.\r
 334 \r
 335             var query = (from c in db.Customers\r
 336                          where System.Data.Linq.SqlClient.SqlMethods.Like(c.CompanyName, "Alfre%")\r
 337                          select c).ToList();\r
 338             var list = query.ToList();\r
 339             Assert.AreEqual(1, list.Count);\r
 340         }\r
 341 \r
 342         [Test]\r
 343         public void C8_SelectPenByLocalVariable()\r
 344         {\r
 345             Northwind db = CreateDB();\r
 346             string pen = "Chai";\r
 347 \r
 348             var q = from p in db.Products\r
 349                     where (p.ProductName == pen)\r
 350                     select p;\r
 351             var productIDs = q.ToList();\r
 352             int productCount = productIDs.Count;\r
 353             Assert.AreEqual(productCount, 1, "Expected one pen, got count=" + productCount);\r
 354         }\r
 355 \r
 356         [Test]\r
 357         public void C9_OrderByLeftJoin()\r
 358         {\r
 359             Northwind db = CreateDB();\r
 360             var q = from p in db.Orders\r
 361                     orderby p.Customer.City\r
 362                     select p;\r
 363             \r
 364             int count = q.ToList().Count();\r
 365             int ordcount = db.Orders.Count();\r
 366             Assert.AreEqual(ordcount, count);\r
 367         }\r
 368 \r
 369         [Test]\r
 370         public void C10_ConstantPredicate()\r
 371         {\r
 372             Northwind db = CreateDB();\r
 373             var q = from p in db.Customers\r
 374                     where true\r
 375                     select p;\r
 376 \r
 377             int count = q.ToList().Count;\r
 378             Assert.AreEqual(count, db.Customers.Count());\r
 379         }\r
 380 \r
 381         [Test]\r
 382         public void C10b_ConstantPredicate()\r
 383         {\r
 384             Northwind db = CreateDB();\r
 385             var q = from p in db.Customers\r
 386                     where false\r
 387                     select p;\r
 388 \r
 389             int count = q.Count();\r
 390             Assert.AreEqual(count, 0);\r
 391         }\r
 392 \r
 393         [Test]\r
 394         public void C10c_ConstantPredicate()\r
 395         {\r
 396             Northwind db = CreateDB();\r
 397             var q = from p in db.Customers\r
 398                     where (p.Address.StartsWith("A") && false)\r
 399                     select p;\r
 400 \r
 401             int count = q.Count();\r
 402             Assert.AreEqual(count, 0);\r
 403         }\r
 404 \r
 405         [Test]\r
 406         public void C10d_ConstantPredicate()\r
 407         {\r
 408             Northwind db = CreateDB();\r
 409             var q = from p in db.Customers\r
 410                     where (p.Address.StartsWith("A") || true)\r
 411                     select p;\r
 412 \r
 413             int count = q.Count();\r
 414             Assert.AreEqual(count, db.Customers.Count());\r
 415         }\r
 416 \r
 417         [Test]\r
 418         public void C10e_ConstantPredicate()\r
 419         {\r
 420             Northwind db = CreateDB();\r
 421             var q = from p in db.Customers\r
 422                     where (p.Address.StartsWith("A") || false)\r
 423                     select p;\r
 424 \r
 425             int count = q.Count();\r
 426             Assert.Less(count, db.Customers.Count());\r
 427         }\r
 428 \r
 429         [Test]\r
 430         public void C10f_ConstantPredicate()\r
 431         {\r
 432             Northwind db = CreateDB();\r
 433             var q = from p in db.Customers\r
 434                     where (p.Address.StartsWith("A") && true)\r
 435                     select p;\r
 436 \r
 437             int count = q.Count();\r
 438             Assert.Less(count, db.Customers.Count());\r
 439         }\r
 440 \r
 441         [Test]\r
 442         public void C11_SelectProductsDiscontinued()\r
 443         {\r
 444             Northwind db = CreateDB();\r
 445             var q = from p in db.Products \r
 446 #if INGRES\r
 447                     where p.Discontinued != 0\r
 448 #else\r
 449                     where p.Discontinued == true \r
 450 #endif\r
 451                     select p.ProductID;\r
 452 \r
 453             var productIDs = q.ToList();\r
 454             int productCount = productIDs.Count;\r
 455             Assert.AreEqual(productCount, 8, "Expected eight products discontinued, got count=" + productCount);\r
 456         }\r
 457 \r
 458         [Test]\r
 459         public void C12_SelectEmployee_MultiJoinWithWhere()\r
 460         {\r
 461             Northwind db = CreateDB();\r
 462             var q = from t in db.Territories\r
 463                       join l in db.EmployeeTerritories on t.TerritoryID equals l.TerritoryID\r
 464                       join e in db.Employees on l.EmployeeID equals e.EmployeeID\r
 465                       where t.RegionID > 3\r
 466                       select e; \r
 467             /* Note that written this way it work, but it's not always possible.\r
 468             var q = from t in db.Territories.Where(t => t.RegionID > 3)\r
 469                     join l in db.EmployeeTerritories on t.TerritoryID equals l.TerritoryID\r
 470                     join e in db.Employees on l.EmployeeID equals e.EmployeeID\r
 471                     select e; \r
 472              */\r
 473             var employeeCount = q.Count();\r
 474             Assert.AreEqual(4, employeeCount, "Expected for employees, got count=" + employeeCount);\r
 475         }\r
 476 \r
 477         [Test]\r
 478         [ExpectedException(ExceptionType=typeof(InvalidOperationException), ExpectedMessage="Data context options cannot be modified after results have been returned from a query.")]\r
 479         public void C13_Changing_ObjectTrackingEnabled2False()\r
 480         {\r
 481             Northwind db = CreateDB();\r
 482             var q = from t in db.Territories\r
 483                     select t;\r
 484             var territoryCount = q.FirstOrDefault();\r
 485             db.ObjectTrackingEnabled = false;\r
 486         }\r
 487 \r
 488         [Test]\r
 489         [ExpectedException(ExceptionType = typeof(InvalidOperationException), ExpectedMessage = "Data context options cannot be modified after results have been returned from a query.")]\r
 490         public void C14_Changing_DeferredLoadingEnabled2False()\r
 491         {\r
 492             Northwind db = CreateDB();\r
 493             var q = from t in db.Territories\r
 494                     select t;\r
 495             var territoryCount = q.FirstOrDefault();\r
 496             db.DeferredLoadingEnabled = false;\r
 497         }\r
 498 \r
 499         [Test]\r
 500         [ExpectedException(ExceptionType = typeof(InvalidOperationException), ExpectedMessage = "Object tracking is not enabled for the current data context instance.")]\r
 501         public void C15_SubmitChanges_DeferredLoadingEnabled_False()\r
 502         {\r
 503             Northwind db = CreateDB();\r
 504             db.ObjectTrackingEnabled = false;\r
 505             var q = from t in db.Territories\r
 506                     select t;\r
 507             var territoryCount = q.Count();\r
 508             db.SubmitChanges();\r
 509         }\r
 510 \r
 511         [Test]\r
 512         public void C16_GettingProperty_DeferredLoadingEnabled2False()\r
 513         {\r
 514             Northwind db = CreateDB();\r
 515             db.DeferredLoadingEnabled = false;\r
 516             var q = from t in db.Territories\r
 517                     select t;\r
 518             Territory territory = q.FirstOrDefault();\r
 519             Assert.IsNotNull(territory);\r
 520             Assert.IsNull(territory.Region);\r
 521         }\r
 522 \r
 523         [Test]\r
 524         public void C17_GettingProperty_ObjectTrackingEnabled2False()\r
 525         {\r
 526             Northwind db = CreateDB();\r
 527             db.ObjectTrackingEnabled = false;\r
 528             var q = from t in db.Territories\r
 529                     select t;\r
 530             Territory territory = q.FirstOrDefault();\r
 531             Assert.IsNotNull(territory);\r
 532             Assert.IsNull(territory.Region);\r
 533         }\r
 534 \r
 535         [Test]\r
 536         public void C18_GettingProperty_LazyLoaded()\r
 537         {\r
 538             Northwind db = CreateDB();\r
 539             var q = from t in db.Territories\r
 540                     select t;\r
 541             Territory territory = q.FirstOrDefault();\r
 542             Assert.IsNotNull(territory);\r
 543             Assert.IsNotNull(territory.Region);\r
 544         }\r
 545 \r
 546         [Test]\r
 547         public void C19_SelectEmployee_Fluent()\r
 548         {\r
 549             Northwind db = CreateDB();\r
 550             var q = db.GetTable<Territory>()\r
 551                         .Join(db.GetTable<EmployeeTerritory>(), t => t.TerritoryID, l => l.TerritoryID, (t, l) => l)\r
 552                         .Join(db.GetTable<Employee>().Where(e => e.EmployeeID > 0), l => l.EmployeeID, e => e.EmployeeID, (l, e) => e);\r
 553             var employeeCount = q.Count();\r
 554             Assert.Greater(employeeCount, 0, "Expected any employees, got count=" + employeeCount);\r
 555         }\r
 556 \r
 557         /// <summary>\r
 558         /// Test the use of DbLinq as a QueryObject\r
 559         /// http://www.martinfowler.com/eaaCatalog/queryObject.html\r
 560         /// </summary>\r
 561         [Test]\r
 562         public void C20_SelectEmployee_DbLinqAsQueryObject()\r
 563         {\r
 564             Northwind db = CreateDB();\r
 565             IQueryable<Employee> allEmployees = db.GetTable<Employee>();\r
 566 \r
 567             allEmployees = filterByNameOrSurnameContains(db, allEmployees, "an");\r
 568 \r
 569             allEmployees = filterByTerritoryName(db, allEmployees, "Neward");\r
 570 \r
 571             Assert.AreEqual(1, allEmployees.Count());\r
 572         }\r
 573 \r
 574         [Test]\r
 575         public void C21_SelectEmployee_DbLinqAsQueryObjectWithOrderCount()\r
 576         {\r
 577             Northwind db = CreateDB();\r
 578             IQueryable<Employee> allEmployees = db.GetTable<Employee>();\r
 579 \r
 580             allEmployees = filterByOrderCountGreaterThan(db, allEmployees, 50);\r
 581             allEmployees = filterByNameOrSurnameContains(db, allEmployees, "an");\r
 582 \r
 583             allEmployees = filterByTerritoryNames(db, allEmployees, "Neward", "Boston", "Wilton");\r
 584 \r
 585             int employeesCount = allEmployees.ToList().Count;\r
 586 \r
 587             Assert.AreEqual(employeesCount, allEmployees.Count());\r
 588         }\r
 589 \r
 590 \r
 591         private IQueryable<Employee> filterByOrderCountGreaterThan(Northwind db, IQueryable<Employee> allEmployees, int minimumOrderNumber)\r
 592         {\r
 593             return from e in allEmployees.Where(e => e.Orders.Count > minimumOrderNumber) select e;\r
 594         }\r
 595 \r
 596         private IQueryable<Employee> filterByNameOrSurnameContains(Northwind db, IQueryable<Employee> allEmployees, string namePart)\r
 597         {\r
 598             return from e in allEmployees.Where(e => e.FirstName.Contains(namePart) || e.LastName.Contains(namePart)) select e;\r
 599         }\r
 600 \r
 601         private IQueryable<Employee> filterByTerritoryName(Northwind db, IQueryable<Employee> allEmployees, string territoryName)\r
 602         {\r
 603             IQueryable<Territory> territoryRequired = db.GetTable<Territory>().Where(t => t.TerritoryDescription == territoryName);\r
 604             var q = territoryRequired\r
 605                         .Join(db.GetTable<EmployeeTerritory>(), t => t.TerritoryID, l => l.TerritoryID, (t, l) => l)\r
 606                         .Join(allEmployees, l => l.EmployeeID, e => e.EmployeeID, (l, e) => e);\r
 607             return q;\r
 608         }\r
 609 \r
 610         private IQueryable<Employee> filterByTerritoryNames(Northwind db, IQueryable<Employee> allEmployees, params string[] territoryNames)\r
 611         {\r
 612             IQueryable<Territory> territoryRequired = db.GetTable<Territory>().Where(t => territoryNames.Contains(t.TerritoryDescription));\r
 613             var q = territoryRequired\r
 614                         .Join(db.GetTable<EmployeeTerritory>(), t => t.TerritoryID, l => l.TerritoryID, (t, l) => l)\r
 615                         .Join(allEmployees, l => l.EmployeeID, e => e.EmployeeID, (l, e) => e);\r
 616             return q;\r
 617         }\r
 618 \r
 619         [Test]\r
 620         public void C22_SelectEmployee_GetCommandTextWithNoFilter()\r
 621         {\r
 622             Northwind db = CreateDB();\r
 623             IQueryable<Employee> allEmployees = db.GetTable<Employee>();\r
 624             var commandText = db.GetCommand(allEmployees).CommandText;\r
 625             Assert.IsNotNull(commandText);\r
 626         }\r
 627 \r
 628         [Test]\r
 629         public void C23_SelectEmployees()\r
 630         {\r
 631             Northwind db = CreateDB();\r
 632             var allEmployees = db.GetTable<Employee>();\r
 633             int count = 0;\r
 634             foreach (var emp in allEmployees)\r
 635             {\r
 636                 ++count;\r
 637             }\r
 638             Assert.AreEqual(9, count);\r
 639         }\r
 640 \r
 641 #if !DEBUG && (MSSQL && !L2SQL)\r
 642         [Explicit]\r
 643 #endif\r
 644         [Test]\r
 645         public void C24_SelectEmployee_DbLinqAsQueryObjectWithExceptAndImage()\r
 646         {\r
 647             // This fail becouse Employee contains a ndata, ndata is not comparable\r
 648             // and EXCEPT make a distinct on DATA\r
 649             Northwind db = CreateDB();\r
 650             IQueryable<Employee> allEmployees = db.GetTable<Employee>();\r
 651 \r
 652             var toExclude = filterByOrderCountGreaterThan(db, allEmployees, 50);\r
 653             allEmployees = filterByNameOrSurnameContains(db, allEmployees, "a").Except(toExclude);\r
 654 \r
 655             string commandText = db.GetCommand(allEmployees).CommandText;\r
 656 \r
 657             int employeesCount = allEmployees.ToList().Count;\r
 658 \r
 659             Assert.AreEqual(employeesCount, allEmployees.Count());\r
 660         }\r
 661 \r
 662 \r
 663         #endregion\r
 664 \r
 665         #region region D - select first or last - calls IQueryable.Execute instead of GetEnumerator\r
 666         [Test]\r
 667         public void D01_SelectFirstPenID()\r
 668         {\r
 669             Northwind db = CreateDB();\r
 670 \r
 671             var q = from p in db.Products where p.ProductName == "Chai" select p.ProductID;\r
 672             var productID = q.First();\r
 673             Assert.Greater(productID, 0, "Expected penID>0, got " + productID);\r
 674         }\r
 675 \r
 676 \r
 677         /// <summary>\r
 678         /// Reported by pwy.mail in http://code.google.com/p/dblinq2007/issues/detail?id=67\r
 679         /// </summary>\r
 680         [Test]\r
 681         public void D01b_SelectFirstOrDefaultCustomer()\r
 682         {\r
 683             Northwind db = CreateDB();\r
 684             var q =\r
 685               from c in db.Customers\r
 686               select c;\r
 687 \r
 688             Customer customer = q.FirstOrDefault();\r
 689             Assert.IsNotNull(customer.CustomerID);\r
 690         }\r
 691 \r
 692 \r
 693         [Test]\r
 694         public void D02_SelectFirstPen()\r
 695         {\r
 696             Northwind db = CreateDB();\r
 697 \r
 698             var q = from p in db.Products where p.ProductName == "Chai" select p;\r
 699             Product pen = q.First();\r
 700             Assert.IsNotNull(pen, "Expected non-null Product");\r
 701         }\r
 702 \r
 703 #if !DEBUG && MSSQL\r
 704         // L2SQL: System.NotSupportedException : The query operator 'Last' is not supported.\r
 705         [Explicit]\r
 706 #endif\r
 707         [Test]\r
 708         public void D03_SelectLastPenID()\r
 709         {\r
 710             Northwind db = CreateDB();\r
 711 \r
 712             var q = from p in db.Products where p.ProductName == "Chai" select p.ProductID;\r
 713             var productID = q.Last();\r
 714             Assert.Greater(productID, 0, "Expected penID>0, got " + productID);\r
 715         }\r
 716 \r
 717 #if !DEBUG && (POSTGRES || (MSSQL && !L2SQL))\r
 718         [Explicit]\r
 719 #endif\r
 720         [Test]\r
 721         public void D04_SelectProducts_OrderByName()\r
 722         {\r
 723             Northwind db = CreateDB();\r
 724 \r
 725             var q = from p in db.Products orderby p.ProductName select p;\r
 726             string prevProductName = null;\r
 727             foreach (Product p in q)\r
 728             {\r
 729                 if (prevProductName == p.ProductName && p.ProductName.StartsWith("temp_"))\r
 730                     continue; //skip temp rows\r
 731 \r
 732                 if (prevProductName != null)\r
 733                 {\r
 734                     //int compareNames = prevProductName.CompareTo(p.ProductName);\r
 735                     int compareNames = string.Compare(prevProductName, p.ProductName, stringComparisonType);\r
 736                     Assert.Less(compareNames, 0, "When ordering by names, expected " + prevProductName + " to come after " + p.ProductName);\r
 737                 }\r
 738                 prevProductName = p.ProductName;\r
 739             }\r
 740             //Assert.Greater(productID,0,"Expected penID>0, got "+productID);\r
 741         }\r
 742 \r
 743         [Test]\r
 744         public void D05_SelectOrdersForProduct()\r
 745         {\r
 746             Northwind db = CreateDB();\r
 747             //var q = from p in db.Products where "Chai"==p.ProductName select p.Order;\r
 748             //List<Order> penOrders = q.ToList();\r
 749             //Assert.Greater(penOrders.Count,0,"Expected some orders for product 'Chai'");\r
 750 \r
 751             var q =\r
 752                 from o in db.Orders\r
 753                 where o.Customer.City == "London"\r
 754                 select new { c = o.Customer, o };\r
 755 \r
 756             var list1 = q.ToList();\r
 757             foreach (var co in list1)\r
 758             {\r
 759                 Assert.IsNotNull(co.c, "Expected non-null customer");\r
 760                 Assert.IsNotNull(co.c.City, "Expected non-null customer city");\r
 761                 Assert.IsNotNull(co.o, "Expected non-null order");\r
 762             }\r
 763             Assert.Greater(list1.Count, 0, "Expected some orders for London customers");\r
 764         }\r
 765 \r
 766         [Test]\r
 767         public void D06_OrdersFromLondon()\r
 768         {\r
 769             Northwind db = CreateDB();\r
 770             var q =\r
 771                 from o in db.Orders\r
 772                 where o.Customer.City == "London"\r
 773                 select new { c = o.Customer, o };\r
 774 \r
 775             var list1 = q.ToList();\r
 776             foreach (var co in list1)\r
 777             {\r
 778                 Assert.IsNotNull(co.c, "Expected non-null customer");\r
 779                 Assert.IsNotNull(co.o, "Expected non-null order");\r
 780             }\r
 781             Assert.Greater(list1.Count, 0, "Expected some orders for London customers");\r
 782         }\r
 783 \r
 784         [Test]\r
 785         public void D07_OrdersFromLondon_Alt()\r
 786         {\r
 787             //this is a "SelectMany" query:\r
 788             Northwind db = CreateDB();\r
 789 \r
 790             var q =\r
 791                 from c in db.Customers\r
 792                 from o in c.Orders\r
 793                 where c.City == "London"\r
 794                 select new { c, o };\r
 795 \r
 796             Assert.Greater(q.ToList().Count, 0, "Expected some orders for London customers");\r
 797         }\r
 798 \r
 799         [Test]\r
 800         public void D08_Products_Take5()\r
 801         {\r
 802             Northwind db = CreateDB();\r
 803             var q = (from p in db.Products select p).Take(5);\r
 804             List<Product> prods = q.ToList();\r
 805             Assert.AreEqual(5, prods.Count, "Expected five products");\r
 806         }\r
 807 \r
 808         [Test]\r
 809         public void D09_Products_LetterP_Take5()\r
 810         {\r
 811             Northwind db = CreateDB();\r
 812 \r
 813             //var q = (from p in db.Products where p.ProductName.Contains("p") select p).Take(5);\r
 814             var q = db.Products.Where(p => p.ProductName.Contains("p")).Take(5);\r
 815             List<Product> prods = q.ToList();\r
 816 #if POSTGRES || INGRES\r
 817             int expectedCount = 0; //Only 'Toilet Paper'\r
 818 #else\r
 819             int expectedCount = 2; //Oracle, Mysql: 'Toilet Paper' and 'iPod'\r
 820 #endif\r
 821             Assert.Greater(prods.Count, expectedCount, "Expected couple of products with letter 'p'");\r
 822         }\r
 823 \r
 824         [Test]\r
 825         public void D10_Products_LetterP_Desc()\r
 826         {\r
 827             Northwind db = CreateDB();\r
 828 \r
 829             var q = (from p in db.Products\r
 830                      where p.ProductName.Contains("P")\r
 831                      orderby p.ProductID descending\r
 832                      select p\r
 833             ).Take(5);\r
 834             //var q = db.Products.Where( p=>p.ProductName.Contains("p")).Take(5);\r
 835             List<Product> prods = q.ToList();\r
 836             Assert.Greater(prods.Count, 2, "Expected couple of products with letter 'p'");\r
 837 \r
 838             var prodID0 = prods[0].ProductID;\r
 839             var prodID1 = prods[1].ProductID;\r
 840             Assert.Greater(prodID0, prodID1, "Sorting is broken");\r
 841         }\r
 842 \r
 843         [Test]\r
 844         public void D11_Products_DoubleWhere()\r
 845         {\r
 846             Northwind db = CreateDB();\r
 847             var q1 = db.Products.Where(p => p.ProductID > 1).Where(q => q.ProductID < 10);\r
 848             int count1 = q1.Count();\r
 849         }\r
 850 \r
 851 \r
 852 #if !DEBUG && (SQLITE || MSSQL)\r
 853         // L2SQL: System.InvalidOperationException : The type 'Test_NUnit_MsSql_Strict.ReadTest+Northwind1+CustomerDerivedClass' is not mapped as a Table.\r
 854         [Explicit]\r
 855 #endif\r
 856         [Test]\r
 857         public void D12_SelectDerivedClass()\r
 858         {\r
 859             Northwind dbo = CreateDB();\r
 860             Northwind1 db = new Northwind1(dbo.Connection);\r
 861 \r
 862             var derivedCustomer = (from c in db.ChildCustomers\r
 863                                    where c.City == "London"\r
 864                                    select c).First();\r
 865             Assert.IsTrue(derivedCustomer.City == "London");\r
 866         }\r
 867 \r
 868         public class Northwind1 : Northwind\r
 869         {\r
 870             public Northwind1(System.Data.IDbConnection connection)\r
 871                 : base(connection)\r
 872             { }\r
 873 \r
 874             public class CustomerDerivedClass : Customer { }\r
 875             public class CustomerDerivedClass2 : CustomerDerivedClass { }\r
 876 \r
 877             public DataLinq.Table<CustomerDerivedClass> ChildCustomers\r
 878             {\r
 879                 get { return base.GetTable<CustomerDerivedClass>(); }\r
 880             }\r
 881         }\r
 882 \r
 883 \r
 884         [Test(Description = "Calls ExecuteQuery<> to store result into object type property")]\r
 885         // note: for PostgreSQL requires database with lowercase names, NorthwindReqular.SQL\r
 886         public void D13_ExecuteQueryObjectProperty()\r
 887         {\r
 888             Northwind db = CreateDB();\r
 889 \r
 890             var res = db.ExecuteQuery<Chai>(@"SELECT [ProductID] AS ChaiId FROM [Products] WHERE\r
 891               [ProductName] ='Chai'").Single();\r
 892             Assert.AreEqual(1, res.ChaiId);\r
 893         }\r
 894 \r
 895         class Chai\r
 896         {\r
 897             internal int ChaiId;\r
 898         }\r
 899 \r
 900         [Test]\r
 901         public void D14_ProjectedProductList()\r
 902         {\r
 903             Northwind db = CreateDB();\r
 904 \r
 905             var query = from pr in db.Products\r
 906                         select new\r
 907                         {\r
 908                             pr.ProductID,\r
 909                             pr.ProductName,\r
 910                             pr.Supplier,         // exception!\r
 911                             pr.UnitPrice,        // exception!\r
 912                             pr.UnitsInStock,\r
 913                             pr.UnitsOnOrder\r
 914                         };\r
 915             //WARNING - as of 2008Apr, we return Suppliers without blowing up, but they need to be live\r
 916             var list = query.ToList();\r
 917             Assert.IsTrue(list.Count > 0);\r
 918             foreach (var item in list)\r
 919             {\r
 920                 Assert.IsTrue(item.Supplier != null);\r
 921             }\r
 922         }\r
 923 \r
 924 #if !DEBUG && (SQLITE || MSSQL)\r
 925         // L2SQL: System.InvalidOperationException : The type 'Test_NUnit_MsSql_Strict.ReadTest+NorthwindDupl+CustomerDerivedClass' is not mapped as a Table.\r
 926         [Explicit]\r
 927 #endif\r
 928         [Test]\r
 929         public void D15_DuplicateProperty()\r
 930         {\r
 931             Northwind dbo = CreateDB();\r
 932             NorthwindDupl db = new NorthwindDupl(dbo.Connection);\r
 933             var derivedCustomer = (from c in db.ChildCustomers\r
 934                                    where c.City == "London"\r
 935                                    select c).First();\r
 936             Assert.IsTrue(derivedCustomer.City == "London");\r
 937         }\r
 938 \r
 939         public class NorthwindDupl : Northwind\r
 940         {\r
 941             public NorthwindDupl(System.Data.IDbConnection connection)\r
 942                 : base(connection)\r
 943             { }\r
 944 \r
 945             public class CustomerDerivedClass : Customer\r
 946             {\r
 947                 private string city;\r
 948                 [Column(Storage = "city", Name = "city")]\r
 949                 public new string City\r
 950                 {\r
 951                     get\r
 952                     {\r
 953                         return city;\r
 954                     }\r
 955                     set\r
 956                     {\r
 957                         if (value != city)\r
 958                         {\r
 959                             city = value;\r
 960                         }\r
 961                     }\r
 962                 }\r
 963             }\r
 964 \r
 965             public DataLinq.Table<CustomerDerivedClass> ChildCustomers\r
 966             {\r
 967                 get { return base.GetTable<CustomerDerivedClass>(); }\r
 968             }\r
 969         }\r
 970 \r
 971         /// <summary>\r
 972         /// DbLinq must use field and should not look to setter.\r
 973         /// </summary>\r
 974         // PC: is this specified somewhere?\r
 975         [Test]\r
 976         public void D16_CustomerWithoutSetter()\r
 977         {\r
 978             Assert.Ignore("See if this is specified");\r
 979             Northwind dbo = CreateDB();\r
 980             NorthwindAbstractBaseClass db = new NorthwindAbstractBaseClass(dbo.Connection);\r
 981             var Customer = (from c in db.ChildCustomers\r
 982                             where c.City == "London"\r
 983                             select c).First();\r
 984             Assert.IsTrue(Customer.City == "London");\r
 985         }\r
 986 \r
 987 \r
 988         abstract class AbstractCustomer\r
 989         {\r
 990             public abstract string City { get; }\r
 991         }\r
 992 \r
 993         class NorthwindAbstractBaseClass : Northwind\r
 994         {\r
 995             public NorthwindAbstractBaseClass(System.Data.IDbConnection connection)\r
 996                 : base(connection) { }\r
 997 \r
 998             [Table(Name = "customers")]\r
 999             public class Customer : AbstractCustomer\r
1000             {\r
1001                 string city;\r
1002                 [Column(Storage = "city", Name = "city")]\r
1003                 public override string City\r
1004                 {\r
1005                     get\r
1006                     {\r
1007                         return city;\r
1008                     }\r
1009                 }\r
1010             }\r
1011 \r
1012             [Table(Name = "customers")]\r
1013             public class Customer2 : Customer { }\r
1014 \r
1015             public DataLinq.Table<Customer2> ChildCustomers\r
1016             {\r
1017                 get { return base.GetTable<Customer2>(); }\r
1018             }\r
1019         }\r
1020 \r
1021 \r
1022         #endregion\r
1023 \r
1024         [Test]\r
1025         public void SqlInjectionAttack()\r
1026         {\r
1027             var db = CreateDB();\r
1028             var q = db.Customers.Where(c => c.ContactName == "'; DROP TABLE DoesNotExist; --");\r
1029             Assert.AreEqual(0, q.Count());\r
1030         }\r
1031               \r
1032 #if POSTGRES || MSSQL\r
1033         [Test]\r
1034         public void Storage01()\r
1035         {\r
1036             var db = CreateDB();\r
1037             var q = db.NoStorageCategories.Where(c => c.CategoryID == 1);\r
1038             var r = q.First();\r
1039             Assert.AreEqual(1, q.Count());\r
1040             Assert.AreEqual(1, r.CategoryID);\r
1041             Assert.IsTrue(r.propertyInvoked_CategoryName);     \r
1042             Assert.IsFalse(r.propertyInvoked_Description);     \r
1043         }\r
1044 #endif    \r
1045     }\r
1046 }\r