How to Connect SQL Server with Entity Framework Core

In this post we’ll go through the steps to connect a .NET API to SQL Server using Entity Framework Core, and to create a SQL Server database from code using EF Core migrations.

We’ll start with an example .NET CRUD API from a tutorial I posted recently, it uses an EF Core InMemory database by default for testing, we’ll update it to connect to a SQL Server database and run EF Core migrations to auto generate the database and tables from code.

Requirements

To follow the steps in this tutorial you’ll need the following:

.NET SDK – includes the .NET runtime and command line tools.
Visual Studio Code – code editor that runs on Windows, Mac and Linux. If you have a different preferred code editor that’s fine too.
C# extension for Visual Studio Code – adds support to VS Code for developing .NET applications.
SQL Server – you’ll need access to running SQL Server instance for the API to connect to, it can be remote (e.g. Azure, AWS etc) or on your local machine. The Express edition of SQL Server is available for free at https://www.microsoft.com/en-us/sql-server/sql-server-downloads.

Download & Run the example .NET API

Follow these steps to download and run the .NET CRUD API on your local machine with the default EF Core InMemory database:

Download or clone the tutorial project code from https://github.com/cornflourblue/dotnet-5-crud-api
Start the api by running dotnet run from the command line in the project root folder (where the WebApi.csproj file is located), you should see the message Now listening on: http://localhost:4000.
You can test the API directly with a tool such as Postman.

Starting in debug mode

You can also start the application in debug mode in VS Code by opening the project root folder in VS Code and pressing F5 or by selecting Debug -> Start Debugging from the top menu, running in debug mode allows you to attach breakpoints to pause execution and step through the application code. For detailed instructions including a short demo video see VS Code + .NET – Debug a .NET Web App in Visual Studio Code.

Update .NET API to use SQL Server

Add SQL Server database provider from NuGet

Run the following command from the project root folder to install the EF Core database provider for SQL Server from NuGet:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

Add connection string to app settings

Open the appsettings.json file and add the entry "ConnectionStrings" with a child entry for the SQL Server connection string (e.g. "WebApiDatabase"), the connection string should be in the format "Data Source=[DB SERVER URL]; Initial Catalog=[DB NAME]; User Id=[USERNAME]; Password=[PASSWORD]", or to connect with the same account that is running the .NET API use the connection string format "Data Source=[DB SERVER URL]; Initial Catalog=[DB NAME]; Integrated Security=true".

When EF Core migrations generates the database, the Initial Catalog value will be the name of the database created in SQL Server.

The updated appsettings.json file with the connection string should look something like this:

{
    "ConnectionStrings": {
        "WebApiDatabase": "Data Source=localhost; Initial Catalog=dotnet-5-crud-api; User Id=testUser; Password=testPass123"
    },
    "Logging": {
        "LogLevel": {
            "Microsoft": "Warning",
            "Microsoft.Hosting.Lifetime": "Information"
        }
    }
}

Update Data Context to Use SQL Server

The DataContext class located at /Helpers/DataContext.cs is used for accessing application data through Entity Framework. It derives from the Entity Framework DbContext class and has a public Users property for accessing and managing user data.

Update the OnConfiguring() method to connect to SQL Server instead of an in memory database by replacing options.UseInMemoryDatabase("TestDb"); with options.UseSqlServer(Configuration.GetConnectionString("WebApiDatabase"));.

The updated DataContext class should look like this:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using WebApi.Entities;

namespace WebApi.Helpers
{
    public class DataContext : DbContext
    {
        protected readonly IConfiguration Configuration;

        public DataContext(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            // connect to sql server with connection string from app settings
            options.UseSqlServer(Configuration.GetConnectionString("WebApiDatabase"));
        }

        public DbSet<User> Users { get; set; }
    }
}

Create SQL Database from code with EF Core Migrations

Install dotnet ef tools

The .NET Entity Framework Core tools (dotnet ef) are used to generate EF Core migrations, to install the EF Core tools globally run dotnet tool install -g dotnet-ef, or to update run dotnet tool update -g dotnet-ef. For more info on EF Core tools see https://docs.microsoft.com/ef/core/cli/dotnet

Add EF Core Design package from NuGet

Run the following command from the project root folder to install the EF Core design package, it provides cross-platform command line tooling support and is used to generate EF Core migrations:

dotnet add package Microsoft.EntityFrameworkCore.Design

Generate EF Core migrations

Generate new EF Core migration files by running the command dotnet ef migrations add InitialCreate from the project root folder (where the WebApi.csproj file is located), these migrations will create the database and tables for the .NET Core API.

Execute EF Core migrations

Run the command dotnet ef database update from the project root folder to execute the EF Core migrations and create the database and tables in SQL Server.

Check SQL Server and you should now see your database with the tables Users and __EFMigrationsHistory.

Restart .NET CRUD API

Stop and restart the API with the command dotnet run from the project root folder, you should see the message Now listening on: http://localhost:4000 and the API should now be connected to SQL Server.