Web API OData V4 Open Types - How to configure Controller and Data Context

Question

I have a multi-tenant application that includes a Web API OData service layer. I have a new requirement to support custom fields, that will be unique to each tenant, and adding generic "customfield01", "customfield02" columns to my tables is not flexible enough.

I have explored a number of ways to describe and persist the custom data on the back-end, but the more challenging part seems to be extending my odata services to include the custom fields, differently, for each tenant.

The following link describes "Open Types" in odata v4 with Web API:

http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/odata-v4/use-open-types-in-odata-v4

The sample code works fine and provides the dynamic property behavior I need on my entities. However, the code only goes as far as using a hard-coded list of values for the back end. It isn't at all clear how to populate the entities from an Entity Framework data context.

At first, it seemed like it might be as easy as having a tenant-specific view in the database, for each tenant, but the issue is that the extended properties really need to be "unpivoted" from columns, into key-value pairs. Because of this, I wonder if I need a separate entity for the "extension" properties. So, I could have something like this for my POCOs:

public class Item
{
    [Key]
    public Guid ItemId { get; set; }

    public Guid TenantId { get; set; }

    // navigation property for the extension entity
    public virtual ItemExtension ItemExtension { get; set; }
}

public class ItemExtension
{
    [Key]
    public Guid ItemId { get; set; }    

    // dynamic properties for the open type
    public IDictionary<string, object> DynamicProperties { get; set; }}
}

But again, the question becomes how to populate these objects with data from my data context. Once again, I thought I could have a view to unpivot the columns, but this doesn't work because I could have different data types (that matter to me) for each dynamic property.

So, I really have several questions:

1. Does the POCO model above make sense for what I'm trying to accomplish?
2. What should my ItemController code look like to include the ItemExtension for all HTTP Verbs (GET, POST, PUT, PATCH, DELETE)
3. What should my data context have for the ItemExtension to allow it to access the extended columns on the back-end
4. How should the extended columns be persisted on the back-end to support this.

As far as what I've tried - lots of things that don't work, but I've settled on the following (assuming there isn't a better way):

1. A base POCO for each "extendable" entity with a separate "extension" entity for each (like the model above)

2. On the back end, since I need unlimited flexiblity and strong data types, I plan on having a separate extension table for each Tenant/Entity combination (would be named as [TenantId].[ItemExtension] with each column named and typed as necessary).

What I'm missing is everything in-between my data and my model. Any help would be greatly appreciated.

Answer 1

If you really don't wan't to create create stored procedure entity type object class using ORM, you can simply retrieve the json object from SQL stored procedure itself using FOR JSON PATH and throw it to client.

For example - Reference Link