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 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.
Read More Articles
- Write a value which contain comma to a CSV file in c#?
- Reading CSV File with cells containing commas c#
- Split CSV with columns may contain ‘,’ Comma C#
- [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax
- [Simple Way]-How to get data from database using JQuery Ajax in asp net MVC
- [Simple Way]-ASP.NET Core Upload Multiple File Web API model
- [Simple Way]- Image Upload in .NET Core Web API
- [Easy Way]-Receive File and other form data together in ASP.NET Core Web API
- Replace image in word document using C#
- How to add new rows to an existing word document table in C#
- ASP.NET Core Web API with Entity Framework : return object with relationship
- ASP Net Core Web API doesn't recognize session data
- Asp Net Core web API - How to send image and JSON
- how can i create sql database by entity framework code first in specific path
- where to store database credentials in an ASP.NET core web api project
- How to create models in .NET 5 Web API using EF Core DB first approach
- Creating record for entity framework with relationships to other records : ASP.NET Core 6 Minimal API (SQL / Postgres)
- MVC Web API NET CORE 5.0 Google Auth: No webpage was found for the web address: https://localhost:44307/signin-google
- Migrations - Entity Framework Database First Approach
- How to implement optimistic concurrency on navigation properties in Entity Framework 6 Database First
- Entity Framework Core database
- Removing Identity Column from SaveChanges in Database First Entity Framework 6.2
- ASP.NET Core MVC & C# : insert data into database using Entity Framework
- Controller Net Core 3 with Entity Framework and SQL Server DB. Error in date/time conversion
- Entity Framework database first - result class column generation issue
- NET Core API - referencing DLL which connects to different web services returns 'Object reference not set to an instance of an object'
- ASP.Net Core + .NET Framework Web API - frequent network errors
- How to upgrade Database First Entity Framework 4 To 6
- ASP NET web api POST/DELETE (Angular 6 HttpClient-)request returns: 405 Method not allowed
- Managing normalising a database over time using Entity Framework Core
- Entity Framework Database First generation crash MYSQL
- C# Entity Framework database first missing key violation EntitySet '' is based on type '' that has no keys defined
- Uploading a file along with other parameters in ASP.NET MVC, Web API and Entity Framework
- how to use asp.net core web api sqlite database in a host server
- Database First Entity Framework multiplicity
- Adopting Entity Framework Core with legacy database or databasees with no administrative privileges what is the right approach
- Open Web Socket depend on current route C# asp net core
- Web api routing with entity framework
- How to retrieve Ids of a collection of objects after savechanges from database using Entity Framework code first
- Entity Framework database first with MySQL not working
- Opening a web page and wait for values to be updated by jquery in c#
- Get string between with regex
- An explicit value for the identity column in table 'Fee' can only be specified when a column list is used and IDENTITY_INSERT is ON
- Simple thread-safe list object in .NET < 4 needed
- I get this error while using HTML Agility Pack: system.net.http.httprequestexception' in mscorlib.dll
- C# Lambda Join with an OR condition
- Why does invoking a method with params object[] as args throw a cast error?
- One XElement from multiple streams
- Roslyn.. getting DescendantNodes
- "Gmail style" partial update of a web form
- Namespace and Type issue
- Access fields of CheckBoxComboBox
- Merge 2 list of different types into 1
- C# WPF add Navigation Handler to DataGridHyperlinkColumn
- Expression in DataTable
- Using binded drop downs, value falling through others
- Programmatically create a checkbox binding for a WPF form
- Making only headers bold in a ListView
- How can I change the speed of the lines drawing inside StartCoroutine?
- More than 2 result sets in stored procedure