Huddled Masses

I recently decided to bite the bullet and try using code-generating ORM in my projects instead of manually writing my own data access layer (DAL) with custom SQL and everything … and felt that after careful research into the current state of Linq-to-SQL and ADO Entities, etc. I should use NHibernate …

Everything went ok at first, my first “pretend” project worked, and I feel like my design is definitely more flexible, and using ORM allows me to be more agile with regards to changing customer requirements, because I have less work to do to adjust the data-access layer of my app. However, I’m now trying to apply this to my first real-world problem, one which I consider actually rather trivial, and which I have in fact written a custom DAL for this in the past, but I cannot figure out if it’s even possible to do this in NHibernate.

I’m hoping someone can help me figure out the mappings for this, but failing that, I’d accept suggestions for a alternate design for the database that will make it work for NHibernate. Let me show you my current design and explain it a bit.

First, a basic overview of the application. This is a statistics app, and uses the generic terms of Model, Factor and Level to talk about experiments. For the sake of this example, lets talk about experiments on photocopiers . A Factor is a variable, in the sense of photocopiers, something like paper size or color. Each Factor has a set of Levels which represent the possible values for it — to use our previous example, sizes like ’8.5 × 11” Letter’ and ‘Tabloid’ or colors like “White” and “Ivory.”

The most important thing to understand is that in our application, all of the Models in a specific instance of the database are presumed to be experiments of the same basic type, so all of the Factors could apply to any experiment model. However, in a given experiment, not all of them will be used, and of those which are used, not all of the possible Levels will be used.

As you can see, the database design is fairly simple, there is one table for each basic type of data, and a fourth table which essentially stores the specific makeup of a given Model. The idea is that when you create an experiment Model, you choose a few Factors and for each of those you choose a few Levels. We simply store which levels you’re interested in for this experiment, since we can trivially determine the Factors based on that. For the sake of completeness, here’s some pesudo-code of the database setup script (leaving out constraints and such, for the sake of clarity).


create table [Factor] ( [Id] int IDENTITY not null, [Name] varchar(250) not null, primary key ([Id])
)

create table [ModelLevels] ( [Model] int not null, [Level] int not null
)


The C# classes are equally simple, but remarkably different. In an object-oriented design we model collections, rather than belonging. So while the database stores which Factor a Level belongs to, in the application we want to represent which Levels are in a Factor.

First problem

The firs major problem is that what I would like is to be able to represent the “Factor” object as a derived type of List<Level>, like this:

This would allow me to iterate the levels as foreach(Level lev in aFactorVariable){...}, and other such niceties of grammar. But NHibernate doesn’t seem to have any idea how to deal with this, so I’ve resigned myself to representing the levels as an IList<Level> member of the Factor class, like this:

Major Problem

This brings us to our second problem. I can’t find any way to explain to NHibernate the relationship between a Model and a Factor. I need my Model to be represented something like this:

However, I can’t find a way to explain to NHibernate how to determine which Factors should load into the Model (based on the ModelContents table which maps Levels). I could create a view or a stored procedure in the database which shows the Model-to-Factors mapping, but I can’t see how to make NHibernate handle the updates by adding the Levels to this ModelContents table. I could hypothetically use SQL Server’s updatable views and INSTEAD OF triggers, but I’m not honestly sure it would work, and I don’t particularly like that it would lock me into SQL Server.

If someone can riddle me that one, I have one other issue: that is, how do I limit the Factor object that is based on a specific Model so that it only has Levels in it which belong to that specific model (that is, that are present in the ModelContents table for this model)? Of course, the idea is for the user interface to present to the user an “Add Level” dialog which shows them all the Levels which exist for that Factor, and allows adding them to the model (as well as adding new Levels), but lets tackle one problem at a time, shall we?

Begging for help

I’m going to post this question (and this url) to the various Alt.Net and NHibernate discussion groups, and I’ll post back here any answers I get which seem to lead in the right direction …

I can post my current NHibernate mappings if anyone wants to see them, but there’s nothing special about them, and they certainly don’t work correctly as I’ve described above.

5 Responses to “A cry for help with NHibernate …”