Create Asp.Net Core Web Api Using Entity Framework Database First

In this article, we will learn how to perform CRUD operations in an ASP.NET Core Web API using the Entity Framework Database-First approach.

I will explain a step-by-step tutorial for performing CRUD operations in ASP.NET Core Web API using the Entity Framework Database-First approach.

Whenever we create a web application, we require a database to store the data. Here, we need to perform insert, update, and delete operations on the database table with the help of Entity Framework. Therefore, we need a database."

We can divide the complete article into below step

Creating Our the Database and Tables
Creating .NET Core 5.0 API Project in VS 2019
Install Nuget Packages in our project for Entity Framework
Setup database connection string in appsettings.json and Dependency Injection
Creating a Controller and Create Crud API
Finally, we will perform the Testing on the API End Points in Swagger

Database-First Approach

Database first approach provides us an alternative to code first and models the first approach and it creates model classes and code from the database.

In a database-first development approach, you generate references and entities for existing databases using by executing EF commands.

Database First
In this chapter, let's learn how to create an ASP.NET Core API using the Entity Framework Database-First approach. The Database-First approach constructs Entity Framework from an existing database. We utilize all other functionalities, such as model/database synchronization and code generation.

If you want to use the code first approach read below article

Create .NET Core Web API Using Entity Framework Core Code First Approach

Create SQL table for performing the CRUD Operation in API

In this step, we are going to create the database table, I have created a table called TblDoctors for that.
which is got, the Id, DoctorName, Gender, and Salary columns.

CREATE TABLE [dbo].[TblDoctors](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[DoctorName] [nvarchar](max) NOT NULL,
	[Gender] [nvarchar](50) NULL,
	[Salary] [decimal](18, 0) NULL,
    CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TblDoctors] ON 
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (1, N'Carter', N'Male', CAST(20000 AS Decimal(18, 0)))
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (3, N'Gianna', N'Female', CAST(10000 AS Decimal(18, 0)))
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (4, N'New Name', N'male', CAST(3000 AS Decimal(18, 0)))
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (6, N'Julian', N'Male', CAST(45000 AS Decimal(18, 0)))
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (7, N'Kayla', N'Female', CAST(25000 AS Decimal(18, 0)))
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (9, N'Autumn', N'Female', CAST(13000 AS Decimal(18, 0)))
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (10, N'Sebastian', N'Male', CAST(35000 AS Decimal(18, 0)))
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (11, N'Blake', N'Male', CAST(39000 AS Decimal(18, 0)))
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (12, N'Dr. Jacob', N'Male', CAST(41000 AS Decimal(18, 0)))
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (13, N'Dr. Henry', N'Male', CAST(28000 AS Decimal(18, 0)))
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (14, N'Dr. Tanmay', N'Male', CAST(36000 AS Decimal(18, 0)))
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (15, N'John', N'Male', CAST(20000 AS Decimal(18, 0)))
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary]) VALUES (16, N'John', N'Male', CAST(20000 AS Decimal(18, 0)))
GO
SET IDENTITY_INSERT [dbo].[TblDoctors] OFF
GO

Create ASP.NET Core 5.0 Web API Project

Open Visual Studio 2019
Click to >Create a new Project>Asp .Net Core Web Application

Follow the Below Step

Click on next button it will create the Project for Us

The our Web Api solution will look like the below image, by default it create “WeatherForecaseController” controller.

Open NuGet package Install Nuget Packages for Entity Framework

As we are going to use the Entity Framework Core as ORM for this in our project. So To use Entity Framework Core, we need to install NuGet packages that will provide classes to work with Entity Framework Core.

you can install packages using Package Manager Console by the command or by searching on NuGet Package Manager.

1. Install Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.SqlServer is a database provider that allows Entity Framework Core to be used with Microsoft SQL Server

2. Insatll Microsoft.EntityFrameworkCore.SqlServer.Design

The second NuGet package is Microsoft.EntityFrameworkCore.Design will provide all design-time logic for Entity Framework Core.

3.Insatll Microsoft.EntityFrameworkCore.Tools

