How to use NHibernate with an entity distributed across multiple tables?

Question

I have existing tables using an open schema. I have an Item table, and various objects are classified as Elements, and then have properties stored in Item property tables. A single object type can have fields stored in several tables. We expose objects with representations. Thus, most objects are consistent with the view, and then when we insert / update, we must systematically update tables or use stored procedures.

I am trying to determine if NHibernate can get anything compared to our custom repositories (which follow the factory pattern). Right now, I see great difficulty getting NHibernate to work with such a database schema. The way I see it, we either have to completely reorganize our database to follow NHibernate conventions, or completely refactor or entities in some way.

I don't see much in the documentation on how to do this, with the exception of the simplest examples, which include databases that more or less comply with NHibernate conventions.

Here is a representative database diagram. We have Episode as an object that retrieves information from Item, IP_Episode, IP_EpisodeBroadcastInfo, IP_Show, etc. To create all the fields that he needs.

Answer 1

You mention the agreement. This is the concept of Fluent NHibernate, and yes, what you are doing does not exactly comply with existing Fluent NHibernate conventions. However, this is well within the scope of NHibernate. NHibernate is different in that it can be mapped to all kinds of different database schemas. Feel free how Fluent NHibernate wants you to leave. I am not saying that do not use Fluent NHibernate. If you agree and are reasonable in your database design, you can write your own conventions to make them consistent.

To increase the flexibility of NHibernate, suppose we have a table structure like this:

create table Episode ( Id int not null primary key, NumberInSeries int null ); create table Show ( Episode_id int not null primary key, Title nvarchar(100) not null, foreign key (Episode_id) references Episode (Id) ); create table Broadcast ( Episode_id int not null primary key, InitialAirDate datetime not null, foreign key (Episode_id) references Episode (Id) ); 

One line in Episode corresponds to zero or one line in Show and zero or one line in Broadcast . You can model this type of relationship in several ways in .NET. Here are some options available to you through NHibernate:

1. Inheritance

 public class Episode { public virtual int Id { get; set; } public virtual int? NumberInSeries { get; set; } } public class Show : Episode { public virtual string Title { get; set; } } public class Broadcast : Episode { public virtual DateTime InitialAirDate { get; set; } } 

Use this if you want to model relationships that don't change. If an episode is a show, it is always a show. In addition, this simulation will mean that the Episode cannot be both a show and a broadcast. I do not think this is what you want, but you may find it useful in other places in your model.

For more information, see ....

• Formal inheritance mapping documentation
• Ayende Inheritance Mapping Blog Entry

2. one-to-one

 public class Episode { public virtual int Id { get; set; } public virtual int? NumberInSeries { get; set; } public virtual Show Show { get; set; } public virtual Broadcast Broadcast { get; set; } } public class Show { public virtual Episode Episode { get; set; } public virtual string Title { get; set; } } public class Broadcast { public virtual Episode Episode { get; set; } public virtual DateTime InitialAirDate { get; set; } } 

This gives you more control over which tables contain the row associated with this Episode, because you can set episode.Broadcast = null , for example. It’s also good that there are both Show and Broadcast for this episode.

For more information, see ....

• one-to-one papers
• Ayende's one-to-one blog post

3. join

 public class Episode { // These properties come from the Episode table... public virtual int Id { get; set; } public virtual int? NumberInSeries { get; set; } // This one comes from the Show table. public virtual string Title { get; set; } // This one comes from the Broadcast table. public virtual DateTime InitialAirDate { get; set; } } 

This is a good and easy way to present data, but you do not get control over whether rows are included in non-Show and Broadcast tables.

For more information, see ....

• Official join documentation
• Join iende blog post

Since you said, “One type of entity can have fields stored in several tables,” that sounds to me like join should be able to handle the way you are currently modeling.