NHibernate is now my framework of choice for working with a relational database. With a little bit of knowledge and a willingness to research, it’s pretty straightforward to make NH jump through whichever hoops you need it to. That said, there are certainly a number of friction points which can slow down any development effort. Mostly, these can be solved with a couple of tricks and tools which can be applied at various stages of a project.

Fluent NHibernate is a method of cutting down on XML mappings which are commonly associated with NH. While in the past, tools such as ActiveWriter have enabled developers to bypass the most laborious parts of creating mappings, Fluent NH takes it a step further and provides a strongly typed means of setting up your entity mappings without even touching XML. You can either use the auto mapping facility or fully customise the mappings, but it’s all done with full intellisense support and a serious lack of angle brackets.

Once the mappings have been defined – either in XML or in code – you need to start working with the database itself. NHibernate has a built-in facility which can help you out here, by generating the database schema based on your entity mappings. In fact, in the latest releases, you can even update the existing schema without having to delete and recreate, which makes it possible to preserve your test data during development. This allows you to go end-to-end from the mappings direct to the database and keep in that context throughout the development process.

Here’s a quick aside: some of the criticisms put against ORMs are that a developer with great SQL knowledge can tweak and craft code better than any autogenerating tool. From my point of view, every query would have to be written to a high standard, all column choices would have to be perfect every time. NHibernate’s generated code has been reviewed by experts and it will be produced in the same way time after time, to the same level of quality.

With that in mind, I don’t really have a problem letting NH control every part of my database interactions. What I do need is a way of ensuring that if the mappings i’ve created are wrong, or are sub-optimal, that I have a method of finding out what’s going on. NH Profiler is a piece of software which has recently started a beta phase and hooks in to NH itself to provide real-time analysis of what queries and sessions are being used by your application. As well as reporting, it also provides suggestions if it detects sub-optimal behaviour. Lazy loading, for example, can cause some nasty performance problems if used incorrectly, so NH Prof can pick up situations in which it would be better to change mappings to specify eager loading.

Over the last six months, new features and community involvement have transformed NH from a great development framework to the premier ORM for rapid development teams. The bumps have been smoothed, and we can get on with leveraging the wonderful work of the NH team.

In my last post I discussed code reuse in the form of an abstract CrudController class: a means of providing create, read, update and delete actions for a given entity. In addition, that class provided the ability to work with an entity specification class to allow for filtered reads – which is just another name for search results.

I’m going to show C# code samples for building with a class like this, but you’ll have to fill in the gaps in terms of how you can work with it. The code to list entities is the most interesting and will demonstrate the concept best, so I’m going to focus on that. To begin with, let’s assume a very simple entity:

public class Post
{
	public DateTime CreatedOn { get; set; }
	public string Headline { get; set; }
	public string Body { get; set; }
	public string Username { get; set; }
}

So, to begin with we’d like to list Posts. A typical method to do so would be:

public void List()
{
    PropertyBag["posts"] = PostRepository.FindAll();
}

But remember that in my case, I’m trying to create a reusable method of doing this, and that I’m also going to be working with ExtJS which is going to consume JSON. So, I have the following class:

public abstract class CrudController<Entity>
{
	private IRepository<Entity> _repository;

	public void GetJsonList()
	{
		RenderText(
			JsonHelper.Serialize(_repository.FindAll())
		);
	}
}

public class PostController : CrudController<Post>{}

I need a shell PostController, but most of the action is happening in the CrudController, and I’m using the magic of Windsor and generics to make it happen. By passing Post as a type parameter to CrudController, Windsor will then give me the correct IRepository<T> to work with, and from there it’s a simple matter of fetching the list of Post Entities in the same way I did in the previous code sample. I want to get a JSON string back, so I’m passing that list to a helper to serialize to JSON.

The next step is to make this listing method a bit more flexible and a bit more powerful. I want to do a couple of things – paging, sorting, and searching. Here’s my new method signature for GetListJson:

void GetListJson(int start, int limit, string sort, string dir, EntitySpecification spec)

The arguments “start” and “limit” are for paging, saying which record I should start from and how many I should return. The “sort” argument tells me the column I should sort on, and “dir” gives me the sort direction. But what about EntitySpecification? Let’s show it in context:

