NHibernate 动态列数 [英] NHibernate Dynamic Columns Number

问题描述

在我的 C# 项目中，我需要在代码中创建一个数据库，其中包含一个带有动态列号的表.像这样

In my C# project I need create in code one database with one table with dynamic columns number. Like this

------------------------------------------------------------------------------     
| Time        ¦ Element #1       | Element#2        ¦ ... ¦ Element#N        ¦
------------------------------------------------------------------------------
¦ TimeValue#1 ¦ Element#1Value#1 ¦ Element#2Value#1 ¦ ... ¦ Element#NValue#1 ¦
¦ TimeValue#2 ¦ Element#1Value#2 ¦ Element#2Value#2 ¦ ... ¦ Element#NValue#2 ¦
                                      ...
¦ TimeValue#M ¦ Element#1Value#M ¦ Element#2Value#M ¦ ... ¦ Element#NValue#M ¦
------------------------------------------------------------------------------

我使用简单的 SQL 查询 'SQL CREATE TABLE'

I use simple SQL query 'SQL CREATE TABLE'

public void AddTable(string TableName, Dictionary<string,Type> columns)
{        
    ...
    string query = @"CREATE TABLE " + TableName + "(";

    foreach (var c in columns)
    {
        SqlParameter temp = new SqlParameter();
        string t = SetSQLType(ref temp, c.Value).ToString();
        query += c.Key + " " + (t == SqlDbType.NVarChar.ToString() ? (t + "(200)") : (t)) + ",";
    }

    query = query.Substring(0, query.Length - 1);
    query += " )";
    ...
}

但我想，也许，我可以为此使用更舒适的 ORM，例如 - NHibernate.我可以吗?我读到了 mapping-by-code 和动态组件，但我不确定在我的情况下我到底需要做什么.

But I think, maybe, I can use more comfortable ORM for this, for example - NHibernate. Can i? I read about mapping-by-code and dynamic component, but I'm not sure, what exactly I need to do in my case.

我想，我需要这样的东西:

I think, I need something like this:

public class Elements
{
    public virtual DateTime Timestamp { get; set; }
    public virtual IEnumerable<double> Values { get; set; }
}

我可以映射这个类吗?

推荐答案

这个话题很有趣.乍一看可能不太清楚.以我的回答作为概述，来自下面列出的所有来源的摘要.也许它会给你答案.

This topic is interesting. And maybe not so clear at first look. Take my answer as an overview, a summary from all the sources listed below. And maybe it will give you the answer.

从 C# 的角度来看，你可以这样考虑这些动态属性

From the C# point of view, you can think about these dynamic properties this way

public virtual IDictionary<keyType, valueType> Additional { get; set; } 
                 // (e.g. <string, object>

public virtual IDictionary Additional { get; set; }

这两个都是动态的.没有用于处理 IDictionary 的编译时检查.更好的情况是检查IDictinary<,> 的通用参数.但是因为我们谈论的是动态映射，所以我们可以牺牲编译时检查...

Both of these are dynamic. There is no compile-time checking for handling IDictionary. Better situation is the generic arguments checking for IDictinary<,>. But because we are talking about dynamic mapping, the compile time check is what we can sacrifice...

要将数据加载到这些字典之一中，我们必须(在大多数情况下)进行不同的映射并具有不同的表结构.对于行中包含的数据的转置，Generic 会很方便.Non-Generic 可用于列映射(如问题中的示例).让我们一起讨论

To load data into one of these dictionaries we have to (in most cases) do different mapping and have different structure of tables. The Generic would be handy for transpostion of data contained in rows. The Non-Generic could be used for column mapping (as the example in the question). Let's discuss both

让我们从更安全的场景开始.然后触摸问题附近的解决方案

Let's start with more type safe scenario. Then touch solution close to the question

例如，让我们根据他们的角色/类型将一些个人映射到一些实体(例如合同).1) 经理 2) 领导 3) 测试员.如果我们知道每个类型/角色只能有一个人(只有一个测试员或没有)，我们可以将其解释为:

For example, let's map a few Persons to some Entity (e.g. Contract) based on their roles/types. 1) Manager 2) Leader 3) Tester. If we know that there could be only one person per type/role (only one Tester or none), we can explain it as:

public virtual IDictionary<PersonType, Person> Persons { get; set; }

我们现在充满活力.可能有 0、1 或 1+ 个人与合同相关.PersonType 中的每一个都必须是唯一的.并且我们还可以在运行时引入新的 PersonType ，并扩展任何 Contract 相关的 Person 集...