The third and last one is Microsoft.EntityFrameworkCore.Tools help with design-time development tasks with database for example manage Migrations, script migration, scaffold a DbContext, update the database, etc.

Creating DbContext and Model Classes

Now we have installed all the needed NuGet packages that are required to work with the Entity Framework Core database first approach.

Now let’s start, creating Entity Framework DbContext and Models Classes from the SQL database.

Open NuGet Package Manager Console
For Opening it, In Visual Studio go to Tools > NuGet Package Manager > Package Manager Console and paste the below command and execute it.

Copy Code the below code

Scaffold-DbContext "Data Source=ADEQUATE-ASHOK\SQLEXPRESS01;Initial Catalog=CrudCoreDb;User ID=adk;Password=adk@1234" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DatabaseEntity -Force

In the above command, we have passed the database connection string. and we also provided an Output directory where DbContext and Models classes should be created.

The above Nuget command creates one class inside the “DatabaseEntity” folder as CrudCoreDbContext which is nothing but DbContext class for our application and Model classes is related to the database tables.

Now open CrudCoreDbContext class file and comment on the OnConfiguring() function of CrudCoreDbContext class because we will configure a database connection in “appsettings.json” and will set our Dependency Injection in the Startup. cs class just like we put the database connection string in Web. config file.

CrudCoreDbContext.cs

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

#nullable disable

namespace crudcoredatabasefirst.DatabaseEntity
{
    public partial class CrudCoreDbContext : DbContext
    {
    public CrudCoreDbContext()
        {
        }

    public CrudCoreDbContext(DbContextOptions<CrudCoreDbContext> options)
            : base(options)
        {
        }

    public virtual DbSet<TblDoctor> TblDoctors { get; set; }

//        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. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
//                optionsBuilder.UseSqlServer("Data Source=SQLEXPRESS01;Initial Catalog=CrudCoreDb;User ID=adk;Password=adk@1234");
//            }
//        }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("Relational:Collation", "SQL_Latin1_General_CP1_CI_AS");

            modelBuilder.Entity<TblDoctor>(entity =>
            {
                entity.Property(e => e.DoctorName).IsRequired();

                entity.Property(e => e.Gender).HasMaxLength(50);

                entity.Property(e => e.Salary).HasColumnType("decimal(18, 0)");
            });

            OnModelCreatingPartial(modelBuilder);
        }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

This is a TblDoctor Model class which is auto-generated from the database.

public partial class TblDoctor
    {
    public int Id { get; set; }
    public string DoctorName { get; set; }
    public string Gender { get; set; }
    public decimal? Salary { get; set; }
    }

As we have commented OnConfiguring() method of class and now we are going to define our database connection string inside the appsettings.json as follows:

{
    "Logging": {
    "LogLevel": {
    "Default": "Information",
    "Microsoft": "Warning",
    "Microsoft.Hosting.Lifetime": "Information"
    }
  },
    "ConnectionStrings": {
    "BlogDBConnection": "Data Source=SQLEXPRESS01;Initial Catalog=CrudCoreDb;User ID=admin;Password=adk@1234"
  },
    "AllowedHosts": "*"
}

Setup Dependency Injection

Open Startup.cs class and add dependency injection for CrudCoreDbContext class.

using crudcoredatabasefirst.DatabaseEntity;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.OpenApi.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace crudcoredatabasefirst
{
    public class Startup
    {
    public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

    public IConfiguration Configuration { get; }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
        {

            services.AddControllers();
            services.AddDbContext<CrudCoreDbContext>(item => item.UseSqlServer(Configuration.GetConnectionString("DBConnection"))); //register connection string
            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo { Title = "crudcoredatabasefirst", Version = "v1" });
            });
        }

    // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
    public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
    if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseSwagger();
                app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "crudcoredatabasefirst v1"));
            }

            app.UseHttpsRedirection();

            app.UseRouting();

            app.UseAuthorization();

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

Create a Controller and Create API Calls

Now, we are going to create an API controller to implement crud operations the END-POINT.
So, Let’s add a new API controller name as ‘DoctorController’.

