I’ve recently been working on a project where it was a client’s requirement that we use nHibernate as the data access layer. Although I wasn’t keen at first, I actually quite like the control you have over the domain model. It also worked extremely well with WCF contracts, simply by adding DataMember decoration to the properties.
Some of the queries however were a little tricky once I had the datamodel set up. Let’s take the following example. A car rental company gets some new cars delivered. These are all registered in the vehicle table in the database. Customers are also registered when they rent a vehicle, and an entry in the join table for CustomerVehicle is created. nHibernate handles all this for us using the XML mapping files that are marked as embedded resources for the project. We therefore have the following schema…
So far everything looks good. The object mapping creates a Customer which has a Vehicles property which can contain one or more vehicles that the customer has rented. We can get a customer using the following…
var customers = s.CreateCriteria()
And for example, the first vehicle make…
string firstRentalMake = customers.Vehicles.Make;
The trickiness comes when we now want to find out which vehicles haven’t ever been rented. We haven’t got our objects mapped in a way that would make this easy. After a fair bit of googling, I found I had two options.
Firstly, I could create a new set of objects that could map from the vehicles point of view. Each vehicle would have a customers property that would then allow me to query for vehicles where the number of customers is zero.
Secondly, I could map the join table (so that nHibernate knows how the join table is represented in the database) and use the following query…
DetachedCriteria rentedVehicleList = DetachedCriteria.For(typeof(CustomerVehicle)).SetProjection(Projections.Property("VehicleId"));
DetachedCriteria vehicles = DetachedCriteria.For(typeof(Vehicle)).Add(Subqueries.PropertyNotIn("Id", rentedVehicleList));
vehicleList = vehicles.GetExecutableCriteria(session).List<Vehicle>();
This effectively runs a query that gets all vehicles where the vehicleId is not in the join table.
I’m still getting to grips with the ins and outs of nHibernate, so if anyone has any alternatives to this problem I’d be glad to hear them.