// EF Core Using Filtered Include - Introduced in EF Core 5.0
private static void EfUsingFilteredInclude()
{
#region cache query
using (var db = new CatsDbContext())
{
var owners = db.Owners
.Where(o => o.Name.Contains("1"))
.Include(o => o.Cats.Where(c => c.Name.Contains("1")))
.ToList();
}
#endregion
var stopWatch = Stopwatch.StartNew();
using (var db = new CatsDbContext())
{
var owners = db.Owners
.Where(o => o.Name.Contains("1"))
.Include(o => o.Cats.Where(c => c.Name.Contains("1")))
.ToList();
Console.WriteLine(
$"EF Core Filtered Include: {stopWatch.Elapsed} - {owners.SelectMany(o => o.Cats).Count()} Results");
}
}
public async Task EfMultipleRowsWithLINQ()
{
using (var db = new CatsDbContext())
{
var catsToDelete = await db.Cats.Where(c => c.Age == 9)
.Select(c => c.Id)
.ToListAsync();
db.RemoveRange(catsToDelete.Select(id => new Cat {
Id = id
}));
await db.SaveChangesAsync();
}
// ...
//DELETE FROM[Cats]
//WHERE[Id] = @p33;
//SELECT @@ROWCOUNT;
//DELETE FROM[Cats]
//WHERE[Id] = @p34;
//SELECT @@ROWCOUNT;
//DELETE FROM[Cats]
//WHERE[Id] = @p35;
//SELECT @@ROWCOUNT;
// ...
}
private static async Task <CatsDbContext> GetData()
{
var dbContextOptions = new DbContextOptionsBuilder <CatsDbContext>()
.UseInMemoryDatabase(Guid.NewGuid().ToString()) // Or use SQLite, if you need relationships
.EnableSensitiveDataLogging()
.Options;
var dbContext = new CatsDbContext(dbContextOptions);
dbContext.Cats.Add(new Cat
{
Id = 1,
Name = "TestCat",
Age = 10,
Owner = new Owner
{
Id = 1,
Name = "TestOwner"
}
});
await dbContext.SaveChangesAsync();
return(dbContext);
}
private static void EfCoreUsingJoin()
{
#region cache query
using (var db = new CatsDbContext())
{
var cats = db.Owners
.Where(o => o.Name.Contains("1"))
.Join(db.Cats.Where(c => c.Name.Contains("1")),
o => o.Id,
c => c.OwnerId, (o, c) => c)
.ToList();
}
#endregion
var stopWatch = Stopwatch.StartNew();
// EF Core Using Join
using (var db = new CatsDbContext())
{
var cats = db.Owners
.Where(o => o.Name.Contains("1"))
.Join(db.Cats.Where(c => c.Name.Contains("1")),
o => o.Id,
c => c.OwnerId, (o, c) => c)
.ToList();
Console.WriteLine($"EF Core Join: {stopWatch.Elapsed} - {cats.Count} Results");
}
}
//delete multiple rows
// cats with age 4 = 1000 rows
// cats with age 5 = 1000 rows
// cats with age 7 = 1000 rows
public async Task Execute()
{
await EfMultipleRowsWithLINQ();
// EF Core Makes 2 Queries - One Read and One Delete
using (var db = new CatsDbContext())
{
var cat = await db.Cats.FindAsync(1);
db.Remove(cat);
await db.SaveChangesAsync();
}
// EF Core Makes One Query - Only Delete
using (var db = new CatsDbContext())
{
var cat = new Cat {
Id = 2
};
db.Remove(cat);
await db.SaveChangesAsync();
}
}
public async Task EfUsingInclude()
{
using (var db = new CatsDbContext())
{
var owners = await db.Owners.Where(o => o.Name.Contains("1"))
.Include(o => o.Cats)
.AsNoTracking()
.ToListAsync();
var total = 0;
foreach (var owner in owners)
{
total += owner.Cats.Count(c => c.Name.Contains("1"));
}
Console.WriteLine($"EF Core Include: {total} Results");
}
//SELECT[o].[Id], [o].[Name], [c].[Id], [c].[Age], [c].[BirthDate], [c].[Color], [c].[Name], [c].[OwnerId]
//FROM[Owners] AS[o]
//LEFT JOIN[Cats] AS[c] ON[o].[Id] = [c].[OwnerId]
//WHERE[o].[Name] LIKE N'%1%'
//ORDER BY[o].[Id], [c].[Id]
}
public async Task EfMultipleRowsWithRawSQL()
{
using (var db = new CatsDbContext())
{
await db.Database.ExecuteSqlInterpolatedAsync($"DELETE FROM Cats WHERE Age = {5}");
}
}
private static string GetAllOwnerNamesWithCatSelect()
{
using var db = new CatsDbContext();
var owners = db.Owners
.Where(o => o.Cats.Any())
.Select(o => o.Name)
.ToList();
return(string.Join(", ", owners));
}
static void Main(string[] args)
{
using (var db = new CatsDbContext())
{
db.Cats.Add(new Cat
Name = "Vanio",
Age = 5,
Color = "Black",
Owner = "Bai Ivan"
);
}
}
//var s = new Stopwatch();
//s.Start();
////Console.WriteLine("First query");
////var unitToProfiles = await _context.UnitToProfiles
//// .Where(e => e.ProfileID == 1
//// && e.Unit.RecordStatus == 1
//// && e.Unit.UnitTypeRef.HasEngine)
//// .Select(up => new UnitToProfile
//// {
//// ProfileID = up.ProfileID,
//// UnitID = up.UnitID,
//// Unit = new Unit()
//// {
//// RecordStatus = up.Unit.RecordStatus,
//// UnitTypeRef = up.Unit.UnitTypeRef,
//// UnitToUnitCombinations = up.Unit.UnitToUnitCombinations
//// .Select(uc => new UnitToUnitCombination()
//// {
//// UnitCombinationID = uc.UnitCombinationID,
//// UnitID = uc.UnitID
//// })
//// }
//// })
//// .ToListAsync();
//s.Stop();
// var ts = s.ElapsedMilliseconds;
//Console.WriteLine("\n");
// Console.WriteLine("\n");
// Console.WriteLine("Second query");
// var s1 = new Stopwatch();
//s1.Start();
// //var unitToProfiles1 = await _context.UnitToProfiles
// //.Include(e => e.Unit).ThenInclude(e => e.UnitTypeRef)
// //.Include(e => e.Unit).ThenInclude(e => e.UnitToUnitCombinations).ThenInclude(e => e.UnitCombination)
// //.Where(e => e.ProfileID == 1
// // && e.Unit.RecordStatus == 1
// // && e.Unit.UnitTypeRef.HasEngine)
// //.ToListAsync();
// s1.Stop();
// var ts1 = s1.ElapsedMilliseconds;
//Console.ReadLine();
// //s1.Stop();
// //var ts1 = s1.ElapsedMilliseconds;
public async Task <List <UnitToProfile> > NewMethod()
{
CatsDbContext _context = new CatsDbContext();
var unitToProfiles = await _context.UnitToProfiles
.Join(_context.Units,
up => up.UnitID,
u => u.ID,
(up, u) => new
{
UnitToProfile = new UnitToProfile
{
ProfileID = up.ProfileID,
UnitID = up.UnitID,
},
Unit = new Unit()
{
RecordStatus = up.Unit.RecordStatus,
UnitTypeRef = up.Unit.UnitTypeRef,
UnitToUnitCombinations = up.Unit.UnitToUnitCombinations
.Select(uc => new UnitToUnitCombination()
{
UnitCombinationID = uc.UnitCombinationID,
UnitID = uc.UnitID
})
}
})
.Where(e => e.UnitToProfile.ProfileID == 1 &&
e.Unit.RecordStatus == 1 &&
e.Unit.UnitTypeRef.HasEngine)
.Select(up => new UnitToProfile
{
ProfileID = up.UnitToProfile.ProfileID,
UnitID = up.UnitToProfile.UnitID,
Unit = up.Unit
})
.ToListAsync();
var unitToProfiles1 = await _context.UnitToProfiles
.Include(e => e.Unit).ThenInclude(e => e.UnitTypeRef)
.Include(e => e.Unit).ThenInclude(e => e.UnitToUnitCombinations).ThenInclude(e => e.UnitCombination)
.Where(e => e.ProfileID == 1 &&
e.Unit.RecordStatus == 1 &&
e.Unit.UnitTypeRef.HasEngine)
.ToListAsync();
return(unitToProfiles);
}
public static void Fight()
{
Console.WriteLine("Compiled Queries Battle");
var stopWatch = Stopwatch.StartNew();
// EF Core - First Query Is Cold
using (var db = new CatsDbContext())
{
var cats = CatQuery(db, 5, "C");
Console.WriteLine($"EF Core - Normal Query Cold: {stopWatch.Elapsed} - {cats} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core - Second Query Is Warm
using (var db = new CatsDbContext())
{
var cats = CatQuery(db, 5, "C");
Console.WriteLine($"EF Core - Normal Query Warm: {stopWatch.Elapsed} - {cats} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core - Same Query Compiled Cold
using (var db = new CatsDbContext())
{
var cats = CatQueries
.CatQuery(db, 5, "C")
.Count();
Console.WriteLine($"EF Core - Compiled Query Cold: {stopWatch.Elapsed} - {cats} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core - Same Query Compiled Warm
using (var db = new CatsDbContext())
{
var cats = CatQueries
.CatQuery(db, 5, "C")
.Count();
Console.WriteLine($"EF Core - Compiled Query Warm: {stopWatch.Elapsed} - {cats} Results");
}
Console.WriteLine(new string('-', 50));
}
private static void EFCoreRawSql()
{
#region cache query
using (var db = new CatsDbContext())
{
var cats = db.Cats
.FromSqlRaw(
@"SELECT
[c_1].[Id],
[c_1].[Name],
[c_1].[Age],
[c_1].[BirthDate],
[c_1].[Color],
[c_1].[OwnerId]
FROM
[Owners] [o]
INNER JOIN [Cats] [c_1] ON [o].[Id] = [c_1].[OwnerId]
WHERE
[c_1].[Name] LIKE N'%1%' AND [o].[Name] LIKE N'%1%'")
.AsNoTracking()
.ToList();
}
#endregion
var stopWatch = Stopwatch.StartNew();
// EF Core Raw SQL Query
using (var db = new CatsDbContext())
{
var cats = db.Cats
.FromSqlRaw(
@"SELECT
[c_1].[Id],
[c_1].[Name],
[c_1].[Age],
[c_1].[BirthDate],
[c_1].[Color],
[c_1].[OwnerId]
FROM
[Owners] [o]
INNER JOIN [Cats] [c_1] ON [o].[Id] = [c_1].[OwnerId]
WHERE
[c_1].[Name] LIKE N'%1%' AND [o].[Name] LIKE N'%1%'")
.AsNoTracking()
.ToList();
Console.WriteLine($"EF Core Raw SQL Query: {stopWatch.Elapsed} - {cats.Count} Results");
}
}
private static void EfCoreNPlus1Problem2(bool lazyLoadingEnabled)
{
using (var db = new CatsDbContext(lazyLoadingEnabled))
{
var oldCats = db.Cats
.Where(o => o.Name.Contains("1"))
.ToList();
foreach (var oldCat in oldCats)
{
// In case of lazy loading enabled
Console.WriteLine($"{oldCat.Name} - {oldCat.Age} - {oldCat.Owner.Name}");
}
}
}
private static IEnumerable <Cat> GetOldCats(bool printCats)
{
using var db = new CatsDbContext();
var oldCats = db.Cats
.Where(c => c.Age > 5)
.ToList();
if (printCats)
{
PrintOldCats(oldCats);
}
return(oldCats);
}
private static void EfUsingInclude()
{
#region cache query
using (var db = new CatsDbContext())
{
var owners = db.Owners
.Where(o => o.Name.Contains("1"))
.Include(o => o.Cats)
.ToList();
var total = 0;
foreach (var owner in owners)
{
var cats = owner.Cats
.Where(c => c.Name.Contains("1"))
.ToList();
total += cats.Count;
}
}
#endregion
var stopWatch = Stopwatch.StartNew();
using (var db = new CatsDbContext(true))
{
var owners = db.Owners
.Where(o => o.Name.Contains("1"))
.Include(o => o.Cats)
.ToList();
var total = 0;
foreach (var owner in owners)
{
var cats = owner.Cats
.Where(c => c.Name.Contains("1"))
.ToList();
total += cats.Count;
}
Console.WriteLine($"EF Core Include: {stopWatch.Elapsed} - {total} Results");
}
}
private static string GetAllOwnerNamesWithCat()
{
using var db = new CatsDbContext();
var owners = db.Owners
.Where(o => o.Cats.Any())
.ToList();
var names = new List <string>();
foreach (var owner in owners)
{
names.Add(owner.Name);
}
return(string.Join(", ", names));
}
private static void EfCoreNPlus1Problem2()
{
var stopWatch = Stopwatch.StartNew();
using (var db = new CatsDbContext(enableLazyLoading: true))
{
var oldCats = db.Cats.Where(o => o.Name.Contains("1")).ToList();
// Solution
//var oldCats = db.Cats.Include(c => c.Owner).Where(o => o.Name.Contains("1")).ToList();
foreach (var oldCat in oldCats)
{
// In case of lazy loading enabled
Console.WriteLine($"{oldCat.Name} - {oldCat.Age} - {oldCat.Owner.Name}");
}
Console.WriteLine($"EF Core N+1 (problem 2): {stopWatch.Elapsed}.");
}
}
private static void EfCoreNPlus1Problem3(bool lazyLoadingEnabled)
{
using var db = new CatsDbContext();
var oldCats = db.Cats
.Where(o => o.Name.Contains("1"))
.Select(c => new OldCatResult
{
Name = c.Name,
Age = c.Age,
Owner = c.Owner.Name
})
.ToList();
foreach (var oldCat in oldCats)
{
// In case of lazy loading enabled
Console.WriteLine($"{oldCat.Name} - {oldCat.Age} - {oldCat.Owner}");
}
}
private static void DataNoLoaded()
{
// 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
using var db = new CatsDbContext();
// Database will not be queried in the cats variable.
var cats = db.Cats
.Where(c => c.Name.Contains("1"))
.OrderBy(c => c.Name)
.Select(c => c.Name);
// Database will be queried here.
Console.WriteLine(cats.Count());
}
public async Task EfUsingFilteredInclude()
{
using (var db = new CatsDbContext())
{
var owners = await db.Owners.Where(o => o.Name.Contains("1"))
.Include(o => o.Cats.Where(c => c.Name.Contains("1")))
.AsNoTracking()
.ToListAsync();
Console.WriteLine($"EfUsingFilteredInclude: {owners.SelectMany(o => o.Cats).Count()} Results");
}
//SELECT[o].[Id], [o].[Name], [t].[Id], [t].[Age], [t].[BirthDate], [t].[Color], [t].[Name], [t].[OwnerId]
//FROM[Owners] AS[o]
//LEFT JOIN(
// SELECT[c].[Id], [c].[Age], [c].[BirthDate], [c].[Color], [c].[Name], [c].[OwnerId]
// FROM [Cats] AS [c]
// WHERE [c].[Name] LIKE N'%1%') AS[t] ON[o].[Id] = [t].[OwnerId]
//WHERE[o].[Name] LIKE N'%1%'
//ORDER BY[o].[Id], [t].[Id]
}
private static int CatQuery(CatsDbContext db, int age, string nameStart)
{
var cats = db.Cats
.Where(c =>
c.BirthDate.Year > 2019 &&
c.Color.Contains("B") &&
c.Owner.Cats.Any(cat => cat.Age < age) &&
c.Owner.Cats.Count(cat => cat.Name.Length > 3) > 3)
.Select(c => new CatFamilyResult
{
Name = c.Name,
Cats = c.Owner
.Cats
.Count(cat =>
cat.Age < age &&
cat.Name.StartsWith(nameStart))
})
.ToList();
return(cats.Count);
}
private static IEnumerable <OldCatResult> GetOldCatsSelect(bool printCats)
{
using var db = new CatsDbContext();
var oldCats = db.Cats
.Where(c => c.Age > 5)
.Select(c => new OldCatResult
{
Name = c.Name,
Age = c.Age,
Owner = c.Owner.Name
})
.ToList();
if (printCats)
{
PrintOldCatsSelect(oldCats);
}
return(oldCats);
}
public async Task EFCoreRawSql()
{
using (var db = new CatsDbContext())
{
var cats = await db.Cats
.FromSqlRaw(
@"SELECT
[c_1].[Id],
[c_1].[Name],
[c_1].[Age],
[c_1].[BirthDate],
[c_1].[Color],
[c_1].[OwnerId]
FROM [Owners] [o]
INNER JOIN [Cats] [c_1] ON [o].[Id] = [c_1].[OwnerId]
WHERE [c_1].[Name] LIKE N'%1%' AND [o].[Name] LIKE N'%1%'")
.AsNoTracking()
.ToListAsync();
Console.WriteLine($"EF Core Raw SQL Query: - {cats.Count} Results");
}
}
public async Task EfCoreUsingJoin()
{
using (var db = new CatsDbContext())
{
var cats = await db.Owners.Where(o => o.Name.Contains("1"))
.Join(db.Cats.Where(c => c.Name.Contains("1")),
o => o.Id,
c => c.OwnerId, (o, c) => c)
.AsNoTracking()
.ToListAsync();
Console.WriteLine($"EF Core Join: {cats.Count} Results");
}
//SELECT[t].[Id], [t].[Age], [t].[BirthDate], [t].[Color], [t].[Name], [t].[OwnerId]
//FROM[Owners] AS[o]
//INNER JOIN(
// SELECT[c].[Id], [c].[Age], [c].[BirthDate], [c].[Color], [c].[Name], [c].[OwnerId]
// FROM [Cats] AS [c]
// WHERE [c].[Name] LIKE N'%1%') AS[t] ON[o].[Id] = [t].[OwnerId]
//WHERE[o].[Name] LIKE N'%1%'
}
private static void EfNPlus1Problem1()
{
var stopWatch = Stopwatch.StartNew();
using (var db = new CatsDbContext(enableLazyLoading: true))
{
var owners = db.Owners.Where(o => o.Name.Contains("1")).ToList();
// Solution
// var owners = db.Owners.Where(o => o.Name.Contains("1")).Include(o => o.Cats).ToList();
var total = 0;
foreach (var owner in owners)
{
var cats = db.Cats.Where(c => c.OwnerId == owner.Id && c.Name.Contains("1")).ToList();
total += cats.Count;
}
Console.WriteLine($"EF Core N+1: {stopWatch.Elapsed} - {total} Results");
}
}
public static void Fight()
{
Console.WriteLine("Cold And Warm Queries Battle");
var stopWatch = Stopwatch.StartNew();
// EF Core - First Query Will Be Slow
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c =>
c.BirthDate.Year > 2019 &&
c.Color.Contains("B") &&
c.Owner.Cats.Any(cat => cat.Age < 5) &&
c.Owner.Cats.Count(cat => cat.Name.Length > 3) > 3)
.Select(c => new
{
c.Name,
Cats = c.Owner
.Cats
.Count(cat =>
cat.Age < 5 &&
cat.Name.StartsWith("C"))
})
.ToList();
Console.WriteLine($"EF Core - First Query: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core - Second Query Will Be Fast
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c =>
c.BirthDate.Year > 2019 &&
c.Color.Contains("B") &&
c.Owner.Cats.Any(cat => cat.Age < 5) &&
c.Owner.Cats.Count(cat => cat.Name.Length > 3) > 3)
.Select(c => new
{
c.Name,
Cats = c.Owner
.Cats
.Count(cat =>
cat.Age < 5 &&
cat.Name.StartsWith("C"))
})
.ToList();
Console.WriteLine($"EF Core - Second Query: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB - First Query Will Be Slow
using (var db = new CatsDataConnection())
{
var cats = db.Owners
.Where(o => o.Name.Contains("1"))
.Join(db.Cats.Where(c => c.Name.Contains("1")),
o => o.Id,
c => c.OwnerId, (o, c) => c)
.ToList();
Console.WriteLine($"LINQ to DB - First Query: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB - Second Query Will Be Fast
using (var db = new CatsDataConnection())
{
var cats = db.Owners
.Where(o => o.Name.Contains("1"))
.Join(db.Cats.Where(c => c.Name.Contains("1")),
o => o.Id,
c => c.OwnerId, (o, c) => c)
.ToList();
Console.WriteLine($"LINQ to DB - Second Query: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper - First Query
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <Cat>(
@"SELECT [c].[Name], [c].[Age]
FROM [Cats] AS [c]
INNER JOIN [Owners] AS [o] ON [c].[OwnerId] = [o].[Id]
WHERE (
SELECT COUNT(*)
FROM [Cats] AS [c0]
WHERE [o].[Id] = [c0].[OwnerId]) > 1");
Console.WriteLine($"Dapper - First Query: {stopWatch.Elapsed} - {cats.Count()} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper - Second Query
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <Cat>(
@"SELECT [c].[Name], [c].[Age]
FROM [Cats] AS [c]
INNER JOIN [Owners] AS [o] ON [c].[OwnerId] = [o].[Id]
WHERE (
SELECT COUNT(*)
FROM [Cats] AS [c0]
WHERE [o].[Id] = [c0].[OwnerId]) > 1");
Console.WriteLine($"Dapper - First Query: {stopWatch.Elapsed} - {cats.Count()} Results");
}
Console.WriteLine(new string('-', 50));
}
public PetsService(CatsDbContext db)
{
this.db = db;
}
public CatsController(CatsDbContext data) => this.data = data;
public CatsWithOwnersQueryHandler(CatsDbContext data) => this.data = data;
public static void Fight()
{
Console.WriteLine("Select Specific Columns Battle");
var stopWatch = Stopwatch.StartNew();
// EF Core Getting All Columns
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c => c.Owner.Cats.Count > 1)
// .AsNoTracking()
.ToDictionary(c => c.Name, c => c.Age);
Console.WriteLine($"EF Core Getting All Columns: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core Getting All Columns Cached
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c => c.Owner.Cats.Count > 1)
// .AsNoTracking()
.ToDictionary(c => c.Name, c => c.Age);
Console.WriteLine($"EF Core Getting All Columns Cached: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core Getting Only The Columns We Need
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c => c.Owner.Cats.Count > 1)
.Select(c => new CatResult
{
Name = c.Name,
Age = c.Age
})
.ToDictionary(c => c.Name, c => c.Age);
Console.WriteLine($"EF Core Getting Only The Columns We Need: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core Getting Only The Columns We Need Cached
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c => c.Owner.Cats.Count > 1)
.Select(c => new CatResult
{
Name = c.Name,
Age = c.Age
})
.ToDictionary(c => c.Name, c => c.Age);
Console.WriteLine($"EF Core Getting Only The Columns We Need Cached: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core Using Joins
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Join(
db.Owners.Where(o => o.Cats.Count > 1),
c => c.OwnerId,
o => o.Id,
(c, o) => new CatResult
{
Name = c.Name,
Age = c.Age
})
.ToDictionary(c => c.Name, c => c.Age);
Console.WriteLine($"EF Core Using Joins: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB Getting All Columns
using (var db = new CatsDataConnection())
{
var cats = (from cat in db.Cats
join owner in db.Owners
on cat.OwnerId equals owner.Id
join ownerCat in db.Cats
on owner.Id equals ownerCat.OwnerId
into ownersWithCats
where ownersWithCats.Count() > 1
select cat).ToDictionary(c => c.Name, c => c.Age);
Console.WriteLine($"LINQ to DB Getting All Columns: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB Getting All Columns Cached
using (var db = new CatsDataConnection())
{
var cats = (from cat in db.Cats
join owner in db.Owners
on cat.OwnerId equals owner.Id
join ownerCat in db.Cats
on owner.Id equals ownerCat.OwnerId
into ownersWithCats
where ownersWithCats.Count() > 1
select cat).ToDictionary(c => c.Name, c => c.Age);
Console.WriteLine($"LINQ to DB Getting All Columns Cached: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB Getting Only The Columns We Need
using (var db = new CatsDataConnection())
{
var cats = (from cat in db.Cats
join owner in db.Owners
on cat.OwnerId equals owner.Id
join ownerCat in db.Cats
on owner.Id equals ownerCat.OwnerId
into ownersWithCats
where ownersWithCats.Count() > 1
select new CatResult
{
Name = cat.Name,
Age = cat.Age
}).ToDictionary(c => c.Name, c => c.Age);
Console.WriteLine($"LINQ to DB Getting Only The Columns We Need: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB Getting Only The Columns We Need Cached
using (var db = new CatsDataConnection())
{
var cats = (from cat in db.Cats
join owner in db.Owners
on cat.OwnerId equals owner.Id
join ownerCat in db.Cats
on owner.Id equals ownerCat.OwnerId
into ownersWithCats
where ownersWithCats.Count() > 1
select new CatResult
{
Name = cat.Name,
Age = cat.Age
}).ToDictionary(c => c.Name, c => c.Age);
Console.WriteLine($"LINQ to DB Getting Only The Columns We Need Cached: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper Getting All Columns
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <Cat>(
@"SELECT *
FROM [Cats] AS [c]
INNER JOIN [Owners] AS [o] ON [c].[OwnerId] = [o].[Id]
WHERE (
SELECT COUNT(*)
FROM [Cats] AS [c0]
WHERE [o].[Id] = [c0].[OwnerId]) > 1");
Console.WriteLine($"Dapper Getting All Columns: {stopWatch.Elapsed} - {cats.Count()} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper Getting All Columns Cached
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <Cat>(
@"SELECT *
FROM [Cats] AS [c]
INNER JOIN [Owners] AS [o] ON [c].[OwnerId] = [o].[Id]
WHERE (
SELECT COUNT(*)
FROM [Cats] AS [c0]
WHERE [o].[Id] = [c0].[OwnerId]) > 1");
Console.WriteLine($"Dapper Getting All Columns Cached: {stopWatch.Elapsed} - {cats.Count()} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper Getting Only The Columns We Need
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <CatResult>(
@"SELECT [c].[Name], [c].[Age]
FROM [Cats] AS [c]
INNER JOIN [Owners] AS [o] ON [c].[OwnerId] = [o].[Id]
WHERE (
SELECT COUNT(*)
FROM [Cats] AS [c0]
WHERE [o].[Id] = [c0].[OwnerId]) > 1");
Console.WriteLine($"Dapper Getting Only The Columns We Need: {stopWatch.Elapsed} - {cats.Count()} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper Getting Only The Columns We Need Cached
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <CatResult>(
@"SELECT [c].[Name], [c].[Age]
FROM [Cats] AS [c]
INNER JOIN [Owners] AS [o] ON [c].[OwnerId] = [o].[Id]
WHERE (
SELECT COUNT(*)
FROM [Cats] AS [c0]
WHERE [o].[Id] = [c0].[OwnerId]) > 1");
Console.WriteLine($"Dapper Getting Only The Columns We Need Cached: {stopWatch.Elapsed} - {cats.Count()} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core Raw SQL Query
using (var db = new CatsDbContext())
{
// EF Core cannot translate this query.
try
{
var cats = db.Cats
.FromSqlRaw(
@"SELECT *
FROM [Cats] AS [c]
INNER JOIN [Owners] AS [o] ON [c].[OwnerId] = [o].[Id]
WHERE (
SELECT COUNT(*)
FROM [Cats] AS [c0]
WHERE [o].[Id] = [c0].[OwnerId]) > 1")
.ToList();
Console.WriteLine($"EF Core Raw SQL Query: {stopWatch.Elapsed} - {cats.Count} Results");
}
catch
{
Console.WriteLine("EF Core Raw SQL Query: CANNOT EXECUTE.");
}
}
Console.WriteLine(new string('-', 50));
}