public abstract class CrudController<Entity, EntitySpecification> where EntitySpecification : ISpec
{
	private IRepository<Entity> _repository;

	public void GetJsonList(int start, int limit, string sort, string dir, [DataBind("spec")]EntitySpecification spec)
	{
		spec.AddOrder(sort, dir);
		spec.FindAll(_repository, start, limit);
	}
}

public class PostController : CrudController<Post, PostSpecification>{}

As you can see, EntitySpecification is databound by Monorail; incoming parameters are passed to the specification to build up a criteria for querying, as described in Ayende’s search specification post. That means that I don’t have to explicitly handle searching in my CrudController at all, because it’s all handled by the EntitySpecification. A sample PostSpecification could look like this:

public class PostSpecification : ISpec
{
	private ICriteria _criteria;
	private string _username;

	public virtual string Username
	{
		get { return _username; }
		set {
			_username = value;

			if (value == null)
				return;

			_criteria.Add(
					Expression.Eq("Username", value)
				);
		}
	}

	public void AddOrder(string sort, string dir)
	{
		var order = (dir == "ASC" ? .Order.Asc(sort) : Order.Desc(sort));

		_criteria.AddOrder(new Order());
	}
	public IList<Post> FindAll(IRepository<Post> repo, int start, int limit)
	{
		repo.Find(_criteria, start, limit);
	}
}

When Monorail runs the databinder, the Username property’s getter will be called and the private criteria will be altered. When the specification’s FindAll method is called, that criteria is passed through to filter the returned records.

Let me know if you have any improvements or suggestions, and thank you again to Ayende for the specification ideas.

I have a relationship which would normally be described as one-to-one. It’s very similar to a Employee / EmployeeDetail relationship which you see used in many examples. My issue is that I’m working with legacy data, so the “child” part of the relationship, the EmployeeDetail, probably won’t exist.

I’m struggling to understand how to map this. I know if it wasn’t for the absent EmployeeDetails, this would be one-to-one. So I’m loathe to think about this in terms of another relationship type because as soon as the EmployeeDetail is available, it will be one-to-one again.

I’d appreciate any thoughts anyone out there might have.

I’m banging my head trying to solve a particular type of querying problem. I think I can simplify it like this:

Categories have many Posts, Posts have many Comments

I’d like to search for Comments with a particular author, which is easy enough, but I’d like my result set to be made up of Blogs which contain Posts which contain Comments with author = ‘ramsay’.

The problem I’m having is that if I have 50 Comments then it’ll return 50 duplicate Blogs to contain those comments. Argh! I can add groupings (i.e. group by Blog), but that means it’ll only pull back the things I group by and not the full Blog objects.

This post specifically refers to the use of the inverse attribute on Many-To-One relationships, such as a Category with many products. That’s simply because it’s the one I’m dealing with right now, and it’s the one I’ve been using most commonly.
(more…)

I’m starting work on a new project on Monday and I’ve got full control of all development aspects. With that in mind, I’ve been thinking of how I can get to beta status as quickly as possible – finish main development and get to the stage where the client is reviewing minor details. This is dependant on a combination of my skillset and the tools I’m going to use. I’ve got:

• Existing Plastiscenic.Commons code and project structure for quick setup
• C# 3.0 to remove some boiler plate code (automatic properties for example)
• Monorail to develop with fewer LOC in the main app
• NHibernate for low friction database work
• ExtJS for rich backend
• Strong cross-technology skills to integrate the above

Are there any additions to this that you use to accelerate the development process?

I don’t find the NHibernate documentation to be that useful for anything more than the basics. When querying through an association (which you’ll probably do a lot), I found there are different ways of achieving the same result. Using a blog as an example, I can query for Posts in a particular Category like this:

DetachedCriteria criteria = DetachedCriteria.For<Post>()
	.CreateCriteria("Category")
	.Add(Expression.Eq("CategoryID", 55));

When I first started using NHibernate in earnest, I saw CreateCriteria as a way of "drilling down" into the association, in this case the Category property on a Post. After that I can then add a constraint on the Categories which will affect the Posts which are returned. This is indeed the way it works, and the above approach returns what I'd expect. However, the same result can be achieved like this:

