In legacy databases, data is often spread across two or more tables, even though the rows represent one single entity. In SQL, such scenarios are handled using JOIN constructs in the queries, and NHibernate supports this.
- Add a new folder named
MappingJoinsto theMappingRecipesproject. - Add a class named
Articleto the folder:namespace MappingRecipes.MappingJoins { public class Article { public virtual int Id { get; protected set; } public virtual string Title { get; set; } public virtual string Abstract { get; set; } public virtual string Author { get; set; } public virtual string FullText { get; set; } } } - Add a new embedded mapping named
Article.hbm.xmlto the folder:<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="MappingRecipes" namespace="MappingRecipes.MappingJoins"> <class name="Article"> <id name="Id"> <generator class="native"/> </id> <property name="Title"/> <property name="Abstract"/> <property name="Author"/> <join table="ArticleFullText" optional="true"> <key column="ArticleId" unique="true"/> <property name="FullText"/> </join> </class> </hibernate-mapping> - Add a class named
Recipeto the folder:using System; using NH4CookbookHelpers; using NHibernate; namespace MappingRecipes.MappingJoins { public class Recipe : HbmMappingRecipe { protected override void AddInitialData(ISession session) { session.Save(new Article { Title = "Lazy properties", Author = "NHibernate", Abstract = "Supporting lazy properties is cool", FullText = "A really long article" }); } public override void RunQueries(ISession session) { var article = session.Get<Article>(1); Console.WriteLine("Title:" + article.Title); Console.WriteLine("Author:" + article.Author); Console.WriteLine("Abstract:" + article.Abstract); Console.WriteLine("Full text:" + article.FullText); } } } - Run the application and start the
MappingJoinsrecipe.
The <join> element in the mapping specifies that the mappings it contains belong to a different table than the one used for the mapped class. In the example, we have decided to store FullText in the joined table ArticleFullText, but we could just as well have added more properties and even related entities, collections, and so on. Running the recipe, the following queries are executed:
[screenshots]
The optional="false" setting (which is the default) tells NHibernate that we expect the ArticleFullText table to always contain a corresponding row. In other words, a full INNER JOIN can be used when data is retrieved. Had we specified optional="true", an OUTER JOIN would be used, and rows will only be inserted if any of the joined properties contains a value (not null). You can try this out by changing the mapping to optional="true" and removing the line, which assigns the FullText property. The queries executed are now.
The <join> functionality is very useful in legacy scenarios, where the database schema already has distributed entity data between two or more tables. However, we can also put it to good use in new models. One such incidence is when we use one table, in a table-per-class-hierarchy mapping, to handle inheritance hierarchies. Normally, we would have to add columns, for all properties of all classes in the hierarchy, to the single, common table. By using a <join> inside a subclass mapping, we can add an extra table, which will only hold the values specific to that subclass.