private static void SelectSpecificColumns()
{
var stopWatch = Stopwatch.StartNew();
// Getting All Columns
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c => c.Name.Contains("1"))
.ToDictionary(c => c.Name, c => c.Age);
}
Console.WriteLine(stopWatch.Elapsed);
stopWatch = Stopwatch.StartNew();
// Getting Only The Columns We Need
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c => c.Name.Contains("1"))
.Select(c => new CatResult
{
Name = c.Name,
Age = c.Age
})
.ToDictionary(c => c.Name, c => c.Age);
}
Console.WriteLine(stopWatch.Elapsed);
}
private static void LazyLoadingTooManyQueries()
{
var stopWatch = Stopwatch.StartNew();
// N + 1 With Lazy Loading
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c => c.Name.Contains("1"))
.ToList();
var ownerNames = new List <string>();
foreach (var cat in cats)
{
var ownerName = cat.Owner.Name;
ownerNames.Add(ownerName);
}
}
Console.WriteLine(stopWatch.Elapsed);
stopWatch = Stopwatch.StartNew();
// Using Select
using (var db = new CatsDbContext())
{
var ownerNames = db.Cats
.Where(c => c.Name.Contains("1"))
.Select(c => c.Owner.Name)
.ToList();
}
Console.WriteLine(stopWatch.Elapsed);
}
private static void IncorrectDataLoading()
{
// Methods which load data from the database
// .ToList(), .ToArray(), .ToDictionary(),
// .Any(), .All(), Count(), .Contains()
// .First...(), .Last...(), .Single...()
// .Max(), Min(), Average(), Sum()
// All Async versions and more
// No Database Call
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c => c.Name.Contains("1"))
.OrderBy(c => c.Name)
.Select(c => c.Name);
}
var stopWatch = Stopwatch.StartNew();
// Getting All Rows
using (var db = new CatsDbContext())
{
var cats = db.Cats
.ToList()
.Where(c => c.Name.Contains("1"));
}
Console.WriteLine(stopWatch.Elapsed);
stopWatch = Stopwatch.StartNew();
// Getting Only The Rows We Need
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c => c.Name.Contains("1"))
.ToList();
}
Console.WriteLine(stopWatch.Elapsed);
stopWatch = Stopwatch.StartNew();
// Inner Queries Work OK Depending On EF Version
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c => c.Name.Contains("1"))
.Select(c => new
{
c.Name,
OtherCats = c.Owner.Cats.ToList()
})
.ToList();
}
Console.WriteLine(stopWatch.Elapsed);
}
private static void TooManyQueries()
{
var stopWatch = Stopwatch.StartNew();
// N + 1
using (var db = new CatsDbContext())
{
var owners = db.Owners
.Where(o => o.Name.Contains("1"))
.ToList();
foreach (var owner in owners)
{
var cats = db.Cats
.Where(c => c.OwnerId == owner.Id && c.Name.Contains("1"))
.ToList();
}
}
Console.WriteLine(stopWatch.Elapsed);
stopWatch = Stopwatch.StartNew();
// Using Include
using (var db = new CatsDbContext())
{
var owners = db.Owners
.Where(o => o.Name.Contains("1"))
.Include(o => o.Cats)
.ToList();
foreach (var owner in owners)
{
var cats = owner.Cats
.Where(c => c.Name.Contains("1"))
.ToList();
}
}
Console.WriteLine(stopWatch.Elapsed);
stopWatch = Stopwatch.StartNew();
// Using Select
using (var db = new CatsDbContext())
{
var owners = db.Owners
.Where(o => o.Name.Contains("1"))
.Select(o => new
{
Cats = o.Cats
.Where(c => c.Name.Contains("1"))
})
.ToList();
}
Console.WriteLine(stopWatch.Elapsed);
}
private static void MiscellaneousOptimizations()
{
// Evaluating Slow Queries
using (var db = new CatsDbContext())
{
db.Cats
.Where(c =>
c.BirthDate.Year == 2019 &&
c.Color.Contains("B") &&
c.Owner.Cats.Any(c => c.Age < 3) &&
c.Owner.Cats.Count(c => c.Name.Length > 3) > 3)
.OrderBy(c => c.Owner.Cats.Average(c => c.BirthDate.Year))
.Select(c => new CatFamilyResult
{
Name = c.Name,
Cats = c.Owner
.Cats
.Count(c =>
c.Age < 3 &&
c.Name.StartsWith("A"))
})
.ToList();
}
// Indexing
using (var db = new CatsDbContext())
{
var cat = db.Cats
.Where(c => c.Age > 10)
.ToList();
}
// Client Functions
using (var db = new CatsDbContext())
{
var cat = db.Cats
.Where(c => c.Owner.Name.Length > 3)
.Select(c => new
{
c.Name,
Owner = c.Owner.Name
})
.ToList();
Console.WriteLine($"{db.ChangeTracker.Entries().Count()} Tracked Entities.");
}
// Use Async/Await
// Dispose The DbContext Frequently
// Use Caching Techniques
// Enable Multiple Result Sets
// Try Entity Framework Plus
// Entity Framework May Not Be The Answer
// -- Try Dapper
// -- Use Native SQL
}
private static void DeleteOptimized()
{
// Makes two queries - one read and one delete
using (var db = new CatsDbContext())
{
var cat = db.Cats.Find(15);
db.Remove(cat);
db.SaveChanges();
}
// Makes one query - only delete
using (var db = new CatsDbContext())
{
var cat = new Cat {
Id = 16
};
db.Remove(cat);
db.SaveChanges();
}
// Delete multiple rows - slow
using (var db = new CatsDbContext())
{
var catsToDelete = db.Cats
.Where(c => c.Age == 9)
.Select(c => c.Id);
db.RemoveRange(catsToDelete.Select(id => new Cat {
Id = id
}));
db.SaveChanges();
}
// Delete multiple rows - fast with SQL
using (var db = new CatsDbContext())
{
db.Database
.ExecuteSqlInterpolated($"DELETE FROM Cats WHERE Age = {9}");
}
}
private static void SameQuery(CatsDbContext db)
{
var cat = db.Cats
.Where(c =>
c.BirthDate.Year == 2019 &&
c.Color.Contains("B") &&
c.Owner.Cats.Any(c => c.Age < 5) &&
c.Owner.Cats.Count(c => c.Name.Length > 3) > 3)
.Select(c => new
{
c.Name,
Cats = c.Owner
.Cats
.Count(c =>
c.Age < 5 &&
c.Name.StartsWith("C"))
})
.ToList();
}
private static void CatQuery(CatsDbContext db, int age, string nameStart)
{
var cat = db.Cats
.Where(c =>
c.BirthDate.Year == 2019 &&
c.Color.Contains("B") &&
c.Owner.Cats.Any(c => c.Age < age) &&
c.Owner.Cats.Count(c => c.Name.Length > 3) > 3)
.Select(c => new CatFamilyResult
{
Name = c.Name,
Cats = c.Owner
.Cats
.Count(c =>
c.Age < age &&
c.Name.StartsWith(nameStart))
})
.ToList();
}
private static void CompiledQueries()
{
var stopWatch = Stopwatch.StartNew();
// First query will be slow
using (var db = new CatsDbContext())
{
CatQuery(db, 5, "C");
}
Console.WriteLine($"{stopWatch.Elapsed} - Cold");
stopWatch = Stopwatch.StartNew();
// Second query will be faster because of EF internal cache
using (var db = new CatsDbContext())
{
CatQuery(db, 5, "C");
}
Console.WriteLine($"{stopWatch.Elapsed} - Warm");
stopWatch = Stopwatch.StartNew();
// Third query will use our compiled query for the first time
using (var db = new CatsDbContext())
{
CatQueries.CatQuery(db, 5, "C");
}
Console.WriteLine($"{stopWatch.Elapsed} - Compiled Cold");
stopWatch = Stopwatch.StartNew();
// Fourth query will use our compiled query after it is processed
using (var db = new CatsDbContext())
{
CatQueries.CatQuery(db, 5, "C");
}
Console.WriteLine($"{stopWatch.Elapsed} - Compiled Warm");
}
private static void ColdAndWarmQueries()
{
var stopWatch = Stopwatch.StartNew();
// First query will be slow
using (var db = new CatsDbContext())
{
SameQuery(db);
}
Console.WriteLine(stopWatch.Elapsed);
stopWatch = Stopwatch.StartNew();
// Second query will be fast
using (var db = new CatsDbContext())
{
SameQuery(db);
}
Console.WriteLine(stopWatch.Elapsed);
}
private static void SeedData()
{
using (var db = new CatsDbContext())
{
db.Database.Migrate();
db.ChangeTracker.AutoDetectChangesEnabled = true;
for (int i = 1; i <= 10000; i++)
{
var owner = new Owner
{
Name = $"Owner {i}"
};
for (int j = 1; j <= 10; j++)
{
owner.Cats.Add(new Cat
{
Name = $"Cat {i} {j}",
Color = j % 2 == 0 ? "Black" : "White",
BirthDate = DateTime.Now.AddDays(-j),
Age = j
});
}
db.Owners.Add(owner);
if (i % 200 == 0)
{
db.SaveChanges();
Console.Write(".");
}
}
}
}
private static void ChangeTracking()
{
// Warm-up
using (var db = new CatsDbContext())
{
var cat = db.Cats
.Where(c => c.Id % 10 == 0)
.ToList();
}
var stopWatch = Stopwatch.StartNew();
// No tracking on a DbContext instance level
using (var db = new CatsDbContext())
{
db.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
var cat = db.Cats
.Where(c => c.Id % 10 == 0)
.ToList();
Console.WriteLine($"{stopWatch.Elapsed} - No tracking per context");
Console.WriteLine($"{db.ChangeTracker.Entries().Count()} Tracked Entities.");
}
Console.WriteLine();
stopWatch = Stopwatch.StartNew();
// No tracking per single query
using (var db = new CatsDbContext())
{
var cat = db.Cats
.AsNoTracking()
.Where(c => c.Id % 10 == 0)
.ToList();
Console.WriteLine($"{stopWatch.Elapsed} - No tracking per query");
Console.WriteLine($"{db.ChangeTracker.Entries().Count()} Tracked Entities.");
}
Console.WriteLine();
stopWatch = Stopwatch.StartNew();
// Default tracking
using (var db = new CatsDbContext())
{
var cat = db.Cats
.Where(c => c.Id % 10 == 0)
.ToList();
Console.WriteLine($"{stopWatch.Elapsed} - Tracking");
Console.WriteLine($"{db.ChangeTracker.Entries().Count()} Tracked Entities.");
}
Console.WriteLine();
stopWatch = Stopwatch.StartNew();
// Tracking in projection without a data model
using (var db = new CatsDbContext())
{
var cat = db.Cats
.Where(c => c.Id % 10 == 0)
.Select(c => new
{
c.Id,
c.Name,
Owner = c.Owner.Name
})
.ToList();
Console.WriteLine($"{stopWatch.Elapsed} - Tracking in projection without a data model");
Console.WriteLine($"{db.ChangeTracker.Entries().Count()} Tracked Entities.");
}
Console.WriteLine();
stopWatch = Stopwatch.StartNew();
// Tracking in projection with a data model
using (var db = new CatsDbContext())
{
var cat = db.Cats
.Where(c => c.Id % 10 == 0)
.Select(c => new
{
c.Id,
c.Name,
c.Owner
})
.ToList();
Console.WriteLine($"{stopWatch.Elapsed} - Tracking in projection with a data model");
Console.WriteLine($"{db.ChangeTracker.Entries().Count()} Tracked Entities.");
}
Console.WriteLine();
stopWatch = Stopwatch.StartNew();
// No tracking in projection with a data model
using (var db = new CatsDbContext())
{
var cat = db.Cats
.AsNoTracking()
.Where(c => c.Id % 10 == 0)
.Select(c => new
{
c.Id,
c.Name,
c.Owner
})
.ToList();
Console.WriteLine($"{stopWatch.Elapsed} - No tracking in projection with a data model");
Console.WriteLine($"{db.ChangeTracker.Entries().Count()} Tracked Entities.");
}
}
