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.
- Database Diagram
- Class Diagram
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 [Level] (
[Id] int IDENTITY not null,
[Name] varchar(250) not null,
[Factor] int not null,
primary key ([Id])
)
create table [Model] (
[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:
public class Factor : List<Level>
{
public virtual int Id { get; protected set; } }
public virtual string Name { get; set; } }
public Factor() : base()
{
Id = 0;
Name = null;
}
}
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:
[Serializable]
public class Factor
{
public virtual int Id { get; protected set; } }
public virtual string Name { get; set; } }
public virtual IList<Level> Levels { get; protected set; }
public Factor()
{
Id = 0;
Name = null;
Levels = new List<Level>();
}
}
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:
[Serializable]
public class Model
{
public virtual int Id { get; protected set; } }
public virtual string Name { get; set; } }
public virtual IList<Factor> Factors { get; protected set; }
public Model()
{
Id = 0;
Name = null;
}
}
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.
First issue, you want to implement IEnumerable, not List
Second issue, you need a set of many to many.
You can ask a specific question in teh mailing list
First,
“foreach(Level lev in aFactorVariable){...}” You may still do this, implementing IEnumerable on you Factor.
Second,
You seem to have unclear/unexpressed concepts. You have a pool of Factors with levels as your basis. And you have instances of those for an experiment/model. Currently you seem to mix these to. I would go and decouple those: Have Factor->Levels and Have Model->Specific Factors->Specific Levels (as copies).
There may be possibilities, to map a Factor inbetween Model and assigned Levels (through embedded components, maps, ...). I suggest reading the NH / Hibernate.Java Doc+Samples on these topics. However, this is going to be a complex mapping and still, you ´would have concepts you should make more explicit (in the sense of DDD).
-Sebastian
IEnumerable is, of course, the right answer to the List thing, I’m not sure why I didn’t think of that …
I’m digging around the Java docs now, they seem more complete than the .Net ones …
This is why ORM is more hype than anything – you end up working for the tool – you’ve already changed your object model – now they want you to change from one to many to a many to many…see a problem here?
When you start trading design for cohesion with a tool you are in essence trying to fit the proverbial square peg in a round hole. Your instincts told you stay clear (you decided to bite the bullet) – listen to your gut.
You should check out this article – the first person who responded to it (Brian Towers) sums up ORM very elegantly.
http://www.simple-talk.com/community/blogs/tony_davis/archive/2008/08/06/66526.aspx
At the end of the day, I’ve fallen back on writing a custom data access layer with LINQ to SQL in order to finish the project.
I could do the same thing with NHibernate, but I’m not going to introduce a new non-Microsoft dependency to a project someone else will most likely end up maintaining if it doesn’t gain me anything, that’s like asking someone to learn calculus before I let them balance my checkbook — if learning calculus meant I didn’t have to balance the checkbook by hand, it might be worth it…
I’m not content with the end result because the business objects aren’t POCO (Plain Old CLR Objects) now (I could make POCO objects, but taking the persistence out means I loose lazy loading).
Also, I had to leave a few
EntitySetcollection properties exposed, because LINQ goes into a tail spin during my unit-tests if I try to actually claimIEnumerableon theFactorobjects … I had never seen theOperation could destabilize the runtimeerror before!