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