{"id":3664,"date":"2019-04-03T17:26:01","date_gmt":"2019-04-03T17:26:01","guid":{"rendered":"https:\/\/wspelt.com\/?p=3664"},"modified":"2024-07-16T16:00:42","modified_gmt":"2024-07-16T16:00:42","slug":"generic-entity-framework-crud-controller-for-web-api","status":"publish","type":"post","link":"https:\/\/wspelt.com\/?p=3664","title":{"rendered":"Generic Entity Framework CRUD controller for Web API"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

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

What do you need<\/h2>\n\n\n\n

The starting situation is a created empty MVC Web API 2 project or a WebForms project with the \u201cMicrosoft.AspNet.WebApi\u201d 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<\/em><\/p>\n\n\n\n

Set up Web API and Data Model<\/h2>\n\n\n\n

Add an empty Web API 2 Controller to the project and name it “BaseApiController”.<\/p>\n\n\n\n

\"\"
Adding an empty Web API 2 Controller<\/em><\/figcaption><\/figure>\n\n\n\n

To provide database actions, we first add a new ADO.NET Entity Data Model.<\/p>\n\n\n\n

\"\"
Adding a new ADO.NET Entity Data Model<\/em><\/figcaption><\/figure>\n\n\n\n

Various options are possible in the wizard depending on your preference and project method.
I chose the \u201cDatabase First<\/strong>\u201d method, because in this way the data model can be developed from SQL Server Management Studio.<\/p>\n\n\n\n

\"\"
Choice of model type: Database first or Code first<\/em><\/figcaption><\/figure>\n\n\n\n

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 \u201cone to many\u201d and \u201cmany to many\u201d relationships in the object model with “navigation properties” and maintain integrity in the database.<\/p>\n\n\n\n

In the created .EDMX file, the object model can then be updated at any time with database adjustments by clicking on \u201cUpdate model from database\u201d in the context menu (right mouse button).<\/p>\n\n\n\n

\n

\u201cNow 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.\u201d<\/p>\n<\/blockquote>\n\n\n\n

Attribute routing and inheritance<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

\"\"
Adding the Inheritance Direct RouteProvider class<\/em><\/figcaption><\/figure>\n\n\n\n

Note: Pay particular attention to the \u201ctrue\u201d bit in the overload, which makes the difference.<\/p>\n\n\n\n

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:<\/p>\n\n\n\n

\"\"
Set up routing with Inheritance Direct RouteProvider in a Web Forms project<\/em><\/figcaption><\/figure>\n\n\n\n

Or like this, if you are working in an MVC project:<\/p>\n\n\n\n

\"\"
Set up routing with InheritanceDirectRouteProvider in an MVC project<\/em><\/figcaption><\/figure>\n\n\n\n

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 \u201cMapHttpAttributeRoutes\u201d.<\/p>\n\n\n\n

Initialization and the \u201cR\u201d in CRUD<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

\"\"
Initialization of the BaseApiController class and the GetAll method<\/em><\/figcaption><\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

The generic GetEntityById method looks like this:<\/p>\n\n\n\n

\"\"
Adding the generic GetEntityById method<\/em><\/figcaption><\/figure>\n\n\n\n

It can be seen that the action is once again taking place on the DbSet, this time with the Find method.<\/p>\n\n\n\n

The \u201cD\u201d in CRUD: DeleteEntity<\/h2>\n\n\n\n

The generic DeleteEntity method looks like this:<\/p>\n\n\n\n

\"\"
Adding the generic DeleteEntity method<\/em><\/figcaption><\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

The \u201cCU\u201d in CRUD: Post Batch<\/h2>\n\n\n\n

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 \u201cpostbatch\u201d method. This is elaborated below.<\/p>\n\n\n\n

\"\"
The Create section in the postbatch method<\/em><\/figcaption><\/figure>\n\n\n\n

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 \u201cpost\u201d 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 \u201capplication\/json\u201d.<\/p>\n\n\n\n

Furthermore, it can be seen that the \u201cgeneric element\u201d is achieved by creating a DbSet based on the concrete (inheriting) type and serializing it to an entityObject of type var.<\/p>\n\n\n\n

In the data model, the \u201cId\u201d field is the primary key in most tables, which can be overridden with the property \u201cIdPropertyName\u201d.
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 \u201cModified\u201d, as shown in the Update section below.<\/p>\n\n\n\n

\"\"
The Update section in the postbatch method<\/em><\/figcaption><\/figure>\n\n\n\n

The call to this postbatch method can be done with client-side code (JavaScript) as follows:<\/p>\n\n\n\n

\"\"
Calling the postbatch method with clientside code (JavaScript)<\/em><\/figcaption><\/figure>\n\n\n\n

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

\"\"
Implementation of a concrete class with immediately available CRUD actions<\/em><\/figcaption><\/figure>\n\n\n\n

Finally, the auxiliaries below are also needed to get the above code working.<\/p>\n\n\n\n

\"\"
Adding helper methods for CRUD actions<\/em><\/figcaption><\/figure>\n\n\n\n

Finally<\/h2>\n\n\n\n

In the way described above it is possible to create a generic web service that provides standard CRUD database actions.<\/p>\n\n\n\n

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<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"

During a job I came across the following wish: Developing a generic web service that provides as many standard database actions as possible.
\nI will elaborate on the implementation in this blog post.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_genesis_hide_title":false,"_genesis_hide_breadcrumbs":false,"_genesis_hide_singular_image":false,"_genesis_hide_footer_widgets":false,"_genesis_custom_body_class":"","_genesis_custom_post_class":"","_genesis_layout":"","footnotes":""},"categories":[38,39,37],"tags":[],"class_list":["post-3664","post","type-post","status-publish","format-standard","category-net","category-entity-framework","category-javascript","entry","has-post-thumbnail"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/wspelt.com\/index.php?rest_route=\/wp\/v2\/posts\/3664","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wspelt.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wspelt.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wspelt.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wspelt.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3664"}],"version-history":[{"count":13,"href":"https:\/\/wspelt.com\/index.php?rest_route=\/wp\/v2\/posts\/3664\/revisions"}],"predecessor-version":[{"id":3691,"href":"https:\/\/wspelt.com\/index.php?rest_route=\/wp\/v2\/posts\/3664\/revisions\/3691"}],"wp:attachment":[{"href":"https:\/\/wspelt.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3664"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wspelt.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3664"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wspelt.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3664"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}