types-of-database-providers-in-asp-net-core

When building enterprise-grade applications in ASP.NET Core, choosing the right database provider is not just a technical decision — it directly impacts performance, scalability, maintainability, and cloud strategy.

In this article, we'll explore the different types of database providers available in ASP.NET Core, how they work internally, how to choose the right database provider, and when to use each — with real-world coding examples.

1. Understanding Database Providers in ASP.NET Core

ASP.NET Core itself does not talk directly to a database. Instead, it relies on:

The abstraction layer allows you to switch providers without rewriting your business logic.

The most common abstraction used today is Entity Framework Core (EF Core).

2. Entity Framework Core Relational Providers

EF Core supports multiple relational database engines through pluggable providers .

2.1 SQL Server Provider

Package

  
    Microsoft.EntityFrameworkCore.SqlServer

Best for

Setup Example

Install package

  
    dotnet add package Microsoft.EntityFrameworkCore.SqlServer

Register in Program.cs

  
    builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(
    builder.Configuration.GetConnectionString("DefaultConnection")));

Connection string ( appsettings.json )

  
    "ConnectionStrings": {
  "DefaultConnection": "Server=.;Database=AppDb;Trusted_Connection=True;"
}

Production scenario

2.2 PostgreSQL Provider

Package

  
    Npgsql.EntityFrameworkCore.PostgreSQL

Best for

Setup Example

  
    dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

builder.Services.AddDbContext<AppDbContext>(options =>
options.UseNpgsql(
    builder.Configuration.GetConnectionString("PostgresConnection")));

Connection string

  
    "PostgresConnection": "Host=localhost;Database=AppDb;Username=postgres;Password=pass"

Real-world use case

2.3 MySQL / MariaDB Provider

Package

  
    Pomelo.EntityFrameworkCore.MySql

Setup

  
    builder.Services.AddDbContext<AppDbContext>(options =>
options.UseMySql(
    builder.Configuration.GetConnectionString("MySqlConnection"),
    ServerVersion.AutoDetect(
        builder.Configuration.GetConnectionString("MySqlConnection"))));

Best for

2.4 SQLite Provider

Package

  
    Microsoft.EntityFrameworkCore.Sqlite

Use cases

Example

  
    builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlite("Data Source=app.db"));

Common in

3. In-Memory Provider (Testing)

Package

  
    Microsoft.EntityFrameworkCore.InMemory

Important: Not a real relational database.

Used for

Example

  
    builder.Services.AddDbContext<AppDbContext>(options =>
options.UseInMemoryDatabase("TestDb"));

Example Test

  
    [Fact]
public void AddUser_ShouldSaveUser()
{
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseInMemoryDatabase("TestDb")
        .Options;

    using var context = new AppDbContext(options);
    context.Users.Add(new User { Name = "John" });
    context.SaveChanges();

    Assert.Equal(1, context.Users.Count());
}

Use SQLite in-memory mode instead for realistic relational behavior.

4. NoSQL Database Providers

ASP.NET Core also supports NoSQL databases via dedicated SDKs.

4.1 MongoDB

Package

  
    MongoDB.Driver

Setup

  
    builder.Services.AddSingleton<IMongoClient>(
new MongoClient(builder.Configuration["MongoSettings:Connection"]));

Repository Example

  
    public class UserRepository
{
    private readonly IMongoCollection<User> _collection;

    public UserRepository(IMongoClient client)
    {
        var database = client.GetDatabase("AppDb");
        _collection = database.GetCollection<User>("Users");
    }

    public async Task CreateAsync(User user)
    {
        await _collection.InsertOneAsync(user);
    }
}

Use cases

4.2 Azure Cosmos DB

Provider

  
    Microsoft.EntityFrameworkCore.Cosmos

Setup

  
    builder.Services.AddDbContext<AppDbContext>(options =>
options.UseCosmos(
    builder.Configuration["Cosmos:AccountEndpoint"],
    builder.Configuration["Cosmos:AccountKey"],
    databaseName: "AppDb"));

Best for

5. Micro-ORM Providers (Dapper)

Sometimes EF Core is overkill.

Dapper works directly on ADO.NET providers.

Install

  
    dotnet add package Dapper

Example

  
    using (var connection = new SqlConnection(connectionString))
{
    var users = await connection.QueryAsync<User>(
        "SELECT * FROM Users WHERE IsActive = @IsActive",
        new { IsActive = true });
}

Use cases

6. ADO.NET Native Providers

Low-level database access.

Example with SQL Server

  
    using SqlConnection connection = new SqlConnection(connectionString);
await connection.OpenAsync();

SqlCommand command = new SqlCommand(
    "SELECT COUNT(*) FROM Users", connection);

int count = (int)await command.ExecuteScalarAsync();

Best for

7. Switching Providers Without Changing Business Logic

One of EF Core's biggest strengths is provider abstraction.

Example

Change

  
    options.UseSqlServer(...)

To

  
    options.UseNpgsql(...)

If your code avoids provider-specific features, everything works without changes.

Advice

Avoid raw SQL that ties you to one engine unless necessary.

8. How to Choose the Right Provider

Consider

  1. Hosting environment (Azure, AWS, on-prem)

  2. Licensing cost

  3. Team expertise

  4. Scalability requirements

  5. Transaction complexity

  6. Reporting needs

  7. Cloud-native architecture

Enterprise rule of thumb

9. Production Best Practices

Example Enabling Retry Logic

  
    options.UseSqlServer(connectionString,
sqlOptions => sqlOptions.EnableRetryOnFailure());

Key Takeaways

Database providers in ASP.NET Core are not just interchangeable components — they define how your system scales, performs, and evolves.

The power of ASP.NET Core lies in its provider abstraction model. You can build your domain logic once and swap infrastructure as needed.

As architects and senior developers, our responsibility is not just to make it work — but to make it scalable, testable, and future-ready.

Happy Coding!

I write about modern C#, .NET, and real-world development practices. Follow me on C# Corner for regular insights, tips, and deep dives.