We are dynamic right now. There could 0, 1 or 1+ Persons related to the Contract. Each of them has to be unique by the PersonType. And we can also introduce new PersonTypes in a runtime, and extend any Contract's related Person set...

映射应该是这样的

<map name="Persons" table="ContractPerson" >
    <key column="ContractId"/>
     <index-many-to-many column="PersonTypeId" class="PersonType"/>
     <one-to-many class="Person"/>
</map>

这是 6.9 的示例.三元关联.涉及三个实体，我们在运行时仍然具有灵活性.如前所述，我们可以插入新的 PersonType 并修改这些 Contract-Person 关系.

This is an example of 6.9. Ternary Associations. There are three entities involved, and we still do have flexibility in the runtime. As already said, we can insert new PersonTypes and amend these Contract-Person relations.

这个场景(与IDictinary相比)仍然提供了很多编译时检查.

This scenario (in comparison with IDictiniary<string, object>) still provides lot of compile-time checking.

在上述场景中，如果我们想使用 并且从 rows 的角度来看是动态的 - 我们需要一个像这样的表:

In the scenario described above, if we would like to use <map> and be dynamic from a rows perspective - we need a table like this:

ElemntId| TheKey       | TheValue (e.g. nvarchar)
1       | "name"       | "Element A"
1       | "time"       | "20:02"
1       | "date"       | "2013-05-22"
1       | "value"      | "11.22"

C# 看起来像这样

public class Elements
{
    ...
    public virtual IDictionary<string, string> Values { get; set; }
}

映射:

<map name="Values" table="DynamicElementValues" >
   <key column="ElementId"/>
    <index column="TheKey" type="string"/>
    <element column="TheValue" type="string"/>
</map>

因为我们使用了 IDictionary 所有值都是字符串.我们需要一些MetaData来正确解释它的值

Because we've used IDictionary<string, string> all values are strings. We would need some MetaData to correctly interpret its value

我们获得了:

• 多种类型的多种值的动态集合

我们输了:

• 能够将任何值放入 SELECT 或 ORDER BY 子句中
• 必须转换数据类型(从字符串到任何其他类型)

事实上，带有string键的字典是动态的，但是太多了.作为如图 1a) 所示，以某种方式管理集合总是更好的主意用作键的值.这就是我们讨论三元的原因协会.因为我们迟早要解释数据这本字典 - 带有一些元数据，使用它们可能很方便也作为钥匙...

In fact, dictionary with a string key is dynamic, but too much. As shown in the 1a) it is always better idea, to somehow manage the set of values to be used as a key. That's why we've discussed Ternary associations. Because we sooner or later have to interpret the data in this dictionary - with some MetaData, it could be handy to use them also as a key...

2) 非通用 IDictionary - 动态 列

这次我们将真正尝试对列进行动态解决方案.

2) Non-Generic IDictionary - Dynamic columns

This time we will really try to do dynamic solution over columns.

NHibernate 的特性，我们可以在这里使用的是:

The NHibernate features, we can use here are:

• 4.4.动态模型、
• 5.1.13.组件，动态组件，
• 5.1.18.加入

这种映射非常接近问题，符合我们的要求.我们将有这个 C# 表示

This kind of mapping is very close to the Question, to our requirement. We will have this C# representation

public class Elements
{
    ...
    public virtual IDictionary DynamicValues { get; set; }
}

这可能是映射:

<join table="ElemntValues" >

  <key column="ElementId" />
  <dynamic-component name="DynamicValues"   >

    <property name="Time"        type="TimeSpan" />
    <property name="Date"        type="DateTime" />
    <property name="Salary"      type="decimal" />
    <property name="Color"       type="string" />
    <property name="WorkingDays" type="integer"  />
    <many-to one....
    ...

  </dynamic-component>
</join>

在这种情况下，我们确实有分离表 ElementValues，加入到我们的父实体(作为其 映射的一部分).

In this case, we do have separted table ElementValues, joined to our Parent entity (as a part of its <class> mapping).

这不是唯一的映射.可能还有其他映射类型，例如使用 4.4.动态模型

This is not the only mapping. There could be other mapping types e.g. with 4.4. Dynamic models

<class entity-name="DynamicValues"...

这些可能需要一些更特殊的处理(插入、更新、删除)

These could require some more special handling (insert, udpate, delete)

Join 会简化很多东西，但总是会在 SQL 语句中使用(即使只需要 Parent 核心属性)

