Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 41 ยท Getting Started

Providers and Connection Strings

Learn how EF Core database providers work, how to configure SQL Server, SQLite, PostgreSQL and in-memory options, and how to keep connection strings and secrets out of your source code.

EF Core itself knows nothing about any particular database. A database provider is a NuGet package that teaches it: which SQL dialect to generate, which column types exist, how to open connections and how to run migrations. Switching from SQLite on your laptop to PostgreSQL in production is mostly a change of provider package and connection string, while your entities and LINQ queries stay the same.

This lesson covers the common providers, the two ways to configure a context, how connection strings are built and kept safe, and a few provider differences that regularly cause surprises. The code is real EF Core, shown read-only; two stand-ins at the end let you practise parsing and choosing configuration.

The common providers

Database Package Configure with
SQL Server / Azure SQL Microsoft.EntityFrameworkCore.SqlServer UseSqlServer(cs)
SQLite Microsoft.EntityFrameworkCore.Sqlite UseSqlite(cs)
PostgreSQL Npgsql.EntityFrameworkCore.PostgreSQL UseNpgsql(cs)
MySQL / MariaDB Pomelo.EntityFrameworkCore.MySql UseMySql(cs, serverVersion)
Cosmos DB Microsoft.EntityFrameworkCore.Cosmos UseCosmos(...)
In-memory (tests only) Microsoft.EntityFrameworkCore.InMemory UseInMemoryDatabase(name)

SQL Server, SQLite, Cosmos and in-memory ship from Microsoft; Npgsql and Pomelo are community-maintained and follow EF Core releases closely. Always check that the provider version matches your EF Core major version.

flowchart TB
    A["Your DbContext + LINQ"] --> B["EF Core (database-agnostic)"]
    B --> C["SQL Server provider"]
    B --> D["SQLite provider"]
    B --> E["Npgsql provider"]
    C --> F[("SQL Server")]
    D --> G[("shop.db file")]
    E --> H[("PostgreSQL")]

One model, many providers.

Two ways to configure a context

1. Inside the context with OnConfiguring. Quick for demos and console apps:

C#
public class ShopContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=shop.db");
}

2. From the outside with options passed to the constructor. This is the normal choice in applications, because tests and environments can supply different options:

C#
public class ShopContext(DbContextOptions<ShopContext> options) : DbContext(options)
{
    public DbSet<Book> Books => Set<Book>();
}

In ASP.NET Core you register it once at startup, and the framework creates a context per request:

C#
builder.Services.AddDbContext<ShopContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("Shop")));

Registering with AddDbContext gives the context a scoped lifetime (one per request), which is exactly what EF Core expects. For background services and Blazor Server, use AddDbContextFactory so you can create short-lived contexts yourself.

Connection strings

A connection string is a semicolon-separated list of key=value settings. The keys depend on the provider:

TEXT
SQL Server : Server=localhost;Database=Shop;Trusted_Connection=True;TrustServerCertificate=True
SQLite     : Data Source=shop.db
PostgreSQL : Host=localhost;Port=5432;Database=shop;Username=app;Password=...

Store them in appsettings.json under ConnectionStrings, and read them with GetConnectionString("Shop"):

JSON
{
  "ConnectionStrings": {
    "Shop": "Host=localhost;Database=shop;Username=app"
  }
}

Keeping secrets out of source control

Never commit passwords. Use the layers .NET gives you, from least to most secure:

  1. User secrets for development: dotnet user-secrets set "ConnectionStrings:Shop" "...". They are stored outside the project folder.
  2. Environment variables in production: ConnectionStrings__Shop (double underscore maps to the colon). Container platforms and CI systems support them natively.
  3. A secret store such as Azure Key Vault, loaded into configuration at startup.
  4. Passwordless identity where the provider supports it (for example managed identity with Azure SQL), so there is no password at all.

Provider differences that bite

  • Types: SQLite stores decimal as text or real, so some decimal comparisons and aggregates behave differently from SQL Server. Test on the database you ship.
  • Case sensitivity: string comparison and LIKE depend on collation. SQL Server is usually case-insensitive; PostgreSQL is case-sensitive.
  • Features: ALTER COLUMN, schemas, sequences and certain index options are not available everywhere. SQLite migrations often rebuild the table.
  • In-memory provider: it is not a relational database. It ignores constraints and transactions, so it can hide bugs (testing is covered later in the course; prefer SQLite in-memory or the real database).

Stand-in: parse a connection string

This stand-in splits a connection string into settings, the way a provider's builder does, and hides the password when printing. It is not EF Core code.

C# Stand-in: parse and mask a connection string

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Stand-in: choose a provider by environment

A common pattern is SQLite for development and a server database for production. The stand-in shows the decision logic with plain strings.

C# Stand-in: pick a provider from configuration

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a database provider package do?

    EF Core is database-agnostic; each provider adapts it to a particular database.
  2. 2. Which registration gives a web app one DbContext per request?

    AddDbContext registers the context as scoped, which means one instance per request.
  3. 3. Where should a production password live?

    Use environment variables, a secret store or passwordless identity; never commit passwords.
  4. 4. Why is the in-memory provider a poor stand-in for your real database in tests?

    It can accept data a real database would reject, hiding bugs.

Summary

  • A provider package adapts EF Core to one database; switching is mostly a package plus a connection string.
  • Configure the context through constructor options and AddDbContext in real applications.
  • Keep connection strings in configuration; keep secrets in user secrets, environment variables or a vault.
  • Watch provider differences: decimal handling, collation, available DDL features.
  • The in-memory provider is not relational; prefer SQLite or the real database for tests.

Next: Logging and Seeing the SQL EF Core Runs