Right-click on controller folder>add>controller> choose Api Controller from left side and select Empty controller

and name according your choice

it will create the empty api controller for you

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace crudcoredatabasefirst.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class DoctorController : ControllerBase
    {
    }
}

now let’s implement crud operation endpoints ,copy paste the below code

using crudcoredatabasefirst.DatabaseEntity;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace crudcoredatabasefirst.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class DoctorController : ControllerBase
    {

    private readonly CrudCoreDbContext _context;

    public DoctorController(CrudCoreDbContext context)
        {
            _context = context;
        }

    // GET: api/Doctor
        [HttpGet]
    public IEnumerable<TblDoctor> GetDoctors()
        {
    return _context.TblDoctors;
        }

    // GET: api/ Doctor/5
        [HttpGet("{id}")]
    public async Task<IActionResult> GetDoctor([FromRoute] int id)
        {
    if (!ModelState.IsValid)
            {
    return BadRequest(ModelState);
            }

    var TblDoctor = await _context.TblDoctors.FindAsync(id);

    if (TblDoctor == null)
            {
    return NotFound();
            }

    return Ok(TblDoctor);
        }

    // PUT: api/Doctor/5
        [HttpPut("{id}")]
    public async Task<IActionResult> PutDoctor([FromRoute] int id, [FromBody] TblDoctor tblDoctor)
        {
    if (!ModelState.IsValid)
            {
    return BadRequest(ModelState);
            }

    if (id != tblDoctor.Id)
            {
    return BadRequest();
            }

            _context.Entry(tblDoctor).State = EntityState.Modified;

    try
            {
    await _context.SaveChangesAsync();
            }
    catch (DbUpdateConcurrencyException)
            {
    if (!DoctorExists(id))
                {
    return NotFound();
                }
    else
                {
    throw;
                }
            }

    return NoContent();
        }

    // POST: api/Doctor
        [HttpPost]
    public async Task<IActionResult> PostDoctor([FromBody] TblDoctor tblDoctor)
        {
    if (!ModelState.IsValid)
            {
    return BadRequest(ModelState);
            }

            _context.TblDoctors.Add(tblDoctor);
    await _context.SaveChangesAsync();

    return CreatedAtAction("GetDoctorspecilization", new { id = tblDoctor.Id }, tblDoctor);
        }

    // DELETE: api/Doctor/5
        [HttpDelete("{id}")]
    public async Task<IActionResult> DeleteDoctor([FromRoute] int id)
        {
    if (!ModelState.IsValid)
            {
    return BadRequest(ModelState);
            }

    var TblDoctor = await _context.TblDoctors.FindAsync(id);
    if (TblDoctor == null)
            {
    return NotFound();
            }

            _context.TblDoctors.Remove(TblDoctor);
    await _context.SaveChangesAsync();

    return Ok(TblDoctor);
        }

    private bool DoctorExists(int id)
        {
    return _context.TblDoctors.Any(e => e.Id == id);
        }
    }
}

Code Explanation

Constructor and Dependency Injection:

Here we have created a constructor of the controller which taking dbcontext as a parameter, to initialize our db context object.The constructor injects an instance of CrudCoreDbContext into the controller. This is the database context used to interact with the database.

 private readonly CrudCoreDbContext _context;

    public DoctorController(CrudCoreDbContext context)
        {
            _context = context;
        }

and then we have created Get,Put,Post,delete api endpoint for performing the database operation.

GET Methods:

GetDoctors(): This method handles HTTP GET requests to retrieve all doctors. It returns an enumerable list of TblDoctor entities retrieved from the database context.

GetDoctor(int id): This method handles HTTP GET requests to retrieve a specific doctor by ID. It retrieves the doctor with the given ID from the database context. If the doctor is not found, it returns a 404 (Not Found) response.

PUT Method:

PutDoctor(int id, TblDoctor tblDoctor): This method handles HTTP PUT requests to update a specific doctor by ID. It expects the ID of the doctor to update and the updated doctor object (tblDoctor). It validates the model state and ensures that the ID in the request matches the ID of the provided doctor object. Then it updates the doctor's state in the database context and saves the changes. If the doctor doesn't exist, it returns a 404 (Not Found) response.

