Example #1
0
        internal static int Create()
        {
            using (AdventureWorks adventureWorks = new AdventureWorks())
            {
                ProductCategory category = new ProductCategory()
                {
                    Name = "Category"
                };
                ProductSubcategory subcategory = new ProductSubcategory()
                {
                    Name = "Subcategory"
                };
                category.ProductSubcategories.Add(subcategory);
                adventureWorks.ProductCategories.InsertOnSubmit(category);

                Trace.WriteLine(category.ProductCategoryID);       // 0.
                Trace.WriteLine(subcategory.ProductCategoryID);    // null.
                Trace.WriteLine(subcategory.ProductSubcategoryID); // 0.

                adventureWorks.SubmitChanges();

                Trace.WriteLine(category.ProductCategoryID);       // 5.
                Trace.WriteLine(subcategory.ProductCategoryID);    // 5.
                Trace.WriteLine(subcategory.ProductSubcategoryID); // 38.
                return(subcategory.ProductSubcategoryID);
            }
        }
Example #2
0
 internal static void DbTransaction()
 {
     using (AdventureWorks adventureWorks = new AdventureWorks())
         using (DbConnection connection = adventureWorks.Connection)
         {
             connection.Open();
             using (DbTransaction transaction = connection.BeginTransaction())
             {
                 try
                 {
                     adventureWorks.Transaction = transaction;
                     ProductCategory category = new ProductCategory()
                     {
                         Name = "Transaction"
                     };
                     adventureWorks.ProductCategories.InsertOnSubmit(category);
                     adventureWorks.SubmitChanges();
                     using (DbCommand command = connection.CreateCommand())
                     {
                         command.CommandText = "DELETE FROM [Production].[ProductCategory] WHERE [Name] = N'Transaction'";
                         command.Transaction = transaction;
                         Trace.WriteLine(command.ExecuteNonQuery()); // 1.
                     }
                     transaction.Commit();
                 }
                 catch (Exception)
                 {
                     transaction.Rollback();
                     throw;
                 }
             }
         }
 }
Example #3
0
 internal static void EntitiesFromSameDataContext()
 {
     using (AdventureWorks adventureWorks = new AdventureWorks())
     {
         Product productById   = adventureWorks.Products.Single(product => product.ProductID == 999);
         Product productByName = adventureWorks.Products.Single(product => product.Name == "Road-750 Black, 52");
         Trace.WriteLine(object.ReferenceEquals(productById, productByName)); // True.
     }
 }
Example #4
0
 internal static void DataContextLog()
 {
     using (AdventureWorks adventureWorks = new AdventureWorks())
     {
         adventureWorks.Log = new CustomTextWriter(log => Trace.Write(log));
         IQueryable <ProductCategory> source = adventureWorks.ProductCategories; // Define query.
         source.ForEach(category => Trace.WriteLine(category.Name));             // Execute query.
         // TODO.
     }
 }
Example #5
0
 internal static void Delete()
 {
     using (AdventureWorks adventureWorks = new AdventureWorks())
     {
         ProductCategory category = adventureWorks.ProductCategories
                                    .Single(entity => entity.Name == "Category");
         adventureWorks.ProductCategories.DeleteOnSubmit(category);
         adventureWorks.SubmitChanges();
     }
 }
Example #6
0
 internal static void DisableDeferredLoading()
 {
     using (AdventureWorks adventureWorks = new AdventureWorks())
     {
         adventureWorks.DeferredLoadingEnabled = false; // Default: true.
         IQueryable <ProductSubcategory> subcategories = AdventureWorks.ProductSubcategories;
         subcategories.ForEach(subcategory => Trace.WriteLine(
                                   $@"{subcategory.ProductCategory?.Name}/{subcategory.Name}: {string.Join(
                 ", ", subcategory.Products.Select(product => product.Name))}"));
     }
 }
Example #7
0
 internal static void UpdateWithNoChange()
 {
     using (AdventureWorks adventureWorks = new AdventureWorks())
     {
         Product product = adventureWorks.Find <Product>(999);
         product.ListPrice += product.ListPrice;
         product.ListPrice /= 2;                                       // Change tracked entity then change back.
         Trace.WriteLine(adventureWorks.GetChangeSet().Updates.Any()); // False.
         adventureWorks.SubmitChanges();
     }
 }
