Logo

NHibernate

The object-relational mapper for .NET

How to

This page is converted from the old nhforge.org Wiki. First published by: Robert Byrne on 02-27-2009, Last revision by: John Davidson on 09-07-2011

Mapping the same class to a view and a table using entity-name

The Scenario

You have a single class which you must map to both a table and a database view (or perhaps another table). The view is returning records from the table itself, usually filtered, and the view may have more or less columns than the table. A more practical example is a multi tenant database, where a table has a tenant column, and the view filters the records to show only the ones associated with the current tenant. In this case, the backing table has a tenant column, but the view doesn't. To make things even more interesting, the table is in a separate database schema. For more details on this multi tenant architectural pattern see this article. Other possible uses for this mapping technique include dynamically mapping the same base class to multiple subclass tables, which is described here,

You want to map this in NHibernate, in a way that will allow you to specify which 'representation' of the entity you want to see, either the table containing records for all tenants, or the view containing records for the currently active tenant. Perhaps your system needs to access all the records for all tenants to perform auditing or cleanup. In all other cases, you only want to show the records filtered for the current tenant, via the mapped view.

So, can this be done? Thankfully, yes, but only if you like living on the cutting edge!

Prerequisites

In order to set this up with NHibernate, you will need to use the up and coming version 2.1, as we will be taking advantage of the new 'entity-name' feature. This means you will need to checkout and build the latest version of NHibernate. There's plenty of guidance online about this.

In this article, I'll be working against version 2.1.0.1001, revision 4099.

If you are using NHibernate.Linq, there's some special considerations that I'll explain later. You'll need to modify this library to take advantage of entity names, so you will need to checkout and build it from the nhcontrib project: 

I'll be working against version 1.0.0.0, revision 803.

Setting up the mappings

The class we're using is fairly simple, as the magic happens in the mapping. Note that the TenantId property is nullable, as it wont be mapped when we are using the view, more on that later.


public class MyEntity {
	public virtual int Id { get; private set; }
	
	public virtual string MyProperty { get; set; }
	
	public virtual int? TenantId { get; private set; }
}

The database is also pretty straightforward, we have a table which maps to the entity class, and a view which returns records from that table. Notice that the view has no tenant id. Also notice that both of these objects are in different schemas. This just highlights the fact that the mappings for the two objects would have to be different, even if the properties were identical.


CREATE TABLE [AllItemsSchema].[MyEntity](

	[ID] int not null,

	[MyProperty] varchar(200) NULL,
	
	[TenantId] int not null,
	
	CONSTRAINT [PK_MyEntity] PRIMARY KEY CLUSTERED ([ID])
)



CREATE VIEW [TenantSpecificSchema].[MyEntity]

AS

SELECT [ID], [MyProperty] FROM [AllItemsSchema].[MyEntity] WHERE [TenantId] = dbo.GetCurrentTenant()

So far so good. Now we move on to the mappings. Firstly, the table specific mapping. This will allow us to access all of the records for a particular entity, regardless of the tenant they are associated with.


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="MyAssembly" namespace="MyAssembly.MyNamespace">
	<class name="MyEntity" table="`MyEntity`" entity-name="Table" schema="`AllItemsSchema`">
		<id name="Id">
			<generator class="native" />
		</id>
		<property name="MyProperty" length="200" />
		<property name="TenantId" not-null="true" />
	</class>
</hibernate-mapping>

Some things to notice here are:

  • The 'schema' attribute is set to 'AllItemsSchema'
  • The new 'entity-name' attribute is specified. In this case we are keeping it fairly simple, in the real world you would want to use something that's unique for each entity.
  • The TenantId property is mapped

The rest of the mapping is the exactly the same as you would expect. As I noted above, the entity name here is deliberately simplistic. Its also worth mentioning that the entity name is used to uniquely identity an entity mapping across the entire session factory, so you cant repeat it on different class mappings. Next, on to the mapping of the view, which will provide only the records for the currently active tenant.


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="MyAssembly" namespace="MyAssembly.MyNamespace">
	<class name="MyEntity" table="`MyEntity`" entity-name="View" schema="`TenantSpecificSchema`" schema-action="none">
		<id name="Id">
			<generator class="native" />
		</id>
		<property name="MyProperty" length="200" />
	</class>
</hibernate-mapping>

This is where things get interesting:

  • We have used the same table and class name, but notice that the 'entity-name' is different
  • We have a different database schema 'TenantSpecificSchema'
  • The TenantId property is absent as the view does not provide this
  • The new 'schema-action' is set to none, this will prevent NHibernate from including this mapping in its schema export, it would otherwise attempt to create a table for this view

Although we are using the same class in both cases, the mappings can be quite different. In this example I've included the TenantId property on the entity class even though it will always be null when the view mapping is used. Whether you want to do this yourself is a matter of taste. One possibility would be to implement the TenantId property with an explicit interface, which would hide it from the consumers of the class unless needed.

