Overview

Quick overview on how to connect to an MS SQL Server database with ASP.NET Core 3.x to 5.x with Entity Frameworks. This post will go over which Nuget Packages are needed and how to connect to an existing database.

Visual Studios

For ASP.NET Core 3.1 and up you should use Visual Studio 2019 16.11.x.

To check the version go to Help -> About Microsoft Visual Studio

Go to Help -> Check For Updates.

New Project

Create a new ASP.NET Core MVC or API Project. From the top menu go to File -> New -> Project.

Select .NET 5.0 for Core 5 or Core 3.1 if you want to use an older version.

Nuget Packages

Install the following Nuget Packages that correspond to the ASP.NET Core version. So Core 3.1 you need to install 3.1x packages:

• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.Tools

For Microsoft.EntityFrameworkCore

Make sure the version matches the version of ASP.NET Core.

Example below uses 3.0 therefore the NuGet Package is 3.0.3.

For Microsoft.EntityFrameworkCore.SqlServer

For Microsoft.EntityFrameworkCore.Tools

Set up Application Settings File

Open appsettings.json and add a connection string like the example below:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "MyDB": "Server=SERVER;Database=DB_NAME;user=DB_USER;Password=DB_PASSWORD;Encrypt=false;"
  }
}

You will need to provide the following: SERVER, DB_NAME, DB_USER, and DB_PASSWORD. In this example the connection string is labeled “MyDB“.

Scaffold Command

Go to the Package Manager Console in VS2019

If don’t see the Package Manager Console go to the top menu and open View -> Other Windows -> Package Manager Console OR View > Package Manager Console. This will depend on your set up /preferences.

Execute the command below. Replace DB_NAME with what you labeled the connector as in the appsettings.json.

Scaffold-DbContext -Connection name=DB_NAME -Provider Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Note: Below is a single command,

This will generate all the models in the Database identified in the connection string.

Under the “./Models “folder find the DB_Context file.

Configure Services

In the Startup.cs file add the following code to enable the scaffolding tool to find your model files.

Add the imports for

using Microsoft.EntityFrameworkCore;
using YOURAPPNAME.Models;

Under ConfigureServices() add services.AddDbContext(). Use the DBContext with the context that was generated in the “./Models” folder where is and the connection name from “./appsettings.json” for MyDB

using Microsoft.EntityFrameworkCore;
using YOURPROJECTNAME.Models;

namespace YOURPROJECTNAME
{
    public class Startup
    {
        private readonly IConfiguration _configuration;
        public void Configure(IApplicationBuilder app)
        {
         
        }
        public Startup(IConfiguration configuration)
        {
            _configuration = configuration ?? throw new ArgumentNullException(nameof(configuration));
        }
 
       public void ConfigureServices(IServiceCollection services)
       {
           services.AddControllers();
           services.AddDbContext<DBContext>(options =>
                        options.UseSqlServer(_configuration.GetConnectionString("MyDB")));
       }

       public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
       {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            app.UseHttpsRedirection();
            app.UseRouting();
            //app.UseAuthentication();
            //app.UseAuthorization();
            //app.UseStatusCodePages();
            //app.UseCors();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

Now you can create a new controller using a model.

Select API -> API Controller with actions, using Entity Framework.

You should see the Model Classes in your Data context class.

Click add and you have a basic CRUD controller for the chosen model.