NHibernate dynamic mapping

Question

I am looking for some way to dynamically map database tables classes in my application using nhibernate (or if some other ORM works then let me know). I am fairly new to nhibernate, I used entity frameworks in the past though.

Most of my application will be using a static structures and fluent nhibernate to map them.

However there are multiple database tables that will be needed to be created and mapped to objects at each install site. These will all have as a base structure (id,name etc) however they will have additional fields depending on the type of data they are capturing. From some reading I found that I can use the "dynamic-component" mapping in xml to add fields using an IDictionary Attributes property. This is the first step and seems relatively straight forward. Ref (http://ayende.com/blog/3942/nhibernate-mapping-dynamic-component)

The second step is where I am struggling. I will need to define tables and map them depending on the client’s need. As stated above each of the tables will have a set of static properties, and some dynamic ones. They will also need to reference a static “Location”Class as shown below

Location (STATIC) (id,coordinates)
-----DynamicTable1 (DYNAMIC) (id,Name,location_id, DynamicAttribute1, DynamicAttribute2........)
-----DynamicTable2 (DYNAMIC) (id,Name,location_id, DynamicAttributeA, DynamicAttributeB....)

We will need to be able to create / map as many of these DynamicTables as the client needs. DynamicTable1, DynamicTable2 etc will most likely be different in some ways for most client sites. Is there any way in nhibernate to achieve this? The creating / management of the tables in the Database will be managed elsewhere, I just need some way to get this to map in my ORM.

A bit of background
This application will be used to store geological data. As geological data is inherently different depending on where it is, and geologist are using different methods and looking for different elements (gold, coal etc), the data structure to store this information needs to be extremely flexible.

Answer 1

see answer in Using nNHibernate with Emitted Code

class DynamicClass
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual Location Location { get; set; }
    public virtual IDictionary DynamicData { get; set; }
}

Template

<hibernate-mapping>
  <class name="DynamicClass">
    ...
    <dynamic-component name="DynamicData">
      <!--placeholder -->
    </dynamic-component>
  </class>
</hibernate-mapping>

replace <!--placeholder --> with generated

<property
  name="P1"
  type="int" />
<property
  name="P2"
  type="string" />

configure Sessionfactory

var sessionFactory = new NHibernate.Cfg.Configuration()
    .AddXml(generatedXml)
    ...                      // DatabaseIntegration and other mappings
    .BuildSessionFactory();

Query

var query = session.CreateCriteria<DynamicClass>();

foreach (var restriction in restrictions)
{
    query.Add(Restrictions.Eq(restriction.Name, restriction.Value))
}

var objects = query.List<DynamicClass>();

Edit: ups i havent realised you need multiple tables per client

Option 1:

<class name="DynamicClass" table="tablenameplaceholder"> with replace and a different Sessionfactory for each dynamic class

Option 2:

Subclassing the dynamic class and use TPS (table per subclass) mappings

Option 3: see Stefans answer just with xml

<class name="DynamicTable1" class="DynamicClass" table="DynamicTable1">

Answer 2

Take a look at the new Mapping By Code functionality of NH 3.2. It should make it easy to create new table definitions at runtime. In contrast to Fluent, you don't need to write a mapping class, you just can add new classes in for loops:

// lookup all dynamic tables in the database using SQL or SMO or whatever
var dynamicTables = GetDynamicTables();

// map all dynamic tables
foreach(var table in dynamicTables)
{
  mapper.Class<MyGenericEntity>(ca =>
  {
      // use an entity name to distinguish the mappings.
      ca.EntityName(table.Name);
      ca.Id(x => x.Id, map =>
      {
          map.Column("Id");
          map.Generator(Generators.HighLow, gmap => gmap.Params(new { max_low = 100 }));
      });
      // map properties, using what ever is required: if's, for's ...
      ca.Property(x => x.Something, map => map.Length(150));
  });
}

Using the entity name you can store and load the entities to and from different tables, even if they are mapped as the same entity class. It is like Duck Typing With NHibernate..

Believe me, it won't be easy. If you are interested in a big challenge which impresses every NH expert, just go for it. If you just want to get it working you should choose a more classic way: create a static database model which is able to store dynamic data in a generic way (say: name value pairs).