ASP.NET Core Web API with EF Core Database First

This article shows how to create a web api with Entity Framework Database First approach.
We will use SQLite as database engine.

Download source code.

Prerequisites

Create new ASP.NET Core Web Application

Create a new ASP.NET Core Web Application, name : LB.Chinook.WebAPI.WebApp

Select .NET Core, ASP.NET Core 3.1 and API

Delete WeatherForecast.cs, Controllers\WeatherForecastController.cs
In Properties\launchSettings.json, remove value for launchUrl key.

"launchUrl": ""

Get chinook sample database

Download Chinook.db from URL above and copy to project in App_Data folder.

If you want to see what’s inside the database, use DB Browser for SQLite.

Install Nuget packages for Entity Framework

Microsoft.EntityFrameworkCore.Sqlite will provide classes to connect with SQLite for CRUD Operation to Entity Framework Core.

Microsoft.EntityFrameworkCore.Design will provide design-time core functionality to Entity Framework Core for SQLite.

Generates Model Classes

Open Package Manager Console and execute the command below. This will create model classes in Models folder.

Scaffold-DbContext "Data Source=.\App_Data\chinook.db;" Microsoft.EntityFrameworkCore.Sqlite -OutputDir Models

ConnectionString

In Models\ChinookContext.cs file, remove hardcoded connectionstring, this will be saved appsettings.json file

        //protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        //{
        //    if (!optionsBuilder.IsConfigured)
        //    {
//#warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
//                optionsBuilder.UseSqlite("Data Source=.\\App_Data\\chinook.db;");
        //    }
        //}

Add ConnectionString to appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "ChinookConnectionString": "Data Source=.\\App_Data\\Chinook.db;"
  },
  "AllowedHosts": "*"
}

Setup Dependency Injection for ChinookContext

In Startup.cs class, add dependency injection for ChinookContext in ConfigureServices method.

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddEntityFrameworkSqlite().AddDbContext<ChinookContext>(item => item.UseSqlite(Configuration.GetConnectionString("ChinookConnectionString")));

            services.AddControllers();
        }

Create ViewModel classes

Create ViewModels folder and add EmployeeViewModel class. This will be used to return data in our controller.

using System;

namespace LB.Chinook.WebAPI.WebApp.ViewModels
{
    public class EmployeeViewModel
    {
        public int EmployeeId { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string Phone { get; set; }
        public string Email { get; set; }
    }
}

Create Repository and Implement GetEmployees method

Add IEmployeeRepository interface.

using LB.Chinook.WebAPI.WebApp.ViewModels;
using System.Collections.Generic;

namespace LB.Chinook.WebAPI.WebApp.Repositories
{
    public interface IEmployeeRepository
    {
        List<EmployeeViewModel> GetEmployees();
    }
}

Add EmployeeRepository class implementing IEmployeeRepository interface.
In constructor, we will pass ChinookContext.

using LB.Chinook.WebAPI.WebApp.Models;
using LB.Chinook.WebAPI.WebApp.ViewModels;
using System;
using System.Collections.Generic;
using System.Linq;

namespace LB.Chinook.WebAPI.WebApp.Repositories
{
    public class EmployeeRepository : IEmployeeRepository
    {
        private ChinookContext db;

        public EmployeeRepository(ChinookContext db)
        {
            this.db = db;
        }

        public List<EmployeeViewModel> GetEmployees()
        {
            if (db != null)
            {
                List<EmployeeViewModel> employees = new List<EmployeeViewModel>();

                var result = from o in db.Employees
                             orderby o.FirstName ascending, o.LastName ascending
                             select o;

                foreach (var r in result)
                {
                    EmployeeViewModel employee = new EmployeeViewModel();
                    employee.EmployeeId = r.EmployeeId;
                    employee.FirstName = r.FirstName;
                    employee.LastName = r.LastName;
                    employee.Phone = r.Phone;
                    employee.Email = r.Email;

                    employees.Add(employee);
                }

                return employees;
            }

            return null;
        }
    }
}

Create a controller and API

Create an empty API Controller named EmployeeController.

In the controller, we will pass our IEmployeeRepository. Then we add a new API called GetEmployees, route api/employees.

using LB.Chinook.WebAPI.WebApp.Repositories;
using Microsoft.AspNetCore.Mvc;
using System;

namespace LB.Chinook.WebAPI.WebApp.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class EmployeeController : ControllerBase
    {
        private IEmployeeRepository employeeRepository;

        public EmployeeController(IEmployeeRepository employeeRepository)
        {
            this.employeeRepository = employeeRepository;
        }

        [HttpGet]
        [Route("api/employees")]
        public IActionResult GetEmployess()
        {
            try
            {
                var messages = employeeRepository.GetEmployees();
                if (messages == null)
                {
                    return NotFound();
                }

                return Ok(messages);
            }
            catch (Exception)
            {
                return BadRequest();
            }
        }
    }
}

Setup Dependency Injection for EmployeeRepository

In Startup.cs, add a new scope for EmployeeRepository.

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddEntityFrameworkSqlite().AddDbContext<ChinookContext>(item => item.UseSqlite(Configuration.GetConnectionString("ChinookConnectionString")));

            services.AddScoped<IEmployeeRepository, EmployeeRepository>();

            services.AddControllers();
        }

Run and test API

Build and run the application.
In browser, type the following URL. You can also use Postman to test your API.

https://localhost:44311/api/employee/employees

Enable CORS

If we will deploy this API somewhere and will use with some other application, then it will throw some CORS related exceptions. So, open Startup.cs and ConfigureService() method and add the following lines of code to create CORS policy.

            services.AddCors(option => option.AddPolicy("ChinookAPIPolicy", builder => {
                builder.AllowAnyOrigin().AllowAnyHeader().AllowAnyMethod();

            }));

And then, use this CORS policy inside the Configure method.

app.UseCors("ChinookAPIPolicy");