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

Introduction

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

Graph from

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

We can do that such as in sample 1, «How to call stored procedure in c# web api», and sample 2, «ASP.NET Web API : Return JSON object by implementing multiple stored procedure», like this,

ASP.NET Web API

However, adding ADO.NET entity data model is not available for ASP.NET Core Web API,

ASP,NET Core Web API

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.

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).

Click the Get Button, then Execute. You will get the JSON result like this:

Note

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.