internal static SelectExpression WhereAndSelectDatabaseExpressions(AdventureWorks adventureWorks)
{
QueryCompilationContext compilationContext = adventureWorks.GetService <IQueryCompilationContextFactory>()
.Create(async: false);
SelectExpression databaseExpression = new SelectExpression(
dependencies: new SelectExpressionDependencies(adventureWorks.GetService <IQuerySqlGeneratorFactory>(), adventureWorks.GetService <IRelationalTypeMappingSource>()),
queryCompilationContext: (RelationalQueryCompilationContext)compilationContext);
MainFromClause querySource = new MainFromClause(
itemName: "product",
itemType: typeof(Product),
fromExpression: Expression.Constant(adventureWorks.ProductCategories));
TableExpression tableExpression = new TableExpression(
table: nameof(Product),
schema: AdventureWorks.Production,
alias: querySource.ItemName,
querySource: querySource);
databaseExpression.AddTable(tableExpression);
IEntityType productEntityType = adventureWorks.Model.FindEntityType(typeof(Product));
IProperty nameProperty = productEntityType.FindProperty(nameof(Product.Name));
ColumnExpression nameColumn = new ColumnExpression(
name: nameof(Product.Name), property: nameProperty, tableExpression: tableExpression);
databaseExpression.AddToProjection(nameColumn);
databaseExpression.AddToPredicate(Expression.GreaterThan(
left: new ExplicitCastExpression(
operand: new SqlFunctionExpression(
functionName: "LEN",
returnType: typeof(int),
arguments: new Expression[] { nameColumn }),
type: typeof(int)),
right: Expression.Constant(10)));
return(databaseExpression.WriteLine());
}
internal static void SelectAndFirst(AdventureWorks adventureWorks)
{
// string first = adventureWorks.Products.Select(product => product.Name).First();
IQueryable <Product> sourceQueryable = adventureWorks.Products;
IQueryable <string> selectQueryable = sourceQueryable.Select(product => product.Name);
string first = selectQueryable.First().WriteLine(); // Execute query.
}
internal static void ExecutionStrategy2(AdventureWorks adventureWorks)
{
adventureWorks.Database.CreateExecutionStrategy().Execute(() =>
{
// Single retry operation, which can have custom transaction.
});
}
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 EagerLoadingWithInclude(AdventureWorks adventureWorks)
{
IQueryable <ProductSubcategory> subcategoriesWithCategory = adventureWorks.ProductSubcategories
.Include(subcategory => subcategory.ProductCategory);
subcategoriesWithCategory.WriteLines(subcategory =>
$"{subcategory.ProductCategory.Name}: {subcategory.Name}");
// SELECT [subcategory].[ProductSubcategoryID], [subcategory].[Name], [subcategory].[ProductCategoryID], [p].[ProductCategoryID], [p].[Name]
// FROM [Production].[ProductSubcategory] AS [subcategory]
// INNER JOIN [Production].[ProductCategory] AS [p] ON [subcategory].[ProductCategoryID] = [p].[ProductCategoryID]
IQueryable <ProductSubcategory> subcategoriesWithProducts = adventureWorks.ProductSubcategories
.Include(subcategory => subcategory.Products);
subcategoriesWithProducts.WriteLines(subcategory => $@"{subcategory.Name}: {string.Join(
", ", subcategory.Products.Select(product => product.Name))}");
// SELECT [subcategory].[ProductSubcategoryID], [subcategory].[Name], [subcategory].[ProductCategoryID]
// FROM [Production].[ProductSubcategory] AS [subcategory]
// ORDER BY [subcategory].[ProductSubcategoryID]
// SELECT [p].[ProductID], [p].[ListPrice], [p].[Name], [p].[ProductSubcategoryID], [p].[RowVersion]
// FROM [Production].[Product] AS [p]
// WHERE EXISTS (
// SELECT 1
// FROM [Production].[ProductSubcategory] AS [subcategory]
// WHERE [p].[ProductSubcategoryID] = [subcategory].[ProductSubcategoryID])
// ORDER BY [p].[ProductSubcategoryID]
}
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 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();
stopwatch.ElapsedMilliseconds.WriteLine(); // 1682
}
using (AdventureWorks adventureWorks = new AdventureWorks())
{
Stopwatch stopwatch = Stopwatch.StartNew();
Product[] products = adventureWorks.Products.ToArray();
adventureWorks.Products.RemoveRange(products);
stopwatch.Stop();
stopwatch.ElapsedMilliseconds.WriteLine(); // 2
}
}
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 async Task DbContextTransactionAsync(AdventureWorks adventureWorks)
{
await adventureWorks.Database.CreateExecutionStrategy().ExecuteAsync(async() =>
{
using (IDbContextTransaction transaction = await adventureWorks.Database.BeginTransactionAsync(
IsolationLevel.ReadUncommitted))
{
try
{
adventureWorks.CurrentIsolationLevel().WriteLine(); // ReadUncommitted
ProductCategory category = new ProductCategory()
{
Name = nameof(ProductCategory)
};
await adventureWorks.ProductCategories.AddAsync(category);
(await adventureWorks.SaveChangesAsync()).WriteLine(); // 1
await adventureWorks.Database.ExecuteSqlCommandAsync(
sql: "DELETE FROM [Production].[ProductCategory] WHERE [Name] = {0}",
parameters: nameof(ProductCategory)).WriteLine(); // 1
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
});
}
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();
stopwatch.ElapsedMilliseconds.WriteLine(); // 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();
stopwatch.ElapsedMilliseconds.WriteLine(); // 2
}
}
internal static SelectExpression SelectAndFirstDatabaseExpressions(AdventureWorks adventureWorks)
{
QueryCompilationContext compilationContext = adventureWorks.GetService <IQueryCompilationContextFactory>()
.Create(async: false);
SelectExpression selectExpression = new SelectExpression(
dependencies: new SelectExpressionDependencies(adventureWorks.GetService <IQuerySqlGeneratorFactory>(), adventureWorks.GetService <IRelationalTypeMappingSource>()),
queryCompilationContext: (RelationalQueryCompilationContext)compilationContext);
MainFromClause querySource = new MainFromClause(
itemName: "product",
itemType: typeof(Product),
fromExpression: Expression.Constant(adventureWorks.ProductCategories));
TableExpression tableExpression = new TableExpression(
table: nameof(Product),
schema: AdventureWorks.Production,
alias: querySource.ItemName,
querySource: querySource);
selectExpression.AddTable(tableExpression);
IEntityType productEntityType = adventureWorks.Model.FindEntityType(typeof(Product));
IProperty nameProperty = productEntityType.FindProperty(nameof(Product.Name));
selectExpression.AddToProjection(new ColumnExpression(
name: nameof(Product.Name), property: nameProperty, tableExpression: tableExpression));
selectExpression.Limit = Expression.Constant(1);
return(selectExpression.WriteLine());
}
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 IQueryable <Product> QueryCategoryProducts(string category)
{
using (AdventureWorks adventureWorks = new AdventureWorks())
{
return(adventureWorks.Products.Where(
product => product.ProductSubcategory.ProductCategory.Name == category));
}
}
internal static void Find(AdventureWorks adventureWorks)
{
Product[] products = adventureWorks.Products
#pragma warning disable CA1307 // Specify StringComparison
.Where(entity => entity.Name.StartsWith("Road")).ToArray(); // Execute query.
#pragma warning restore CA1307 // Specify StringComparison
Product product = adventureWorks.Products.Find(999); // No database query.
object.ReferenceEquals(products.Last(), product).WriteLine(); // True
}
internal static void SelectAndFirstSql(AdventureWorks adventureWorks)
{
SelectExpression databaseExpression = SelectAndFirstDatabaseExpressions(adventureWorks);
IRelationalCommand sql = adventureWorks.Generate(databaseExpression: databaseExpression, parameters: null);
sql.CommandText.WriteLine();
// SELECT TOP(1) [product].[Name]
// FROM [Production].[Product] AS [product]
}
internal static void TranslationCache(AdventureWorks adventureWorks)
{
int minLength = 1;
IQueryable <Product> query = adventureWorks.Products
.Where(product => product.Name.Length >= minLength)
.Include(product => product.ProductSubcategory);
query.Load();
}
internal static void WhereAndSelectWithLocalPredicate(AdventureWorks adventureWorks)
{
IQueryable <Product> source = adventureWorks.Products;
IEnumerable <string> products = source
.Select(product => product.Name) // LINQ to Entities.
.AsEnumerable() // LINQ to Objects.
.Where(name => FilterName(name)); // Define query, IEnumerable<string> instead of IQueryable<string>.
products.WriteLines(); // Execute query.
}
internal static void DetectChanges(AdventureWorks adventureWorks)
{
adventureWorks.ChangeTracker.AutoDetectChangesEnabled = false;
Product product = adventureWorks.Products.First();
product.ListPrice += 100;
adventureWorks.ChangeTracker.HasChanges().WriteLine(); // False
adventureWorks.ChangeTracker.DetectChanges();
adventureWorks.ChangeTracker.HasChanges().WriteLine(); // True
}
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 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 WhereAndSelectSql(AdventureWorks adventureWorks)
{
SelectExpression databaseExpression = WhereAndSelectDatabaseExpressions(adventureWorks);
IRelationalCommand sql = adventureWorks.Generate(databaseExpression: databaseExpression, parameters: null);
sql.CommandText.WriteLine();
// SELECT [product].[Name]
// FROM [Production].[ProductCategory] AS [product]
// WHERE CAST(LEN([product].[Name]) AS int) > 10
}
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 WhereAndSelectQuery(AdventureWorks adventureWorks)
{
IQueryable <string> products = adventureWorks.Products
.Where(product => product.Name.Length > 10)
.Select(product => product.Name);
// Equivalent to:
// IQueryable<string> products =
// from product in adventureWorks.Products
// where product.Name.Length > 10
// select product.Name;
}
internal static void CompileWhereAndSelectExpressions(AdventureWorks adventureWorks)
{
Expression linqExpression = adventureWorks.Products
.Where(product => product.Name.Length > 10)
.Select(product => product.Name).Expression;
(SelectExpression DatabaseExpression, IReadOnlyDictionary <string, object> Parameters)result =
adventureWorks.Compile(linqExpression);
result.DatabaseExpression.WriteLine();
result.Parameters.WriteLines(parameter => $"{parameter.Key}: {parameter.Value}");
}
internal static void DatabaseOperator(AdventureWorks adventureWorks)
{
IQueryable <string> products = adventureWorks.Products
.Select(product => product.Name)
.Where(name => EF.Functions.Like(name, "%Touring%50%")); // Define query.
products.WriteLines(); // Execute query.
// SELECT [product].[Name]
// FROM [Production].[Product] AS [product]
// WHERE [product].[Name] LIKE N'%Touring%50%'
}
internal static void WhereAndSelectWithCustomPredicate(AdventureWorks adventureWorks)
{
IQueryable <Product> source = adventureWorks.Products;
IQueryable <string> products = source
.Where(product => FilterName(product.Name))
.Select(product => product.Name); // Define query.
products.WriteLines(); // Execute query.
// SELECT [product].[Name]
// FROM [Production].[Product] AS [product]
}
internal static void UnreusedTranslationCache(AdventureWorks adventureWorks)
{
IQueryable <Product> queryWithConstant1 = adventureWorks.Products
.Where(product => product.Name.Length >= 1);
queryWithConstant1.Load();
IQueryable <Product> queryWithConstant2 = adventureWorks.Products
.Where(product => product.Name.Length >= 10);
queryWithConstant2.Load();
}
internal static void ObjectsFromSameDbContext(AdventureWorks adventureWorks)
{
var productById = adventureWorks.Products
.Select(product => new { ProductID = product.ProductID, Name = product.Name })
.Single(product => product.ProductID == 999);
var productByName = adventureWorks.Products
.Select(product => new { ProductID = product.ProductID, Name = product.Name })
.Single(product => product.Name == "Road-750 Black, 52");
adventureWorks.ChangeTracker.Entries().Count().WriteLine(); // 0
object.ReferenceEquals(productById, productByName).WriteLine(); // False
}
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 DatabaseFunction(AdventureWorks adventureWorks)
{
var photos = adventureWorks.ProductPhotos.Select(photo => new
{
LargePhotoFileName = photo.LargePhotoFileName,
UnmodifiedDays = EF.Functions.DateDiffDay(photo.ModifiedDate, DateTime.UtcNow)
}); // Define query.
photos.WriteLines(); // Execute query.
// SELECT [photo].[LargePhotoFileName], DATEDIFF(DAY, [photo].[ModifiedDate], GETUTCDATE()) AS [UnmodifiedDays]
// FROM [Production].[ProductPhoto] AS [photo]
}