Example #8
0
 internal static void UntrackedChanges()
 {
     using (AdventureWorks adventureWorks = new AdventureWorks())
     {
         adventureWorks.ObjectTrackingEnabled = false;
         IQueryable <Product> products = adventureWorks.Products.Take(10);
         adventureWorks.Products.DeleteAllOnSubmit(products);
         adventureWorks.SubmitChanges();
         // InvalidOperationException: Object tracking is not enabled for the current data context instance.
     }
 }
Example #9
0
 internal static void DataContexGetCommand()
 {
     using (AdventureWorks adventureWorks = new AdventureWorks())
     {
         IQueryable <ProductCategory> source = adventureWorks.ProductCategories; // Define query.
         DbCommand command = adventureWorks.GetCommand(source);
         Trace.WriteLine($@"{command.CommandText}{string.Concat(command.Parameters
             .Cast<DbParameter>()
             .Select(parameter => $", {parameter.ParameterName}={parameter.Value}"))}");
         // TODO.
     }
 }
Example #10
0
 internal static void DisableObjectTracking()
 {
     using (AdventureWorks adventureWorks = new AdventureWorks())
     {
         adventureWorks.ObjectTrackingEnabled = false;
         Product product = adventureWorks.Products.First();
         product.ListPrice += 100;
         Trace.WriteLine(adventureWorks.GetChangeSet().Updates.Any()); // False
         adventureWorks.ObjectTrackingEnabled = true;
         // System.InvalidOperationException: Data context options cannot be modified after results have been returned from a query.
     }
 }
Example #11
0
 internal static void DataQueryToString()
 {
     using (AdventureWorks adventureWorks = new AdventureWorks())
     {
         IQueryable <ProductCategory> source = adventureWorks.ProductCategories; // Define query.
         string translatedSql = source.ToString();
         Trace.WriteLine(translatedSql);
         // SELECT[t0].[ProductID], [t0].[Name], [t0].[ListPrice], [t0].[ProductSubcategoryID]
         // FROM[Production].[Product]
         // AS[t0]
         // WHERE[t0].[ListPrice] > @p0
         source.ForEach(category => Trace.WriteLine(category.Name)); // Execute query.
     }
 }
Example #12
0
 internal static void EagerLoadingWithAssociation()
 {
     using (AdventureWorks adventureWorks = new AdventureWorks())
     {
         DataLoadOptions options = new DataLoadOptions();
         options.LoadWith <ProductSubcategory>(subcategory => subcategory.Products);
         options.LoadWith <ProductSubcategory>(subcategory => subcategory.ProductCategory);
         adventureWorks.LoadOptions = options;
         IQueryable <ProductSubcategory> subcategories = adventureWorks.ProductSubcategories;
         subcategories.ForEach(subcategory => Trace.WriteLine(
                                   $@"{subcategory.ProductCategory?.Name}/{subcategory.Name}: {string.Join(
                 ", ", subcategory.Products.Select(product => product.Name))}"));
     }
 }
Example #13
0
 internal static void DeleteWithAssociation()
 {
     Create(); // Insert ProductCategory "Category" and ProductSubcategory "Subcategory".
     using (AdventureWorks adventureWorks = new AdventureWorks())
     {
         ProductCategory category = adventureWorks.ProductCategories
                                    .Single(entity => entity.Name == "Category");
         ProductSubcategory subcategory = adventureWorks.ProductSubcategories
                                          .Single(entity => entity.Name == "Subcategory");
         adventureWorks.ProductCategories.DeleteOnSubmit(category);
         adventureWorks.ProductSubcategories.DeleteOnSubmit(subcategory);
         adventureWorks.SubmitChanges();
     }
 }
