In many articles and websites, there are many examples of how Dapper is faster than EF to select data in a database.
In this post instead, I want to check if Dapper is faster than EF for some CRUD operations like Insert, Update and Delete.
I have this curiosity because I have seen that there is an interesting plug-in for Dapper, called Dapper Plus, that extends the IDbConnection with high-performance bulk operations like insert, update, delete and more.
So, I decided to test Dapper and EF to check the performance using a Console Application project using .NET 7.0.
DB DEFINITION
For this post, I have used SQL Server 2019 where I have create a database called “TestPerformance” with a table called “TabUser”.
USE [TestPerformance]
GO
/****** Object: Table [dbo].[TabUser] Script Date: 14/06/2023 18:18:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TabUser](
[Id] [int] NOT NULL,
[UserName] [nvarchar](255) NULL,
[Password] [nvarchar](255) NULL,
[CreationDate] [datetime] NULL,
CONSTRAINT [PK_TabUser] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
EF
For EF, I have defined the entity TabUser, the DbContext and the repository.
Finally, I have defined a class called TabUserCore_EF as Business Layer:
[TABUSER.CS]
namespace TestPerformance.EF;
public class TabUser
{
public int Id { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public DateTime CreationDate { get; set; }
}
[DATABASECONTEXT.CS]
using Microsoft.EntityFrameworkCore;
namespace TestPerformance.EF;
public class DatabaseContext: DbContext
{
public DbSet<TabUser> TabUser { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
// In a real project, it would be better not put the connection string here.
string connectionString = "Server=localhost;Database=TestPerformance;User Id=sa;Password=Camillo2021;Encrypt=False;";
optionsBuilder.UseSqlServer(connectionString, options =>
{
options.EnableRetryOnFailure();
});
}
}
[ITABUSERREPOSITORY.CS]
namespace TestPerformance.EF;
public interface ITabUserRepository
{
IEnumerable<TabUser> GetAll();
void DeleteAll();
void UpdateAll(IEnumerable<TabUser> users);
void InsertAll(IEnumerable<TabUser> users);
}
[TABUSERREPOSITORY_EF.CS]
using Microsoft.EntityFrameworkCore;
namespace TestPerformance.EF
{
public class TabUserRepository_EF : ITabUserRepository
{
private readonly DatabaseContext _context;
public TabUserRepository_EF(DatabaseContext context)
{
_context = context;
}
public void DeleteAll()
{
var allUsers = _context.TabUser.ToList();
_context.TabUser.RemoveRange(allUsers);
_context.SaveChanges();
}
public IEnumerable<TabUser> GetAll()
{
return _context.TabUser.ToList();
}
public void InsertAll(IEnumerable<TabUser> users)
{
_context.AddRange(users);
_context.SaveChanges();
}
public void UpdateAll(IEnumerable<TabUser> users)
{
foreach (var user in users)
{
_context.Entry(user).State = EntityState.Modified;
}
_context.SaveChanges();
}
}
}
[ITABUSERCORE.CS]
namespace TestPerformance.EF;
public interface ITabUserCore
{
IEnumerable<TabUser> GetAllUsers();
bool DeleteAllUsers();
bool UpdateAllUsers(IEnumerable<TabUser> users);
bool InsertAllUsers(IEnumerable<TabUser> users);
}
[TABUSERCORE_EF.CS]
using Microsoft.EntityFrameworkCore;
using TestPerformance.Dapper;
namespace TestPerformance.EF;
public class TabUserCore_EF : ITabUserCore
{
private readonly TabUserRepository_EF _objEF;
public TabUserCore_EF()
{
_objEF = new TabUserRepository_EF(new DatabaseContext());
}
public bool DeleteAllUsers()
{
try
{
_objEF.DeleteAll();
return true;
}
catch
{
return false;
}
}
public IEnumerable<TabUser> GetAllUsers()
{
try
{
return _objEF.GetAll();
}
catch
{
return new List<TabUser>();
}
}
public bool InsertAllUsers(IEnumerable<TabUser> users)
{
try
{
_objEF.InsertAll(users);
return true;
}
catch
{
return false;
}
}
public bool UpdateAllUsers(IEnumerable<TabUser> users)
{
try
{
_objEF.UpdateAll(users);
return true;
}
catch
{
return false;
}
}
}
DAPPER
For Dapper, I have used the entity TabUser then, I have created a repository and finally, I have defined a class called TabUserCore_Dapper as Business Layer:
[TABUSERREPOSITORY_DAPPER.CS]
using Dapper;
using Microsoft.Data.SqlClient;
using TestPerformance.EF;
namespace TestPerformance.Dapper;
public class TabUserRepository_Dapper : ITabUserRepository
{
private string _connectionString = "Server=localhost;Database=TestPerformance;User Id=sa;Password=Camillo2021;Encrypt=False;";
public void DeleteAll()
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
var query = "DELETE FROM [TabUser]";
connection.Execute(query);
}
}
public IEnumerable<TabUser> GetAll()
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
var query = "SELECT * FROM [TabUser]";
return connection.Query<TabUser>(query);
}
}
public void InsertAll(IEnumerable<TabUser> users)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
connection.Execute($"INSERT INTO [TabUser] (Id, UserName, Password, CreationDate) VALUES (@Id, @UserName, @Password, @CreationDate)", users);
}
}
public void UpdateAll(IEnumerable<TabUser> users)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
var query = "UPDATE [TabUser] SET UserName = @UserName, Password = @Password, CreationDate = @CreationDate WHERE Id = @Id";
connection.Execute(query, users);
}
}
}
[TABUSERCORE_DAPPER.CS]
using TestPerformance.EF;
namespace TestPerformance.Dapper;
public class TabUserCore_Dapper : ITabUserCore
{
private readonly TabUserRepository_Dapper _objDapper;
public TabUserCore_Dapper()
{
_objDapper= new TabUserRepository_Dapper();
}
public bool DeleteAllUsers()
{
try
{
_objDapper.DeleteAll();
return true;
}
catch
{
return false;
}
}
public IEnumerable<TabUser> GetAllUsers()
{
try
{
return _objDapper.GetAll();
}
catch
{
return new List<TabUser>();
}
}
public bool InsertAllUsers(IEnumerable<TabUser> users)
{
try
{
_objDapper.InsertAll(users);
return true;
}
catch
{
return false;
}
}
public bool UpdateAllUsers(IEnumerable<TabUser> users)
{
try
{
_objDapper.UpdateAll(users);
return true;
}
catch
{
return false;
}
}
}
Finally, in order to check the performance, I have created a class called TestAllMethods.cs that it will be called in Program.cs
[TESTALLMETHODS.CS]
using System.Diagnostics;
using TestPerformance.Dapper;
using TestPerformance.EF;
namespace TestPerformance;
public class TestAllMethods
{
private readonly ITabUserCore? _coreTabUserEF;
private readonly ITabUserCore? _coreTabUserDapper;
private readonly Stopwatch _stopWatch;
public TestAllMethods()
{
_coreTabUserEF = new TabUserCore_EF();
_coreTabUserDapper = new TabUserCore_Dapper();
_stopWatch = new Stopwatch();
}
public void RunAll(int totalItems)
{
Console.WriteLine("Testing EF");
EF_InsertAllUsers(totalItems);
EF_UpdateAllUsers(totalItems);
EF_DeleteAllUsers(totalItems);
Console.WriteLine("\n");
Console.WriteLine("Testing Dapper");
Dapper_InsertAllUsers(totalItems);
Dapper_UpdateAllUsers(totalItems);
Dapper_DeleteAllUsers(totalItems);
Console.WriteLine("\n\n");
}
private void EF_InsertAllUsers(int totalItems)
{
_stopWatch.Start();
TabUser objTabUser = null;
List<TabUser> lstTabUser = new List<TabUser>();
for (int i = 1; i <= totalItems; i++)
{
objTabUser = new TabUser();
objTabUser.Id = i;
objTabUser.UserName = "UserNameEF" + i.ToString();
objTabUser.Password = "PasswordEF" + i.ToString();
objTabUser.CreationDate = DateTime.Now;
lstTabUser.Add(objTabUser);
}
_coreTabUserEF.InsertAllUsers(lstTabUser);
_stopWatch.Stop();
Console.WriteLine($"The time elapsed to insert {totalItems} items is: {_stopWatch.Elapsed.TotalSeconds.ToString("N3")}");
}
private void EF_UpdateAllUsers(int totalItems)
{
_stopWatch.Restart();
var allUsers = _coreTabUserEF.GetAllUsers().ToList();
int index = 1;
foreach (var user in allUsers)
{
// Update user properties as needed
user.UserName = "NewUserNameEF" + index.ToString();
user.Password = "NewPasswordEF" + index.ToString();
user.CreationDate = DateTime.Now;
index++;
}
_coreTabUserEF.UpdateAllUsers(allUsers);
_stopWatch.Stop();
Console.WriteLine($"The time elapsed to update {totalItems} items is: {_stopWatch.Elapsed.TotalSeconds.ToString("N3")}");
}
private void EF_DeleteAllUsers(int totalItems)
{
_stopWatch.Restart();
_coreTabUserEF.DeleteAllUsers();
_stopWatch.Stop();
Console.WriteLine($"The time elapsed to delete {totalItems} items is: {_stopWatch.Elapsed.TotalSeconds.ToString("N3")}");
}
private void Dapper_InsertAllUsers(int totalItems)
{
_stopWatch.Start();
TabUser objTabUser = null;
List<TabUser> lstTabUser = new List<TabUser>();
for (int i = 1; i <= totalItems; i++)
{
objTabUser = new TabUser();
objTabUser.Id = i;
objTabUser.UserName = "UserNameDapper" + i.ToString();
objTabUser.Password = "PasswordDapper" + i.ToString();
objTabUser.CreationDate = DateTime.Now;
lstTabUser.Add(objTabUser);
}
_coreTabUserDapper.InsertAllUsers(lstTabUser);
_stopWatch.Stop();
Console.WriteLine($"The time elapsed to insert {totalItems} items is: {_stopWatch.Elapsed.TotalSeconds.ToString("N3")}");
}
private void Dapper_UpdateAllUsers(int totalItems)
{
_stopWatch.Restart();
var allUsers = _coreTabUserDapper.GetAllUsers().ToList();
int index = 1;
foreach (var user in allUsers)
{
// Update user properties as needed
user.UserName = "NewUserNameDapper" + index.ToString();
user.Password = "NewPasswordDapper" + index.ToString();
user.CreationDate = DateTime.Now;
index++;
}
_coreTabUserDapper.UpdateAllUsers(allUsers);
_stopWatch.Stop();
Console.WriteLine($"The time elapsed to update {totalItems} items is: {_stopWatch.Elapsed.TotalSeconds.ToString("N3")}");
}
private void Dapper_DeleteAllUsers(int totalItems)
{
_stopWatch.Restart();
_coreTabUserDapper.DeleteAllUsers();
_stopWatch.Stop();
Console.WriteLine($"The time elapsed to delete {totalItems} items is: {_stopWatch.Elapsed.TotalSeconds.ToString("N3")}");
}
}
[PROGRAM.CS]
using System.Diagnostics;
using TestPerformance;
Stopwatch stopwatch = new Stopwatch();
Console.WriteLine("Start Benchmark");
TestAllMethods objRun = new TestAllMethods();
Console.WriteLine("Test with 100000 items");
objRun.RunAll(100000);
Now, if we run the application, the following will be the result:
From this report, we can see that Dapper for Insert and Update is slower than EF instead, for Delete, it is much faster.
The result wasn’t a surprise for me because EF, for the Insert operation, uses “_context.AddRange(users)” that can be considered as a form of bulk insert.
Instead, I didn’t expect this result for the update.
I can image that, using Dapper Plus, it will improve the Dapper’s performance but, I have found some solutions that could change drastically the performance.
For the Insert, we can use the SqlBulkCopy which provides a high-performance mechanism for inserting a large number of rows into a SQL Server table.
Let’s change the method InsertAll in the TabUserRepository_Dapper.cs:
public void InsertAll(IEnumerable<TabUser> users)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
// Create a DataTable to hold the data
var dataTable = new DataTable();
dataTable.Columns.Add("Id", typeof(int)); // Add Id column
dataTable.Columns.Add("UserName", typeof(string)); // Add UserName column
dataTable.Columns.Add("Password", typeof(string)); // Add Password column
dataTable.Columns.Add("CreationDate", typeof(DateTime)); // Add CreationDate column
// Add rows to the DataTable
foreach (var user in users)
{
dataTable.Rows.Add(user.Id, user.UserName, user.Password, user.CreationDate); // Add a row with TabUser property values
}
// Use SqlBulkCopy to perform the bulk insert
using (var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "[TabUser]"; // Set the destination table name
bulkCopy.WriteToServer(dataTable); // Perform the bulk insert
}
}
}
If we run the application, the following will be the result:
Great! We can see that now Dapper is faster then EF!
For the Update, we can use the same approach:
public void UpdateAll(IEnumerable<TabUser> users)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
// Create a temporary table to hold the updated data
connection.Execute("CREATE TABLE #TempTabUser (Id INT, UserName NVARCHAR(100), Password NVARCHAR(100), CreationDate DATETIME)");
// Populate the temporary table with the updated data
using (var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "#TempTabUser"; // Set the destination table name to the temporary table
var dataTable = ConvertToDataTable(users); // Convert the IEnumerable<TabUser> to a DataTable
bulkCopy.WriteToServer(dataTable); // Perform the bulk insert into the temporary table
}
// Update the target table with the data from the temporary table
connection.Execute(@"UPDATE tu
SET tu.UserName = t.UserName,
tu.Password = t.Password,
tu.CreationDate = t.CreationDate
FROM [TabUser] tu
INNER JOIN #TempTabUser t ON tu.Id = t.Id");
// Drop the temporary table
connection.Execute("DROP TABLE #TempTabUser");
}
}
private DataTable ConvertToDataTable(IEnumerable<TabUser> users)
{
var dataTable = new DataTable();
dataTable.Columns.Add("Id", typeof(int)); // Add Id column to the DataTable
dataTable.Columns.Add("UserName", typeof(string)); // Add UserName column to the DataTable
dataTable.Columns.Add("Password", typeof(string)); // Add Password column to the DataTable
dataTable.Columns.Add("CreationDate", typeof(DateTime)); // Add CreationDate column to the DataTable
foreach (var user in users)
{
dataTable.Rows.Add(user.Id, user.UserName, user.Password, user.CreationDate); // Add a row with TabUser property values
}
return dataTable;
}
We have done and now, if we run the application, the following will be the result:
Finally, we will modify the Program file in order to run more tests:
using System.Diagnostics;
using TestPerformance;
Stopwatch stopwatch = new Stopwatch();
Console.WriteLine("Start Benchmark");
TestAllMethods objRun = new TestAllMethods();
Console.WriteLine("Test with 100000 items");
objRun.RunAll(100000);
Console.WriteLine("Test with 300000 items");
objRun.RunAll(300000);
Console.WriteLine("Test with 500000 items");
objRun.RunAll(500000);
If we run the application, the following will be the result:
