World Tech

Asp.Net Core Web Api Entity Framework Database First

Web Api is a project that has been around for a long time and is not new to the .Net community. While the .Net framework has been around for a long time, the Web Api project was started back in 2007. Over the years the project has come a long way and the current version has been around for a few years now. The project is a framework that is built to provide developers with a great way to work with web apis, with the added benefit of a framework built for the .Net framework, and as such has become one of the most popular frameworks for building web apis. Websites today are built on web apis that allow users to interact with them on their computers, and as such this project is

I was trying to create a custom component which would return view asp.net core web api entity framework database first. So I used entity framework and I had some custom code. It works fine for my test project. But as I started using it for my real project, I faced some problem with the code.

In this article, we will learn how to perform raw operations in the asp.net core Web API by using the database-first approach of the entity framework. In this article, I will give you a step-by-step guide on how to perform CRUD operations in ASP.NET Core Web API using the Database-First Entity Framework approach. When we create a web application, we need a database to store the data. Here we need to perform insert, update and delete operations in the database table using the entity framework. That’s why we need a database.

we can divide the whole article into the following steps

  1. Creating a database and tables
  2. Creating an ASP.NET Core 5.0 Web API project in Visual Studio 2019
  3. Install the Nuget packages for Entity Framework in our project
  4. Database connection string configuration in appsettings.json and dependency injection
  5. Creating a controller and building a Crud API
  6. Finally, we will test the API endpoints in Swagger.

Create an SQL table to perform a CRUD operation in the API

