3


Entity Framework 8 v Dapper for select * with 1000 records




Date Added (UTC):

03 May 2024 @ 00:30

Date Updated (UTC):

03 May 2024 @ 00:31


.NET Version(s):

.NET 8

Tag(s):

#DBOperations #EntityFramework #Dapper


Added By:
Profile Image

Blog   
Ireland    
.NET Developer and tech lead from Ireland!

Benchmark Results:





Benchmark Code:



using BenchmarkDotNet.Attributes;
using Microsoft.EntityFrameworkCore;
using Dapper;
using System;
using System.Collections.Generic;
using Microsoft.Data.SqlClient;
using System.Linq;
using BenchmarkDotNet.Configs;
using BenchmarkDotNet.Reports;
using BenchmarkDotNet.Columns;

public class Event
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime Date { get; set; }
    public string Venue { get; set; }
}

public class SportsContext : DbContext
{
    public DbSet<Event> Events { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer(@"Server=localhost;Database=TestDB;Trusted_Connection=True;TrustServerCertificate=true");
    }
}

[MemoryDiagnoser]
[Config(typeof(Config))]
[HideColumns(Column.AllocRatio, Column.RatioSD, Column.Median, Column.Gen0, Column.Gen1)]
public class EFDapperBenchmark
{
    private const string ConnectionString = 
        @"Server=localhost;Database=TestDB;Trusted_Connection=True;TrustServerCertificate=true";

    private SportsContext _context;
    private SqlConnection _connection;

    [GlobalSetup]
    public void Setup()
    {
        _context = new SportsContext();
        _connection = new SqlConnection(ConnectionString);

        _context.Database.EnsureDeleted();
        _context.Database.EnsureCreated();

        // Seed data
        var events = Enumerable.Range(1, 1000).Select(i => new Event
        {
            Name = $"Event {i}",
            Date = DateTime.Now.AddDays(i),
            Venue = $"Venue {i}"
        }).ToList();

        _context.Events.AddRange(events);
        _context.SaveChanges();
    }

    [Benchmark]
    public List<Event> EFWithContextCost()
    {
        using var context = new SportsContext();
        return context.Events.AsNoTracking().ToList();
    }

    [Benchmark]
    public List<Event> EFNoContextCost()
    {
        return _context.Events.AsNoTracking().ToList();
    }


    [Benchmark]
    public List<Event> DapperWithConnectionCost()
    {
        using var connection = new SqlConnection(ConnectionString);
        return connection.Query<Event>("SELECT * FROM Events").ToList();
    }

    [Benchmark(Baseline = true)]
    public List<Event> DapperNoConnectionCost()
    {
        return _connection.Query<Event>("SELECT * FROM Events").ToList();
    }

    private class Config : ManualConfig
    {
        public Config()
        {
            SummaryStyle =
                SummaryStyle.Default
                .WithRatioStyle(RatioStyle.Percentage)
                .WithTimeUnit(Perfolizer.Horology.TimeUnit.Millisecond);
        }
    }
}

// .NET 8 Lowered C# Code unavailable due to errors:
error CS0234: The type or namespace name 'EntityFrameworkCore' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)
error CS0246: The type or namespace name 'Dapper' could not be found (are you missing a using directive or an assembly reference?)
error CS0234: The type or namespace name 'Data' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)
error CS0246: The type or namespace name 'DbContext' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbSet<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbContextOptionsBuilder' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)
error CS1061: 'SportsContext' does not contain a definition for 'Database' and no accessible extension method 'Database' accepting a first argument of type 'SportsContext' could be found (are you missing a using directive or an assembly reference?)
error CS1061: 'SportsContext' does not contain a definition for 'SaveChanges' and no accessible extension method 'SaveChanges' accepting a first argument of type 'SportsContext' could be found (are you missing a using directive or an assembly reference?)
error CS1674: 'SportsContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'.

// .NET 8 IL Code unavailable due to errors:
error CS0234: The type or namespace name 'EntityFrameworkCore' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)
error CS0246: The type or namespace name 'Dapper' could not be found (are you missing a using directive or an assembly reference?)
error CS0234: The type or namespace name 'Data' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)
error CS0246: The type or namespace name 'DbContext' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbSet<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbContextOptionsBuilder' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)
error CS1061: 'SportsContext' does not contain a definition for 'Database' and no accessible extension method 'Database' accepting a first argument of type 'SportsContext' could be found (are you missing a using directive or an assembly reference?)
error CS1061: 'SportsContext' does not contain a definition for 'SaveChanges' and no accessible extension method 'SaveChanges' accepting a first argument of type 'SportsContext' could be found (are you missing a using directive or an assembly reference?)
error CS1674: 'SportsContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'.

// .NET 8 Jit Asm Code unavailable due to errors:
error CS0234: The type or namespace name 'EntityFrameworkCore' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)
error CS0246: The type or namespace name 'Dapper' could not be found (are you missing a using directive or an assembly reference?)
error CS0234: The type or namespace name 'Data' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)
error CS0246: The type or namespace name 'DbContext' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbSet<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbContextOptionsBuilder' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)
error CS1061: 'SportsContext' does not contain a definition for 'Database' and no accessible extension method 'Database' accepting a first argument of type 'SportsContext' could be found (are you missing a using directive or an assembly reference?)
error CS1061: 'SportsContext' does not contain a definition for 'SaveChanges' and no accessible extension method 'SaveChanges' accepting a first argument of type 'SportsContext' could be found (are you missing a using directive or an assembly reference?)
error CS1674: 'SportsContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'.


Benchmark Description:


Benchmark comparing simple select * (1000 records) for Entity Framework v Dapper. When both the context and the connection is initialized and ***included in the benchmark itself*** the difference is about 38%. Obviously creating a DbContext is expensive compared to creating an SqlConnection object. When both the context and the connection is ***initialized in the global setup method*** the difference is 20% with Dapper being faster on both setups.

The provided benchmark code is designed to compare the performance of fetching data from a database using two popular .NET technologies: Entity Framework Core (EF Core) and Dapper. It aims to measure and compare the overhead of different approaches within each technology, specifically focusing on the costs associated with context initialization in EF Core and connection management in Dapper. The benchmarks are set up to run in a .NET environment, though the exact .NET version isn't specified in the provided code. Given the use of EF Core and Dapper, it's likely intended for .NET Core 3.1 or .NET 5/6, as these versions are commonly used with these libraries. ### General Setup - **Database Context and Model**: A `SportsContext` derived from `DbContext` is defined with a `DbSet<Event>` to interact with `Event` entities in the database. The `Event` class includes properties for `Id`, `Name`, `Date`, and `Venue`. - **Database Configuration**: The connection string points to a local SQL Server instance, using a database named `TestDB`. It's configured for trusted connection and to trust the server certificate. - **Benchmark Configuration**: The benchmarks use `BenchmarkDotNet`, a powerful library for benchmarking in .NET. Memory usage is diagnosed, and certain columns are hidden in the output to focus on relevant metrics. A custom configuration modifies the summary style. - **Data Seeding**: In the `GlobalSetup` method, the database is recreated, and 1000 `Event` entities are seeded. This ensures a consistent state for each benchmark run. ### Benchmark Methods #### 1. `EFWithContextCost` - **Purpose**: Measures the performance of querying data using EF Core with a new instance of `DbContext` for each operation. - **Rationale**: Instantiating a new context involves overhead. This benchmark assesses the impact of that overhead on query performance. - **Expected Insights**: Higher execution time compared to reusing a context, reflecting the cost of context initialization. #### 2. `EFNoContextCost` - **Purpose**: Evaluates the performance when reusing an existing `DbContext` instance for querying data with EF Core. - **Rationale**: Reusing a context should reduce overhead compared to creating new instances. This benchmark helps quantify that benefit. - **Expected Insights**: Lower execution time than `EFWithContextCost`, demonstrating the advantage of context reuse in EF Core. #### 3. `DapperWithConnectionCost` - **Purpose**: Measures the performance of executing a query with Dapper, creating a new database connection for each operation. - **Rationale**: Similar to the context cost in EF Core, opening a new connection has overhead. This benchmark assesses its impact when using Dapper. - **Expected Insights**: Higher execution time compared to reusing a connection, highlighting the cost of connection management. #### 4. `DapperNoConnectionCost` (Baseline) - **Purpose**: Evaluates Dapper's performance when reusing an existing database connection. - **Rationale**: Serves as the baseline for comparison, showing the efficiency of Dapper with minimal overhead from connection management. - **Expected Insights**: This method should have the lowest execution time among the benchmarks, showcasing the efficiency of Dapper when connection overhead is minimized. ### Conclusion These benchmarks are designed to provide insights into the performance trade-offs between using EF Core and Dapper for data access in .NET applications, with a particular focus on the costs associated with context and connection management. By analyzing the results, developers can make informed decisions about which technology and approach best suit their needs, balancing performance with the convenience and features offered by each library.


Benchmark Comments: