ASP.NET Core, Web API - Entity Framework Call Stored Procedure


This article will give developers a practical way to build a Web API with ASP.NET Core and Entity Framework to call Stored procedure. This way will be the simplest and most practical for developers to do a Web API job for any kind of database with the least accesses.
At the beginning of the article, I will give some basic background for Microservice architecture and Web API. For seasonal developers, you can skip A, B, C, and jump to D: Web API implementation.

A, Software Development Architecture

Software development architectures have evolved as described below"
  • Server-side programming, such as IBM mainframe or UNIX server with a dumn terminal
  • Client-side programming, such as HTML page with Dynamic javascript
  • Client-server architecture, such as ASP webserver/page
  • Three-tier architecture, enterprise business architecture
  • SOA: service-oriented architecture, also loose-coupled enterprise business architecture
  • Microservice architecture: loose-coupled architecture with application scope
Server-side programming, Client-side programming, Client/Server architecture, and Three-tier architecture are monolithic architecture, while SOA and Microservice architecture is loose-coupled architecture. The latter has advantages such as being built with different languages in different platforms, isolated and loose-coupled, extremely scalable, and so on.

Microservices Architecture

Highly scalable, flexible applications by decomposing the application into discrete services that implement specific business functions, often referred to as “loosely coupled,” can then be built, deployed, and scaled independently. It is the trend of software development.
API ASP.NET Core, Web API - Entity Framewor Call Stored Procedure

B, Web API, the role in Microservice

The Microservice basically includes two parts: Client and API. The client is the consumer that could be applications of a smartphone, tablet, Windows, Mac, or any kind of browsers. The APi (Application Programming Interface) is the service provider that could be accessed by any or all of the kind of Clients, i.e, the RESTful service, or JSON format output.
Theoretically, API could be built by any languages or any platforms or any devices, while ASP.NET Web API is a Microsoft tool to produce the RESTful output through HTTP for Clients. It works more or less the same way as the ASP.NET MVC web application except that it sends data as a response instead of an HTML view.

C, Choices of Web API implementation

The RESTful service is basically returning data to clients. In most cases, we get the data from the database stored procedure. How to talk to the database? Even if we choose Web API as a tool to build RESTful service, and specifically, using C# as language, we still have different choices for implementation, such as:

1, Using ADO.NET
This legacy way absolutely works, but might need to write a lot of code for database access, also for input/output to RESTful JSON format. See sample 《WebAPI: Restful CRUD Operations in WebAPI Using ADO.NET Objects and SQL Server》;
2, Using ASP.NET Web API with Entity Framework to call Stored Procedure
API ASP.NET Core, Web API - Entity Framewor Call Stored Procedure
However, adding ADO.NET entity data model is not available for ASP.NET Core Web API,
API ASP.NET Core, Web API - Entity Framewor Call Stored Procedure
3, Using ASP.NET Core Web API with Code First Entity Framework
See the sample, however, the code first method of entity framework does not work for a stored procedure.
4, Now, we build up the Web API by ASP.NET Core with Entity Framework to call the Stored procedure,

D, Implementation of ASP.NET Core, Web API with Entity Framework to call Stored Procedure

We use Visual Studio 2019 and ASP.NET Core 5.0, the latest version.
Step 1 - Create an ASP.NET Core Web API application
  • From the File menu, select New > Project.
  • Select the ASP.NET Core Web Application template and click Next.
  • Name the project WebAPICallSP and click Create.
  • In the Create a new ASP.NET Core Web Application dialog, confirm that .NET Core and ASP.NET Core 5.0 are selected. Select the API template and click Create.
API ASP.NET Core, Web API - Entity Framewor Call Stored Procedure
Test the API
The project template creates a WeatherForecast API. Call the Get method from a browser to test the app.
Press Ctrl+F5 to run the app. Visual Studio launches a browser and navigates to https://localhost:<port>/WeatherForecast, where <port> is a randomly chosen port number.
If you get a dialog box that asks if you should trust the IIS Express certificate, select Yes. In the Security Warning dialog that appears next, select Yes.
Then, you will see the Swagger UI that is an alternative to Postman (if you used the version less than ASP.NET Core 5.0, you would have to manually insert the Swagger UI if you wanted, you can do that from this article, following Step 8).
API ASP.NET Core, Web API - Entity Framewor Call Stored Procedure
Click the Get Button, then Execute. You will get the JSON result like this:
API ASP.NET Core, Web API - Entity Framewor Call Stored Procedure
If you use a version below ASP.NET Core 5.0 and do not install Swagger, you will get the JSON result directly in the browser.
Now you are ready to write your web API in ASP.NET Core. We will discuss the next step in Part II.