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.
}
