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}."); } } } }