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

Answer 2

Now I do not use Entity Framework after his error with caching data. Look at Fluent NHibernate. In it ORM you can adjust the mapping of the OData v4 dynamic properties to the user type. Use nuget package Newtonsoft.Json.

Your class:

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

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

    ... 
}

and StoreDynamicProperties custom type of the Fluent NHibernate class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Newtonsoft.Json;
using NHibernate.UserTypes;
using NHibernate.SqlTypes;

[Serializable]
public class StoreDynamicProperties : IUserType
{
    private JsonSerializerSettings _settings = new JsonSerializerSettings(); // { TypeNameHandling = TypeNameHandling.All };

    public new bool Equals(object x, object y)
    {
        if (x == null && y == null)
            return true;

        if (x == null || y == null)
            return false;

        var xdocX = JsonConvert.SerializeObject((IDictionary<string, object>)x, _settings);
        var xdocY = JsonConvert.SerializeObject((IDictionary<string, object>)y, _settings);

        return xdocY == xdocX;
    }

    public int GetHashCode(object x)
    {
        if (x == null)
            return 0;

        return x.GetHashCode();
    }

    public object NullSafeGet(IDataReader rs, string[] names, object owner)
    {
        if (names.Length != 1)
            throw new InvalidOperationException("Only expecting one column…");

        var val = rs[names[0]] as string;

        if (val != null && !string.IsNullOrWhiteSpace(val))
        {
            return JsonConvert.DeserializeObject<IDictionary<string, object>>(val, _settings);
        }

        return null;
    }

    public void NullSafeSet(IDbCommand cmd, object value, int index)
    {
        var parameter = (DbParameter)cmd.Parameters[index];

        if (value == null)
        {
            parameter.Value = DBNull.Value;
        }
        else
        {
            parameter.Value = JsonConvert.SerializeObject((IDictionary<string, object>)value, _settings);
        }
    }

    public object DeepCopy(object value)
    {
        if (value == null)
            return null;

        //Serialized and Deserialized using json.net so that I don't
        //have to mark the class as serializable. Most likely slower
        //but only done for convenience. 

        var serialized = JsonConvert.SerializeObject((IDictionary<string, object>)value, _settings);

        return JsonConvert.DeserializeObject<IDictionary<string, object>>(serialized, _settings);
    }

    public object Replace(object original, object target, object owner)
    {
        return original;
    }

    public object Assemble(object cached, object owner)
    {
        var str = cached as string;

        if (string.IsNullOrWhiteSpace(str))
            return null;

        return JsonConvert.DeserializeObject<IDictionary<string, object>>(str, _settings);
    }

    public object Disassemble(object value)
    {
        if (value == null)
            return null;

        return JsonConvert.SerializeObject((IDictionary<string, object>)value);
    }

    public SqlType[] SqlTypes
    {
        get
        {
            return new SqlType[] { new StringSqlType(8000) };
        }
    }

    public Type ReturnedType
    {
        get { return typeof(IDictionary<string, object>); }
    }

    public bool IsMutable
    {
        get { return true; }
    }
}

and in ItemMap class:

using FluentNHibernate.Mapping;

public class ItemMap : ClassMap<Item>
{
    public ItemMap()
    {
        Table("Items");

        Id(item => item.ItemId)
            .GeneratedBy
            .GuidComb();

        Map(item => item.DynamicProperties)
            .CustomType<StoreDynamicProperties>()
            .Column("Properties")
            .CustomSqlType("varchar(8000)")
            .Length(8000);
        ...
    }
}