Dapper vs EF Core Query Performance Benchmarking

More than four years ago, I published a post called Dapper vs Entity Framework vs ADO.NET Performance Benchmarking.  

In that post, I determined that Dapper performed markedly better for queries against a SQL Server database than Entity Framework did, and even outperformed ADO.NET in certain cases.

A lot has changed in the .NET world since then, and I figured it might be time for an updated version of this test. Only this time, we're going to compare Dapper with Entity Framework Core, and run the test in an ASP.NET Core application for good measure.

Let's see if we can determine whether Dapper or Entity Framework Core performs better for simple queries!

The Sample Project

As with all of my posts tagged as "Sample Project", there is a corresponding sample project on GitHub which has all the code used in this post. If you'd like to just skip to the code, click on the link below. Pull requests are welcome!

exceptionnotfound/DapperEFCoreQueryPerformance2019

Contribute to exceptionnotfound/DapperEFCoreQueryPerformance2019 development by creating an account on GitHub.

GitHubexceptionnotfound

A Little Background

Dapper and EF Core are two of the primary ORMs in use in the .NET world, despite the fact that Dapper isn't really an ORM at all; it's more of a mapper library designed to map data results to C# objects. Thus, it's really a very thin layer between your application and the database you are using.

EF Core, on the other hand, is a full-fledged ORM with lots of cool features, including change tracking. These features make this library a bit more cumbersome, and possibly a lot less performant than Dapper. But, as I have said before, performance doesn't matter unless you can prove that it matters, and for most apps the difference in speed between EF Core and Dapper will probably not be noticeable to the average user.

Because it appears to be common knowledge that EF Core is "slow", one of the most common pieces of advice I have heard when trying to speed up Entity Framework queries is to use a little method called .AsNoTracking(). Supposedly this method greatly improves performance on EF queries; our test shall treat this separately from "basic" EF Core queries.

With all that in mind, let's get started building our test!

Database Schema

Our test setup will use the following schema:

In other words: a Sport has many Teams, and a Team has many Players.  

I also needed some sample data to test against. The example project has an entire section for generating sample Sports, Teams, and Players; feel free to take a look.

Queries

I decided to run simple tests on three different kinds of queries:

1. Player by ID: Get a single Player object by their ID. This is the simplest kind of query.
2. Roster by Team ID: Gets a single Team and all related Player objects.
3. All Rosters by Sport ID: Gets a single Sport along with all Teams and every Player for each Team in that Sport.

As you can see, the queries get progressively more difficult, and each requires more data returned than the last. The goal with these queries is to see how Dapper and EF Core handle dealing with various amounts of data.

Test Setup - EF Core with Tracking

Note: Code samples in this post have been simplified from their true content to make them more readable.  Check out the sample project for the full code.

EF Core's claim to fame is the ease with which we can write queries, so let's take a look at the test setup for it first.

First, here's the code for the Player by ID query:

public void GetPlayerByID(int id)
{
    using (SportContextEfCore context = new SportContextEfCore(Database.GetOptions()))
    {
        var player = context.Players.Find(id);
    }
}

This query uses the .Find() method to get a single Player. In theory, this should be very fast, and it's about as simple as this kind of query can get.

Let's also see the code for the Roster by Team ID query:

public void GetRosterByTeamID(int teamId)
{
    using (SportContextEfCore context = new SportContextEfCore(Database.GetOptions()))
    {
        var teamRoster = context.Teams.Include(x => x.Players).Single(x => x.Id == teamId);
    }
}

The main difference here is the .Include() method which brings in the related Players objects for the given Team. This will definitely make this query slower than the simple Player by ID query, but it remains to be seen exactly how much slower.

Finally, let's see the code for the Rosters by Sport ID query:

public void GetTeamRostersForSport(int sportId)
{
    using (SportContextEfCore context = new SportContextEfCore(Database.GetOptions()))
    {
        var players = context.Teams.Include(x => x.Players).Where(x => x.SportId == sportId).ToList();
    }
}

The only real difference between this query and the prior one is that this one returns a list of Teams, not just a single one.

Test Setup - EF Core Without Tracking

The test setup for EF Core without tracking is so similar that I'm just going to put it here:

public class EntityFrameworkCore : ITestSignature
{
    public void GetPlayerByID(int id)
    {
        using (SportContextEfCore context = 
               new SportContextEfCore(Database.GetOptions()))
        {
            var player = context.Players.Find(id);
        }
    }