Now that we have the mappings we can spin up a session factory using your favorite technique. Because we have used entity names for both of our mappings, you will see that we cant just store and retrieve entities by their type as usual. The entities are no longer identified by their types, but their entity name instead. Initially, I had planned to specify the entity name only for one of the mappings, and leave the other mapping as a 'default' with just its [Type] 'name' specified, but as I will explain later in more detail, this wont work as expected. If you want to reliably use entity name on one mapping of the class, you must use an entity name on them all.

Entity name and the ISession

In the latest revision of NHibernate, all the methods of ISession which take a System.Type have been overloaded to accept a string specifying the entity name. Apart form this, working with entity name mappings is exactly the same as their Type counterparts. If you are using a generic implementation of IRepository, things get even easier, as you can control the entity name in a single place, for example:


public enum EntityNameMode {
	Default,
	Table,
	View
}

public class NHibernateRepository<TEntity, TKey> : IRepository<TEntity, TKey> {
	public NHibernateRepository(ISession session, EntityNameMode mode) {
		this.session = session;
		this.mode = mode;
	}
	
	public TEntity Get(TKey key) {
		return (TEntity)session.Get(GetEntityName(), key);
	}
	
	public IList<TEntity> List() {
		return session.CreateCriteria(GetEntityName()).List<TEntity>();
	}
	
	public void SaveOrUpdate(TEntity entity) {
		session.SaveOrUpdate(GetEntityName(), entity);
	}
	
	private string GetEntityName() {
		switch (mode) {
			case EntityNameMode.Table: return "Table";
			case EntityNameMode.View: return "View";
			default: return typeof(TEntity).FullName;
		}
	}
	
	private ISession session;
	private EntityNameMode mode;
}

The entity name to use is based on the parameter we pass into the repository. In the real world you will want to use something more sophisticated to control the entity names. By specifying the Table mode when creating this repository, all the operations we perform against it will now deal with the records for all tenants. If we only want to deal with the records for a specific tenant, then we pass in the View mode.

	
	//this repository will work with data for all tenants
	var allTenantsRepository = new NHibernateRepository<MyEntity, int>(session, EntityNameMode.Table);

	//this repository will work with data for the current tenant only
	var currentTenantRepository = new NHibernateRepository<MyEntity, int>(session, EntityNameMode.View);
	
	

One thing that's important to realize is that internally, NHibernate implicitly creates an entity name whether you specify one explicitly or not. The entity name it uses by default is the FullName of the mapped type. This means that passing in the Type.FullName to the methods accepting a string has the same effect as passing in the System.Type itself. This can make it easier to manage your Repository implementation as you can use the string overloads exclusively, and pass in the type name when no specific entity name is required.

Now we have a repository that we can use to work with the two different profiles for our entities, as well as falling back to the standard behavior when no entity name mappings are required. This approach can be extended in any number of ways to support various complex mapping scenarios.

Its worth mentioning that you can also specify the entity name via IInterceptor.GetEntityName(object), but I haven't done that here because it only kicks in when saving or updating transient instances, as far as I could tell. You still have to supply the entity name to the ISession methods when querying the database, and I prefer to keep the entity name behavior in one place.

If you want to use NHibernate.Linq with this approach then you will soon find that there's currently (r.803) no way to specify the entity name when using the Linq<T> extension method on ISession. Not to fear, there's a fairly straightforward way to provide this feature with minimal impact on any querying code you may have.

Using Entity Name with Linq to NHibernate

Lets start by adding a protected property to our Repository implementation which will provide us with an INHibernateQueryable<T>. All we need to know about that type for now is that its returned from the ISession.Linq<T> extension method.

	
	protected INHibernateQueryable<TEntity> Linq {
		get {
			return session.Linq<TEntity>();
		}
	}

	//the rest of the repository is exactly the same...
	

We can now use Linq functionality in our repository by starting with that property. Lets add a useful Find() method to our repository that accepts a predicate which we can pass to the Linq implementation.

	
	public TEntity Find(Expression<Func<TEntity, bool>> predicate) {
		return Linq.Where(predicate).FirstOrDefault();
	}
	
	

There's one problem with this approach however. As you can see in the Linq property, we are passing in the current generic type of our repository. This means that our entity name mappings are not being used, and even worse than that, NHibernate will throw an error informing us that the type in question could not be found. This is to be expected, recall that we are overriding the implicit entity name, which defaults to the mapped types name, with our own.

The current implementation of NHibernate.Linq is based on ICriteria, and ISession.CreateCriteria has an overload that will accept an entity name, just like all the other ISession methods. Because the entity name (or the Type) of an ICriteria can not be modified after it is created, we must control the creation of the ICriteria ourselves. The way I chose to do it was by adding a function callback to the QueryOptions object in NHibernate.Linq. This callback accepts the Linq ISession and the current expression tree and returns a new ICriteria. This lets us quickly create a delegate that will call Session.CreateCriteria(string)

	
	protected INHibernateQueryable<TEntity> Linq {
		get {
			//create the new ICriteria with the current entity name, as opposed to the entity type
			var queryable = Session.Linq<TEntity>();
			queryable.QueryOptions.CreateCriteraFunction = (s, e) => s.CreateCriteria(GetEntityName());
			
			return queryable;
		}
	}
	
	

