internal static void DbContextTransaction(AdventureWorks adventureWorks) { adventureWorks.Database.CreateExecutionStrategy().Execute(() => { using (IDbContextTransaction transaction = adventureWorks.Database .BeginTransaction(IsolationLevel.ReadUncommitted)) { try { ProductCategory category = new ProductCategory() { Name = nameof(ProductCategory) }; adventureWorks.ProductCategories.Add(category); adventureWorks.SaveChanges().WriteLine(); // 1 adventureWorks.Database .ExecuteSqlCommand($@"DELETE FROM [Production].[ProductCategory] WHERE [Name] = {nameof(ProductCategory)}") .WriteLine(); // 1 adventureWorks.CurrentIsolationLevel().WriteLine(); // ReadUncommitted transaction.Commit(); } catch { transaction.Rollback(); throw; } } }); }
internal static void DeleteCascade(int categoryId) { using (AdventureWorks adventureWorks = new AdventureWorks()) { ProductCategory category = adventureWorks.ProductCategories .Include(entity => entity.ProductSubcategories) .Single(entity => entity.ProductCategoryID == categoryId); ProductSubcategory subcategory = category.ProductSubcategories.Single(); adventureWorks.ChangeTracker.Entries().Count().WriteLine(); // 2 adventureWorks.ProductCategories.Remove(category); // Track deletion. // Optional: adventureWorks.ProductSubcategories.Remove(subcategory); adventureWorks.ChangeTracker.Entries().Count(tracking => tracking.State == EntityState.Deleted) .WriteLine(); // 2 adventureWorks.SaveChanges().WriteLine(); // 2 // BEGIN TRANSACTION // exec sp_executesql N'SET NOCOUNT ON; // DELETE FROM [Production].[ProductSubcategory] // WHERE [ProductSubcategoryID] = @p0; // SELECT @@ROWCOUNT; // ',N'@p0 int',@p0=49 // // exec sp_executesql N'SET NOCOUNT ON; // DELETE FROM [Production].[ProductCategory] // WHERE [ProductCategoryID] = @p1; // SELECT @@ROWCOUNT; // ',N'@p1 int',@p1=26 // COMMIT TRANSACTION } // Unit of work. }
internal static ProductCategory Create() { using (AdventureWorks adventureWorks = new AdventureWorks()) { ProductCategory category = new ProductCategory() { Name = "Create" }; ProductSubcategory subcategory = new ProductSubcategory() { Name = "Create" }; category.ProductSubcategories = new HashSet <ProductSubcategory>() { subcategory }; // Equivalent to: subcategory.ProductCategory = category; category.ProductCategoryID.WriteLine(); // 0 subcategory.ProductCategoryID.WriteLine(); // 0 subcategory.ProductSubcategoryID.WriteLine(); // 0 adventureWorks.ProductCategories.Add(category); // Track creation. // Equivalent to: adventureWorks.ProductSubcategories.Add(subcategory); adventureWorks.ChangeTracker.Entries() .Count(tracking => tracking.State == EntityState.Added).WriteLine(); // 2 object.ReferenceEquals(category.ProductSubcategories.Single(), subcategory).WriteLine(); // True adventureWorks.SaveChanges().WriteLine(); // 2 // BEGIN TRANSACTION // exec sp_executesql N'SET NOCOUNT ON; // INSERT INTO [Production].[ProductCategory] ([Name]) // VALUES (@p0); // SELECT [ProductCategoryID] // FROM [Production].[ProductCategory] // WHERE @@ROWCOUNT = 1 AND [ProductCategoryID] = scope_identity(); // ',N'@p0 nvarchar(50)',@p0=N'Create' // // exec sp_executesql N'SET NOCOUNT ON; // INSERT INTO [Production].[ProductCategory] ([Name]) // VALUES (@p0); // SELECT [ProductCategoryID] // FROM [Production].[ProductCategory] // WHERE @@ROWCOUNT = 1 AND [ProductCategoryID] = scope_identity(); // ',N'@p0 nvarchar(50)',@p0=N'Create' // COMMIT TRANSACTION adventureWorks.ChangeTracker.Entries() .Count(tracking => tracking.State != EntityState.Unchanged).WriteLine(); // 0 category.ProductCategoryID.WriteLine(); // 5 subcategory.ProductCategoryID.WriteLine(); // 5 subcategory.ProductSubcategoryID.WriteLine(); // 38 return(category); } // Unit of work. }
internal static void UntrackedChanges() { using (AdventureWorks adventureWorks = new AdventureWorks()) { ProductCategory untracked = adventureWorks.ProductCategories .AsNoTracking() .Single(category => category.Name == "Bikes"); adventureWorks.ProductCategories.Remove(untracked); // Track no deletion. adventureWorks.SaveChanges().WriteLine(); // DbUpdateException: An error occurred while updating the entries. // ---> SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_ProductSubcategory_ProductCategory_ProductCategoryID". The conflict occurred in database "ADVENTUREWORKS_DATA.MDF", table "Production.ProductSubcategory", column 'ProductCategoryID'. The statement has been terminated. } // Unit of work. }
internal static void SaveNoChanges(int categoryId) { using (AdventureWorks adventureWorks = new AdventureWorks()) { ProductCategory category = adventureWorks.ProductCategories.Find(categoryId); string originalName = category.Name; category.Name = Guid.NewGuid().ToString(); // Entity property update. category.Name = originalName; // Entity property update. EntityEntry tracking = adventureWorks.ChangeTracker.Entries().Single(); tracking.State.WriteLine(); // Unchanged adventureWorks.ChangeTracker.HasChanges().WriteLine(); // False adventureWorks.SaveChanges().WriteLine(); // 0 } // Unit of work. }
internal static void DbTransaction() { using (DbConnection connection = new SqlConnection(ConnectionStrings.AdventureWorks)) { connection.Open(); using (DbTransaction transaction = connection.BeginTransaction(IsolationLevel.RepeatableRead)) { try { using (AdventureWorks adventureWorks = new AdventureWorks(connection)) { adventureWorks.Database.CreateExecutionStrategy().Execute(() => { adventureWorks.Database.UseTransaction(transaction); adventureWorks.CurrentIsolationLevel().WriteLine(); // RepeatableRead ProductCategory category = new ProductCategory() { Name = nameof(ProductCategory) }; adventureWorks.ProductCategories.Add(category); adventureWorks.SaveChanges().WriteLine(); // 1. }); } using (DbCommand command = connection.CreateCommand()) { command.CommandText = "DELETE FROM [Production].[ProductCategory] WHERE [Name] = @Name"; DbParameter parameter = command.CreateParameter(); parameter.ParameterName = "@Name"; parameter.Value = nameof(ProductCategory); command.Parameters.Add(parameter); command.Transaction = transaction; command.ExecuteNonQuery().WriteLine(); // 1 connection.CurrentIsolationLevel(transaction).WriteLine(); // RepeatableRead } transaction.Commit(); } catch { transaction.Rollback(); throw; } } } }
internal static void Delete(int subcategoryId) { using (AdventureWorks adventureWorks = new AdventureWorks()) { ProductSubcategory subcategory = adventureWorks.ProductSubcategories.Find(subcategoryId); adventureWorks.ChangeTracker.Entries().Count().WriteLine(); // 1 adventureWorks.ChangeTracker.Entries <ProductSubcategory>().Single().State.WriteLine(); // Unchanged adventureWorks.ProductSubcategories.Remove(subcategory); // Track deletion. adventureWorks.ChangeTracker.Entries <ProductSubcategory>().Single().State.WriteLine(); // Deleted adventureWorks.SaveChanges().WriteLine(); // 1 // BEGIN TRANSACTION // exec sp_executesql N'SET NOCOUNT ON; // DELETE FROM [Production].[ProductSubcategory] // WHERE [ProductSubcategoryID] = @p0; // SELECT @@ROWCOUNT; // ',N'@p0 int',@p0=48 // COMMIT TRANSACTION } // Unit of work. }
internal static void TransactionScope(AdventureWorks adventureWorks) { adventureWorks.Database.CreateExecutionStrategy().Execute(() => { using (TransactionScope scope = new TransactionScope( TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.Serializable })) { using (DbConnection connection = new SqlConnection(ConnectionStrings.AdventureWorks)) using (DbCommand command = connection.CreateCommand()) { command.CommandText = "INSERT INTO [Production].[ProductCategory] ([Name]) VALUES(@Name); "; DbParameter parameter = command.CreateParameter(); parameter.ParameterName = "@Name"; parameter.Value = nameof(ProductCategory); command.Parameters.Add(parameter); connection.Open(); command.ExecuteNonQuery().WriteLine(); // 1 connection.CurrentIsolationLevel().WriteLine(); // Serializable } using (AdventureWorks adventureWorks1 = new AdventureWorks()) { ProductCategory category = adventureWorks1.ProductCategories .Single(entity => entity.Name == nameof(ProductCategory)); adventureWorks1.ProductCategories.Remove(category); adventureWorks1.SaveChanges().WriteLine(); // 1 adventureWorks1.CurrentIsolationLevel().WriteLine(); // Serializable } scope.Complete(); } }); }
internal static void Default(AdventureWorks adventureWorks) { ProductCategory category = adventureWorks.ProductCategories.First(); category.Name = "Update"; // Valid value.g ProductSubcategory subcategory = adventureWorks.ProductSubcategories.First(); subcategory.ProductCategoryID = -1; // Invalid value. try { adventureWorks.SaveChanges(); } catch (DbUpdateException exception) { exception.WriteLine(); // Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. // ---> System.Data.SqlClient.SqlException: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_ProductSubcategory_ProductCategory_ProductCategoryID". The conflict occurred in database "AdventureWorks", table "Production.ProductCategory", column 'ProductCategoryID'. The statement has been terminated. adventureWorks.Entry(category).Reload(); category.Name.WriteLine(); // Accessories adventureWorks.Entry(subcategory).Reload(); subcategory.ProductCategoryID.WriteLine(); // 1 } }
internal static void DeleteWithRelationship(int categoryId) { using (AdventureWorks adventureWorks = new AdventureWorks()) { ProductCategory category = adventureWorks.ProductCategories.Find(categoryId); adventureWorks.ChangeTracker.Entries().Count().WriteLine(); // 1 adventureWorks.ProductCategories.Remove(category); // Track deletion. adventureWorks.ChangeTracker.Entries().Count(tracking => tracking.State == EntityState.Deleted) .WriteLine(); // 1 adventureWorks.SaveChanges(); // BEGIN TRANSACTION // exec sp_executesql N'SET NOCOUNT ON; // DELETE FROM [Production].[ProductCategory] // WHERE [ProductCategoryID] = @p0; // SELECT @@ROWCOUNT; // ',N'@p0 int',@p0=1 // ROLLBACK TRANSACTION // DbUpdateException: An error occurred while updating the entries. See the inner exception for details. // ---> UpdateException: An error occurred while updating the entries. See the inner exception for details. // ---> SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_ProductSubcategory_ProductCategory_ProductCategoryID". The conflict occurred in database "D:\DIXIN\ONEDRIVE\WORKS\DRAFTS\CODESNIPPETS\DATA\ADVENTUREWORKS_DATA.MDF", table "Production.ProductSubcategory", column 'ProductCategoryID'. } // Unit of work. }
internal static void UpdateWithoutRead(int categoryId) { using (AdventureWorks adventureWorks = new AdventureWorks()) { ProductCategory category = new ProductCategory() { ProductCategoryID = categoryId, Name = Guid.NewGuid().ToString() // Entity property update. }; adventureWorks.ProductCategories.Attach(category); // Track entity. EntityEntry tracking = adventureWorks.ChangeTracker.Entries <ProductCategory>().Single(); tracking.State.WriteLine(); // Unchanged tracking.State = EntityState.Modified; adventureWorks.SaveChanges().WriteLine(); // 1 // BEGIN TRANSACTION // exec sp_executesql N'SET NOCOUNT ON; // UPDATE [Production].[ProductCategory] SET [Name] = @p0 // WHERE [ProductCategoryID] = @p1; // SELECT @@ROWCOUNT; // ',N'@p1 int,@p0 nvarchar(50)',@p1=25,@p0=N'513ce396-4a5e-4a86-9d82-46f284aa4f94' // COMMIT TRANSACTION } // Unit of work. }
internal static void Update(int categoryId, int subcategoryId) { using (AdventureWorks adventureWorks = new AdventureWorks()) { ProductCategory category = adventureWorks.ProductCategories.Find(categoryId); ProductSubcategory subcategory = adventureWorks.ProductSubcategories.Find(subcategoryId); $"({subcategory.ProductSubcategoryID}, {subcategory.Name}, {subcategory.ProductCategoryID})" .WriteLine(); // (48, Create, 25) subcategory.Name = "Update"; // Entity property update. subcategory.ProductCategory = category; // Relashionship (foreign key) update. adventureWorks.ChangeTracker.Entries().Count(tracking => tracking.State != EntityState.Unchanged) .WriteLine(); // 1 $"({subcategory.ProductSubcategoryID}, {subcategory.Name}, {subcategory.ProductCategoryID})" .WriteLine(); // (48, Update, 1) adventureWorks.SaveChanges().WriteLine(); // 1 // BEGIN TRANSACTION // exec sp_executesql N'SET NOCOUNT ON; // UPDATE [Production].[ProductSubcategory] SET [Name] = @p0, [ProductCategoryID] = @p1 // WHERE [ProductSubcategoryID] = @p2; // SELECT @@ROWCOUNT; // ',N'@p2 int,@p0 nvarchar(50),@p1 int',@p2=25,@p0=N'Update',@p1=25 // COMMIT TRANSACTION } // Unit of work. }