internal static void ExplicitLoadingWithQuery(AdventureWorks adventureWorks)
{
ProductSubcategory subcategory = adventureWorks.ProductSubcategories.First(); // Execute query.
// SELECT TOP(1) [p].[ProductSubcategoryID], [p].[Name], [p].[ProductCategoryID]
// FROM [Production].[ProductSubcategory] AS [p]
subcategory.Name.WriteLine();
string categoryName = adventureWorks
.Entry(subcategory).Reference(entity => entity.ProductCategory)
.Query() // Return IQueryable<ProductCategory>.
.Select(category => category.Name).Single(); // Execute query.
// exec sp_executesql N'SELECT TOP(2) [e].[Name]
// FROM [Production].[ProductCategory] AS [e]
// WHERE [e].[ProductCategoryID] = @__get_Item_0',N'@__get_Item_0 int',@__get_Item_0=1
categoryName.WriteLine();
IQueryable <string> products = adventureWorks
.Entry(subcategory).Collection(entity => entity.Products)
.Query() // Return IQueryable<Product>.
.Select(product => product.Name); // Execute query.
// exec sp_executesql N'SELECT [e].[Name]
// FROM [Production].[Product] AS [e]
// WHERE [e].[ProductSubcategoryID] = @__get_Item_0',N'@__get_Item_0 int',@__get_Item_0=1
products.WriteLines();
}
internal static void ExplicitLoading(AdventureWorks adventureWorks)
{
ProductSubcategory subcategory = adventureWorks.ProductSubcategories.First(); // Execute query.
// SELECT TOP(1) [p].[ProductSubcategoryID], [p].[Name], [p].[ProductCategoryID]
// FROM [Production].[ProductSubcategory] AS [p]
subcategory.Name.WriteLine();
adventureWorks
.Entry(subcategory) // Return EntityEntry<ProductSubcategory>.
.Reference(entity => entity.ProductCategory) // Return ReferenceEntry<ProductSubcategory, ProductCategory>.
.Load(); // Execute query.
// exec sp_executesql N'SELECT [e].[ProductCategoryID], [e].[Name]
// FROM [Production].[ProductCategory] AS [e]
// WHERE [e].[ProductCategoryID] = @__get_Item_0',N'@__get_Item_0 int',@__get_Item_0=1
subcategory.ProductCategory.Name.WriteLine();
adventureWorks
.Entry(subcategory) // Return EntityEntry<ProductSubcategory>.
.Collection(entity => entity.Products) // Return CollectionEntry<ProductSubcategory, Product>.
.Load(); // Execute query.
// exec sp_executesql N'SELECT [e].[ProductID], [e].[ListPrice], [e].[Name], [e].[ProductSubcategoryID]
// FROM [Production].[Product] AS [e]
// WHERE [e].[ProductSubcategoryID] = @__get_Item_0',N'@__get_Item_0 int',@__get_Item_0=1
subcategory.Products.WriteLines(product => product.Name);
}
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 async Task Async(AdventureWorks adventureWorks)
{
IQueryable <ProductCategory> categories = adventureWorks.ProductCategories;
await categories.ForEachAsync( // Async version of foreach/ForEach.
category => category.Name.WriteLine());
ProductSubcategory subcategory = await adventureWorks.ProductSubcategories
.FirstAsync(entity => entity.Name.Contains("Bike")); // Async version of First.
subcategory.Name.WriteLine();
Product[] products = await adventureWorks.Products
.Where(product => product.ListPrice <= 10)
.ToArrayAsync(); // Async version of ToArray.
adventureWorks.Products.RemoveRange(products);
(await adventureWorks.SaveChangesAsync()).WriteLine(); // Async version of SaveChanges.
}
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 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 RelationshipChanges(AdventureWorks adventureWorks)
{
ProductSubcategory subcategory = adventureWorks.ProductSubcategories
.Include(entity => entity.Products).Single(entity => entity.ProductSubcategoryID == 8);
subcategory.Products.Count.WriteLine(); // 2
subcategory.Products
.All(product => product.ProductSubcategory == subcategory).WriteLine(); // True
subcategory.Products.Clear();
// Equivalent to: subcategory.Products.ForEach(product => product.ProductSubcategory = null);
subcategory.Products.Count.WriteLine(); // 0
subcategory.Products
.All(product => product.ProductSubcategory == null).WriteLine(); // True
adventureWorks.ChangeTracker.Entries <Product>().ForEach(tracking =>
{
Product original = (Product)tracking.OriginalValues.ToObject();
Product changed = tracking.Entity;
$"{tracking.State}: {(original.ProductID, original.Name, original.ProductSubcategoryID)} => {(changed.ProductID, changed.Name, changed.ProductSubcategoryID)}".WriteLine();
});
// Modified: (950, ML Crankset, 8) => (950, ML Crankset, )
// Modified: (951, HL Crankset, 8) => (951, HL Crankset, )
}
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.
}
