Generic Entity Framework CRUD controller for Web API

Introduction

During a job I came across the following wish:
Developing a generic web service that provides as many standard database actions as possible.
I will elaborate on the implementation of this below.

What do you need

The starting situation is a created empty MVC Web API 2 project or a WebForms project with the “Microsoft.AspNet.WebApi” NuGet package added.
Also add the EntityFramework NuGet package. This can be easily installed via the NuGet Package Manager console with the following command:
Install-Package EntityFramework -Version 6.2.0

Set up Web API and Data Model

Add an empty Web API 2 Controller to the project and name it “BaseApiController”.

To provide database actions, we first add a new ADO.NET Entity Data Model.

Various options are possible in the wizard depending on your preference and project method.
I chose the “Database First” method, because in this way the data model can be developed from SQL Server Management Studio.

In the next screen, add the database connection, indicate the database tables you want to work with in your project and let the scaffolding do its work. The great thing about Entity Framework is that foreign key relationships are also automatically detected and included in the generated code. Obviously something that can also be expected from an ORM tool. This makes it possible to include “one to many” and “many to many” relationships in the object model with “navigation properties” and maintain integrity in the database.

In the created .EDMX file, the object model can then be updated at any time with database adjustments by clicking on “Update model from database” in the context menu (right mouse button).

“Now that we have the design for the Web API and the data model ready, it is of course fun to actually do something with it.”

Attribute routing and inheritance

Now that the data model and the BaseApiController have been created in Visual Studio, we come to the generic routing piece. After all, the Web API methods will have to be accessed via endpoints.

I find it strange, but by default it is not possible in MVC to inherit routes. Fortunately, it is possible to achieve this by using the class below. With this we are going to introduce a RouteProvider that is suitable for inheritance.

Note: Pay particular attention to the “true” bit in the overload, which makes the difference.

We now add this InheritanceDirectRouteProvider to the initialization of the application.
This can be done as follows in the Application_Start method in a WebForms project:

Or like this, if you are working in an MVC project:

The standard routing is done based on convention.
As you can see in the code above, I chose the BaseApiController to automatically generate the routes in MVC based on the RoutePrefix attribute with the call to “MapHttpAttributeRoutes”.

Initialization and the “R” in CRUD

To make the BaseApiController generic, the type T is given in the signature class. This is then requested in the constructor and placed in a protected variable. The Entity Framework context of the data model is also instantiated in the constructor.

Note that in the GetAll method an entityDbSet variable is created with the database context of the concrete type passed. Since this type is not known in advance, it is stored as type var.
With a StringBuilder the DbSet can then be serialized directly to a Json string and the full collection of objects can be returned.

The generic GetEntityById method looks like this:

It can be seen that the action is once again taking place on the DbSet, this time with the Find method.

The “D” in CRUD: DeleteEntity

The generic DeleteEntity method looks like this:

It can be seen that again the action takes place on the DbSet, but this time with the FindAsync method.
Entity Framework supports asynchronous operations, which can then be used relatively easily with the async await pattern.

The “CU” in CRUD: Post Batch

Separate Create and Update methods can of course be created for creating and updating entities. However, in this implementation it has been decided to combine this in one “postbatch” method. This is elaborated below.

The method signature shows that it has been implemented entirely asynchronously and that the entities parameter is of the type object array. The latter makes it possible to “post” a JSON string with multiple objects using client-side code (JavaScript). The MVC model binder can cast this automatically, provided the contentType of the post is “application/json”.

Furthermore, it can be seen that the “generic element” is achieved by creating a DbSet based on the concrete (inheriting) type and serializing it to an entityObject of type var.

In the data model, the “Id” field is the primary key in most tables, which can be overridden with the property “IdPropertyName”.
As you can see, line 104 checks whether the Id contains the value -1.
Based on this, the Add method on the DbSet is called or the state is set to “Modified”, as shown in the Update section below.

The call to this postbatch method can be done with client-side code (JavaScript) as follows:

If you now want to create a concrete class in which the CRUD actions are immediately available, you can do this as follows:

Finally, the auxiliaries below are also needed to get the above code working.

Finally

In the way described above it is possible to create a generic web service that provides standard CRUD database actions.

If you also wish to use this web service in a secure manner in a SharePoint provider hosted app on pages with client-side code (JavaScript), then this blog may also be interesting for you: Secure Web API in an SP provider hosted app.