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

问题描述

在我的C＃项目，我需要code与动态数列一个表创建一个数据库。像这样

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的。我可以吗？我读到映射逐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; }
}

我可以映射这个类？

Can I mapping this class?

推荐答案

这话题很有趣。也许没有那么在第一次看不清楚。就拿我的答案的概述，下面列出的所有来源的摘要。也许它会给你答案。

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＆LT;，＆GT; 。但由于我们正在谈论的动态的映射，编译时检查是什么，我们可以牺牲...

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...

要数据加载到其中之一的词典的，我们必须（在大多数情况下）做不同的映射，并有表的不同结构。通用将为包含在行数据transpostion得心应手。非泛型可用于的列映射的（如问题的例子）的。让我们来讨论这两种

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 s和扩展任何合同的关系人组...

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。三元协会的。这里涉及三个实体，我们仍然做在运行时的灵活性。前面已经说过，我们可以插入新PersonTypes和修改这些合同人称关系。

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.

这个应用场景（比较 IDictiniary＆LT;字符串对象＆gt; ）仍然提供了大量的编译时检查

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

在如上所述，如果我们想用场景＆LT;地图＆GT; 并从行的角度是动态的 - 我们需要一个表是这样的：

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＆LT;字符串，字符串＆GT; 所有值都是字符串。我们需要一些元数据正确间preT它的值

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

我们获得了：

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

我们失去了：

• 来把任何值转换成SELECT或ORDER BY子句能力


• 有数据类型转换（从字符串到任何其他）

事实上，字典，一个字符串键是动态的，但过多。如
  在1A所示），它始终是更好的主意，以某种方式管理组
  值来作为键即可。这就是为什么我们已经讨论三元
  关联。因为我们迟早要跨preT数据
  这本词典 - 与一些元数据，它可以得心应手地使用它们
  也作为密钥...

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) 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。加入

• 4.4. Dynamic models,
• 5.1.13. component, dynamic-component,
• 5.1.18. join

这样的映射是非常接近的问题，我们的要求。我们都会有这样的C＃重presentation

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>

在这种情况下，我们确实有separted表 ElementValues​​ ，加入到我们的家长实体（作为其＆LT的一部分;类＆GT; 映射）。

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"...

这可能需要一些特殊处理（插入，UDPATE，删除）

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

加入将简化很多东西，但会在SQL说明书总是使用（即使只的父的核心属性是必需的）

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的为我们做的运行时检查。只有正确类型的值可以插入到钥匙（工资必须是十进制）


• 一些元数据模型，我们可以真正动态的用户


• 所有的映射特性能够在SELECT（预测）和顺序使用（用户一定会喜欢它）

• 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＆LT;>（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的映射中，内容是
  管理。只有整数值可以添加到映射为属性
  整数。但是，我们在运行时怎么会知道：我们有什么键？什么
  值可以在那里放置并从那里检索？再次，我们需要
  一些元数据...不是演戏了关键的作用，但他们会
  在运行时是至关重要的。 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.

那么，是什么在文档中陈述？ 7.5。动态组件的：

3) How to change mapping in the runtime (new column added)?

Well, what is stated in the documentation? 7.5. Dynamic components:

这种映射的优点是确定的能力
  在部署时组件的实际特性，仅仅通过编辑
  映射文档。 （运行时映射文件的操纵
  也有可能，使用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.

和亚当也是在酒吧映射逐$ C $ ç - 动态组件规定（见注释）

And also Adam Bar in Mapping-by-Code - dynamic component stated (see comments)

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

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

随着＆LT;地图和GT; 映射我们可以非常动态的。不同的键和值在运行时。无需重新部署。但是，我们的不能在通过选择或为了使用这些动态属性。这是很难通过这些值过滤

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

随着＆LT;动态成分＆gt; 我们（开箱）依赖的映射。但我们有我们的列中的数据，因此我们可以使用连接来检索。易筛选，排序。和/但必须有一些元来指导我们什么，我们就已经和我们能做些什么。

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.

• 组件


• 亚当酒吧，映射逐code - 动态组件


• FIRO和运行映射更换


• 4.4。动态模型


• 7.5。动态组件

• Ayende Dynamic component
• Adam Bar, Mapping-by-Code - dynamic component
• Firo and runtime mapping replacement
• 4.4. Dynamic models
• 7.5. Dynamic components

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