Because we are already using this property in our Find() method above, no changes are necessary, it will pick up this functionality automatically.

The changes to the NHibernate.Linq assembly are quite modest, I have included a diff in this article which you can apply to r.803 to enable this feature.

Some Gotchas

As I mentioned previously, NHibernate specifies an entity name for all entities, whether you specify one or not. If you don't, the entity name is taken from the full name of the mapped type. This means that NHibernate (currently) has no way of telling whether the entity name was explicitly specified, or taken from the class. This has an interesting side effect, which I alluded to at the start of the article, and answers the question of why you cant use a 'default' mapping with no explicit entity name.

The internals of NHibernate still have some assumptions that an entity name will be a full type name, including namespaces. The way things are currently implemented, it will examine the entity name to see if it contains a period ('.'), and if it does, it is assumed to be a class name, if it does not, it is assumed to be a user specified entity name. This means that all the existing code that expects an entity to be identified by its type will continue to work, and therefore, this behaviour is not likely to change any time soon.

So why is that important? Well, if you provide an entity name that contains a period, some sections of NHibernate (specifically SessionFactoryImpl.GetImplementors(string)) will assume it to be a type name and will start looking for all the mappings related to the persistent class. The upshot of this is that it will eventually find all of the mappings, regardless of their entity names that we defined, and use them both.

I first noticed this when I was getting an unexpected number of results in my tests, sometimes twice as many as I was expecting. The NHibernate logs showed that there were two queries being issued, one for each of the mappings, (table and view). It turned out to be the entity names I was using:

  • The view specific mapping used the default (that us, unspecified) entity name: "My.Namespace.TypeName"
  • The table specific mapping used a mangled type name: "[AllTenants] My.Namespace.TypeName"

As you can see, both of these entity names contain periods. This caused NHibernate to use both sets of mappings when performing any operation, regardless of the entity name I specified, hence the two queries being issued. The solution is to specify an explicit entity name for both mappings which contains no periods:

  • View specific: "My_Namspace_TypeName"
  • Table specific: "[AllTenants] My_Namespace_TypeName"

One downside of this approach, is that you must use the entity name in all references to the persistent class, i.e. in many-to-one and one-to-many elements. If you don't, you will get an error when you try to create the session factory complaining about a reference to an unmapped type. Remember that we are overriding the default entity name behavior with our own, so NHibernate can no longer identify an entity using its type. In my particular case, I am generating the mappings dynamically, so this extra check is not a big problem, but if you are mapping by hand or using Fluent NHibernate, its something to be aware of.

Conclusion

I've talked about mapping a single class to multiple tables (or views) using the new entity name feature in the upcoming version of NHibernate. I've also provided some suggestions on working with entity specific mappings, as well as some of the things you need to be aware of when using them. Finally, I've shown how Linq to NHibernate can be adapted for use with entity names.

The example given here, of tenant specific filtering, is perhaps not the best way to get the job done. Something more along the lines of this article may be more appropriate for a greenfield application. But if you find yourself dealing with a legacy database that exhibits this requirement, the new features in NHibernate 2.1 will allow you to do so with little or no impact on your domain architecture.

Patch for NHibernate.Linq r.803

Index: trunk/src/NHibernate.Linq/src/NHibernate.Linq/Visitors/NHibernateQueryTranslator.cs
===================================================================
--- trunk/src/NHibernate.Linq/src/NHibernate.Linq/Visitors/NHibernateQueryTranslator.cs	(revision 803)
+++ trunk/src/NHibernate.Linq/src/NHibernate.Linq/Visitors/NHibernateQueryTranslator.cs	(working copy)
@@ -39,7 +39,12 @@
 		{
 			if (rootCriteria == null)
 			{
-				rootCriteria = session.CreateCriteria(expr.ElementType, expr.Alias);
+				if (options.CreateCriteraFunction != null) {
+					rootCriteria = options.CreateCriteraFunction(session, expr);
+				}
+				else {
+					rootCriteria = session.CreateCriteria(expr.ElementType, expr.Alias);
+				}
 				options.Execute(rootCriteria);
 			}
 			return expr;
Index: trunk/src/NHibernate.Linq/src/NHibernate.Linq/QueryOptions.cs
===================================================================
--- trunk/src/NHibernate.Linq/src/NHibernate.Linq/QueryOptions.cs	(revision 803)
+++ trunk/src/NHibernate.Linq/src/NHibernate.Linq/QueryOptions.cs	(working copy)
@@ -1,4 +1,6 @@
 using System;
+using NHibernate.Criterion;
+using NHibernate.Linq.Expressions;
 
 namespace NHibernate.Linq
 {
@@ -9,6 +11,8 @@
 	{
 		private Action<ICriteria> action;
 
+		public Func<ISession, QuerySourceExpression, ICriteria> CreateCriteraFunction;
+
 		public QueryOptions()
 		{
 			this.action = delegate { };
	
© NHibernate Community 2024