Join would simplify lot of stuff, but will be used in a SQL statment always (even if only Parent core properties are required)

是动态的吗?

我们获得了:

• 在 C# 上，我们只有属性 IDictionary ElementValues
• NHibernate 为我们做了运行时检查.只有正确类型的值才能插入到键中(Salary 必须是十进制)
• 通过一些MetaData模型，我们可以真正对用户动态
• 任何 Mapped 属性都可以在 SELECT(投影)和 ORDER BY(用户会喜欢它)中使用

• On a C# we have only property IDictionary ElementValues
• NHibernate does for us runtime checks. Only correct types of value could be inserted to the keys (Salary must be decimal)
• with some MetaData model we can really be dynamic to the user
• Any of the Mapped properties could be used in SELECT (projections) and ORDER BY (user will love it)

我们输了:

• 一些性能，因为所有数据(例如 session.Get<>(id))都将被加载
• 在添加或删除列的情况下，我们不是动态的.所有映射都是应用程序分发的一部分，并且不能在运行时更改.好吧，我们总是可以重新部署新的映射...

• some performance, because all data (e.g. session.Get<>(id)) will be loaded
• we are not dynamic, in case of adding or removing columns. All the mapping is part of the application distribution, and cannot be change in runtime. Well, we can always re-deploy just new mapping...

虽然 IDictinary 从 C# 的角度来看非常动态(它可以包含任何密钥/对值)，由于 NHibernate 映射，内容是管理.只能将 integer 值添加到映射为整数.但是我们在运行时如何知道:我们有哪些键?什么值可以放在那里并从那里检索?再次，我们需要一些 MetaData... 不是充当密钥的角色，但它们将是在运行时至关重要.NHibernate 检查是最后一道防线.

While the IDictinary is from a C# perspective very dynamic (it could contain any key/pair value), due to NHibernate mapping, the content is managed. Only integer values could be added to the properties mapped as integer. But how would we in a runtime know: what keys we do have? what values could be placed there and retrieved from there? Again, we need some MetaData... not acting the role of a key, but they will be crucial in the runtime. NHibernate checks are last line of defense.

3) 如何在运行时更改映射(添加了新列)?

好吧，文档中说明了什么?7.5.动态组件:

这种映射的优点是能够确定部署时组件的实际属性，只需通过编辑映射文件.(映射文档的运行时操作是也可以，使用 DOM 解析器.)

The advantage of this kind of mapping is the ability to determine the actual properties of the component at deployment time, just by editing the mapping document. (Runtime manipulation of the mapping document is also possible, using a DOM parser.)

...使用 DOM 解析器.老实说，我不知道这意味着什么，如何实施.

...using a DOM parser. To be honest, I do not know what that mean, how to implement that.

还有 Mapping-by- 中的 Adam Bar代码 - 动态组件声明(见评论)

我认为动态组件不能在两个对象上都是动态的和数据库级别.请注意，组件部分存储为普通列，所以我们需要知道它的列表...

I think that dynamic component can't be really dynamic at both object and database level. Note that the component parts are stored as ordinary columns, so we need to know its list...

但是Firo - NHibernate 动态映射有一个不错的想法(看看，太复杂了一些摘录).如果真的需要，这可能是一个真实动态世界的解决方案......运行时的新列...... IDictionary

But there is a nice idea from Firo - NHibernate dynamic mapping (take a look, too complex for some extract). If really needed, this could be solution to a real dynamic world... new columns in runtime ... new keys mapping in IDictionary

使用 映射，我们可以变得非常动态.运行时不同的键和值.无需重新部署.但是我们不能在 select 或 order by 中使用这些动态属性.很难按这些值过滤

With a <map> mapping we can be very dynamic. Different keys and values in runtime. No need to redeploy. But we cannot use these dynamic properties in a select or order by. It is hard to filter by these values

使用 我们(开箱即用)依赖于映射.但是我们确实将数据放在列中，因此我们可以使用连接来检索它们.易于过滤、排序.和/但必须有一些元数据来指导我们拥有什么以及我们能做什么.

With a <dynamic-component> we are (out of the box) dependent on the mapping. But we do have our data in columns and therefore we can use joins to retrieve them. Easy to filter, sort. And/but there must be some metadata to guide us what we do have and what we can do.

• Ayende 动态组件
• Adam Bar，Mapping-by-Code -动态组件
• Firo 和 运行时映射替换
• 4.4.动态模型
• 7.5.动态组件

这篇关于NHibernate 动态列数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！