Example #14
0
        internal static void DeleteWithNoQuery(int subcategoryId)
        {
            ProductSubcategory subcategory = new ProductSubcategory()
            {
                ProductSubcategoryID = subcategoryId
            };

            using (AdventureWorks adventureWorks = new AdventureWorks())
            {
                adventureWorks.ProductSubcategories.Attach(subcategory, false);
                adventureWorks.ProductSubcategories.DeleteOnSubmit(subcategory);
                adventureWorks.SubmitChanges();
            }
        }
Example #15
0
 internal static void ObjectsFromSameDataContext()
 {
     using (AdventureWorks adventureWorks = new AdventureWorks())
     {
         var productById = adventureWorks.Products
                           .Where(product => product.ProductID == 999)
                           .Select(product => new { Name = product.Name, ListPrice = product.ListPrice })
                           .Single();
         var productByName = adventureWorks.Products
                             .Where(product => product.Name == "Road-750 Black, 52")
                             .Select(product => new { Name = product.Name, ListPrice = product.ListPrice })
                             .Single();
         Trace.WriteLine(object.ReferenceEquals(productById, productByName)); // False.
     }
 }
Example #16
0
        internal static void Attach()
        {
            Product product = new UniversalProduct()
            {
                Name = "On the fly", ListPrice = 1
            };

            using (AdventureWorks adventureWorks = new AdventureWorks())
            {
                product.ListPrice = 2;
                Trace.WriteLine(adventureWorks.GetChangeSet().Updates.Any()); // False.
                adventureWorks.Products.Attach(product);
                product.ListPrice = 3;
                adventureWorks.GetChangeSet().Updates.OfType <Product>().ForEach(attached => Trace.WriteLine(
                                                                                     $"{adventureWorks.Products.GetOriginalEntityState(attached).ListPrice} -> {attached.ListPrice}")); // 2 -> 3.
            }
        }
Example #17
0
 internal static void TransactionScope()
 {
     using (TransactionScope scope = new TransactionScope())
         using (AdventureWorks adventureWorks = new AdventureWorks())
             using (DbConnection connection = adventureWorks.Connection)
             {
                 connection.Open();
                 using (DbCommand command = connection.CreateCommand())
                 {
                     command.CommandText = "INSERT INTO [Production].[ProductCategory] ([Name]) VALUES (N'Transaction')";
                     Trace.WriteLine(command.ExecuteNonQuery()); // 1.
                 }
                 ProductCategory category = adventureWorks.ProductCategories.Single(entity => entity.Name == "Transaction");
                 adventureWorks.ProductCategories.DeleteOnSubmit(category);
                 adventureWorks.SubmitChanges();
                 scope.Complete();
             }
 }
Example #18
0
        internal static void AssociationChanges()
        {
            using (AdventureWorks adventureWorks = new AdventureWorks())
            {
                DataLoadOptions loadOptions = new DataLoadOptions();
                loadOptions.LoadWith <ProductCategory>(entity => entity.ProductSubcategories);
                adventureWorks.LoadOptions = loadOptions;
                ProductCategory category = adventureWorks.ProductCategories.First();
                Trace.WriteLine(category.ProductSubcategories.Count);                                       // 12.
                ProductSubcategory[] subcategories = category.ProductSubcategories.ToArray();
                Trace.WriteLine(subcategories.All(subcategory => subcategory.ProductCategory == category)); // True.

                category.ProductSubcategories.Clear();
                Trace.WriteLine(category.ProductSubcategories.Count);                                   // 0.
                Trace.WriteLine(adventureWorks.GetChangeSet().Updates.Count);                           // 12.
                Trace.WriteLine(subcategories.All(subcategory => subcategory.ProductCategory == null)); // True.
            }
        }
Example #19
0
        internal static void Update()
        {
            using (AdventureWorks adventureWorks = new AdventureWorks())
            {
                ProductCategory    category    = adventureWorks.ProductCategories.First();
                ProductSubcategory subcategory = adventureWorks.ProductSubcategories
                                                 .Single(entity => entity.Name == "Subcategory");
                Trace.WriteLine(subcategory.Name);              // Subcategory.
                Trace.WriteLine(subcategory.ProductCategoryID); // 5.

                subcategory.Name            = "Update";         // Update property.
                subcategory.ProductCategory = category;         // Update association.

                adventureWorks.SubmitChanges();

                Trace.WriteLine(subcategory.Name);              // Subcategory update.
                Trace.WriteLine(subcategory.ProductCategoryID); // 4.
            }
        }
