I’ll show two approaches you can choose from:
Approach A (recommended for most reads/writes): Use EF Core for schema, migrations, and most CRUD; use Dapper (Npgsql) for fast raw/complex queries. (Dapper uses its own IDbConnection.)
Approach B (when you need a single transaction across EF + Dapper): Reuse the DbConnection /transaction inside DbContext so Dapper executes on the same connection/transaction as EF Core.
Key libraries used: Dapper , Npgsql (Postgres ADO.NET driver), and Npgsql.EntityFrameworkCore.PostgreSQL (EF Core provider).
1. Create project & add packages
dotnet new webapi -n DapperEfPgDemo
cd DapperEfPgDemo
dotnet add package Dapper
dotnet add package Npgsql
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design
(Install dotnet-ef tool if you need CLI migrations: dotnet tool install --global dotnet-ef .)
2. Connection string (appsettings.json)
{
"ConnectionStrings": {
"DefaultConnection": "Host=localhost;Port=5432;Database=dapperefpg;Username=postgres;Password=your_password"
}
}
3. POCO models
Models/Product.cs
public class Product
{
public int Id { get; set; }
public string Name { get; set; } = null!;
public decimal Price { get; set; }
public int CategoryId { get; set; }
}
Models/Category.cs
public class Category
{
public int Id { get; set; }
public string Name { get; set; } = null!;
}
4. EF Core DbContext
Data/AppDbContext.cs
using Microsoft.EntityFrameworkCore;
public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) {}
public DbSet<Product> Products { get; set; } = null!;
public DbSet<Category> Categories { get; set; } = null!;
}
Register DbContext in Program.cs (below).
5. Program.cs (DI & EF + optional Dapper factory)
Program.cs (minimal)
using Npgsql;
using Microsoft.EntityFrameworkCore;
var builder = WebApplication.CreateBuilder(args);
var conn = builder.Configuration.GetConnectionString("DefaultConnection");
// EF Core
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseNpgsql(conn)
);
// OPTIONAL: register an IDbConnection factory for Dapper (Approach A)
builder.Services.AddScoped<System.Data.IDbConnection>(_ => new NpgsqlConnection(conn));
// register repositories, controllers, swagger...
builder.Services.AddScoped<IProductRepository, ProductRepository>();
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
var app = builder.Build();
app.UseSwagger();
app.UseSwaggerUI();
app.MapControllers();
app.Run();
You can either inject IDbConnection (a new NpgsqlConnection per scope) for Dapper, or use the DbContext ’s connection (shown below) when you need transaction sharing. npgsql.org
6. Create the DB (migrations)
dotnet ef migrations add InitialCreate
dotnet ef database update
(Use EF migrations for schema management — easier than hand-written SQL for most workflows.) npgsql.org
7. Repositories — two patterns
Pattern A — Dapper using its own IDbConnection (no shared transaction)
Good when you only need quick reads/writes and don't need EF & Dapper to share a single transaction.
Repositories/ProductDapperRepository.cs
using Dapper;
using System.Data;
public class ProductDapperRepository : IProductRepository
{
private readonly IDbConnection _db; // injected NpgsqlConnection (scoped)
public ProductDapperRepository(IDbConnection db) => _db = db;
public async Task<IEnumerable<Product>> GetAllAsync()
{
var sql = "SELECT id AS Id, name AS Name, price AS Price, categoryid AS CategoryId FROM products";
if (_db.State != ConnectionState.Open) await _db.OpenAsync();
return await _db.QueryAsync<Product>(sql);
}
public async Task<Product?> GetByIdAsync(int id)
{
var sql = "SELECT id, name, price, categoryid FROM products WHERE id = @Id";
if (_db.State != ConnectionState.Open) await _db.OpenAsync();
return await _db.QueryFirstOrDefaultAsync<Product>(sql, new { Id = id });
}
// Create/Update/Delete can use ExecuteAsync / ExecuteScalarAsync etc.
}
This is the simplest pattern and performs well for read-heavy, SQL-optimized queries.
Pattern B — Use EF DbContext's connection so EF & Dapper share the same connection/transaction
Use this when you need both EF and Dapper operations in the same transaction (commit/rollback together).
Repositories/ProductCombinedRepository.cs
using Dapper;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using System.Data;
public class ProductCombinedRepository
{
private readonly AppDbContext _db;
public ProductCombinedRepository(AppDbContext db) => _db = db;
// Dapper read using DbContext's connection (don't dispose the connection EF owns)
public async Task<IEnumerable<Product>> GetAllWithDapperAsync()
{
var conn = _db.Database.GetDbConnection(); // returns DbConnection
if (conn.State != ConnectionState.Open) await conn.OpenAsync();
var sql = "SELECT id AS Id, name AS Name, price AS Price, categoryid AS CategoryId FROM products";
// Dapper's QueryAsync accepts DbConnection since it implements IDbConnection
return await conn.QueryAsync<Product>(sql);
}
// Example: an operation that uses Dapper + EF in one transaction
public async Task<int> CreateProductAndLogAsync(Product product, string auditMessage)
{
// Start EF transaction
await using var efTx = await _db.Database.BeginTransactionAsync();
try
{
// 1) run a Dapper insert on the same connection/transaction
var conn = _db.Database.GetDbConnection();
if (conn.State != ConnectionState.Open) await conn.OpenAsync();
var insertSql = @"
INSERT INTO products (name, price, categoryid)
VALUES (@Name, @Price, @CategoryId)
RETURNING id;
";
// Get underlying ADO.NET transaction to pass to Dapper
var dbTransaction = efTx.GetDbTransaction(); // IDbTransaction
var newId = await conn.ExecuteScalarAsync<int>(insertSql, product, transaction: dbTransaction);
// 2) use EF Core to write to some tracked audit entity (or any other EF operation)
var audit = new AuditLog { Message = auditMessage, ProductId = newId, CreatedAt = DateTime.UtcNow };
_db.Add(audit);
await _db.SaveChangesAsync();
await efTx.CommitAsync();
return newId;
}
catch
{
await efTx.RollbackAsync();
throw;
}
}
}
Notes
Database.GetDbConnection() returns the DbConnection currently used by EF — do not dispose it if EF created it. Open it if closed.
BeginTransactionAsync() returns an IDbContextTransaction ; you can call .GetDbTransaction() to retrieve the underlying ADO.NET DbTransaction and pass it to Dapper. This is how you make Dapper run in the same DB transaction as EF.
8. Example controller (using combined repo)
Controllers/ProductsController.cs
[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
private readonly ProductCombinedRepository _repo;
public ProductsController(ProductCombinedRepository repo) => _repo = repo;
[HttpGet]
public async Task<IActionResult> GetAll() => Ok(await _repo.GetAllWithDapperAsync());
[HttpPost]
public async Task<IActionResult> Create([FromBody] Product p)
{
var id = await _repo.CreateProductAndLogAsync(p, $"Created via API at {DateTime.UtcNow:O}");
return CreatedAtAction(nameof(GetAll), new { id }, new { Id = id });
}
}
9. Important best-practices & gotchas
Use EF Core for schema/migrations and domain mapping; use Dapper for raw/complex queries or performance hotspots. This combination is common in production.
If you obtain the connection from DbContext.Database.GetDbConnection() do NOT dispose it (EF owns it). Just open it when needed.
To share a transaction: call BeginTransaction() / BeginTransactionAsync() on the DbContext , get the DbTransaction via GetDbTransaction() , and pass it to your Dapper calls (via the transaction: parameter). That keeps EF & Dapper in the same DB transaction.
Parameterize SQL — Dapper supports anonymous parameter objects (always avoid string concatenation to prevent SQL injection).
Unit testing: EF InMemory provider doesn't run raw SQL — tests that use Dapper need a real Postgres (or testcontainer).
Connection pooling: Npgsql pools connections; opening/closing is cheap. Prefer short-lived commands or let EF manage the connection lifetime when sharing.
10. Quick checklist / commands
Add packages: Dapper , Npgsql , Npgsql.EntityFrameworkCore.PostgreSQL , Microsoft.EntityFrameworkCore.Design .
Add AppDbContext and model DbSet s.
builder.Services.AddDbContext<AppDbContext>(options => options.UseNpgsql(conn));
Optionally register IDbConnection factory ( new NpgsqlConnection(conn) ) if you prefer separate Dapper connections.
dotnet ef migrations add InitialCreate → dotnet ef database update .
References/reading
Dapper (NuGet / repo).
Npgsql EF Core provider & docs.
EF Core transactions / using external DbTransaction (how to share a transaction with ADO.NET ).
Practical tutorial: Using EF Core + Dapper together (example & transaction patterns).
Note on GetDbConnection() disposal rules.