POST Method:

PostDoctor(TblDoctor tblDoctor): This method handles HTTP POST requests to create a new doctor. It expects a TblDoctor object (tblDoctor) in the request body. It validates the model state, adds the new doctor to the database context, and saves the changes. It returns a 201 (Created) response with the created doctor's details.

DELETE Method:

DeleteDoctor(int id): This method handles HTTP DELETE requests to delete a specific doctor by ID. It expects the ID of the doctor to delete. It retrieves the doctor with the given ID from the database context, removes it, and saves the changes. If the doctor doesn't exist, it returns a 404 (Not Found) response.

Helper Method:

DoctorExists(int id): This method checks if a doctor with the given ID exists in the database context.

Above controller provides endpoints to perform CRUD operations on the TblDoctor entity, interacting with the database through the Entity Framework database context.

Now Lest perform testing

Now let’s run our project, it will open swagger/v1/swagger, where we can perform our testing.

1. Get All Doctors

GET: api/Doctor

The following GetDoctors() action method in DoctorController class returns all the Doctors from the database using Entity Framework.

// GET: api/Doctor
        [HttpGet]
    public IEnumerable<TblDoctor> GetDoctors()
        {
    return _context.TblDoctors;
        }

Let’s excute api in the swagger,it will return the list

Let’s do test on swagger, Click Get /api/Doctor tab,then click on Try it Out Button
and Then Click on the excute button, we get all records from the Doctors table.

API Response

[
  {
    "id": 1,
    "doctorName": "Carter",
    "gender": "Male",
    "salary": 20000
  },
  {
    "id": 3,
    "doctorName": "Gianna",
    "gender": "Female",
    "salary": 10000
  },
  {
    "id": 4,
    "doctorName": "Brandon",
    "gender": "Male",
    "salary": 15000
  },
  {
    "id": 5,
    "doctorName": "Julia",
    "gender": "Female",
    "salary": 23000
  },
  {
    "id": 6,
    "doctorName": "Julian",
    "gender": "Male",
    "salary": 45000
  },
  {
    "id": 7,
    "doctorName": "Kayla",
    "gender": "Female",
    "salary": 25000
  },
  {
    "id": 9,
    "doctorName": "Autumn",
    "gender": "Female",
    "salary": 13000
  },
  {
    "id": 10,
    "doctorName": "Sebastian",
    "gender": "Male",
    "salary": 35000
  },
  {
    "id": 11,
    "doctorName": "Blake",
    "gender": "Male",
    "salary": 39000
  },
  {
    "id": 12,
    "doctorName": "Dr. Jacob",
    "gender": "Male",
    "salary": 41000
  },
  {
    "id": 13,
    "doctorName": "Dr. Henry",
    "gender": "Male",
    "salary": 28000
  },
  {
    "id": 14,
    "doctorName": "Dr. Tanmay",
    "gender": "Male",
    "salary": 36000
  }
]

2. Get Doctor by Id

GET: api/Doctor/5
It will return all Doctor with id=5 in the database

As in the code, the `GetDoctor()` method retrieves a doctor by their ID using Entity Framework. If no doctor exists in the database table with the provided ID, the method returns a 404 NotFound response. Otherwise, it returns a 200 OK response with the doctor's data.

The `NotFound()` and `Ok()` methods within the `DoctorController` are responsible for returning 404 and 200 responses, respectively, based on our specified conditions.

 // GET: api/Doctor/5
        [HttpGet("{id}")]
    public async Task<IActionResult> GetDoctor([FromRoute] int id)
        {
    if (!ModelState.IsValid)
            {
    return BadRequest(ModelState);
            }

    var TblDoctor = await _context.TblDoctors.FindAsync(id);

    if (TblDoctor == null)
            {
    return NotFound();
            }

    return Ok(TblDoctor);
        }

Let’s do test on swagger, Click Get /api/Doctor/{id} tab,then click on Try it Out Button
Enter Doctor Id for which you want record,Click on the excute button

API Response

