CRUD Operation in ASP.NET Core Web API with Entity Framework Core
This article will explain how to perform CRUD (Create, Read, Update and Delete) operations in Asp.Net Core Web API using Entity Framework Core. We will see step by step instructions about CRUD operations in Asp.Net Core Web API. In this demonstration, we will use the Database First Approach where our database will be ready before creating an actual code.
CREATE THE DATABASE AND TABLES
First, we will open SQL Server Management Studio and create a database name as “CoreWebApiCrud” along with a table as “Category” . So, you can create database and tables using the following SQL snippet code.
USE CoreWebApiCrud
GO
CREATE TABLE Category(Id INT PRIMARY KEY IDENTITY(1,1), Name VARCHAR(255), Price VARCHAR(255));CREATE ASP.NET CORE WEB API PROJECT
Now, we have database and tables are ready. So, let’s move to create Asp.Net Core Web API project, but be sure your development environment is ready for .Net Core application. You should require some of the prerequisites to start this demonstration with Visual Studio 2017. First, you should have installed .NET Core 2.0.0 or above SDK and second, you should have Installed the latest version of Visual Studio 2017 i.e VS 2017 15.7+ version. Once your environment is ready, you are good to go. We should follow below steps while creating new Asp.Net Core Web API.
- Open Visual Studio 2017
- Click to File > New > Project from the Menu
- In New Project windows, from the left panel, select Installed > Visual C# > Web
- Select the ASP.NET Core Web Application project template from the middle panel
- Enter the name of the project and click OK
- Next dialog will appear for the New ASP.NET Core Web Application.
- Choose the target framework as .Net Core and select the version from the dropdown as Asp.Net Core 2.1
- Select API as a template
- Select the Authentication as ‘No Authentication’
- Click OK
As we have selected ‘Configure for HTTPS‘ while creating the project, it will ask to configure SSL for this project. To configure this click to YES.
INSTALL NUGET PACKAGES FOR ENTITY FRAMEWORK
As we have already known that we will use Entity Framework Core as ORM for this demonstration. To use EF Core, we have to install some of the packages which will provide classes to work with EF Core. We can install these packages either using Package Manager Console to execute a command to install specific package or directly search on NuGet Package Manager and install it. So, let install one by one required packages with this solution. So, right click to project and choose Manage NuGet Packages. It will open Nuget Packages Manager from where we can search any required Asp.Net Core package and install the specific version.
First one is Microsoft.EntityFrameworkCore.SqlServer which will provide classes to connect with SQL Server for CRUD Operation to Entity Framework Core.
Next one is Microsoft.EntityFrameworkCore.SqlServer.Design which will provide design-time core functionality to Entity Framework Core for SQL Server.
And the last one is Microsoft.EntityFrameworkCore.Tools which will help to work with database related activity like add migration, script migration, get dbcontext, update database etc.
GENERATES MODEL CLASSES
As above we have installed all the required packages which are required to work with Entity Framework Core. So, generating Entity Framework Core Models Classes from the database As we have a database already exists; we should run the following command from the Package Manager Console. To open it, go to Tools > NuGet Package Manager > Package Manager Console and execute the following commands.Scaffold-DbContext “Server=.;Database=CoreWebApiCrud;Integrated Security=true” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
With above command we have provided SQL Server details like server name, username and password to access that server and existing database which contains tables. Apart from this, we have given Output directory where Models classes should be generated. This above command creates three classes inside the Model folder as CoreWebApiCrudContext which is nothing but DbContext class for application and other class is Model class which are related to database’s tables.
Just for now comment the OnConfiguring() method of CoreWebApiCrudContext class because later we will configure our Dependency Injection inside the Startup.cs class.
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
namespace CoreWebApiCrudOperation.Models
{
public partial class CoreWebApiCrudContext : DbContext
{
public CoreWebApiCrudContext()
{
}
public CoreWebApiCrudContext(DbContextOptions<CoreWebApiCrudContext> options)
: base(options)
{
}
public virtual DbSet<Category> Category { 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. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
//// optionsBuilder.UseSqlServer("Server=.;Database=CoreWebApiCrud;Integrated Security=true");
//// }
// }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Category>(entity =>
{
entity.Property(e => e.Name)
.HasMaxLength(255)
.IsUnicode(false);
entity.Property(e => e.Price)
.HasMaxLength(255)
.IsUnicode(false);
});
}
}
}
This is a Category Model class which is auto-generated from the database.
using System;
using System.Collections.Generic;
namespace CoreWebApiCrudOperation.Models
{
public partial class Category
{
public int Id { get; set; }
public string Name { get; set; }
public string Price { get; set; }
}
}
As we have commented OnConfiguring() method of CoreWebApiCrudContext and now going to create our mechanism to access database and perform CRUD operation on this. First, we will define our connection string inside the appsettings.json as follows.
{
"Logging": {
"LogLevel": {
"Default": "Warning"
}
},
"ConnectionStrings": {
"CrudConnection": "Server=.;Database=CoreWebApiCrud;Integrated Security=true"
},
"AllowedHosts": "*"
}
SETUP DEPENDENCY INJECTION
Now, let’s create a Repository class as the middle layer of this API. So, just create a folder name with ‘Repositories‘ and create two files as the name with ‘ICategoryRepository‘ and ‘CategoryRepository. Just leave these interface and class as for now. We will implement CRUD implementation code with later. For now, just jump to Startup.cs class and add dependency injection for CoreWebApiCrudContext class and CategoryRepository using following code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using CoreWebApiCrudOperation.Models;
using CoreWebApiCrudOperation.Repositories;
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.Logging;
using Microsoft.Extensions.Options;
namespace CoreWebApiCrudOperation
{
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.AddDbContext<CoreWebApiCrudContext>(item => item.UseSqlServer(Configuration.GetConnectionString("CrudConnection")));
services.AddScoped<ICategoryRepository, CategoryRepository>();
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
else
{
app.UseHsts();
}
app.UseHttpsRedirection();
app.UseMvc();
}
}
}
CREATE REPOSITORY AND IMPLEMENT CRUD OPERATIONS
Now jump to actual CRUD implementation with the code. So, open ICategoryRepository and add required methods for the CRUD operations. So, we can see with following ICategoryRepository interface, we have defined different methods for the different purpose. GetAllCategories will get the list of available category, GetCategoryById will get the individual category for specific category Id, AddCategoryAsync will add new category detail, DeleteCategory will delete the individual Category based of CategoryId and last UpdateCategory will update the existing Category. As we are returning Task-specific data, it means, data will return asynchronously.
using CoreWebApiCrudOperation.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace CoreWebApiCrudOperation.Repositories
{
public interface ICategoryRepository
{
Task<List<Category>> GetAllCategories();
Task<Category> GetCategoryById(int? categoryId);
Task<int> AddCategoryAsync(Category category);
Task<int> DeleteCategory(int? categoryId);
Task UpdateCategory(Category category);
}
}
Now, let’s jump to CategoryRepository and implement ICategoryRepository. First, we will get the instance of the CoreWebApiCrudContext using the Constructor Dependency Injection. Once, an instance will be available then we will move to further implementation. So, implement CRUD operations (Create, Read, Update and Delete) using the following codes. In this CategoryRepository class, we will interact with the database using Entity Framework Core and perform the CRUD operations.
using CoreWebApiCrudOperation.Models;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace CoreWebApiCrudOperation.Repositories
{
public class CategoryRepository : ICategoryRepository
{
private CoreWebApiCrudContext db;
public CategoryRepository(CoreWebApiCrudContext _db)
{
this.db = _db;
}
public async Task<int> AddCategoryAsync(Category category)
{
if (db != null)
{
await db.Category.AddAsync(category);
await db.SaveChangesAsync();
return category.Id;
}
return 0;
}
public async Task<int> DeleteCategory(int? categoryId)
{
int result = 0;
if (db != null)
{
//Find the category for specific category id
var category = await db.Category.FirstOrDefaultAsync(x => x.Id == categoryId);
if (category != null)
{
//Delete that category
db.Category.Remove(category);
//Commit the transaction
result = await db.SaveChangesAsync();
}
return result;
}
return result;
}
public async Task<List<Category>> GetAllCategories()
{
if (db != null)
{
return await db.Category.ToListAsync();
}
return null;
}
public async Task<Category> GetCategoryById(int? categoryId)
{
if (db != null)
{
return await db.Category.FirstOrDefaultAsync(x => x.Id == categoryId);
}
return null;
}
public async Task UpdateCategory(Category category)
{
if (db != null)
{
//Delete that category
db.Category.Update(category);
//Commit the transaction
await db.SaveChangesAsync();
}
}
}
}
CREATE A CONTROLLER AND CREATE API CALL
Now, we will do the actual implementation with the API controller to expose the END-POINT. So, first add a new API controller name as ‘CategoryControler‘ to right click on Controler folder and choose Add > New Item. So, again we will get the instance of CategoryRepository using Constructor Dependency Injection and then first get the list of the category using the GetAllCategories() End Point, similarly we will define rest of the End Point for CRUD operations.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using CoreWebApiCrudOperation.Models;
using CoreWebApiCrudOperation.Repositories;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
namespace CoreWebApiCrudOperation.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class CategoryController : ControllerBase
{
ICategoryRepository categoryRepository;
public CategoryController(ICategoryRepository _categoryRepository)
{
categoryRepository = _categoryRepository;
}
[HttpGet]
[Route("GetAllCategories")]
public async Task<IActionResult> GetAllCategories()
{
try
{
var categories = await categoryRepository.GetAllCategories();
if (categories == null)
{
return NotFound();
}
return Ok(categories);
}
catch (Exception)
{
return BadRequest();
}
}
[HttpGet]
[Route("GetCategoryById")]
public async Task<IActionResult> GetCategoryById(int? categoryId)
{
try
{
var categories = await categoryRepository.GetCategoryById(categoryId);
if (categories == null)
{
return NotFound();
}
return Ok(categories);
}
catch (Exception)
{
return BadRequest();
}
}
[HttpPost]
[Route("AddCategory")]
public async Task<IActionResult> AddCategory([FromBody]Category model)
{
if (ModelState.IsValid)
{
try
{
var categoryId = await categoryRepository.AddCategoryAsync(model);
if (categoryId > 0)
{
return Ok(categoryId);
}
else
{
return NotFound();
}
}
catch (Exception)
{
return BadRequest();
}
}
return BadRequest();
}
[HttpPost]
[Route("DeleteCategory")]
public async Task<IActionResult> DeleteCategory(int? categoryId)
{
int result = 0;
if (categoryId == null)
{
return BadRequest();
}
try
{
result = await categoryRepository.DeleteCategory(categoryId);
if (result == 0)
{
return NotFound();
}
return Ok();
}
catch (Exception)
{
return BadRequest();
}
}
[HttpPost]
[Route("UpdateCategory")]
public async Task<IActionResult> UpdateCategory([FromBody]Category model)
{
if (ModelState.IsValid)
{
try
{
await categoryRepository.UpdateCategory(model);
return Ok();
}
catch (Exception ex)
{
if (ex.GetType().FullName == "Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException")
{
return NotFound();
}
return BadRequest();
}
}
return BadRequest();
}
}
}TEST THE API END POINTS IN POSTMAN
Finally, we have done with the implementation of CRUD operations with Asp.Net Core Web API using Entity Framework Core and Repository. So, let move to Postman and test our API End Points. So, let’s first test the GetAllCategories End Point as follows. Choose the Get as a method and pass the End Point inside the URL and define a header as ‘Content-Type’: ‘application/json’ and click to Send button. It will return the list of available category.
ENABLE CORS
We have created an Asp.Net Core Web API and added Entity Framework Core and implement CRUD operations. At the end, we have also tested each End Point for the API and everything working fine. But one problem is persisting yet and that is CORS issue. If you will deploy this API somewhere and will use with some other application then it will throw some CORS related exceptions. So, let configure CORS also to make this API perfect. So, open Startup.cs and ConfigureService() method and add the following lines of code to create CORS policy.
services.AddCors(option => option.AddPolicy("MyCors", builder => {
builder.AllowAnyOrigin().AllowAnyHeader().AllowAnyMethod();
}));And then use this CORS policy inside the Configure method as follows.
app.UseCors("MyCors");using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using CoreWebApiCrudOperation.Models;
using CoreWebApiCrudOperation.Repositories;
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.Logging;
using Microsoft.Extensions.Options;
namespace CoreWebApiCrudOperation
{
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.AddCors(option => option.AddPolicy("MyCors", builder => {
builder.AllowAnyOrigin().AllowAnyHeader().AllowAnyMethod();
}));
services.AddDbContext<CoreWebApiCrudContext>(item => item.UseSqlServer(Configuration.GetConnectionString("CrudConnection")));
services.AddScoped<ICategoryRepository, CategoryRepository>();
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
else
{
app.UseHsts();
}
app.UseCors("MyCors");
app.UseHttpsRedirection();
app.UseMvc();
}
}
}
So, today we have learned how to perform CRUD operations with Asp.Net Core Web API using Entity Framework Core.
I hope this post will help you. Please put your feedback using comment which helps me to improve myself for next post. If you have any doubts please ask your doubts or query in the comment section and If you like this post, please share it with your friends. Thanks
