< Browse > Home / Code Samples, Software Development / Blog article: Custom Dot Net MVC OData Web API provider via native SQL when Entity Framework is not feasible

| RSS

Custom Dot Net MVC OData Web API provider via native SQL when Entity Framework is not feasible

October 30th, 2016 | No Comments | Posted in Code Samples, Software Development

OData is a very powerful platform for accessing data via rest. Visual Studio provides out of the box OData services leveraging Enterprise Framework.

 

However we often run into scenarios where we need to expose a complex data set however the database table schemas themselves simply do not translate to anything compatible with Enterprise Framework. Further to this in the real world we sometimes do not have the luxury of changing the database schema to suit our needs. For example consider SAP data dump tables in a large corporate maintained by another party and where copying the data into a separate database is not viable or permitted.

 

I wrote the following implementation to translate OData queryOptions into native MS Sql statements and return List<T> collections. This OData To SQL Builder is for MS SQL Server but you could convert the SQL statements into another DB format as well.

 

**Usage scenario:**
Create a “Web API 2 OData V3 Controller with read/write actions” and bind it to the routing as per normal.
Next add your data select methods by modifying the following template to fit your scenario:

 

 

**Implementation Details**

 

The Data Entity Class:
Create a data entity class that supports ILoadFromDataRow Interface below and provides an Enum for the columns:

Interface ILoadFromDataRow:

 

Entity Property Enum:

 

Entity Data Class:

 

The SQL Builder Helper Class:

 

 

 

 

Leave a Reply 1297 views, 1 so far today |

Comments are closed.