Web API OData V4打开类型——如何配置控制器和数据上下文

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.

我有一个多租户应用程序，它包含一个Web API OData服务层。我有一个支持自定义字段的新需求，这对每个租户来说都是唯一的，在我的表中添加通用的“customfield01”、“customfield02”列不够灵活。

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.

我已经探索了许多方法来描述和持久化后端上的自定义数据，但更具挑战性的部分似乎是扩展我的odata服务，以使每个租户包含定制字段。

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

以下链接使用Web API描述odata v4中的“开放类型”:

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:

起初，对于每个承租者来说，在数据库中有一个特定于租户的视图似乎很容易，但问题是扩展属性确实需要从列到键-值对进行“unpivote”。因此，我想知道是否需要为“扩展”属性创建一个单独的实体。所以，我可以给我的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:

所以，我有几个问题:

Does the POCO model above make sense for what I'm trying to accomplish?
上面的POCO模型对我要实现的目标有意义吗?
What should my ItemController code look like to include the ItemExtension for all HTTP Verbs (GET, POST, PUT, PATCH, DELETE)
我的ItemController代码应该包含所有HTTP动词的ItemExtension (GET、POST、PUT、PATCH、DELETE)
What should my data context have for the ItemExtension to allow it to access the extended columns on the back-end
对于ItemExtension，我的数据上下文应该有什么来允许它访问后端上的扩展列
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):

就我所尝试过的而言——很多事情都行不通，但我已经确定了以下几点(假设没有更好的方法):

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

每个“可扩展”实体的基本POCO，每个实体都有一个单独的“扩展”实体(如上面的模型)
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).

在后端，由于我需要无限的灵活性和强大的数据类型，我计划为每个租户/实体组合(将命名为[TenantId])拥有一个单独的扩展表。[ItemExtension]，每个列按需要命名和输入)。

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

我缺少的是数据和模型之间的一切。如有任何帮助，我们将不胜感激。

1 个解决方案

#1

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.

现在我不使用实体框架后，他的错误缓存数据。看看连贯NHibernate。在它的ORM中，您可以调整OData v4动态属性到用户类型的映射。使用Newtonsoft.Json nuget包。

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:

和StoreDynamicProperties自定义类型的Fluent NHibernate类:

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:

而在ItemMap类:

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);
        ...
    }
}

#1