private static void LINQtoDb()
{
#region cache query
using (var db = new CatsDataConnection())
{
// LINQ to DB does not support Select joins
// You need to join your tables explicitly
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();
// LINQ to DB
using (var db = new CatsDataConnection())
{
// LINQ to DB does not support Select joins
// You need to join your tables explicitly
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 Join: {stopWatch.Elapsed} - {cats.Count} 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 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));
}
public static void Fight()
{
Console.WriteLine("Incorrect Data Loading Battle");
var stopWatch = Stopwatch.StartNew();
// EF Core Getting All Rows
using (var db = new CatsDbContext())
{
var cats = db.Cats
.ToList()
.Where(c => c.Name.Contains("1")); // This Where will be executed in memory.
Console.WriteLine($"EF Core Getting All Rows: {stopWatch.Elapsed} - {cats.Count()} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core Getting Only The Rows We Need
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c => c.Name.Contains("1"))
// .AsNoTracking()
.ToList();
Console.WriteLine($"EF Core Getting Only The Rows We Need: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core Getting Only The Rows We Need Cached
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Where(c => c.Name.Contains("1"))
// .AsNoTracking()
.ToList();
Console.WriteLine($"EF Core Getting Only The Rows We Need Cached: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB Getting All Rows
using (var db = new CatsDataConnection())
{
var cats = db.Cats
.ToList()
.Where(c => c.Name.Contains("1")); // This Where will be executed in memory.
Console.WriteLine($"LINQ to DB Getting All Rows: {stopWatch.Elapsed} - {cats.Count()} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB Getting Only The Rows We Need
using (var db = new CatsDataConnection())
{
var cats = db.Cats
.Where(c => c.Name.Contains("1"))
.ToList();
Console.WriteLine($"LINQ to DB Getting Only The Rows We Need: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB Getting Only The Rows We Need Cached
using (var db = new CatsDataConnection())
{
var cats = db.Cats
.Where(c => c.Name.Contains("1"))
.ToList();
Console.WriteLine($"LINQ to DB Getting Only The Rows We Need Cached: {stopWatch.Elapsed} - {cats.Count} Results");
}
Console.WriteLine(new string('-', 50));
}
public static void Fight()
{
Console.WriteLine("Too Many Queries Battle");
var stopWatch = Stopwatch.StartNew();
// EF Core N+1
using (var db = new CatsDbContext())
{
var owners = db.Owners
.Where(o => o.Name.Contains("1"))
.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");
}
stopWatch = Stopwatch.StartNew();
// EF Core Using Include
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;
}
Console.WriteLine($"EF Core Include: {stopWatch.Elapsed} - {total} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core Using Filtered Include - Introduced in EF Core 5.0
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");
}
stopWatch = Stopwatch.StartNew();
// EF Core 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($"EF Core Select: {stopWatch.Elapsed} - {owners.SelectMany(o => o.Cats).Count()} Results");
}
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");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB
using (var db = new CatsDataConnection())
{
// LINQ to DB does not support Select joins
// You need to join your tables explicitly
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 Join: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB Cached
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 Join Cached: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// RepoDB Query And In Memory Join
using (var connection = new SqlConnection(Settings.ConnectionString).EnsureOpen())
{
var dbOwners = connection.Query <Owner>(o => o.Name.Contains("1"));
var dbCats = connection.Query <Cat>(c => c.Name.Contains("1"));
var cats = dbOwners
.Join(dbCats,
o => o.Id,
c => c.OwnerId, (o, c) => c)
.ToList();
Console.WriteLine($"RepoDB Query And In Memory Join: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// RepoDB Query Multiple And In Memory Join
using (var connection = new SqlConnection(Settings.ConnectionString).EnsureOpen())
{
var result = connection.QueryMultiple <Owner, Cat>(
o => o.Name.Contains("1"),
c => c.Name.Contains("1"));
var dbOwners = result.Item1.AsList();
var dbCats = result.Item2.AsList();
var cats = dbOwners
.Join(dbCats,
o => o.Id,
c => c.OwnerId, (o, c) => c)
.ToList();
Console.WriteLine($"RepoDB Query Multiple And In Memory Join: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper With EF Query
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <Cat>(
@"SELECT [o].[Id], [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 CHARINDEX(N'1', [c].[Name]) > 0
) AS[t] ON[o].[Id] = [t].[OwnerId]
WHERE CHARINDEX(N'1', [o].[Name]) > 0
ORDER BY[o].[Id], [t].[Id]");
Console.WriteLine($"Dapper (EF): {stopWatch.Elapsed} - {cats.Count()} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper With EF Query Cached
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <Cat>(
@"SELECT [o].[Id], [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 CHARINDEX(N'1', [c].[Name]) > 0
) AS[t] ON[o].[Id] = [t].[OwnerId]
WHERE CHARINDEX(N'1', [o].[Name]) > 0
ORDER BY[o].[Id], [t].[Id]");
Console.WriteLine($"Dapper (EF) Cached: {stopWatch.Elapsed} - {cats.Count()} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper With LINQ to DB Query
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <Cat>(
@"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%'" );
Console.WriteLine($"Dapper (LINQ to DB): {stopWatch.Elapsed} - {cats.Count()} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper With LINQ to DB Query Cached
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <Cat>(
@"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%'" );
Console.WriteLine($"Dapper (LINQ to DB) cached: {stopWatch.Elapsed} - {cats.Count()} Results");
}
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");
}
Console.WriteLine(new string('-', 50));
}
public static void Fight()
{
Console.WriteLine("Delete Battle");
var stopWatch = Stopwatch.StartNew();
// EF Core Makes 2 Queries - One Read and One Delete
using (var db = new CatsDbContext())
{
var cat = db.Cats.Find(1);
db.Remove(cat);
db.SaveChanges();
Console.WriteLine($"EF Core - 2 Queries: {stopWatch.Elapsed}");
}
stopWatch = Stopwatch.StartNew();
// EF Core Makes One Query - Only Delete
using (var db = new CatsDbContext())
{
var cat = new Cat {
Id = 2
};
db.Remove(cat);
db.SaveChanges();
Console.WriteLine($"EF Core - 1 Query: {stopWatch.Elapsed}");
}
Console.WriteLine(new string('-', 20));
stopWatch = Stopwatch.StartNew();
// EF Core Delete Multiple Rows - Slow
using (var db = new CatsDbContext())
{
var catsToDelete = db.Cats
.Where(c => c.Age == 1)
.Select(c => c.Id)
.ToList();
db.RemoveRange(catsToDelete.Select(id => new Cat {
Id = id
}));
db.SaveChanges();
Console.WriteLine($"EF Core Delete Multiple Rows - Remove Range: {stopWatch.Elapsed} - {catsToDelete.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core Delete Multiple Rows - Fast with SQL
using (var db = new CatsDbContext())
{
var deleted = db.Database.ExecuteSqlInterpolated($"DELETE FROM Cats WHERE Age = {2}");
Console.WriteLine($"EF Core Delete Multiple Rows - SQL: {stopWatch.Elapsed} - {deleted} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Plus
using (var db = new CatsDbContext())
{
var catsToDelete = db.Cats
.Where(c => c.Age == 3)
.Delete();
Console.WriteLine($"EF Plus: {stopWatch.Elapsed} - {catsToDelete} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB Delete Multiple Rows
using (var db = new CatsDataConnection())
{
var deleted = LinqToDB.LinqExtensions.Delete(db.Cats, c => c.Age == 4);
Console.WriteLine($"LINQ to DB: {stopWatch.Elapsed} - {deleted} Results");
}
stopWatch = Stopwatch.StartNew();
// RepoDB Delete Multiple Rows
using (var sqlConnection = new SqlConnection(Settings.ConnectionString).EnsureOpen())
{
var deleted = sqlConnection.Delete <Cat>(c => c.Age == 5);
Console.WriteLine($"RepoDB Delete Multiple Rows: {stopWatch.Elapsed} - {deleted} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper
using var connection = new SqlConnection(Settings.ConnectionString);
var dapperDeleted = connection.Execute("DELETE FROM Cats WHERE Age = @Id", new { Id = 6 });
Console.WriteLine($"Dapper: {stopWatch.Elapsed} - {dapperDeleted} Results");
Console.WriteLine(new string('-', 50));
}
public static void Fight()
{
Console.WriteLine("Lazy Loading Too Many Queries Battle");
var stopWatch = Stopwatch.StartNew();
// EF Core N+1 Lazy Loading
using (var db = new CatsDbContext(true))
{
var cats = db.Cats
.Where(c => c.Name.Contains("1"))
.ToList();
var ownerNames = new List <string>();
// Usually in another method
foreach (var cat in cats)
{
var ownerName = cat.Owner.Name;
ownerNames.Add(ownerName);
}
Console.WriteLine($"EF Core N+1 Lazy Loading: {stopWatch.Elapsed} - {ownerNames.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core Using Join
using (var db = new CatsDbContext())
{
var ownerNames = db.Cats
.Where(o => o.Name.Contains("1"))
.Join(db.Owners,
c => c.OwnerId,
o => o.Id, (c, o) => o.Name)
.ToList();
Console.WriteLine($"EF Core Join: {stopWatch.Elapsed} - {ownerNames.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core 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($"EF Core Using Select: {stopWatch.Elapsed} - {ownerNames.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core Using Select Cached
using (var db = new CatsDbContext())
{
var ownerNames = db.Cats
.Where(c => c.Name.Contains("1"))
.Select(c => c.Owner.Name)
.ToList();
Console.WriteLine($"EF Core Using Select Cached: {stopWatch.Elapsed} - {ownerNames.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB
using (var db = new CatsDataConnection())
{
// LINQ to DB does not support Select joins
// You need to join your tables explicitly
var ownerNames = db.Cats
.Where(o => o.Name.Contains("1"))
.Join(db.Owners,
c => c.OwnerId,
o => o.Id, (c, o) => o.Name)
.ToList();
Console.WriteLine($"LINQ to DB Join: {stopWatch.Elapsed} - {ownerNames.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB Cached
using (var db = new CatsDataConnection())
{
// LINQ to DB does not support Select joins
// You need to join your tables explicitly
var ownerNames = db.Cats
.Where(o => o.Name.Contains("1"))
.Join(db.Owners,
c => c.OwnerId,
o => o.Id, (c, o) => o.Name)
.ToList();
Console.WriteLine($"LINQ to DB Join Cached: {stopWatch.Elapsed} - {ownerNames.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// RepoDB Query And In Memory Join
using (var connection = new SqlConnection(Settings.ConnectionString).EnsureOpen())
{
var dbOwners = connection.QueryAll <Owner>();
var dbCats = connection.Query <Cat>(c => c.Name.Contains("1"));
var cats = dbOwners
.Join(dbCats,
o => o.Id,
c => c.OwnerId, (o, c) => c.Name)
.ToList();
Console.WriteLine($"RepoDB Query And In Memory Join: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper With EF Query
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <Cat>(
@"SELECT [o].[Name]
FROM [Cats] AS [c]
INNER JOIN [Owners] AS [o] ON [c].[OwnerId] = [o].[Id]
WHERE CHARINDEX(N'1', [c].[Name]) > 0");
Console.WriteLine($"Dapper (EF): {stopWatch.Elapsed} - {cats.Count()} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper With EF Query Cached
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <Cat>(
@"SELECT [o].[Name]
FROM [Cats] AS [c]
INNER JOIN [Owners] AS [o] ON [c].[OwnerId] = [o].[Id]
WHERE CHARINDEX(N'1', [c].[Name]) > 0");
Console.WriteLine($"Dapper (EF) Cached: {stopWatch.Elapsed} - {cats.Count()} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper With LINQ to DB Query
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <Cat>(
@"SELECT
[o_1].[Name]
FROM
[Cats] [o_2]
INNER JOIN [Owners] [o_1] ON [o_2].[OwnerId] = [o_1].[Id]
WHERE
[o_2].[Name] LIKE N'%1%'" );
Console.WriteLine($"Dapper (LINQ to DB): {stopWatch.Elapsed} - {cats.Count()} Results");
}
stopWatch = Stopwatch.StartNew();
// Dapper With LINQ to DB Query Cached
using (var connection = new SqlConnection(Settings.ConnectionString))
{
var cats = connection.Query <Cat>(
@"SELECT
[o_1].[Name]
FROM
[Cats] [o_2]
INNER JOIN [Owners] [o_1] ON [o_2].[OwnerId] = [o_1].[Id]
WHERE
[o_2].[Name] LIKE N'%1%'" );
Console.WriteLine($"Dapper (LINQ to DB) 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] [o_2]
INNER JOIN [Owners] [o_1] ON [o_2].[OwnerId] = [o_1].[Id]
WHERE
[o_2].[Name] LIKE N'%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));
}
public static void Fight()
{
Console.WriteLine("Inner Query Battle");
var stopWatch = Stopwatch.StartNew();
// EF Core Inner Query (Depends On EF Version - Optimized After 3.0)
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Select(c => new CatSiblingsResult
{
Id = c.Id,
Name = c.Name,
Siblings = c.Owner.Cats
.Where(s => s.Id != c.Id)
.Select(s => s.Name)
.ToList()
})
.ToList();
Console.WriteLine($"EF Core Inner Query: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core Inner Query Cached (Depends On EF Version - Optimized After 3.0)
using (var db = new CatsDbContext())
{
var cats = db.Cats
.Select(c => new CatSiblingsResult
{
Id = c.Id,
Name = c.Name,
Siblings = c.Owner.Cats
.Where(s => s.Id != c.Id)
.Select(s => s.Name)
.ToList()
})
.ToList();
Console.WriteLine($"EF Core Inner Query Cached: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// EF Core No Inner Query - In Memory Calculation
using (var db = new CatsDbContext())
{
var dbCats = db.Cats
.Select(c => new
{
c.Id,
c.Name,
c.OwnerId
})
.ToList();
// Evaluated in memory.
var cats = dbCats
.GroupJoin(
dbCats,
c => c.OwnerId,
s => s.OwnerId,
(f, s) => new CatSiblingsResult
{
Id = f.Id,
Name = f.Name,
Siblings = s
.Where(si => si.Id != f.Id)
.Select(si => si.Name)
.ToList()
})
.ToList();
Console.WriteLine($"EF Core No Inner Query - In Memory Calculation: {stopWatch.Elapsed} - {cats.Count} Results");
}
stopWatch = Stopwatch.StartNew();
// LINQ to DB Does Not Allow Inner Queries
using (var db = new CatsDataConnection())
{
var dbCats = db.Cats
.Select(c => new
{
c.Id,
c.Name,
c.OwnerId
})
.ToList();
// Evaluated in memory.
var cats = dbCats
.GroupJoin(
dbCats,
c => c.OwnerId,
s => s.OwnerId,
(f, s) => new CatSiblingsResult
{
Id = f.Id,
Name = f.Name,
Siblings = s
.Where(si => si.Id != f.Id)
.Select(si => si.Name)
.ToList()
})
.ToList();
Console.WriteLine($"LINQ to DB No Inner Query - In Memory Calculation: {stopWatch.Elapsed} - {cats.Count} Results");
}
Console.WriteLine(new string('-', 50));
}