Example #20
0
        internal static void EntityChanges()
        {
            using (AdventureWorks adventureWorks = new AdventureWorks())
            {
                Product insert = new UniversalProduct()
                {
                    Name = "Insert", ListPrice = 123
                };
                // Product insert = new Product() causes InvalidOperationException for InsertOnSubmit:
                // InvalidOperationException: Instance of type 'Dixin.Linq.LinqToSql.Product' could not be added. This type is not part of the mapped type system.
                adventureWorks.Products.InsertOnSubmit(insert);
                IQueryable <Product> update = adventureWorks.Products
                                              .Where(product => product.Name.Contains("HL"));
                update.ForEach(product => product.ListPrice += 50);
                IQueryable <Product> delete = adventureWorks.Products
                                              .Where(product => product.Name.Contains("ML"));
                adventureWorks.Products.DeleteAllOnSubmit(delete);

                ChangeSet changeSet = adventureWorks.GetChangeSet();
                Trace.WriteLine(changeSet.Inserts.Any()); // True.
                Trace.WriteLine(changeSet.Updates.Any()); // True.
                Trace.WriteLine(changeSet.Deletes.Any()); // True.

                changeSet.Inserts.OfType <Product>().ForEach(product => Trace.WriteLine(
                                                                 $"{nameof(ChangeSet.Inserts)}: ({product.ProductID}, {product.Name}, {product.ListPrice})"));
                changeSet.Updates.OfType <Product>().ForEach(product =>
                {
                    Product original = adventureWorks.Products.GetOriginalEntityState(product);
                    Trace.WriteLine($"{nameof(ChangeSet.Updates)}: ({original.ProductID}, {original.Name}, {original.ListPrice}) -> ({product.ProductID}, {product.Name}, {product.ListPrice})");
                });
                changeSet.Deletes.OfType <Product>().ForEach(product => Trace.WriteLine(
                                                                 $"{nameof(ChangeSet.Deletes)}: ({product.ProductID}, {product.Name}, {product.ListPrice})"));
                // Inserts: (0, Insert, 123)
                // Updates: (951, HL Crankset, 404.9900) -> (951, HL Crankset, 454.9900)
                // Updates: (996, HL Bottom Bracket, 121.4900) -> (996, HL Bottom Bracket, 171.4900)
                // Deletes: (950, ML Crankset, 256.4900)
                // Deletes: (995, ML Bottom Bracket, 101.2400)
            }
        }
Example #21
0
 internal static void Default()
 {
     using (AdventureWorks adventureWorks = new AdventureWorks())
     {
         ProductCategory category = adventureWorks.ProductCategories.First();
         Trace.WriteLine(category.Name); // Accessories.
         category.Name = "Update";
         ProductSubcategory subcategory = adventureWorks.ProductSubcategories.First();
         Trace.WriteLine(subcategory.ProductCategoryID); // 1.
         subcategory.ProductCategoryID = -1;
         try
         {
             adventureWorks.SubmitChanges();
         }
         catch (SqlException exception)
         {
             Trace.WriteLine(exception);
             // SqlException: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_ProductSubcategory_ProductCategory_ProductCategoryID". The conflict occurred in database "D:\ONEDRIVE\WORKS\DRAFTS\CODESNIPPETS\DATA\ADVENTUREWORKS_DATA.MDF", table "Production.ProductCategory", column 'ProductCategoryID'. The statement has been terminated.
             adventureWorks.Refresh(RefreshMode.OverwriteCurrentValues, category, subcategory);
             Trace.WriteLine(category.Name);                 // Accessories.
             Trace.WriteLine(subcategory.ProductCategoryID); // 1.
         }
     }
 }
Example #22
0
 internal static IQueryable <string> GetProductNames
     (this AdventureWorks adventureWorks, decimal listPrice) =>
 GetProductNamesCompiled(adventureWorks, listPrice);