    public void GetRosterByTeamID(int teamId)
    {
        using (SportContextEfCore context = 
               new SportContextEfCore(Database.GetOptions()))
        {
            var players = context.Teams
                                 .Include(x => x.Players)
                                 .AsNoTracking()
                                 .Single(x => x.Id == teamId);
        }
    }

    public void GetTeamRostersForSport(int sportId)
    {
        using (SportContextEfCore context = 
               new SportContextEfCore(Database.GetOptions()))
        {
            var players = context.Teams
                                 .Include(x => x.Players)
                                 .Where(x => x.SportId == sportId)
                                 .AsNoTracking()
                                 .ToList();
        }
    }
}

Note: the GetPlayerByID() test is exactly the same in both EF Core test setups, because AsNoTracking() cannot be applied to the .Find() method.

Test Setup: Dapper

Finally, let's walk through the test setup for Dapper.

First, the code for the Player by ID query:

public void GetPlayerByID(int id)
{
    using (SqlConnection conn = 
             new SqlConnection(Constants.SportsConnectionString))
    {
        conn.Open();
        var player = conn.QuerySingle<PlayerDTO>("SELECT * FROM Player WHERE Id = @ID", new { ID = id });
    }
}

This one is pretty straightforward: use Dapper's QuerySingle<T>() to get a single Player by their ID.

Second, the code for the Roster by Team ID query:

public void GetRosterByTeamID(int teamId)
{
    using (SqlConnection conn = 
             new SqlConnection(Constants.SportsConnectionString))
    {
        conn.Open();
        var team = conn.QuerySingle<TeamDTO>("SELECT * FROM Team WHERE ID = @id", new { id = teamId });

        team.Players = conn.Query<PlayerDTO>("SELECT * FROM Player WHERE TeamId = @ID", new { ID = teamId }).ToList();
    }
}

Notice that this is actually two different hits to the database: one to get the team, and one to get all players. Dapper does have a MultiMap feature, but to the best of my knowledge it only handles one-to-one relationships and this is one-to-many, hence we need two database hits.

Finally, let's see the code for the Rosters by Sport ID query:

public void GetTeamRostersForSport(int sportId)
{
    using (SqlConnection conn = 
             new SqlConnection(Constants.SportsConnectionString))
    {
        conn.Open();
        var teams = conn.Query<TeamDTO>("SELECT * FROM Team WHERE SportID = @ID", new { ID = sportId });

        var teamIDs = teams.Select(x => x.Id).ToList();

        var players = conn.Query<PlayerDTO>("SELECT * FROM Player WHERE TeamID IN @IDs", new { IDs = teamIDs });

        foreach (var team in teams)
        {
            team.Players = players.Where(x => x.TeamId == team.Id).ToList();
        }
    }
}

Again, this is two different database hits. But does this even matter? Let's find out.

Results

Here's the results for a run with 10 Sports, 10 Teams per Sport, and 10 Players per Team:

Conclusions

Let's start with the obvious conclusion: Dapper is way faster than EF Core whether or not AsNoTracking() is used. That much is just as true as it was four years ago.

But there's also a less obvious conclusion, one that I saw every time I ran the test harness: the Roster by Team ID query takes less time than the "simpler" Player by ID query in both variants of EF Core tests. I find this puzzling; it suggests that maybe the .Find() method in EF Core isn't as optimized as I was lead to believe.

Indeed, when I ran the test again using .First() instead of .Find(), the results showed that .First() performed markedly better, on the order of 3-5 times quicker than the calls using .Find().

Drawbacks

Let's be honest: this kind of test isn't a true benchmarking. A real set of benchmarks would take into account execution time for creates, updates, deletes, and all other sorts of functionality.

What this test does not do is say that Dapper is "better" than EF Core. Dapper is faster in the absolute sense than EF Core, but that does not mean better. That kind of decision can only be made from your own investigations, requirements, circumstances, etc.  

I make no claim to saying Dapper is "better" than anything, though I freely admit to it being my favorite.

Summary

In short, and as expected, Dapper is still faster than EF Core, at least for these kinds of queries. This merely confirms what the developer community has long suspected: that in terms of raw performance, Dapper is still king of the ORM jungle.

Don't forget to check out the sample project over on GitHub!

Got any questions about this methodology? Think you can improve upon it? Think that maybe I'm off my rocker (which is entirely possible)?  Submit a pull request!

Also, if this post helped you, would you consider buying me a coffee? Your support funds all of my projects and helps me keep traditional ads off this site. Thanks!

Matthew Jones

I’m a .NET developer, blogger, speaker, husband and father who just really enjoys the teaching/learning cycle.One of the things I try to do differently with Exception...

BuyMeACoffee

Happy Coding!