How to configure the connection to databases in Entity Framework

4 min
Beginner

We have seen that Entity Framework supports different databases, such as SQL Server, SQLite, and MySQL.

The only difference between them is how we configure the database connection in our Entity Framework project.

But, apart from this configuration, the usage is identical and independent of the database we are using.

In fact, we can switch from one database provider to another without changing any code.

So let’s see how to configure our project to work with different types of databases.

Configuration of supported databases

The first thing we need to do to configure one database or another is to install the appropriate package for the database provider we are going to use.

Here are some examples of providers:

SQL Server

SQL Server: It is the most widely used relational database system with .NET, and it is easily configured with EF Core.
```
Install-Package Microsoft.EntityFrameworkCore.SqlServer
```

And we will define the connection string with

options.UseSqlServer(your_connection_string)

SQLite

SQLite: SQLite is a lightweight database, ideal for desktop or small-scale applications.

Install-Package Microsoft.EntityFrameworkCore.Sqlite

    And we will define the connection string with

options.UseSqlite(your_connection_string)

MySQL

MySQL: This provider allows integration of EF Core with MySQL and MariaDB databases.
```
Install-Package Pomelo.EntityFrameworkCore.MySql
```

PostgreSQL

PostgreSQL: PostgreSQL is a powerful and versatile database system.
```
Install-Package Npgsql.EntityFrameworkCore.PostgreSQL
```
And we will define the connection string with

options.UseNpgsql(your_connection_string)

Connection string configuration

On the other hand, we will need to format our connection string according to the database we are going to use.

The Connection String is a text fragment that contains the information Entity Framework needs to connect to your database instance.

Some examples of Connection Strings are,

SQL Server

SQL Server

"ConnectionStrings": {
  "DefaultConnection": "Server=localhost;Database=MyDatabase;User Id=myUsername;Password=myPassword;"
}

SQLite

SQLite

"ConnectionStrings": {
  "DefaultConnection": "Data Source=MyDatabase.db"
}

MySQL

MySQL

"ConnectionStrings": {
  "DefaultConnection": "Server=localhost;Database=MyDatabase;User=myUsername;Password=myPassword;"
}

PostgreSQL

PostgreSQL

"ConnectionStrings": {
  "DefaultConnection": "Host=localhost;Database=MyDatabase;Username=myUsername;Password=myPassword"
}

Where to store the connection string

A very important topic is where to store the Connection String. This generally contains sensitive information (like passwords, users…) that we obviously do not want to be public or leaked.

In a .NET project, we have different secure places where we can store resources (like the Connection String) securely, depending on the type of application and security and configuration requirements.

Let’s look at some of them,

Option	Use in…	Security	Ease
`appsettings.json` / `app.config`	Local, development, ASP.NET Core	🔴 Low	🟢 High
Environment variables	Production, servers	🟢 High	🟡 Medium
Azure Key Vault	Applications in Azure	🟢 Very high	🟡 Medium
User Secrets	Local development in .NET Core	🟡 Medium	🟢 High

Here are the most common options:

appsettings.json

For ASP.NET Core and .NET 5+ applications (including Web API, MVC, Blazor), it is stored in appsettings.json within the root folder of the project.

{
  "ConnectionStrings": {
    "MyConnection": "Server=myServer;Database=myDB;User Id=myUser;Password=myPass;"
  }
}

It is accessed in code through dependency injection

var connectionString = builder.Configuration.GetConnectionString("MyConnection");

app.config

For .NET Framework and older ASP.NET applications, it is stored in app.config (for console/WPF applications) or in web.config (for older applications).

For example in app.config / web.config:

<configuration>
  <connectionStrings>
    <add name="MyConnection" connectionString="Server=myServer;Database=myDB;User Id=myUser;Password=myPass;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

And it is accessed in code with:

using System.Configuration;
string connStr = ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;

Environment Variables

You can also define environment variables, or in a .env file (for Docker, Kubernetes, etc.). To read them in .NET:

string connStr = Environment.GetEnvironmentVariable("DB_CONNECTION_STRING");

Azure Key Vault

If the application runs in Azure, it is better to store the Connection String in Azure Key Vault instead of configuration files. It is obtained in code with:

var kvClient = new SecretClient(new Uri("https://my-key-vault.vault.azure.net/"), new DefaultAzureCredential());
string connStr = kvClient.GetSecret("MyConnection").Value;

User Secrets

If you are in development, you can store the connection in User Secrets to avoid exposing credentials in appsettings.json. Run in the terminal:

dotnet user-secrets init
dotnet user-secrets set "ConnectionStrings:MyConnection" "Server=myServer;Database=myDB;User Id=myUser;Password=myPass;"

And it is accessed the same way as appsettings.json:

var connectionString = builder.Configuration.GetConnectionString("MyConnection");

Practical example

Let’s look at a summary configuration example for SQL Server:

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=MyDatabase;User Id=sa;Password=MySecurePassword;"
  }
}

DbContext

The DbContext is the main class of Entity Framework that manages the connection to the database. To configure it, we must pass the connection string to the constructor of the DbContext class.

public class MyContext : DbContext
{
    public MyContext(DbContextOptions<MyContext> options) : base(options)
    {
    }

    public DbSet<MyEntity> MyEntities { get; set; }
}

Program.cs

var builder = WebApplication.CreateBuilder(args);

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

var app = builder.Build();

For MySQL, we use UseMySql instead of UseSqlServer or UseSqlite: