The last step in implementing data access using EF Core is to connect a DbContext instance to the database and run the migrations using the command exposed by the .NET CLI. Before doing that, we need to have a working database in our local environment. To keep our local development environment as lightweight as possible, this example will use a Docker image of Microsoft SQL Server on Linux. It is possible to get the Docker image here: https://hub.docker.com/r/microsoft/mssql-server-linux/. If you don't have any previous experience with Docker, you can follow this guide to install and set it up on your local machine: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-2017.

After running our SQL instance, let's create a new database called Store, by running the following commands:

docker exec -it sql1 "bash"

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YOUR_PASSWORD>'

1> CREATE LOGIN catalog_srv WITH PASSWORD = 'P@ssw0rd';
2> CREATE DATABASE Store;
3> GO
1> USE Store;
2> CREATE USER catalog_srv;
3> GO
1> EXEC sp_addrolemember N'db_owner', N'catalog_srv';
2> GO

Once we get Microsoft SQL Server working in our local environment, we can proceed by connecting our service with the database. The Startup class already present in the Catalog.API project will define the connection string and the provider used by our service. As we will see, all the migration classes will also be stored in the same project. This kind of approach guarantees a unique entry point, Catalog.API, for our .NET CLI instructions, without being tightly coupled with the database logic (Catalog.Infrastructure).

Before proceeding further, we need to add the Catalog.Infrastructure project as a reference for the API project by using the following command in the API project folder:

dotnet add reference ../Catalog.Infrastructure

The API project also requires you to refer to the Microsoft.EntityFrameworkCore.Design NuGet package, which shares design-time components for EF Core tools. We can add the latest version of the package by executing the following CLI instruction into the Catalog.API project folder: 

dotnet add package Microsoft.EntityFrameworkCore.Design

After that, we can proceed by adding the database connection in the Startup class:

using System;
using System.Reflection;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;
using Catalog.Infrastructure;

namespace Catalog.API
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
          ...
        }

        ...
        public void ConfigureServices(IServiceCollection services)
        {
              services
                .AddEntityFrameworkSqlServer()
                .AddDbContext<CatalogContext>(contextOptions =>
                {
                    contextOptions.UseSqlServer(
 "Server=localhost,1433;Initial Catalog=Store;User Id=<SA_USER>;Password=<PASSWORD>",
                        serverOptions => {             
                           serverOptions.MigrationsAssembly
                           (typeof(Startup).Assembly.FullName); });
                });
             ...
        }

        public void Configure(IApplicationBuilder app, 
            IHostingEnvironment env)
        {
          ...
        }
    }
}

The ConfigureServices method contains the initialization of the SQL connection. First of all, it adds the services required by the SQL provider using AddEntityFameworkSqlServer. Following that, it adds CatalogContext, utilizing the AddContext<T> generic method by passing an action method of the Action<DbContextOptionsBuilder> type. 

Finally, the action method configures the SQL Server provider by using the UseSqlServer extension method and passing the connection string for our database. The MigrationsAssembly method defines where the assemblies should be stored. In this case, it specifies that all migrations will be stored in our Catalog.API project.

To keep our Startup class clean and readable, we may create a custom extension method to initialize the connection to the Catalog database. Let's create a new folder called Extensions in our Catalog.API project, add a new DatabaseExtension class, and move our code into a new AddCatalogContext method:

using Catalog.Infrastructure;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;

namespace Catalog.API.Extensions
{
        public static class DatabaseExtensions
        {
            public static IServiceCollection AddCatalogContext(this 
                IServiceCollection services)
            {
                return services
                    .AddEntityFrameworkSqlServer()
                    .AddDbContext<CatalogContext>(contextOptions =>
                    {
                        contextOptions.UseSqlServer(
                            "Server=localhost,1433;Initial Catalog=Store;User Id=<SA_USER>;Password=<PASSWORD>",
                            serverOptions => { 
                                serverOptions.MigrationsAssembly
 (typeof(Startup).Assembly.FullName); });
                    });
            }
        }
}

We can simplify the Startup class as follows:

   public class Startup
    {
       ...

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddCatalogContext();
            ...
        }
    }

Now that the Startup class is ready, execute migrations in the Catalog.API project folder using the following commands:

dotnet ef migrations add InitMigration
dotnet ef database update

The first command generates the Migration folder and two different files inside it:

• {timestamp}_InitMigration.cs: This class creates the tables, constraints, and indexes present in the database.
• CatalogContextModelSnapshot.cs: This is formed only on the first migration command and represents the current state of the entities of the service.

Every migration class, including the one we just generated, has the following structure:

using Microsoft.EntityFrameworkCore.Migrations;

namespace Catalog.API.Migrations
{
    public partial class InitMigration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
          ...
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
          ...
        }
    }
}

The class contains two methods: Up and Down. The Up method is called during the generation of the database schema. The Down method is called during the deletion of the schema.

The generated tables and SQL entities are created under the catalog schema. The dotnet ef CLI tool will create a new migration class each time we execute the following command:

dotnet ef migrations add <migration_name>

The schema of the database will be refreshed every time we run the EF Core update process Core inside our project folder. Therefore, we can proceed by executing the following CLI command in the Catalog.API project folder:

dotnet ef database update

The preceding command creates the SQL schema using the migrations stored in the Migration folder of the project: it will connect to the database specified in the connection string, which is stored in the AddCatalogContext() extension method. In the next section, we will examine how to move the specified connection string into the appsettings.json file.