DetachedCriteria criteria1 = DetachedCriteria.For<Post>()
	.Add(Expression.Eq("Category.CategoryID", 55));

This is perhaps more intuitive for some, though it wasn't how I approached the problem. Both methods return the same result set, but there is one important difference. When you look at the generated SQL for each approach, the first method, drilling down via CreateCriteria, includes all of the columns for the Category entity, and so in this example you're selecting more data than you need to.

Chaining with CreateCriteria allows you to return data from multiple entities/tables, but for a simple query like I've shown, it's not the correct approach.

As I mentioned previously, I am a only just beginning to get a feel for NHibernate’s querying abilities. Yesterday I had a situation where I wanted to retrieve particular records in a table based on values from another table. Here’s an example, with names changed to protect the innocent:

Category
--Has Many Posts

I wanted to select all categories where the Post date was within a certain range. Here’s what I started with:

DetachedCriteria criteria = DetachedCriteria.For(typeof (Category))
	.CreateCriteria("Posts")
	.Add(Expression.Between("PostDate", startDate, endDate));

The Category object has a collection property on it called Posts, so when I CreateCriteria(“Posts”), I am effectively telling NH I want to drill down through that property and begin filtering on the properties of Post, which is what I do with the next line.

This was pretty straightforward, once I’d thought it through. However, running the query gave me hundreds of matches instead of the few I knew I should be getting. A bit of digging showed me that this was because (in SQL terms) this wasn’t running a DISTINCT query, so I was getting a Category back for every matching Post. After a quick Google search, I found out how to replicate the DISTINCT keyword that I thought I was after:

DetachedCriteria criteria = DetachedCriteria.For(typeof (Category))
	.CreateCriteria("Posts")
	.Add(Expression.Between("PostDate", startDate, endDate))
        .SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer());

I tell NH to run a DISTINCT on the root entity, in this case Category, and hey presto, I get the number of records I was expecting. I sat down to test this out a little bit more and think about the solution in my head. It started getting a bit slow when I increased the date range, so I fired up SQL Server Profiler to see what was happening under the hood.

What I saw made lots of sense. It was pulling back all of the duplicate records I saw before I used the result transformer. It was also pulling back all of the data for the Post entity. The result transformer must work on the client side, so I was pulling back far too much information, and my test run was taking 10 seconds to complete.

There were two clear areas for optimisation here, and they were linked. I didn’t need all of the Post data, and it was that which was causing me to get duplicate rows, so I rethought my code:

DetachedCriteria postList = DetachedCriteria.For(typeof(Post))
	.SetProjection(Projections.Property("Category.CategoryUID"))
	.Add(Expression.Between("PostDate", startDate, endDate));

DetachedCriteria criteria = DetachedCriteria.For(typeof (Category))
	.Add(Subqueries.PropertyIn("CategoryUID", postList));

Firstly I create a criteria to get the posts which I need, but with one important difference: SetProjection. In this example, I am saying that I only want to retrieve the CategoryUID on the Category property of Post. In other words, despite this being a Criteria for typeof(Post), I will actually pull back a list of integers representing the CategoryUIDs which match. I am narrowing down the columns which I am returning using Projections.

Secondly, I pass those results to a subquery, which is pretty much like using IN (SELECT) in your WHERE clause in a SQL statement. Using the PropertyIn method took me a while to discover, but when I did it worked like a charm. My test run now takes zero seconds to complete, and I am a happy developer.

I’ve just ripped out a load of SqlAdapter/SqlDataReader code from the application I’m working on and replaced the whole lot with NHibernate. Much less code, with all my data access stuff in a single place – I don’t have to go to the database and set up a stored procedure or anything like that.

This is the first time I’ve used Criteria to do anything complicated and thus far I’ve been able to come up with tidy looking solutions to all of my problems. But in a couple of cases I’ve been struggling to express my query in terms of criteria, and I’ve become pretty frustrated with the documentation. What’s there is good, but woefully brief.

Google and Ayende to the rescue, and by piecing together knowledge from various blog posts, I’ve got great code. But I had to do a fair bit of trawling to get there!