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 MVC Database First - Lose all validations when refreshing EF Entity Framework
- How to enrich azure b2c token with custom claims using api connectors and asp net core web api
- Asp Net Core Web Api POST Request.Body always length 0
- Keeping state of logged in user in ASP .Net Core Web API
- ASP NET Core Minimal API always returns 200 instead of specified 204
- How to set a new database for Entity Framework Code First project
- Image Uploading in Dot net core Web API
- Accept x-www-form-urlencoded in Asp .net core Web Api
- Storing System.Version in database with Entity Framework Code First
- Use Entity Framework database first db context with mvvm view model classes
- How to Publish a Web API net core 3.0 in multiple servers
- WebHost doesn't exist in the current context in Asp Net Core 2.2 default API template
- CamelCase not working in net core 6 web api
- Entity framework code first does not create many-to-many database table
- Entity Framework Core 2.1.1 Code First
- How best to handle existing database column mappings with an Entity Framework Code First model class manually?
- Entity Framework Database First regeneration make me lose manual changes
- How to update entity framework 7 migrations and database - Code first
- asp net web api custom filter and http verb
- Entity Framework 4 Code First Custom table mapping Fluent API issue
- Return complete XML response from ASP .NET Core Web API method
- How do I connect to database in ASP.NET Core Web API without using EF?
- Entity Framework 3.5 - how to update row without first loading it from database
- Entity Framework Database Update First Multiplicity conflicts
- 'Page not found' error on my controller for my NET Core Web API
- Can't bind params using [FromQuery] ASP Net Core 2 API
- Cannot run Asp Net Core API on IIS
- No 'Access-Control-Allow-Origin' header is present - asp.net core web api targeting .net framework 4.5.1
- How do you load an existing data project in EF 6.2 with database first methodology into a Web API Core2 Controller?
- How do I get an Option<T> from an Entity Framework Core database call?
- Xamarin.Forms command binding TargetInvocationException
- Monotouch.Dialog: how to preselect an element from a RadioGroup?
- ".Add" on Dictionary with a list as value
- Do I need to call IsolatedStorageSettings.Save method in windows phone application?
- Get real path of a log file stored in VirtualStore
- Anyway to forbid deleting my changes made in Form1.Designer.cs if i edit form via Form Designer in C#?
- XPath extension method failing in .NET 3.5, works in .NET 4.0
- Redirect to post with nancyfx
- Enable all rules with .NET analyzers in .NET standard projects
- Use DataGridView.SelectedRows as DataSource for another DataGridView
- Intercepting ASP NET MVC POST Values
- Why can't I use this nested lambda expression?
- Could not load file or assembly {assembly name here} or one of it's dependencies. Access is denied
- Advanced Search using Lambda Expression
- Pass parameters to constructor, when initializing a lazy instance
- Problem loading RTF file into windows richTextBox
- Fast import into MongoDb
- c# split input of a string
- Drawing circle on existing PDF using itextsharp c#
- Canceling async httpwebrequests