PageRenderTime 54ms CodeModel.GetById 2ms app.highlight 41ms RepoModel.GetById 2ms app.codeStats 0ms

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

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