A Certain Point of View Part 2 - Fluent NHibernate

Friday, April 11, 2014 @ 11:06

By: Corey Adler, Staff Developer

In a previous post I outlined how one would create a SQL View and map it to an object using Entity Framework Code-First with Migrations. In this post I will show you how you can accomplish the same thing but using Fluent NHibernate instead. You will see many similarities with the Entity Framework method, and some subtle differences.

Much like with Entity Framework there is no way to generate the view directly from the NHibernate class. It simply cannot be done, for the same reasons mentioned in the previous post (inability to specify tables and logic, populating the data, etc.). Any attempt to do so will result in a SQL Table being created instead of a SQL View. So now let’s start by doing the same thing we did in the previous post: Creating the SQL View. We will use the same example View as did the previous post, noting the use, again, of the ID field.

After constructing the View, it is then time to create the C# class. Much like Entity Framework, make sure that the field names are a precise match with the SQL View. On a side note, due to the many different ways of setting up NHibernate in any given project, I will not fully discuss getting NHibernate to see this C# class and to know that it should be mapped to the database—only to say that it should be done in the exact same way that you do for your other classes.

Afterwards it’s time to create the ClassMap for this C# class.

There should be 3 things that you notice about this mapping right away. First is the usage of SchemaAction.None(). Depending on how you’ve set up NHibernate in your project this will be required in order to get NHibernate to understand that it should never try and create this—that it should only map to something that exists, and fire an error when it doesn’t. On a project I was working on that needed to map NHibernate to SQL Views this ended up being needed because of an error that would be thrown when trying to update the database schema.

The second thing to notice is the use of ReadOnly(). This is absolutely required. Remember, you’re mapping to a SQL View that is based on the data coming from other tables. There shouldn’t be any attempt to save or update information on that View, unless it’s to the actual tables that they come from. Third is the use of Not.LazyLoad(). This is completely optional, but I’ve found it quite useful in bringing over all of the data right away—exactly what you need with a SQL View.

Once that has been completed, all that is left to do is to update your database, and run your program. Unlike Entity Framework Code-First, there’s no manual migration class needed in order to get it to work. Once you update your database schema then the mapping is completed, and you should be able to get C# objects from querying the SQL View.  This schema update is done differently depending on how each project has setup NHibernate to interact with SQL, and as such there is no code posted here for that process.

So let’s run the program, and we see in fact…

It works!

One final point to mention is the same point I made at the end of the Entity Framework post: If you need to adjust the SQL View for any reason, you must do it in the following order: Adjust the SQL View, adjust the C# class, adjust the Mapping class, and run the schema update. Not as simple as I’m sure you’d like, but it was certainly less painful than figuring out how to do a manual migration in Entity Framework Code-First.

In conclusion, SQL Views can make your application perform complex queries faster and better, and do not require you to give up using an ORM, or to manually create SQL or HQL queries in your C# code. The steps involved in mapping them may not be straightforward, but once you’ve mastered them, you’ll see how much better your application is for having taken the time to have done so.

Good luck!
Until next time, I wish you good coding.