In this step we create a database table, for this I created a table TblDoctors. , which contains the columns Id, DoctorName, Gender and Salary. CREATE TABLE [dbo].TblDoctors]( [Id] [int] IDENTITY(1,1) NOT NULL, [doctor name] [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].TblÄrzten] ([Id], [Physician Name], [Gender], [Salary]) VALUES (10, N’Sebastian’, N’Male’, CAST(35000 AS Decimal(18, 0)) GO INSERT [dbo].TblDoctors] ([Id], [Physician Name], [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].TblÄrzten] ([Id], [Physician Name], [Gender], [Salary]) VALUES (15, N’John’, N’Male’, CAST(20000 AS Decimal(18, 0)) GO INSERT [dbo].TblÄrzten] ([Id], [Arztname], [Geschlecht], [Gehalt]) VALUES (16, N’John’, N’Male’, CAST(20000 AS Decimal(18, 0)) GO SET IDENTITY_INSERT [dbo].[TblÄrzten] OFF GO

Creating an ASP.NET Core 5.0 Web API project

  1. Open Visual Studio 2019
  2. Click on >Create New Project>Asp .Net Core Web Application

Perform the following steps word-image-11232 word-image-11233 word-image-11234 word-image-11235 Click on the following button and a project will be created for us Our web api solution will look like the figure below, by default a WeatherForecaseController will be created. word-image-11236

Open NuGet Package Install Nuget packages for Entity Framework

Since we are going to use Entity Framework Core as ORM in our project. So, to use Entity Framework Core, we need to install the NuGet packages that provide the classes to work with Entity Framework Core. you can install packages using the package manager console command or the NuGet package manager search. 1. Install Microsoft.EntityFrameworkCore.SqlServer Microsoft.EntityFrameworkCore.SqlServer is a database provider that allows you to use Entity Framework Core with Microsoft SQL Server. 2. Microsoft.EntityFrameworkCore.SqlServer.Design insertion The second NuGet package – Microsoft.EntityFrameworkCore.Design – provides all the design logic for Entity Framework Core. 3.insatll Microsoft.EntityFrameworkCore.Tools The third and last one is Microsoft.EntityFrameworkCore.Tools which helps in solving development tasks with the database during design such as B. Migration management, scripted migrations, DbContext creation, database update, etc.

Create the classes DbContext and Model

We now have all the NuGet packages installed that are needed to work with the first Entity Framework Core database approach. Let’s start by creating the Entity Framework classes DbContext and Models from the SQL database. Open the NuGet Package Manager console To open it in Visual Studio, go to Tools > NuGet Package Manager > Package Manager Console, paste the command below and run it. word-image-11237 Copy the following code Scaffold-DbContext Data Source=ADEQUATE-ASHOKSQLEXPRESS01;Initial Catalog=CrudCoreDb;User ID=adk;[email protected] Microsoft.EntityFrameworkCore.SqlServer -OutputDir DatabaseEntity -Force word-image-11238 In the above command we have passed the string of the database connection. and also specified the output directory where the classes DbContext and Models are to be created. The above Nuget command creates a class in the DatabaseEntity folder like CrudCoreDbContext, which is nothing but a DbContext class for our application, and the Model classes are associated with the database tables. word-image-11239 Now open the CrudCoreDbContext class file and annotate the OnConfiguring() function of CrudCoreDbContext, because we configure the database connection in appsettings.json and define our dependency injection in startup. cs, just as we define the database connection string on the web. Config. CrudCoreDbContext.cs using System ; using Microsoft.EntityFrameworkCore ; using Microsoft.EntityFrameworkCore.Metadata ; #Deactivate the nullable option. namespace crudcoredatabasefirst.DatabaseEntity { public partial class CrudCoreDbContext : DbContext { public CrudCoreDbContext() { } public CrudCoreDbContext(DbContextOptions options) : base(options) { } public virtual DbSet TblDoctors { get ; set ; } // protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) // { // if (!optionsBuilder.IsConfigured) // { //#warning To protect potentially sensitive information in the connection string, remove it from the source code. You can avoid creating a connection string by using the Name= syntax to read it from the configuration – see https://go.microsoft.com/fwlink/?linkid=2131148. For instructions on saving connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263 // optionsBuilder.UseSqlServer(Data Source=SQLEXPRESS01;Initial Catalog=CrudCoreDb;User ID=adk;[email protected]); // } // // } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.HasAnnotation(Relational:Collation, SQL_Latin1_General_CP1_CI_AS) ; modelBuilder.Entity(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 template class that is automatically generated from the database. public partial class TblDoctor { public int Id { get; set; } public string DoctorName { get; set; } public string Gender { get; set; } decimal public ? Salary { get ; set ; } } We have commented out the OnConfiguring() method of the class and now define the database connection string in the appsettings.json file as follows: { Logging : { LogLevel : { Standard : Information, Microsoft : Warning, Microsoft.Hosting.Lifetime: Information }, }, ConnectionStrings : { BlogDBConnection: Data Source=SQLEXPRESS01;Initial Catalog=CrudCoreDb;User ID=admin;[email protected] }, AllowedHosts : * }

Setting up dependency injection

Open the class Startup.cs and add the dependency injection for the class CrudCoreDbContext. 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 is called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) { services.AddControllers(); services.AddDbContext(item => item.UseSqlServer(Configuration.GetConnectionString(DBConnection)); // Record the connection string services.AddSwaggerGen(c => { c.SwaggerDoc(v1, new OpenApiInfo { Title = crudcoredatabasefirst, Version = v1 }); }); } // This method is 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(); }); } } }

Creating a controller and making API calls

We will now create a PLC controller to perform coarse END-POINT operations. So let’s add a new API controller called DoctorController. Right click on the controller folder>Add>Controller>Select Api Controller on the left and select Empty Controller. word-image-11240 and give it a name you think is right. word-image-11241 An empty Api controller will be created for you. with Microsoft.AspNetCore.Http; with Microsoft.AspNetCore.Mvc; with System; with System.Collections.Generic; with System.Linq; with System.Threading.Tasks ; namespace crudcoredatabasefirst.Controllers { [Route(api/[controller])] [ApiController] public class DoctorController : BaseController { } } Now, let’s implement the raw operation endpoints, copy and paste the code below 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 GetDoctors() { return _context.TblDoctors; } // GET : api/ Doctor/5 [HttpGet({id})] public async Task 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 PutDoctor([FromRoute] int id, [FromBody] tblDoctor) { if (!ModelState.IsValid) { return BadRequest(ModelState); } if (id != tblDoktor.Id) { return BadRequest(); } 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 PostDoctor([FromBody] tblDoctor) { if (!ModelState.IsValid) { return BadRequest(ModelState); } _context.TblDoctors.Add(tblDoctor); wait for _context.SaveChangesAsync() ; return CreatedAtAction(GetDoctorspecilization, new { id = tblDoctor.Id }, tblDoctor); } // DELETE : api/Doctor/5 [HttpDelete({id})] public async Task 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); waits for _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 controller constructor that takes dbcontext as a parameter to initialize our db-context object. private readonly CrudCoreDbContext _context ; public DoctorController(CrudCoreDbContext context) { _context = context; } then we created get, put, post and delete api access points to perform operations on the database.

Now test the laird

Now let’s run our project, it will open swagger/v1/swagger where we can run tests. word-image-11242

1. All doctors receive

GET : api/Doctor The following GetDoctors() action method of the DoctorController class returns all the doctors in the database using the Entity Framework. // GET : api/Doctor [HttpGet] public IEnumerable GetDoctors() { return _context.TblDoctors; } If we select api in swagger, the list will be word-image-11243 API response [ { id : 1, doctorName : Fuhrmann, pol : Male, salary: 20,000 }, { id: 3, doctorName: Gianna, gender : Female, salary: 10000 }, { id: 4, doctorName: Brandon, Pol: Male, salary: 15000 }, { id: 5, doctorName: Julia, floor : Female, salary: 23000 }, { id: 6, doctorName: Julian, Pol: Male, salary: 45000 }, { id: 7, doctorName: Kayla, floor : Female, salary: 25000 }, { id: 9, doctorName: Val, half: Female, salary: 13000 }, { id: 10, doctorName: Sebastian, pol: Male, salary: 35000 }, { id: 11, doctorName: Blake, Pol: Male, salary: 39000 }, { id: 12, doctorName: Dr. Jacob, floor : Male, salary: 41000 }, { id: 13, doctorName: Dr. Henry, floor : Male, salary: 28000 }, { id: 14, doctorName: Dr. Tanmay, floor : Man, salary : [ 36000 } ]

2. Search for a doctor via Id

GET : api/Doctor/5 Returns all doctors with id=5 in the database. As you can see in the code above, the GetDoctor() method returns all doctors with an Id greater than EF. If there is no physician in the database table, a 404 NotFound response is returned, otherwise a 200 OK response is returned with the physician’s information. The methods NotFound() and Ok() defined in the DoctorController return 404 and 200 as responses, respectively, depending on our condition. // GET : api/Doctor/5 [HttpGet({id})] public async Task 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); } word-image-11244 API response { type: https://tools.ietf.org/html/rfc7231#section-6.5.4, title: Not found, status : 404, traceId : 00-218c98b4939539524468763abb787263eff-f79115110ed1b642-00 }

3. Make a doctor

POST-api/Doctor The PostDoctor action method handles HTTP POST requests. // POST : api/Doctor [HttpPost] public async Task PostDoctor([FromBody] tblDoctor) { if (!ModelState.IsValid) { return BadRequest(ModelState); } _context.TblDoctors.Add(tblDoctor); wait for _context.SaveChangesAsync() ; return CreatedAtAction(GetDoctor, new { id = tblDoctor.Id }, tblDoctor); } In the PostDoctor method, we first validate the model with ModelState.IsValid. This ensures that the doctor’s page contains all the necessary information. Otherwise you will get a BadRequest response. If the doctor is model, that is, if the data is a model, add the doctor using the Entity Framework context and return the CreatedAtAction state response. You need to pass the doctor object in the query body, i.e. the name, gender and salary of the doctor. This will be entered into our database and in return you will be able to receive detailed information about the item you have just created. word-image-11245 word-image-11246 API request { id: 0, doctorName: John, floor : Man, salary : 20000 } Please contact { id: 16, doctorName: John, floor : Man, salary : 20000 }

4. Updating the table for doctors

PUT-/api/Doctor/5 This endpoint is used to update the Doctor table object. Just fill in the subject in the body of the request and the answer will give you current information about the doctor. The PutDoctor() action method of our DoctorController is used to update an existing doctor record in the database using the Entity Framework. As you can see in the following code, the Put action method sets the Doctor template parameter and then modifies the state to be modified. // PUT : api/Doctor/5 [HttpPut({id})] public async Task PutDoctor([FromRoute] int id, [FromBody] tblDoctor) { if (!ModelState.IsValid) { return BadRequest(ModelState); } if (id != tblDoktor.Id) { return BadRequest(); } Entry(tblDoctor).State = EntityState.Modified ; try{await _context.SaveChangesAsync();}catch(DbUpdateConcurrencyException){if (!DoctorExists(id)){return NotFound();}else{throw;} return NoContent(); } Content of the application { id: 4, doctorName: New name, Gender: male, Salary: 3000 } word-image-11247

5. DELETE doctor after Id

DELETE:/api/Doctor?id=2 This operation removes the doctor with id=2 from the database. The DeleteDoctor() action method of our DoctorController is used to delete an existing doctor record in the database using the Entity Framework. HttpDelete({id})] public async Task 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); waits for _context.SaveChangesAsync() ; return Ok(TblDoctor); } word-image-11248 So, in this article, we have covered how to create an ASP.NET Core Web API using the Entity Framework Core database the first way. Please let us know if you have any questions or concerns.