{
"type": "https://tools.ietf.org/html/rfc7231#section-6.5.4",
"title": "Not Found",
"status": 404,
"traceId": "00-218c98b4939524468763abb787263eff-f79115110ed1b642-00"
}

3. Create Doctor

POST–api/Doctor

The PostDoctor action method will handle HTTP POST requests.

// POST: api/Doctor
        [HttpPost]
    public async Task<IActionResult> PostDoctor([FromBody] TblDoctor tblDoctor)
        {
    if (!ModelState.IsValid)
            {
    return BadRequest(ModelState);
            }

            _context.TblDoctors.Add(tblDoctor);
    await _context.SaveChangesAsync();

    return CreatedAtAction("GetDoctor", new { id = tblDoctor.Id }, tblDoctor);
        }

In the `PostDoctor` method, we first validate the model using `ModelState.IsValid`. This ensures that the doctor object includes all the required information. If this is not true, then a BadRequest response is returned. If the doctor model, i.e., the data model, is valid, we add the doctor using the Entity Framework context and return a CreatedAtAction status response.

To test this functionality on Swagger:

1. Click on the PostDoctor tab.

2. Click on the 'Try it Out' button.

3. Input the body parameters for the doctor object, including `doctorName`, `gender`, and `salary`.

4. Click on the 'Execute' button.

5. This will insert the record into the table. In the response, you will be able to see the details of the newly created object."

API Request

{
    "id": 0,
    "doctorName": "John",
    "gender": "Male",
    "salary": 20000
}

Response body

{
    "id": 16,
    "doctorName": "John",
    "gender": "Male",
    "salary": 20000
}

4. Update Doctor table

PUT–/api/Doctor/5
This endpoint is used to update the doctor table object, You just need to pass the doctor object in the request body, and in response, you will able to get an updated doctor detail.

PutDoctor() action method in our DoctorController is used to update an existing doctor record in the database using Entity Framework.

As you can see from the below code, Put action method includes a parameter of the Doctor model and then changes the state to be modified.

// PUT: api/Doctor/5
        [HttpPut("{id}")]
    public async Task<IActionResult> PutDoctor([FromRoute] int id, [FromBody] TblDoctor tblDoctor)
        {
    if (!ModelState.IsValid)
            {
    return BadRequest(ModelState);
            }

    if (id != tblDoctor.Id)
            {
    return BadRequest();
            }

            _context.Entry(tblDoctor).State = EntityState.Modified;

    try
            {
    await _context.SaveChangesAsync();
            }
    catch (DbUpdateConcurrencyException)
            {
    if (!DoctorExists(id))
                {
    return NotFound();
                }
    else
                {
    throw;
                }
            }

    return NoContent();
        }

Let’s do test on swagger, Click PUT /api/Doctor/{id} tab,then click on Try it Out Button
Enter Doctor Id for which you want to update the record,and let’s say i want to update record with Id=4
Input updated value,Click on the excute button,
It will update Doctor record with Id=4

Request body

{
"id": 4,
"doctorName": "New Name",
"gender": "male",
"salary": 3000
}

5. DELETE Doctor by Id

DELETE:/api/Doctor?id=2
It will delete the Doctor with id=2 in the database

DeleteDoctor() action method in our DoctorController use to delete an existing Doctor record in the database using Entity Framework.

[HttpDelete("{id}")]
    public async Task<IActionResult> DeleteDoctor([FromRoute] int id)
        {
    if (!ModelState.IsValid)
            {
    return BadRequest(ModelState);
            }

    var TblDoctor = await _context.TblDoctors.FindAsync(id);
    if (TblDoctor == null)
            {
    return NotFound();
            }

            _context.TblDoctors.Remove(TblDoctor);
    await _context.SaveChangesAsync();

    return Ok(TblDoctor);
        }

Let’s do test on swagger, Click DELETE /api/Doctor/{id} tab,then click on Try it Out Button
Enter Doctor Id for deleting record,Click on the excute button

So, in this post, we have seen how to perform creating ASP.NET Core Web API using Entity Framework Core database first approach.

Please share your using comments and if you have any queries or doubts.