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

In this article, we will learn how to perform the crud operations in the asp.net core web API using the entity framework database first approach.

In this article, I will explain to you a step-by-step tutorial for performing CRUD operations in ASP.NET Core Web API using the entity framework database first approach.

Whenever we are creating a web application we need a database for saving the data. Here we need to perform insert, update, delete operations in the database table with the help of the entity framework. So 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.

In this chapter, let’s learn about create an Asp .Net Core API using the Entity framework database first approach.The Database First approach builds Entity Framework from an existing database. We use all other functionalities, such as model/database sync 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

Here we have created a constructor of the controller which taking dbcontext as a parameter, to initialize our db context object.

 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.

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 you can see in code, GetDoctor() method returns all the Doctor with by Id using EF. If no Doctor exists in the database table then it will return 404 NotFound responses otherwise it will return 200 OK responses with Doctor data.

The NotFound() and Ok() methods defined in the DoctorController returns 404 and 200 response respectively according to our condition.

 // 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 validating the model using ModelState.IsValid. This will make sure that the doctor object includes all the required information. If this is not true then you get BadRequest response. If doctor model i.e data is model then add doctor using Entity Framework context and return CreatedAtAction status response.

Let’s do test on swagger, Click PostDoctor tab,then click on Try it Out Button
Input Body parameter and click on the excute button, it will insert the record in the table.

You need to pass the doctor object in the request body i.e doctorName, gender, and salary. it will make an entry in our database and in response, you will able to get the newly created object detail.

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.

The post Create Asp.Net Core Web Api Using Entity Framework Database First appeared first on Software Development | Programming Tutorials.

Ashok Patel

I'm a software engineer, having good experience in software programming web designing with great command on ASP.NET, React JS, Angular JS,.NET Core HTML5, JavaScript, T-SQL, JQuery. Also have great experience in Electronics and electrical engineers design.