mcs/class/System.Data.Linq/src/DbLinq/Test/Providers/WriteTest.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 \r
  27 using System;\r
  28 using System.Collections.Generic;\r
  29 using System.Text;\r
  30 using System.Linq;\r
  31 using System.Linq.Expressions;\r
  32 \r
  33 using NUnit.Framework;\r
  34 using Test_NUnit;\r
  35 using System.ComponentModel;\r
  36 using System.Data.Linq.Mapping;\r
  37 \r
  38 using nwind;\r
  39 \r
  40 #if MONO_STRICT\r
  41 using System.Data.Linq;\r
  42 #else\r
  43 using DbLinq.Data.Linq;\r
  44 #endif\r
  45 \r
  46 #if ORACLE\r
  47 using Id = System.Decimal;\r
  48 #else\r
  49 using Id = System.Int32;\r
  50 #endif\r
  51 \r
  52 // test ns \r
  53 #if MYSQL\r
  54     namespace Test_NUnit_MySql\r
  55 #elif ORACLE && ODP\r
  56     namespace Test_NUnit_OracleODP\r
  57 #elif ORACLE\r
  58     namespace Test_NUnit_Oracle\r
  59 #elif POSTGRES\r
  60     namespace Test_NUnit_PostgreSql\r
  61 #elif SQLITE\r
  62     namespace Test_NUnit_Sqlite\r
  63 #elif INGRES\r
  64     namespace Test_NUnit_Ingres\r
  65 #elif MSSQL && MONO_STRICT\r
  66     namespace Test_NUnit_MsSql_Strict\r
  67 #elif MSSQL\r
  68     namespace Test_NUnit_MsSql\r
  69 #elif FIREBIRD\r
  70     namespace Test_NUnit_Firebird\r
  71 #endif\r
  72 {\r
  73     [SetUpFixture]\r
  74     public class WriteTestSetup : TestBase\r
  75     {\r
  76         [SetUp]\r
  77         public void TestSetup()\r
  78         {\r
  79             Northwind db = CreateDB();\r
  80             // "[Products]" gets converted to "Products".\r
  81             //This is a DbLinq-defined escape sequence, by Pascal.\r
  82             //db.ExecuteCommand("DELETE FROM [Products] WHERE [ProductName] like 'temp%'");\r
  83 \r
  84             var deleteProducts = db.Products.Where(p => p.ProductName.StartsWith("temp")).ToList();\r
  85             db.Products.DeleteAllOnSubmit(deleteProducts);\r
  86 \r
  87             var deleteCategories = db.Categories.Where(c => c.CategoryName.StartsWith("temp")).ToList();\r
  88             db.Categories.DeleteAllOnSubmit(deleteCategories);\r
  89 \r
  90             db.SubmitChanges();\r
  91         }\r
  92     }\r
  93 \r
  94     [TestFixture]\r
  95     public class WriteTest : TestBase\r
  96     {\r
  97 \r
  98         #region Tests 'E' test live object cache\r
  99         [Test]\r
 100         public void E1_LiveObjectsAreUnique()\r
 101         {\r
 102             //grab an object twice, make sure we get the same object each time\r
 103             Northwind db = CreateDB();\r
 104             var q = from p in db.Products select p;\r
 105             Product pen1 = q.First();\r
 106             Product pen2 = q.First();\r
 107             string uniqueStr = "Unique" + Environment.TickCount;\r
 108             pen1.QuantityPerUnit = uniqueStr;\r
 109             bool isSameObject1 = pen2.QuantityPerUnit == uniqueStr;\r
 110             Assert.IsTrue(isSameObject1, "Expected pen1 and pen2 to be the same live object, but their fields are different");\r
 111             object oPen1 = pen1;\r
 112             object oPen2 = pen2;\r
 113             bool isSameObject2 = oPen1 == oPen2;\r
 114             Assert.IsTrue(isSameObject2, "Expected pen1 and pen2 to be the same live object, but their fields are different");\r
 115         }\r
 116 \r
 117         [Test]\r
 118         public void E2_LiveObjectsAreUnique_Scalar()\r
 119         {\r
 120             //grab an object twice, make sure we get the same object each time\r
 121             Northwind db = CreateDB();\r
 122             var q = from p in db.Products select p;\r
 123             Product pen1 = q.First(p => p.ProductName == "Pen");\r
 124             Product pen2 = q.Single(p => p.ProductName == "Pen");\r
 125             bool isSame = object.ReferenceEquals(pen1, pen2);\r
 126             Assert.IsTrue(isSame, "Expected pen1 and pen2 to be the same live object");\r
 127         }\r
 128 \r
 129 #if MYSQL && USE_ALLTYPES\r
 130         [Test]\r
 131         public void E3_UpdateEnum()\r
 132         {\r
 133             Northwind db = CreateDB();\r
 134 \r
 135             var q = from at in db.Alltypes where at.int_ == 1 select at;\r
 136 \r
 137             Alltype row = q.First();\r
 138             DbLinq_EnumTest newValue = row.DbLinq_EnumTest == DbLinq_EnumTest.BB\r
 139                 ? DbLinq_EnumTest.CC\r
 140                 : DbLinq_EnumTest.BB;\r
 141 \r
 142             row.DbLinq_EnumTest = newValue;\r
 143 \r
 144             db.SubmitChanges();\r
 145         }\r
 146 #endif\r
 147         #endregion\r
 148 \r
 149 \r
 150         #region Tests 'G' do insertion\r
 151         private int insertProduct_priv()\r
 152         {\r
 153             Northwind db = CreateDB();\r
 154 \r
 155             Product newProd = new Product();\r
 156             newProd.CategoryID = db.Categories.First().CategoryID;\r
 157             newProd.ProductName = "Temp." + Environment.TickCount;\r
 158             newProd.QuantityPerUnit = "33 1/2";\r
 159             db.Products.InsertOnSubmit(newProd);\r
 160             db.SubmitChanges();\r
 161             Assert.Greater(newProd.ProductID, 0, "After insertion, ProductID should be non-zero");\r
 162             //Assert.IsFalse(newProd.IsModified, "After insertion, Product.IsModified should be false");\r
 163             return (int)newProd.ProductID; //this test cab be used from delete tests\r
 164         }\r
 165 \r
 166         [Test]\r
 167         public void G1_InsertProduct()\r
 168         {\r
 169             insertProduct_priv();\r
 170         }\r
 171 \r
 172         [Test]\r
 173         public void G2_DeleteTest()\r
 174         {\r
 175             int insertedID = insertProduct_priv();\r
 176             Assert.Greater(insertedID, 0, "DeleteTest cannot operate if row was not inserted");\r
 177 \r
 178             Northwind db = CreateDB();\r
 179 \r
 180             var q = from p in db.Products where p.ProductID == insertedID select p;\r
 181             List<Product> insertedProducts = q.ToList();\r
 182             foreach (Product insertedProd in insertedProducts)\r
 183             {\r
 184                 db.Products.DeleteOnSubmit(insertedProd);\r
 185             }\r
 186             db.SubmitChanges();\r
 187 \r
 188             int numLeft = (from p in db.Products where p.ProductID == insertedID select p).Count();\r
 189             Assert.AreEqual(numLeft, 0, "After deletion, expected count of Products with ID=" + insertedID + " to be zero, instead got " + numLeft);\r
 190         }\r
 191 \r
 192         [Test]\r
 193         public void G3_DeleteTest()\r
 194         {\r
 195             int insertedID = insertProduct_priv();\r
 196             Assert.Greater(insertedID, 0, "DeleteTest cannot operate if row was not inserted");\r
 197 \r
 198             Northwind db = CreateDB();\r
 199 \r
 200             var q = from p in db.Products where p.ProductID == insertedID select p;\r
 201             List<Product> insertedProducts = q.ToList();\r
 202             foreach (Product insertedProd in insertedProducts)\r
 203             {\r
 204                 db.Products.DeleteOnSubmit(insertedProd);\r
 205             }\r
 206             db.SubmitChanges();\r
 207 \r
 208             int numLeft = (from p in db.Products where p.ProductID == insertedID select p).Count();\r
 209             Assert.AreEqual(numLeft, 0, "After deletion, expected count of Products with ID=" + insertedID + " to be zero, instead got " + numLeft);\r
 210         }\r
 211 \r
 212         [Test]\r
 213         public void G4_DuplicateSubmitTest()\r
 214         {\r
 215             Northwind db = CreateDB();\r
 216             int productCount1 = db.Products.Count();\r
 217 #if INGRES && !MONO_STRICT\r
 218             Product p_temp = new Product { ProductName = "temp_g4", Discontinued = 0 };\r
 219 #else\r
 220             Product p_temp = new Product { ProductName = "temp_g4", Discontinued = false };\r
 221 #endif\r
 222             db.Products.InsertOnSubmit(p_temp);\r
 223             db.SubmitChanges();\r
 224             db.SubmitChanges();\r
 225             int productCount2 = db.Products.Count();\r
 226             Assert.IsTrue(productCount2 == productCount1 + 1, "Expected product count to grow by one");\r
 227         }\r
 228 \r
 229         /// <summary>\r
 230         /// there is a bug in v0.14 where fields cannot be updated to be null.\r
 231         /// </summary>\r
 232         [Test]\r
 233         public void G5_SetFieldToNull()\r
 234         {\r
 235             string productName = "temp_G5_" + Environment.TickCount;\r
 236             Northwind db = CreateDB();\r
 237 #if ORACLE\r
 238             //todo fix Oracle\r
 239             Product p1 = new Product { ProductName = productName, Discontinued = false, UnitPrice = 11 };\r
 240 #elif INGRES && !MONO_STRICT\r
 241             Product p1 = new Product { ProductName = productName, Discontinued = 0, UnitPrice = 11m };\r
 242 #else\r
 243             Product p1 = new Product { ProductName = productName, Discontinued = false, UnitPrice = 11m };\r
 244 #endif\r
 245             db.Products.InsertOnSubmit(p1);\r
 246             db.SubmitChanges();\r
 247 \r
 248             p1.UnitPrice = null;\r
 249             db.SubmitChanges();\r
 250 \r
 251             Northwind db3 = CreateDB();\r
 252             Product p3 = db3.Products.Single(p => p.ProductName == productName);\r
 253             Assert.IsNull(p3.UnitPrice);\r
 254         }\r
 255 \r
 256         /// <summary>\r
 257         /// there is a bug in v0.14 where table Customers cannot be updated,\r
 258         /// because quotes where missing around the primaryKey in the UPDATE statement.\r
 259         /// </summary>\r
 260         [Test]\r
 261         public void G6_UpdateTableWithStringPK()\r
 262         {\r
 263             Northwind db = CreateDB();\r
 264             Customer BT = db.Customers.Single(c => c.CustomerID == "BT___");\r
 265             BT.Country = "U.K.";\r
 266             db.SubmitChanges();\r
 267         }\r
 268 \r
 269         [Test]\r
 270         public void G7_InsertTableWithStringPK()\r
 271         {\r
 272             Northwind db = CreateDB();\r
 273             db.ExecuteCommand("DELETE FROM [Customers] WHERE [CustomerID]='TEMP_'");\r
 274 \r
 275             Customer custTemp = new Customer\r
 276             {\r
 277                 CustomerID = "TEMP_",\r
 278                 CompanyName = "Magellan",\r
 279                 ContactName = "Antonio Pigafetta",\r
 280                 City = "Lisboa",\r
 281             };\r
 282             db.Customers.InsertOnSubmit(custTemp);\r
 283             db.SubmitChanges();\r
 284         }\r
 285 \r
 286         [Test]\r
 287         public void G8_DeleteTableWithStringPK()\r
 288         {\r
 289             Northwind db = CreateDB();\r
 290             Customer cust = (from c in db.Customers\r
 291                              where c.CustomerID == "TEMP_"\r
 292                              select c).Single();\r
 293             db.Customers.DeleteOnSubmit(cust);\r
 294             db.SubmitChanges();\r
 295         }\r
 296 \r
 297         [Test]\r
 298         public void G9_UpdateOnlyChangedProperty()\r
 299         {\r
 300             Northwind db = CreateDB();\r
 301             var cust = (from c in db.Customers\r
 302                         select\r
 303                         new Customer\r
 304                         {\r
 305                             CustomerID = c.CustomerID,\r
 306                             City = c.City\r
 307 \r
 308                         }).First();\r
 309 \r
 310             var old = cust.City;\r
 311             cust.City = "Tallinn";\r
 312             db.SubmitChanges();\r
 313             db.SubmitChanges(); // A second call does not update anything\r
 314 \r
 315             //exposes bug:\r
 316             //Npgsql.NpgsqlException was unhandled\r
 317             //Message="ERROR: 23502: null value in column \"companyname\" violates not-null constraint" \r
 318             cust.City = old;\r
 319             db.SubmitChanges();\r
 320 \r
 321         }\r
 322 \r
 323 #if POSTGRES\r
 324 \r
 325         public class Northwind1 : Northwind\r
 326         {\r
 327             public Northwind1(System.Data.IDbConnection connection)\r
 328                 : base(connection) { }\r
 329 \r
 330             [System.Data.Linq.Mapping.Table(Name = "cust1")]\r
 331             public class Cust1\r
 332             {\r
 333                 \r
 334                 string _customerid;\r
 335 \r
 336                 [System.Data.Linq.Mapping.Column(Storage = "_customerid",\r
 337                 Name = "customerid", IsPrimaryKey = true,\r
 338                 DbType = "char(10)",\r
 339                 IsDbGenerated = true,\r
 340                 Expression = "nextval('seq8')")]\r
 341                 public string CustomerId\r
 342                 {\r
 343                     get { return _customerid; }\r
 344                     set { _customerid = value; }\r
 345                 }\r
 346 \r
 347                 // Dummy property is required only as workaround over empty insert list bug\r
 348                 // If this bug is fixed this may be removed\r
 349                 string _dummy;\r
 350                 [System.Data.Linq.Mapping.Column(Storage = "_dummy",\r
 351                 DbType = "text", Name = "dummy")]\r
 352                 public string Dummy\r
 353                 {\r
 354                     get;\r
 355                     set;\r
 356                 }\r
 357 \r
 358             }\r
 359 \r
 360             public Table<Cust1> Cust1s\r
 361             {\r
 362 \r
 363                 get\r
 364                 {\r
 365                     return base.GetTable<Cust1>();\r
 366                 }\r
 367             }\r
 368         }\r
 369 \r
 370         [Test]\r
 371         public void G10_InsertCharSerialPrimaryKey()\r
 372         {\r
 373             Northwind dbo = CreateDB();\r
 374             Northwind1 db = new Northwind1(dbo.Connection);\r
 375             try\r
 376             {\r
 377                 db.ExecuteCommand(\r
 378                     @"create sequence seq8;\r
 379 create temp table cust1 ( CustomerID char(10) DEFAULT nextval('seq8'),\r
 380 dummy text\r
 381 );\r
 382 ");\r
 383 \r
 384                 Table<Northwind1.Cust1> cust1s =\r
 385                     db.GetTable<Northwind1.Cust1>();\r
 386 \r
 387                 var cust1 = new Northwind1.Cust1();\r
 388                 cust1.Dummy = "";\r
 389                 db.Cust1s.InsertOnSubmit(cust1);\r
 390                 db.SubmitChanges();\r
 391                 Assert.IsNotNull(cust1.CustomerId);\r
 392             }\r
 393             finally\r
 394             {\r
 395                 try { db.ExecuteCommand("drop table cust1;"); }\r
 396                 catch { }\r
 397                 try { db.ExecuteCommand("drop sequence seq8;"); }\r
 398                 catch { }\r
 399             }\r
 400         }\r
 401 #endif\r
 402 \r
 403         public class NorthwindG11 : Northwind\r
 404         {\r
 405             public NorthwindG11(System.Data.IDbConnection connection)\r
 406                 : base(connection) { }\r
 407 \r
 408             [Table(Name = "rid")]\r
 409             public class Rid : INotifyPropertyChanged\r
 410             {\r
 411 \r
 412                 protected int _id;\r
 413 \r
 414                 protected int _reanr;\r
 415 \r
 416 \r
 417 #if INGRES\r
 418           [System.Data.Linq.Mapping.Column(Storage = "_id", Name = "id", DbType = "integer", IsPrimaryKey = true, IsDbGenerated = true, Expression = "next value for rid_id1_seq")]\r
 419 #else\r
 420                 [System.Data.Linq.Mapping.Column(Storage = "_id", Name = "id", DbType = "integer", IsPrimaryKey = true, IsDbGenerated = true, Expression = "nextval('rid_id1_seq')")]\r
 421 #endif\r
 422                 public int Id\r
 423                 {\r
 424                     get { return _id; }\r
 425                     set\r
 426                     {\r
 427                         _id = value;\r
 428                         OnPropertyChanged("Id");\r
 429                     }\r
 430                 }\r
 431 \r
 432 #if INGRES\r
 433           [System.Data.Linq.Mapping.Column(Storage = "_reanr", Name = "reanr", DbType = "integer", IsDbGenerated = true, CanBeNull = false, Expression = "next value for rid_reanr_seq")]\r
 434 #else\r
 435                 [System.Data.Linq.Mapping.Column(Storage = "_reanr", Name = "reanr", DbType = "integer", IsDbGenerated = true, CanBeNull = false, Expression = "nextval('rid_reanr_seq')")]\r
 436 #endif\r
 437                 public int Reanr\r
 438                 {\r
 439                     get { return _reanr; }\r
 440                     set\r
 441                     {\r
 442                         _reanr = value;\r
 443                         OnPropertyChanged("Reanr");\r
 444                     }\r
 445                 }\r
 446 \r
 447 \r
 448                 #region INotifyPropertyChanged handling\r
 449                 public event PropertyChangedEventHandler PropertyChanged;\r
 450                 protected virtual void OnPropertyChanged(string propertyName)\r
 451                 {\r
 452                     if (PropertyChanged != null)\r
 453                     {\r
 454                         PropertyChanged(this, new PropertyChangedEventArgs(propertyName));\r
 455                     }\r
 456                 }\r
 457                 #endregion\r
 458 \r
 459             }\r
 460 \r
 461 \r
 462             public Table<Rid> Rids\r
 463             {\r
 464                 get\r
 465                 {\r
 466                     return base.GetTable<Rid>();\r
 467                 }\r
 468             }\r
 469         }\r
 470 \r
 471 #if (POSTGRES || INGRES) && !MONO_STRICT\r
 472 \r
 473         [Test]\r
 474         public void G11_TwoSequencesInTable()\r
 475         {\r
 476             Northwind dbo = CreateDB();\r
 477             NorthwindG11 db = new NorthwindG11(dbo.Connection);\r
 478 \r
 479             db.ExecuteCommand(@"create sequence rid_id1_seq");\r
 480             db.ExecuteCommand(@"create sequence rid_reanr_seq");\r
 481 #if INGRES\r
 482             db.ExecuteCommand(@"create table Rid ( id int primary key DEFAULT rid_id1_seq.nextval, reanr int DEFAULT rid_reanr_seq.nextval)");\r
 483 #else\r
 484             db.ExecuteCommand(@"create temp table Rid ( id int primary key DEFAULT nextval('rid_id1_seq'), reanr int DEFAULT nextval('rid_reanr_seq'))");\r
 485 #endif\r
 486             DbLinq.Data.Linq.Table<NorthwindG11.Rid> Rids = db.GetTable<NorthwindG11.Rid>();\r
 487 \r
 488             var Rid = new NorthwindG11.Rid();\r
 489             Rid.Reanr = 22;\r
 490             Exception e = null;\r
 491             db.Rids.InsertOnSubmit(Rid);\r
 492 \r
 493             Rid = new NorthwindG11.Rid();\r
 494             Rid.Reanr = 23;\r
 495             db.Rids.InsertOnSubmit(Rid);\r
 496             try\r
 497             {\r
 498                 db.SubmitChanges();\r
 499             }\r
 500             catch (Exception ex)\r
 501             {\r
 502                 e = ex;\r
 503             }\r
 504             db.ExecuteCommand("drop table rid");\r
 505             db.ExecuteCommand("drop sequence rid_reanr_seq");\r
 506             db.ExecuteCommand("drop sequence rid_id1_seq");\r
 507             if (e != null)\r
 508             {\r
 509                 throw e;\r
 510             }\r
 511             Assert.AreEqual(Rid.Id, 2);\r
 512             Assert.AreEqual(Rid.Reanr, 23);\r
 513         }\r
 514 \r
 515 #endif\r
 516 \r
 517         [Test]\r
 518         public void G12_EmptyInsertList()\r
 519         {\r
 520             Northwind db = CreateDB();\r
 521             Region newRegion = new Region() { RegionDescription = "" }; // RegionDescription must be non-null\r
 522             db.Regions.InsertOnSubmit(newRegion);\r
 523             db.SubmitChanges();\r
 524             Assert.IsNotNull(newRegion.RegionID);\r
 525             db.Regions.DeleteOnSubmit(newRegion);\r
 526             db.SubmitChanges();\r
 527         }\r
 528 \r
 529         [Test]\r
 530         public void G13_ProvidedAutoGeneratedColumn()\r
 531         {\r
 532             Northwind db = CreateDB();\r
 533             Category newCat = new Category();\r
 534             newCat.CategoryID = 999;\r
 535             newCat.CategoryName = "test";\r
 536             db.Categories.InsertOnSubmit(newCat);\r
 537             db.SubmitChanges();\r
 538             Assert.AreEqual(999, newCat.CategoryID);\r
 539             // then, load our object\r
 540             var checkCat = (from c in db.Categories where c.CategoryID == newCat.CategoryID select c).Single();\r
 541             Assert.AreEqual(999, checkCat.CategoryID);\r
 542             // remove the whole thing\r
 543             db.Categories.DeleteOnSubmit(newCat);\r
 544             db.SubmitChanges();\r
 545         }\r
 546 \r
 547 \r
 548         [Test]\r
 549         public void G14_AutoGeneratedSupplierIdAndCompanyName()\r
 550         {\r
 551             Northwind db = CreateDB();\r
 552             Supplier supplier = new Supplier();\r
 553             db.Suppliers.InsertOnSubmit(supplier);\r
 554             db.SubmitChanges();\r
 555             Assert.IsNotNull(supplier.SupplierID);\r
 556             Assert.AreEqual(null, supplier.CompanyName);\r
 557             db.Suppliers.DeleteOnSubmit(supplier);\r
 558             db.SubmitChanges();\r
 559         }\r
 560 \r
 561 \r
 562         [Test]\r
 563         [ExpectedException(typeof(InvalidOperationException))]\r
 564         public void G15_CustomerIdUpdate()\r
 565         {\r
 566             //if you run this against Microsoft Linq-to-Sql, it throws an InvalidOperationEx:\r
 567             //{"Value of member 'CustomerID' of an object of type 'Customers' changed. \r
 568             //A member defining the identity of the object cannot be changed.\r
 569             //Consider adding a new object with new identity and deleting the existing one instead."}\r
 570 \r
 571             Northwind db = CreateDB();\r
 572             Customer c1 = (from c in db.Customers\r
 573                            where c.CustomerID == "AIRBU"\r
 574                            select c).Single();\r
 575             c1.CustomerID = "TEMP";\r
 576             db.SubmitChanges();\r
 577             Customer c2 = (from c in db.Customers\r
 578                            where c.CustomerID == "TEMP"\r
 579                            select c).Single();\r
 580 \r
 581             c2.CustomerID = "AIRBU";\r
 582             db.SubmitChanges();\r
 583         }\r
 584 \r
 585         /// <summary>\r
 586         /// Quote from MSDN:\r
 587         /// If the object requested by the query is easily identifiable as one\r
 588         /// already retrieved, no query is executed. The identity table acts as a cache\r
 589         /// of all previously retrieved objects\r
 590 \r
 591         /// From Matt Warren: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=345635&SiteID=1\r
 592         /// The cache is checked when the query is a simple table.Where(pred) or table.First(pred) where the \r
 593         /// predicate refers only to the primary key.  Otherwise the query is always sent and the cache only checked \r
 594         /// after the results are retrieved. \r
 595         /// The DLINQ cache is not distributed or shared, it is local and contained within the context.  It is only a \r
 596         /// referential identity cache used to guarantee that two reads of the same entity return the same instance. \r
 597         /// You are not expected to hold the cache for an extended duration (except possibly for a client scenario), \r
 598         /// or share it across threads, processes, or machines in a cluster. \r
 599         /// </summary>\r
 600         [Test]\r
 601         public void G16_CustomerCacheHit()\r
 602         {\r
 603             Northwind db = CreateDB();\r
 604             Customer c1 = new Customer() { CustomerID = "temp", CompanyName = "Test", ContactName = "Test" };\r
 605             db.Customers.InsertOnSubmit(c1);\r
 606             db.SubmitChanges();\r
 607             db.ExecuteCommand("delete from customers WHERE CustomerID='temp'");\r
 608 \r
 609             var res = (from c in db.Customers\r
 610                        where c.CustomerID == "temp"\r
 611                        select c).Single();\r
 612         }\r
 613 \r
 614 \r
 615 \r
 616         [Test]\r
 617         public void G17_LocalPropertyUpdate()\r
 618         {\r
 619             Northwind dbo = CreateDB();\r
 620             NorthwindLocalProperty db = new NorthwindLocalProperty(dbo.Connection);\r
 621             var det = db.OrderDetailWithSums.First();\r
 622             det.ChangeQuantity();\r
 623             Assert.AreEqual(0, db.GetChangeSet().Updates.Count);\r
 624             db.SubmitChanges();\r
 625         }\r
 626 \r
 627 \r
 628         class NorthwindLocalProperty : Northwind\r
 629         {\r
 630             internal NorthwindLocalProperty(System.Data.IDbConnection connection)\r
 631                 : base(connection) { }\r
 632 \r
 633             internal Table<OrderDetailWithSum> OrderDetailWithSums\r
 634             {\r
 635                 get\r
 636                 {\r
 637                     return GetTable<OrderDetailWithSum>();\r
 638                 }\r
 639             }\r
 640 \r
 641         }\r
 642 \r
 643         class OrderDetailWithSum : OrderDetail, INotifyPropertyChanged\r
 644         {\r
 645             public event PropertyChangedEventHandler PropertyChanged;\r
 646             protected virtual void OnPropertyChanged(string propertyName)\r
 647             {\r
 648                 if (PropertyChanged != null)\r
 649                 {\r
 650                     PropertyChanged(this, new PropertyChangedEventArgs(propertyName));\r
 651                 }\r
 652             }\r
 653 \r
 654             internal decimal? Sum\r
 655             {\r
 656                 get\r
 657                 {\r
 658                     return Quantity * UnitPrice;\r
 659                 }\r
 660             }\r
 661 \r
 662             internal void ChangeQuantity()\r
 663             {\r
 664                 OnPropertyChanged("Sum");\r
 665             }\r
 666         }\r
 667 \r
 668 \r
 669         [Test]\r
 670         public void G18_UpdateWithAttach()\r
 671         {\r
 672             List<Order> list;\r
 673             using (Northwind db = CreateDB())\r
 674                 list = db.Orders.ToList();\r
 675 \r
 676             using (Northwind db = CreateDB())\r
 677             {\r
 678                 var tbl = db.GetTable<Order>();\r
 679                 foreach (var order in list)\r
 680                 {\r
 681                     if (order.Freight == null)\r
 682                         continue;\r
 683                     tbl.Attach(order);\r
 684                     order.Freight += 1;\r
 685                 }\r
 686                 db.SubmitChanges();\r
 687             }\r
 688 \r
 689             using (Northwind db = CreateDB())\r
 690             {\r
 691                 var tbl = db.GetTable<Order>();\r
 692                 foreach (var order in list)\r
 693                 {\r
 694                     if (order.Freight == null)\r
 695                         continue;\r
 696                     tbl.Attach(order);\r
 697                     order.Freight -= 1;\r
 698                 }\r
 699                 db.SubmitChanges();\r
 700             }\r
 701         }\r
 702 \r
 703 \r
 704         [Test]\r
 705         public void G19_ExistingCustomerCacheHit()\r
 706         {\r
 707             Northwind db = CreateDB();\r
 708             string id = "AIRBU";\r
 709             Customer c1 = (from c in db.Customers\r
 710                            where id == c.CustomerID\r
 711                            select c).Single();\r
 712 \r
 713             db.Connection.ConnectionString = null;\r
 714 \r
 715             var x = db.Customers.Single(c => id == c.CustomerID);\r
 716         }\r
 717 \r
 718 \r
 719         [Test]\r
 720         public void G20_CustomerCacheHitComparingToLocalVariable()\r
 721         {\r
 722              Northwind db = CreateDB();\r
 723              try\r
 724              {\r
 725                 Customer c1 = new Customer() { CustomerID = "temp", CompanyName = "Test", ContactName = "Test" };\r
 726                 db.Customers.InsertOnSubmit(c1);\r
 727                 db.SubmitChanges();\r
 728 \r
 729                 string id = "temp";\r
 730                 var res = from c in db.Customers\r
 731                           where c.CustomerID == id\r
 732                           select c;\r
 733 \r
 734                 Assert.AreEqual(1, res.Count(), "#1");\r
 735 \r
 736                 db.ExecuteCommand("delete from customers WHERE CustomerID='temp'");\r
 737 \r
 738                 res = from c in db.Customers\r
 739                       where c.CustomerID == id\r
 740                       select c;\r
 741                 Assert.AreEqual(0, res.Count(), "#2");\r
 742             }\r
 743             finally\r
 744             {\r
 745                 db.ExecuteCommand("delete from customers WHERE CustomerID='temp'");\r
 746 \r
 747             }\r
 748         }\r
 749 \r
 750         #endregion\r
 751 \r
 752         [Test]\r
 753         public void Update01()\r
 754         {\r
 755             var db = CreateDB();\r
 756             Employee p = db.Employees.First(r => r.LastName == "Fuller");\r
 757 \r
 758             DateTime beforeDateTime = p.BirthDate.Value;\r
 759             DateTime now = beforeDateTime.AddMinutes(10);\r
 760 \r
 761             p.BirthDate = now;\r
 762             db.SubmitChanges();\r
 763 \r
 764             Employee p2 = db.Employees.First(r => r.LastName == "Fuller");\r
 765             Assert.AreEqual(p2.BirthDate, now);\r
 766 \r
 767             //undo changes\r
 768             p.BirthDate = beforeDateTime;\r
 769             db.SubmitChanges();\r
 770         }\r
 771 \r
 772         [Test]\r
 773         public void InsertAndDeleteWithDependencies()\r
 774         {\r
 775             const string newCategoryName  = "temp Category";\r
 776             const string newProduct1 = "temp First Test Product";\r
 777             const string newProduct2 = "temp Second Test Product";\r
 778 \r
 779             var db = CreateDB();\r
 780 \r
 781             var product = new Product\r
 782             {\r
 783                 Discontinued = true,\r
 784                 ProductName = newProduct1,\r
 785             };\r
 786 \r
 787             var category = new Category\r
 788             {\r
 789                 CategoryName = newCategoryName,\r
 790                 Description  = "Insert Description Here",\r
 791             };\r
 792             category.Products.Add(product);\r
 793 \r
 794             Assert.AreEqual(0, category.CategoryID);\r
 795             Assert.AreEqual(0, product.CategoryID.Value);\r
 796 \r
 797             db.Categories.InsertOnSubmit(category);\r
 798             db.SubmitChanges();\r
 799 \r
 800             Assert.AreEqual(1, db.Categories.Where(c => c.CategoryName == newCategoryName).Count());\r
 801             Assert.AreNotEqual(0, category.CategoryID);\r
 802             Assert.AreEqual(1, db.Products.Where(p => p.ProductName == newProduct1).Count());\r
 803             Assert.AreEqual(category.CategoryID, product.CategoryID.Value);\r
 804 \r
 805             var p2 = new Product\r
 806             {\r
 807                 Discontinued = true,\r
 808                 ProductName = newProduct2\r
 809             };\r
 810             category.Products.Add(p2);\r
 811             db.SubmitChanges();\r
 812 \r
 813             Assert.AreEqual(1, db.Products.Where(p => p.ProductName == newProduct2).Count());\r
 814 \r
 815             db.Products.DeleteOnSubmit(product);\r
 816             db.Products.DeleteOnSubmit(p2);\r
 817             db.Categories.DeleteOnSubmit(category);\r
 818             db.SubmitChanges();\r
 819 \r
 820             Assert.AreEqual(0, db.Categories.Where(c => c.CategoryName == newCategoryName).Count());\r
 821             Assert.AreEqual(0, db.Products.Where(p => p.ProductName == newProduct1).Count());\r
 822             Assert.AreEqual(0, db.Products.Where(p => p.ProductName == newProduct2).Count());\r
 823         }\r
 824     }\r
 825 }\r