internal static void ExecutionStrategy2(AdventureWorks adventureWorks)
{
adventureWorks.Database.CreateExecutionStrategy().Execute(() =>
{
// Single retry operation, which can have custom transaction.
});
}
internal static void Dispose()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
// Unit of work.
}
}
internal static ProductCategory Create()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
ProductCategory category = new ProductCategory()
{
Name = nameof(ProductCategory)
};
ProductSubcategory subcategory = new ProductSubcategory()
{
Name = nameof(ProductSubcategory)
};
adventureWorks.ProductSubcategories.Add(subcategory);
subcategory.ProductCategory = category;
// Equivalent to: category.ProductSubcategories.Add(subcategory);
Trace.WriteLine(adventureWorks.ChangeTracker.Entries()
.Count(tracking => tracking.State == EntityState.Added)); // 2
Trace.WriteLine(category.ProductCategoryID); // 0
Trace.WriteLine(subcategory.ProductCategoryID); // 0
Trace.WriteLine(subcategory.ProductSubcategoryID); // 0
Trace.WriteLine(adventureWorks.SaveChanges()); // 2
Trace.WriteLine(adventureWorks.ChangeTracker.Entries()
.Count(tracking => tracking.State != EntityState.Unchanged)); // 0
Trace.WriteLine(category.ProductCategoryID); // 25
Trace.WriteLine(subcategory.ProductCategoryID); // 25
Trace.WriteLine(subcategory.ProductSubcategoryID); // 50
return(category);
}
}
internal static void AddRange()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
adventureWorks.ProductCategories.Load(); // Warm up.
}
using (AdventureWorks adventureWorks = new AdventureWorks())
{
Stopwatch stopwatch = Stopwatch.StartNew();
IEnumerable <ProductCategory> categories = Enumerable
.Range(0, 100).Select(index => new ProductCategory()
{
Name = index.ToString()
});
DbSet <ProductCategory> repository = adventureWorks.ProductCategories;
foreach (ProductCategory category in categories)
{
repository.Add(category);
}
stopwatch.Stop();
Trace.WriteLine(stopwatch.ElapsedMilliseconds); // 1682
}
using (AdventureWorks adventureWorks = new AdventureWorks())
{
Stopwatch stopwatch = Stopwatch.StartNew();
IEnumerable <ProductCategory> categories = Enumerable
.Range(0, 100).Select(index => new ProductCategory()
{
Name = index.ToString()
});
adventureWorks.ProductCategories.AddRange(categories);
stopwatch.Stop();
Trace.WriteLine(stopwatch.ElapsedMilliseconds); // 2
}
}
internal static void RemoveRange()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
adventureWorks.Products.Load(); // Warm up.
}
using (AdventureWorks adventureWorks = new AdventureWorks())
{
Stopwatch stopwatch = Stopwatch.StartNew();
Product[] products = adventureWorks.Products.ToArray();
DbSet <Product> repository = adventureWorks.Products;
foreach (Product product in products)
{
repository.Remove(product);
}
stopwatch.Stop();
Trace.WriteLine(stopwatch.ElapsedMilliseconds); // 1682
}
using (AdventureWorks adventureWorks = new AdventureWorks())
{
Stopwatch stopwatch = Stopwatch.StartNew();
Product[] products = adventureWorks.Products.ToArray();
adventureWorks.Products.RemoveRange(products);
stopwatch.Stop();
Trace.WriteLine(stopwatch.ElapsedMilliseconds); // 2
}
}
internal static void Default()
{
using (AdventureWorks adventureWorks = new 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)
{
Trace.WriteLine(exception);
// System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
// ---> System.Data.Entity.Core.UpdateException: 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 "D:\ONEDRIVE\WORKS\DRAFTS\CODESNIPPETS\DATA\ADVENTUREWORKS_DATA.MDF", table "Production.ProductCategory", column 'ProductCategoryID'. The statement has been terminated.
adventureWorks.Entry(category).Reload();
Trace.WriteLine(category.Name); // Accessories
adventureWorks.Entry(subcategory).Reload();
Trace.WriteLine(subcategory.ProductCategoryID); // 1
}
}
}
internal static IQueryable <Product> QueryCategoryProducts(string category)
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
return(adventureWorks.Products.Where(
product => product.ProductSubcategory.ProductCategory.Name == category));
}
}
internal static void LeftOuterJoinWithSelectManyRelationship(AdventureWorks adventureWorks)
{
IQueryable <ProductCategory> source = adventureWorks.ProductCategories;
var categorySubcategories =
from category in source
from subcategory in category.ProductSubcategories.DefaultIfEmpty() // INNER JOIN if DefaultIfEmpty is missing.
select new { Category = category.Name, Subcategory = subcategory.Name }; // Define query.
categorySubcategories.WriteLines(); // Execute query.
}
internal static void InnerJoinWithSelectManyAndRelationship(AdventureWorks adventureWorks)
{
IQueryable <ProductCategory> outer = adventureWorks.ProductCategories;
var categorySubcategories =
from category in outer
from subcategory in category.ProductSubcategories
select new { Category = category.Name, Subcategory = subcategory.Name }; // Define query.
categorySubcategories.WriteLines(); // Execute query.
}
internal static void Find()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
Product[] products = adventureWorks.Products
.Where(product => product.Name.StartsWith("Road")).ToArray(); // SELECT.
Product fromCache = adventureWorks.Products.Find(999); // No database query.
Trace.WriteLine(products.Contains(fromCache)); // True
}
}
internal static void TranslationCache()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
int minLength = 1;
IQueryable <ProductCategory> query = adventureWorks.ProductCategories
.Where(category => category.Name.Length >= minLength)
.Include(category => category.ProductSubcategories);
query.Load();
}
}
internal static void InnerJoinWithJoin(AdventureWorks adventureWorks)
{
IQueryable <ProductCategory> outer = adventureWorks.ProductCategories;
IQueryable <ProductSubcategory> inner = adventureWorks.ProductSubcategories;
var categorySubcategories =
from category in outer
join subcategory in inner
on category.ProductCategoryID equals subcategory.ProductCategoryID
select new { Category = category.Name, Subategory = subcategory.Name }; // Define query.
categorySubcategories.WriteLines(); // Execute query.
}
internal static void UncachedTranslation()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
IQueryable <ProductCategory> queryWithConstant1 = adventureWorks.ProductCategories
.Where(category => category.Name.Length >= 1);
queryWithConstant1.Load();
IQueryable <ProductCategory> queryWithConstant2 = adventureWorks.ProductCategories
.Where(category => category.Name.Length >= 10);
queryWithConstant2.Load();
}
}
internal static void EntitiesFromSameDbContext(AdventureWorks adventureWorks)
{
Product productById = adventureWorks.Products
.Single(product => product.ProductID == 999);
adventureWorks.ChangeTracker.Entries().Count().WriteLine(); // 1
Product productByName = adventureWorks.Products
.Single(product => product.Name == "Road-750 Black, 52");
adventureWorks.ChangeTracker.Entries().Count().WriteLine(); // 1
object.ReferenceEquals(productById, productByName).WriteLine(); // True
}
internal static void InnerJoinWithGroupJoin(AdventureWorks adventureWorks)
{
IQueryable <ProductCategory> outer = adventureWorks.ProductCategories;
IQueryable <ProductSubcategory> inner = adventureWorks.ProductSubcategories;
var categorySubcategories =
from category in outer
join subcategory in inner
on category.ProductCategoryID equals subcategory.ProductCategoryID into subcategories
from subcategory in subcategories // LEFT OUTER JOIN if DefaultIfEmpty is called.
select new { Category = category.Name, Subategory = subcategory.Name }; // Define query.
categorySubcategories.WriteLines(); // Execute query.
}
internal static void LeftOuterJoinWithSelectMany(AdventureWorks adventureWorks)
{
IQueryable <ProductCategory> outer = adventureWorks.ProductCategories;
IQueryable <ProductSubcategory> inner = adventureWorks.ProductSubcategories;
var categorySubcategories =
from category in outer
from subcategory in (from subcategory in inner
where category.ProductCategoryID == subcategory.ProductCategoryID
select subcategory).DefaultIfEmpty() // INNER JOIN if DefaultIfEmpty is missing.
select new { Category = category.Name, Subcategory = subcategory.Name }; // Define query.
categorySubcategories.WriteLines(); // Execute query.
}
internal static void DbQueryToString()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
IQueryable <ProductCategory> source = adventureWorks.ProductCategories; // Define query.
source.ToString().WriteLine();
// SELECT
// [Extent1].[ProductCategoryID] AS [ProductCategoryID],
// [Extent1].[Name] AS [Name]
// FROM [Production].[ProductCategory] AS [Extent1]
source.ForEach(); // Execute query.
}
}
internal static void InnerJoinWithMultipleKeys(AdventureWorks adventureWorks)
{
IQueryable <Product> outer = adventureWorks.Products;
IQueryable <TransactionHistory> inner = adventureWorks.Transactions;
var transactions =
from product in adventureWorks.Products
join transaction in adventureWorks.Transactions
on new { ProductID = product.ProductID, UnitPrice = product.ListPrice }
equals new { ProductID = transaction.ProductID, UnitPrice = transaction.ActualCost / transaction.Quantity }
select new { Name = product.Name, Quantity = transaction.Quantity }; // Define query.
transactions.WriteLines(); // Execute query.
}
internal static void LeftOuterJoinWithGroupJoinAndSelectMany(AdventureWorks adventureWorks)
{
IQueryable <ProductCategory> outer = adventureWorks.ProductCategories;
IQueryable <ProductSubcategory> inner = adventureWorks.ProductSubcategories;
var categories =
from category in outer
join subcategory in inner
on category.ProductCategoryID equals subcategory.ProductCategoryID into subcategories
from subcategory in subcategories.DefaultIfEmpty() // INNER JOIN if DefaultIfEmpty is missing.
select new { Category = category.Name, Subcategory = subcategory.Name }; // Define query.
categories.WriteLines(); // Execute query.
}
internal static void MultipleInnerJoinsWithRelationship(AdventureWorks adventureWorks)
{
IQueryable <Product> source = adventureWorks.Products;
var products =
from product in source
from productProductPhoto in product.ProductProductPhotos
select new
{
Product = product.Name,
Photo = productProductPhoto.ProductPhoto.LargePhotoFileName
}; // Define query.
products.WriteLines(); // Execute query.
}
internal static ProductCategory Create()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
ProductCategory category = new ProductCategory()
{
Name = "Create"
};
ProductSubcategory subcategory = new ProductSubcategory()
{
Name = "Create"
};
adventureWorks.ProductSubcategories.Add(subcategory); // Track creation.
subcategory.ProductCategory = category;
// Equivalent to: category.ProductSubcategories.Add(subcategory);
adventureWorks.ChangeTracker.Entries()
.Count(tracking => tracking.State == EntityState.Added).WriteLine(); // 2
object.ReferenceEquals(category.ProductSubcategories.Single(), subcategory).WriteLine(); // True.
category.ProductCategoryID.WriteLine(); // 0
subcategory.ProductCategoryID.WriteLine(); // 0
subcategory.ProductSubcategoryID.WriteLine(); // 0
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(); // 25
subcategory.ProductCategoryID.WriteLine(); // 25
subcategory.ProductSubcategoryID.WriteLine(); // 50
return(category);
} // Unit of work.
}
internal static void CachedTranslation()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
int minLength = 1;
IQueryable <ProductCategory> queryWithClosure1 = adventureWorks.ProductCategories
.Where(category => category.Name.Length >= minLength);
queryWithClosure1.Load();
minLength = 10;
IQueryable <ProductCategory> queryWithClosure2 = adventureWorks.ProductCategories
.Where(category => category.Name.Length >= minLength);
queryWithClosure2.Load();
}
}
internal static void CrossJoinWithSelectMany(AdventureWorks adventureWorks)
{
IQueryable <Product> outer = from product in adventureWorks.Products
where product.ListPrice > 2000
select product;
IQueryable <Product> inner = from product in adventureWorks.Products
where product.ListPrice < 100
select product;;
var bundles =
from outerProduct in outer
from innerProduct in inner
select new { Expensive = outerProduct.Name, Cheap = innerProduct.Name }; // Define query.
bundles.WriteLines(bundle => $"{bundle.Expensive}: {bundle.Cheap}"); // Execute query.
}
internal static void Translation()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
adventureWorks.Products.Load(); // Warm up.
}
Func <string, Expression <Func <Product, bool> > > getPredicateWithConstant = startWith =>
{
ParameterExpression productParameterExpression = Expression.Parameter(typeof(Product), "product");
Func <string, bool> startsWithMethod = string.Empty.StartsWith;
Expression <Func <Product, bool> > predicateExpression = Expression.Lambda <Func <Product, bool> >(
Expression.Call(
instance: Expression.Property(productParameterExpression, nameof(Product.Name)),
method: startsWithMethod.Method,
arguments: Expression.Constant(startWith, typeof(string))),
productParameterExpression);
return(predicateExpression);
};
Func <string, Expression <Func <Product, bool> > > getPredicateWithVariable =
startWith => product => product.Name.StartsWith(startWith);
using (AdventureWorks adventureWorks = new AdventureWorks())
{
Stopwatch stopwatch = Stopwatch.StartNew();
Enumerable.Range(0, 1000).ForEach(value =>
{
IQueryable <Product> query = adventureWorks.Products
.Where(getPredicateWithConstant(value.ToString()));
query.Load();
});
stopwatch.Stop();
Trace.WriteLine(stopwatch.ElapsedMilliseconds);
stopwatch.Restart();
Enumerable.Range(0, 1000).ForEach(value =>
{
IQueryable <Product> query = adventureWorks.Products
.Where(getPredicateWithVariable(value.ToString()));
query.Load();
});
stopwatch.Stop();
Trace.WriteLine(stopwatch.ElapsedMilliseconds);
}
}
internal static void CachedSkipTake()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
int skip = 1;
int take = 1;
IQueryable <ProductSubcategory> skipTakeWithClosure1 = adventureWorks.ProductSubcategories
.OrderBy(p => p.ProductSubcategoryID).Skip(() => skip).Take(() => take);
skipTakeWithClosure1.Load();
skip = 10;
take = 10;
IQueryable <ProductSubcategory> skipTakeWithClosure2 = adventureWorks.ProductSubcategories
.OrderBy(p => p.ProductSubcategoryID).Skip(() => skip).Take(() => take);
skipTakeWithClosure2.Load();
}
}
internal static void InnerJoinWithSelect(AdventureWorks adventureWorks)
{
IQueryable <ProductCategory> outer = adventureWorks.ProductCategories;
IQueryable <ProductSubcategory> inner = adventureWorks.ProductSubcategories;
var categorySubcategories =
from category in outer
select new
{
Category = category,
Subcategories = from subcategory in inner
where category.ProductCategoryID == subcategory.ProductCategoryID
select subcategory
} into category
from subcategory in category.Subcategories // LEFT OUTER JOIN if DefaultIfEmpty is called.
select new { Category = category.Category.Name, Subcategory = subcategory.Name }; // Define query.
categorySubcategories.WriteLines(); // Execute query.
}
internal static void SelfJoin(AdventureWorks adventureWorks)
{
IQueryable <Product> outer = adventureWorks.Products;
IQueryable <Product> inner = adventureWorks.Products;
var products =
from outerProduct in outer
join innerProduct in inner
on outerProduct.ListPrice equals innerProduct.ListPrice into samePriceProducts
select new
{
Name = outerProduct.Name,
ListPrice = outerProduct.ListPrice,
SamePriceProducts = from samePriceProduct in samePriceProducts
where samePriceProduct.ProductID != outerProduct.ProductID
select samePriceProduct.Name
}; // Define query.
products.WriteLines(product =>
$"{product.Name} ({product.ListPrice}): {string.Join(", ", product.SamePriceProducts)}"); // Execute query.
}
internal static void Initialize()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
IQueryable <ProductCategory> categories = adventureWorks.ProductCategories;
categories.Load();
// select cast(serverproperty('EngineEdition') as int)
// SELECT Count(*)
// FROM INFORMATION_SCHEMA.TABLES AS t
// WHERE t.TABLE_SCHEMA + '.' + t.TABLE_NAME IN ('HumanResources.Employee','Person.Person','Production.ProductCategory','Production.ProductSubcategory','Production.Product','Production.ProductProductPhoto','Production.ProductPhoto','Production.TransactionHistory','HumanResources.vEmployee')
// OR t.TABLE_NAME = 'EdmMetadata'
// exec sp_executesql N'SELECT
// [GroupBy1].[A1] AS [C1]
// FROM ( SELECT
// COUNT(1) AS [A1]
// FROM [dbo].[__MigrationHistory] AS [Extent1]
// WHERE [Extent1].[ContextKey] = @p__linq__0
// ) AS [GroupBy1]',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'Dixin.Linq.EntityFramework.AdventureWorks'
// SELECT
// [GroupBy1].[A1] AS [C1]
// FROM ( SELECT
// COUNT(1) AS [A1]
// FROM [dbo].[__MigrationHistory] AS [Extent1]
// ) AS [GroupBy1]
// SELECT TOP (1)
// [Extent1].[Id] AS [Id],
// [Extent1].[ModelHash] AS [ModelHash]
// FROM [dbo].[EdmMetadata] AS [Extent1]
// ORDER BY [Extent1].[Id] DESC
// SELECT
// [Extent1].[ProductCategoryID] AS [ProductCategoryID],
// [Extent1].[Name] AS [Name]
// FROM [Production].[ProductCategory] AS [Extent1]
}
}
internal static void UncachedEntity()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
ProductCategory category1 = adventureWorks.ProductCategories
.Single(entity => entity.ProductCategoryID == 1);
category1.Name = "Cache";
ProductCategory category2 = adventureWorks.ProductCategories
.AsNoTracking().Single(entity => entity.Name == "Bikes");
Trace.WriteLine(category2.Name); // Bikes
Trace.WriteLine(category1 == category2); // False
ProductCategory category3 = adventureWorks.Database
.SqlQuery <ProductCategory>(@"
SELECT TOP (1) [ProductCategory].[ProductCategoryID], [ProductCategory].[Name]
FROM [Production].[ProductCategory]
ORDER BY [ProductCategory].[ProductCategoryID]")
.Single();
Trace.WriteLine(category1 == category3); // False
}
}
internal static void WhereAndSelect()
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
IQueryable <Product> products = adventureWorks.Products
.Where(product => product.Name.StartsWith("M"));
// products.ForEach(product => Trace.WriteLine(product));
Trace.WriteLine("Get iterator from LINQ to Entities query.");
using (IEnumerator <Product> iterator = products
.GetIterator(adventureWorks)) // products.GetEnumerator()
{
while (new Func <bool>(() =>
{
Trace.WriteLine("Try moving iterator to next.");
return(iterator.MoveNext()); // Translate and execute query.
})())
{
Product product = iterator.Current;
Trace.WriteLine($"Get iterator current product: {product.Name}.");
}
}
}
}
