private static void SimpleLinq(EntitiesModel context) { Console.WriteLine("No Fetch optimization:"); var query1 = context.Cars.Where(c => c.CarYear < 2000).ToList(); Console.WriteLine("Car\tCategory"); foreach (var car in query1) { Console.WriteLine("{0}\t{1}", car.CarID, car.Category.CategoryName); } }
static void Main(string[] args) { using(var context = new EntitiesModel()) { context.Log = Console.Out; //SimpleLinq(context); //LinqWithFetchStrategy(context); //DynamicLinq(context); //CommonLinqMistakes(context); //BulkUpdate(context); //BulkDelete(context); } }
private static void DynamicLinq(EntitiesModel context) { // strongly typed dynamic LINQ Console.WriteLine("Dynamic strongly typed LINQ Where:"); var query1 = context.Cars.Where("CarYear < @0", 2000).ToList(); Console.WriteLine("Car\tCategory"); foreach (var car in query1) { Console.WriteLine("{0}\t{1}", car.CarID, car.Category.CategoryName); } // weakly typed dynamic LINQ with projection Console.WriteLine("Dynamic weakly typed LINQ Where and Select:"); var objList = context.GetAll("OpenAccessModel9.Car").Where("CarYear < @0", 2000) .Select("new (CarID as Id, CarYear)") .Cast<object>().ToList(); foreach (object objCar in objList) { Console.WriteLine(objCar); } }
private static void LinqWithFetchStrategy(EntitiesModel context) { Console.WriteLine("With fetch optimization:"); FetchStrategy strategy = new FetchStrategy(); strategy.LoadWith<Car>(c => c.Category); strategy.LoadWith<Category>(cat => cat.RentalRates); // set the strategy to the context context.FetchStrategy = strategy; var query2 = context.Cars//.Include(c => c.Category) //.Include(c => c.Category.RentalRates.Select(r=>r.SubRates)) .Where(c => c.CarYear < 2000).ToList(); Console.WriteLine("Car\tCategory"); foreach (var car in query2) { Console.WriteLine("{0}\t{1}", car.CarID, car.Category.CategoryName); } // remove the strategy context.FetchStrategy = null; }
private static void CommonLinqMistakes(EntitiesModel context) { Console.WriteLine("Common mistakes:"); Console.WriteLine("Calling .ToList too early:"); var query1 = context.Cars.ToList().Where(c => c.Make == "VW"); Console.WriteLine("************"); Console.WriteLine("Calling CLR method in Where:"); var query2 = context.Cars.Where(c => SomeMethod(c) == "42"); string query2Text = query2.ToString(); var query2Result = query2.ToList(); Console.WriteLine("************"); Console.WriteLine("Using projection will not fill the L1 and L2 caches"); var query3 = context.Cars.Select(c => new { Id = c.CarID, Name = c.Make + c.Model }); var query3Text = query3.ToString(); Console.WriteLine("************"); Console.WriteLine("Getting too much data:"); var query4 = context.Cars.Where(c => c.Make == "BMW"); var query4Text = query4.ToString(); var query4Result = query4.ToList().Count(); // we don't need the whole list of fully loaded cars to count them! Console.WriteLine("************"); Console.WriteLine("Using constants and other non-cacheable pieces instead of parameters:"); string make = "BMW"; Console.WriteLine("With parameter:"); var query5 = context.Cars.Where(c => c.Make == make); // make is converted into parameter and the query is cached properly var query5Text = query5.ToString(); Console.WriteLine("Without parameter:"); var query6 = context.Cars.Where(c => c.Make == "BMW"); // "BMW" cannot be converted to parameter and the query will not be reused if "BMW' is changed to "VW" var query6Text = query6.ToString(); Console.WriteLine("************"); Console.WriteLine("Disposing context before enumerating the query result"); IQueryable<Car> query7 = null; using (EntitiesModel ctx = new EntitiesModel()) { query7 = ctx.Cars.Where(c => c.Make == "BMW"); } Console.WriteLine("Car\tCategory"); foreach (var car in query7) { Console.WriteLine("{0}\t{1}", car.CarID, car.Category.CategoryName); } Console.WriteLine("************"); }
private static void BulkDelete(EntitiesModel context) { // avoid showing the INSERT statements that populate the data context.Log = null; for (int i = 0; i < 10; i++) { Car car = new Car() { Make = "BMW", Model = "1020", CarYear = 2018, Available = true, CategoryID = 1, TagNumber = i.ToString()}; context.Add(car); } context.SaveChanges(); // show the delete statements context.Log = Console.Out; var query = context.GetAll<Car>().Where(c => c.CarYear > DateTime.Now.Year); int deleted = query.DeleteAll(); Console.WriteLine("Deleted cars: {0}", deleted); }
private static void BulkUpdate(EntitiesModel context) { string tooOldName = "Too old"; Category tooOldCategory = context.Categories.FirstOrDefault(c => c.CategoryName == tooOldName); if (tooOldCategory == null) { tooOldCategory = new Category(); tooOldCategory.CategoryName = tooOldName; context.Add(tooOldCategory); context.SaveChanges(); } var query = context.GetAll<Car>().Where(c => c.CarYear < 2000); int updated = query.UpdateAll(u => u.Set(c => c.Category, c => tooOldCategory) .Set(c => c.Available, c => false)); Console.WriteLine("Updated cars: {0}", updated); }