Failure is inevitable

There are manyposts on the web about how easy it is to get started with Microsoft’s Entity Framework and SQL Server Compact Edition (SQL CE). This combination seems to be all the rage thanks to EF’s new “Code First” approach introduced in version 4.1. While I am impressed with the latest version of Entity Framework, I still prefer NHibernate for a lot of reasons, and it’s actually just as easy (maybe even easier) to get up and running with NHibernate+SQL CE as Entity Framework+SQL CE. In this post, I’ll walk you through the simple steps to get up and running.

Making Sure You’re Up To Date – Web Platform Installer to the Rescue!

There have been numerous updates related to Visual Studio 2010 flying around over the last couple of months, and you’ll need a few of them in order to perform the steps described in this article. The best way to grab these is to use the Web Platform Installer (WPI). This great little tool is a bit like NuGet, but it works on applications and tools instead of individual packages. It’s much, much easier than trying to wade through all the dependencies by hand!

Using the Web Platform Installer, find and install the following:

Visual Studio 2010 SP1

Microsoft SQL Server Compact 4.0

Microsoft SQL Server Compact 4.0 Tools

Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0

If you’re missing any other dependencies, WPI should locate and install them for you, as you can see here:

Required References

Now that you have the necessary tooling installed, it’s time to create a project. These steps will work equally well with both a web application or a desktop application. Create whatever project type you want, and use NuGet to add references to the following packages:

Project Setup

Your project now has the required dependencies. You just need to add a few more things to the mix. First, add a SQL CE 4.0 file to your project.

If you are creating an ASP.NET application, add an App_Data folder, and add the database file there. If you are making a WinForms app, you can add the database file wherever you want, just be sure to set it to copy to your output directory. Go ahead and add a SQL CE connection string to your application’s config file (either an app.config or web.config file depending on the project type):

Next, you need to decide where you will put your database entities. For simplicity, it’s best if these can all live in the same namespace within your project, but they certainly don’t have to. I typically organize my projects similar to this:

Configuring Fluent NHibernate

All that remains now is to configure Fluent NHibernate so that it knows where your domain entities live and how to map them to relational entities. We’ll leverage Fluent NHibernate’s auto mapping capabilities to achieve this. Derive a new class from DefaultAutomappingConfiguration, and override the base conventions with your own. Here’s the setup I typically use:

One thing to note: if you decide to use Integers and the standard identity generator for the primary key properties on your domain entities, there is a known bug related to SQL Server CE. You can find out more at this question on Stack Overflow.

You will also need a bootstrapper of some sort to configure Fluent NHibernate at runtime. You need to tell it what database to use, what conventions to apply, etc. I almost always use a bootstrapper that is some variation of this:

There are a few additional things worth noting in my bootstrapper. First, it’s capable of both generating the initial schema for a new database as well as applying updates to an existing schema. NHibernate is capable of doing these things fairly well, though certainly not perfectly. I’ve found that it does well enough for the initial phase of application development though. Note that you don’t have to do anything else, like tell NHibernate about what to actually persist, how to set up relationships, etc. Fluent NHibernate’s automapping figures all that out for you!

Next, notice that the bootstrapper automagically loads additional conventions and overrides from the assembly containing the domain entities. If you want to keep those conventions and overrides elsewhere, you may do so easily by modifying the bootstrapper.

Finally, I opted for changing the connection.release_mode property to work around the bug with integer identifiers mentioned above. You won’t need to do this if you’re using Guid identifiers or another identifier generation strategy.

Wrapping Up

Congratulations, you now have all the plumbing in place. Just make sure you call your bootstrapper once at application startup, and then call the bootstrapper’s GetSession method whenever you need a session. During early development, I also call the bootstrapper’s UpdateSchema method once at application startup as well, that way I can make changes to my domain without worrying about managing updates to the underlying database schema.

Here’s what my typical Global.asax file looks like using this bootstrapper:

And there you have it: NHibernate with SQL Server CE is every bit as easy as using Entity Framework “Code First” with SQL Server CE, plus it’s more flexible to boot. You don’t have to manually specify what needs to be persisted or how, it’s all inferred for you automagically.

Can It Be Any Easier?

Yes, actually, it can. There’s a lot of boilerplate stuff going on here. That’s the sort of thing that’s ripe for becoming a NuGet package. I’m actually working on doing just that, but if someone would like to beat me to it, feel free.

About Matt Honeycutt...

Matt Honeycutt is a software architect specializing in ASP.NET web applications, particularly ASP.NET MVC. He has over a decade of experience in building (and testing!) web applications.
He’s an avid practitioner of Test-Driven Development, creating both the SpecsFor and SpecsFor.Mvc frameworks.

He's also an author for Pluralsight,
where he publishes courses on everything from web applications to testing!