ASP.NET Core Web API + Entity Framework 6.0 – with EF Core Power Tools

Hello All,

In this tutorial we will see how to get started with ASP.NET Core web API with Entity Framework core.

In this tutorial I will be using Visual studio 2022 community edition and .NET 6.0 core, Entity Framework Core 6.0 and SQL Server 2019 community edition.

I will be using Database First Approach where I will be using an existing SQL database to scaffold the required entities. There are two ways to scaffold the SQL database.

1. Using a visual studio extension – EF Core Power Tools (for various tools link is provided below in the description) and
2. Reverse engineering – using commands in package manager console.

A little brief about Entity Framework Core.

Entity Framework is an  ORM-Object-relational mapping framework that helps to represent the Database into the object-oriented programming model in the .NET Core ecosystem helping to interact and perform CRUD operation on relational DB without hassle. EFCore  brings a higher level of abstraction and helps in creating data-oriented applications with less code and in an efficient way.

So let’s Get Started:

First lets install the EF Core power tools. Open the browser and search for EF Core power tools Market place and click on the first link (or use this link https://marketplace.visualstudio.com/items?). Download the extension ( this extension is for Visual Studio) . Once downloaded, double click the extension and follow the wizard to complete the installation. This will add extension in Visual Studio.

Next below is the simple SQL server database, containing two tables (employee and countries). The script is uploaded in github. We will be using EF core power tools to connect to the SQL Server database, scaffold the Employee table and generate entities in Visual Studio.

Next we will be creating ASP.NET Core Web API using Visual Studio 2022.

Lets start visual studio. Select File => New Project.

On the “Create a new project template” => Select Blank solution => select Next

Solution Name : ASPNETCore_WebAPI_EFCore_Demo. Click on Create

to create ASP.NET Core Web API project, Right click on the solution click => New Project. Select ASP.NET Core Web API and Click Next

On the Configure New Project – Enter project name WebAPI_Demo. Select the location you want to create the project and click on Next

On the Additional information – keep the default selection.  Click Create, then wait until visual studio finishes project initialization.  

Once the project is created, do a quick test by running the project locally by pressing F5 or by clicking on the Run button.

The project will build and once the application runs, the default browser will start and you will see the default API swagger documentation page. You will be seeing the WeatherForecast API. Note: The WeatherForcast  API is the deafult API provided out of the box as part of the ASP.NET Core Web API template.

Note: We will delete the default WeatherForecast API  Delete the WeatherForecastController.cs from the controller’s folder and WeatherForecast.cs file in solution Explorer.

Next we will connect the sql server database using the EF Core Power Tools. Note: We have already installed the EFP Core Prower Tools Extension earleir in the tutorial.

Right click on project => select EF Core Power Tools  => Select Reverse Engineer 

“Choose the Database connection” window will popup. Click on Add => Add Database Connection

In the connection properties window. Enter the Server Name, then select the database from the list of databases.

Click on Test Connection to test.

Once the Database is connected, keep thedefault setting and click on OK

On the Choose Database Objects – Select the Employee Table and Click OK.

The Generate EF Core Model in Project window will popup.  You can keep the default information as it is or make the change like Context name, Entity Types path etc snf click OK (make the changes hilighted in Red).

This will auto generate the DbContext and the entities user the Models folder (EmployeeContext.cs and the Employees.cs files).

The EF core power tools will also generate a readme file (PowerToolsReadMe.txt). Follow the instruction and make changes to program.cs and appsettings.json file

Register the data context in the the program.cs file and to the appsettings.json add the connection string.

// Add services to the container.
builder.Services.AddSqlServer<EmployeeContext>(builder.Configuration.GetConnectionString("DefaultConnection"));

Next we will create a new controller to represent the employee’s endpoint that will allow the GET, POST and delete operations to be done on the SQL database through entity Framework core. Before that we need to add  add 2 nugget packages to use  in our controller.

On the Solution Explorer, right click on the project and select manage nugget packages.

Select browse tab and type in ‘EntityFrameworkCore’, select ‘Microsoft.EntityFrameworkCore’ (6.0.0) package, click on install and follow the installation process. Next search for EntityFrameworkCore.SqlServer (6.0.0) package, click on install and follow the installation process.

Now we will add the controller. To add a new controller, Right click on Controllers folder => click on Add => select Controller

By Default MVC is selected, we are creating API, so select API from the installed section (on left), then select “API Controller – Empty.” (on right) and then Click on Add

Then type the name of the controller. in this case EmployeeController as we are creating Employee endpoint.

The Employee Controller is created.

Now we need to create the Employee endpoint like the GET / POST / DELETE to interact with the Database and perfom the CURD Operations.

Copy the below code to create the Employee GET endpoint.

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using WebAPI_Demo.Models;

namespace WebAPI_Demo.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class EmployeeController : ControllerBase
    {
        private EmployeeContext _employeeContext;

        public EmployeeController(EmployeeContext employeeContext)
        {
            _employeeContext = employeeContext;

        }

        //GET API

        [HttpGet]
        public async Task<IActionResult> Get()
        {
            var employees = await _employeeContext.Employees.ToListAsync();
            if (employees == null) { return NotFound(); }

            return Ok(employees);
        }
    }
}

Build the project and Press F5 or Run button to run the application.  Once the application runs, the default browser will start and you would see the default API swagger documentation page. You see the Employee GET endpoint.

Yuo can Try it out – Click on “Try it out” then click on Execute and you would get the Response.

We have successfully created as ASP.NET Core Web API with EntityFramework Core  and connected to SQL server DB to get the Employee data. Similary you can write the Endpoints for POST and DELETE.

You can find complete code on my Website, Github and my Youtube channel.

Github link : https://github.com/HussainPatel/ASPNETCore_WebAPI_EFCore_Demo

Channels: https://www.youtube.com/watch?v=H91KNR7RuMs

Channels: https://www.youtube.com/watch?v=_iFnfquibbk&t=38s