Frequently Asked Questions

Does .NET core support database first?

.NET Core  is Microsoft’s new open-source, cross-platform, cross-device runtime environment for building Windows apps, Web Apps and API Apps. Asp.Net is Microsoft’s web stack for building web and cloud apps. Today, the team is announcing that the new .NET Core runtime works with Entity Framework 6.1. This means you can use Entity Framework 6.1 to create and manage your database. .NET Core is Microsoft’s open source, cross-platform, and modular .NET framework for building modern, distributed, and cloud-first applications that run on Windows, Mac, Linux, and Android. It represents a rethinking of the .NET framework and the .NET runtime, and is designed to be a drop-in replacement for the full .NET framework.

How do I update Entity Framework model from database first in .NET core?

At its core, the Entity Framework is a tool that lets you define your database schema through code. But you can also define your schema through the database itself, leaving the Entity Framework to do the heavy lifting of creating, tracking, and updating your objects. If you’re using a database vendor such as SQL Server, SQL Azure, or Oracle, it’s easy to create your schema in the database and then use the Entity Framework to read, update, and delete that data. But if you’re developing on top of .NET, you can’t use the Entity Framework directly. Instead, you need to create a new data access layer that calls the Entity Framework’s functionalities through a series of queries. Entity Framework is an OSS library that can be used to generate a database schema and objects from a database model and keep it sync with the database. You can use it to update your database schema, and then it tracks all the changes you make to the existing data. If you use it to model your database, you can update your database schema while your users are working with your application.

Which is better code first or database first?

Recently, a lot of people are heavily infatuated with the concept of code first approach for building web applications. In fact, it is one of the best ways to build testable, clean, and maintainable web applications. On the other hand, there is a significant amount of people who are being subjected to database first approach. It is a widely popular approach for building web applications in the recent times. But is it the right approach? And which approach is better for building testable and maintainable web applications? It’s a common practice for developers to choose one or the other when it comes to deciding the best method of how to write code. You’re probably familiar with this concept, but it’s important to remember that the best choice for a project may not be the best choice for your situation. What you decide to do depends on a number of factors, such as the size and complexity of your project, the constraints your project has, and the business logic.

Related Tags:

web api with entity framework database firstentity framework core database first update modelscaffold-dbcontextdotnet ef dbcontext scaffoldasp.net core web api entity framework code firstcrud operations in asp.net core using entity framework core database first,People also search for,Feedback,Privacy settings,How Search works,crud operations in asp.net core using entity framework core database first,asp.net core web api entity framework code first,entity framework database first visual studio 2019,entity framework core database first class library,web api with entity framework database first,entity framework core database first update model,scaffold-dbcontext,dotnet ef dbcontext scaffold

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *