Dapper vs EF Core: Choosing the Right ORM for Your .NET App

using System.Data.SqlClient;
using Dapper;

public class OrderRepository
{
    private readonly string _connectionString;

    public OrderRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task<IEnumerable<Order>> GetOrdersByCustomerAsync(int customerId)
    {
        using var connection = new SqlConnection(_connectionString);
        var sql = "SELECT * FROM Orders WHERE CustomerId = @CustomerId";
        return await connection.QueryAsync<Order>(sql, new { CustomerId = customerId });
    }

    public async Task<Order?> GetOrderWithDetailsAsync(int orderId)
    {
        using var connection = new SqlConnection(_connectionString);
        var sql = @"SELECT * FROM Orders WHERE Id = @Id;
                     SELECT * FROM LineItems WHERE OrderId = @Id;";
        using var multi = await connection.QueryMultipleAsync(sql, new { Id = orderId });
        var order = await multi.ReadSingleOrDefaultAsync<Order>();
        if (order != null)
        {
            order.LineItems = (await multi.ReadAsync<LineItem>()).ToList();
        }
        return order;
    }
}

using Microsoft.EntityFrameworkCore;

public class OrderContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    public DbSet<LineItem> LineItems { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionString");
    }
}

public class OrderService
{
    private readonly OrderContext _context;

    public OrderService(OrderContext context)
    {
        _context = context;
    }

    public async Task<List<Order>> GetOrdersByCustomerAsync(int customerId)
    {
        return await _context.Orders
            .Where(o => o.CustomerId == customerId)
            .ToListAsync();
    }

    public async Task<Order?> GetOrderWithDetailsAsync(int orderId)
    {
        return await _context.Orders
            .Include(o => o.LineItems)
            .FirstOrDefaultAsync(o => o.Id == orderId);
    }
}

using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Running;
using Dapper;
using Microsoft.EntityFrameworkCore;
using System.Data.SqlClient;

public class OrmBenchmark
{
    private readonly string _connectionString = "...";
    private readonly SqlConnection _connection;
    private readonly OrderContext _context;

    public OrmBenchmark()
    {
        _connection = new SqlConnection(_connectionString);
        _context = new OrderContext(new DbContextOptionsBuilder<OrderContext>()
            .UseSqlServer(_connectionString).Options);
    }

    [Benchmark]
    public async Task<List<Order>> DapperGetOrders()
    {
        using var conn = new SqlConnection(_connectionString);
        var orders = await conn.QueryAsync<Order>("SELECT * FROM Orders");
        return orders.ToList();
    }

    [Benchmark]
    public async Task<List<Order>> EFCoreGetOrders()
    {
        return await _context.Orders.AsNoTracking().ToListAsync();
    }
}

// Run: BenchmarkRunner.Run<OrmBenchmark>();

public async Task<IEnumerable<SalesSummary>> GetSalesSummaryAsync(DateTime start, DateTime end)
{
    using var connection = new SqlConnection(_connectionString);
    var sql = @"
        SELECT 
            c.Name AS CustomerName,
            SUM(o.Total) AS TotalSales,
            COUNT(o.Id) AS OrderCount
        FROM Customers c
        INNER JOIN Orders o ON c.Id = o.CustomerId
        WHERE o.OrderDate BETWEEN @Start AND @End
        GROUP BY c.Name
        ORDER BY TotalSales DESC";
    return await connection.QueryAsync<SalesSummary>(sql, new { Start = start, End = end });
}

public async Task<List<SalesSummary>> GetSalesSummaryAsync(DateTime start, DateTime end)
{
    return await _context.Orders
        .Where(o => o.OrderDate >= start && o.OrderDate <= end)
        .GroupBy(o => o.Customer.Name)
        .Select(g => new SalesSummary
        {
            CustomerName = g.Key,
            TotalSales = g.Sum(o => o.Total),
            OrderCount = g.Count()
        })
        .OrderByDescending(s => s.TotalSales)
        .ToListAsync();
}

public class OrderService
{
    private readonly OrderContext _context;
    private readonly string _connectionString;

    public OrderService(OrderContext context, IConfiguration config)
    {
        _context = context;
        _connectionString = config.GetConnectionString("DefaultConnection");
    }

    public async Task<Order> CreateOrderAsync(Order order)
    {
        _context.Orders.Add(order);
        await _context.SaveChangesAsync();
        return order;
    }

    public async Task<IEnumerable<OrderSummary>> GetOrderSummariesAsync()
    {
        using var connection = new SqlConnection(_connectionString);
        var sql = "SELECT Id, CustomerName, Total FROM OrderSummaries";
        return await connection.QueryAsync<OrderSummary>(sql);
    }
}

// Safe: parameterized query in Dapper
var sql = "SELECT * FROM Users WHERE Email = @Email";
var user = await connection.QuerySingleOrDefaultAsync<User>(sql, new { Email = userInput });

// Safe: LINQ in EF Core
var user = await _context.Users.FirstOrDefaultAsync(u => u.Email == userInput);

// Unsafe: string concatenation (never do this)
var sql = $"SELECT * FROM Users WHERE Email = '{userInput}'";
var user = await connection.QueryAsync<User>(sql); // SQL injection!

// EF Core in-memory test
var options = new DbContextOptionsBuilder<OrderContext>()
    .UseInMemoryDatabase(databaseName: "TestDb")
    .Options;
using var context = new OrderContext(options);
context.Orders.Add(new Order { Id = 1, CustomerId = 1 });
context.SaveChanges();

// Dapper integration test (using real DB)
[Fact]
public async Task GetOrdersByCustomer_ReturnsOrders()
{
    using var connection = new SqlConnection(_connectionString);
    // Arrange: insert test data
    await connection.ExecuteAsync("INSERT INTO Orders (Id, CustomerId) VALUES (1, 1)");
    // Act
    var repo = new OrderRepository(_connectionString);
    var orders = await repo.GetOrdersByCustomerAsync(1);
    // Assert
    Assert.Single(orders);
}