NHibernate on legacy DB.

Question

I'm ashamed to say it, but I have to. I have not worked with ORM's. I'm really considering NHibernate as it seems to be the most mature product for .Net out there (please correct me if I'm wrong). Now, the thing is that we have a big e-commerce/booking system with an SqlServer as the main integration point, containing quite a lot of business logic in sprocs. Now - obviously - this architecture is something we want to move away from, and we've been doing so for some time, piece by piece. So, my real question is how feasible is it to start using NHibernate for new features and not going back and map all the legacy stuff? Is this kind of gradual introduction of and ORM supported, and if so, would you recommend it?

Answer 1

Why are the stored procs "obviously" bad? Is the business logic in the stored procs causing pain?

Microsoft appears to have a number of persistence technologies available (e.g., LINQ). I don't know how to assess them versus NHibernate, but I wouldn't recommend ORM unless you have really good objects to map to.

Answer 2

It will depend on how "NHibernate friendly" your current database is. NHibernate likes single column primary keys (if they are all named "id" even better). Most ORM are not very stored proc friendly as far as I know. Did you think about how you are going to define your transfer objects to get data back and forth from the db?

BTW there is nothing to be ashamed of. ORM's are a goot thing but you are right in trying to find the best approach.

Answer 3

I haven't personally done this, but you can copy-paste the stored procedures in named queries in your mapping files, and remove them one by one later on.

If you don't have any experience with ORMs, it might probably hard to start learning it in a large legacy project, because you have to discover all the non-standard features of the ORM that you would not use in greenfield projects, or use only once a year. NHibernate has a lot of options to support legacy databases.

When you are prepaired to cross the learning curve and brave enough to start using a new technology you can start with NHibernate, but if you like to learn new technologies step-by-step I would wait with NHibernate until you start a new greenfield project.

Answer 4

Any decent ORM tool should support gradual integration. The trick here is to have a clear separation at the DAO level (Data Access Object layer). If you can hide your data access quirks by means of a well-defined API, it shouldn't matter if you are using an ORM or not.

Having that said, the complexity of the ORM introduction is directly related to the complexity of your table models. You can have many different mappings for the same tables, just make sure that you do some trials beforehand in order to know the quirks of an ORM solution like having an object cache, write-behind queries, proxied access and such. The real benefit of an ORM solution is using objects instead of dummy POJO classes. This makes doing changes to the business much more easy than using sprocs or flat objects.

As a side note, I worked on a project where we migrated a whole app in sprocs to java. Although we didn't use a sproc, the real pain was that the sprocs architecture was not well defined, lots of sprocs calling other sprocs made it very hard to migrate just some sprocs at a time.

Answer 5

There is nothing within NHibernate that will restrict you from doing this. At some point you will probably want to convert your legacy code to also use NHibernate as you won't see the full benefits of an ORM until you can navigate the relationships between all of your objects.

The biggest challenge with this may be getting your legacy table structures to fit into NHibernate's single table per class methodology. There are ways around this, but it can be tricky. Still, I think it is worthwhile.

Answer 6

If you are using legacy databases, you will find ORM tools rather difficult to use, configure, maintain and optimize. We ran into the many issues when we used NHibernate to map our Domain Model to an existing database. Some of them were:

1. The Model objects were difficult to map to existing tables (we had over a 100 tables), some of NHibernate's requirements were quite obtrusive like every table must have an ID field to be able to be mapped into a Domain object. Also, mapping many to many relationships was quite difficult to grasp and use.

2. Maintaining the large volume of mapping XML required to map to legacy database became a full time job for a developer and was quite challenging, specially in a team of 10+ developers.

3. Our queries degraded in performance as complexity grew since the Data model and Object model did not always match the busineess and needed to be constantly tuned. A large amount of data aggregation code had to be written. For example, if we needed to show a grid which joined multiple tables, we used to have to load multiple Domain objects put them together and show them in one grid. That code was hard to maintain and debug.

4. Also, sometimes we had to run anonymous queries, i.e. where we just wanted a few properties from some objects and not the whole objects. That was really difficult to write, maintain and even implement and was going against the ORM paradigm, but we had no choice but to do so.

5. Another problem we ran into was that the Database was constantly changing as the DBAs would refactor the tables and that would always break our Domain objects. It was an excercise to keep the Models and Tables in synch and also make sure the App still worked.

Long story short ... What we learned that if there was a way to map the SQL that business needed straight to our UI Model or Domain objects without having to worry about configuration, it would be a better solution.

After going through this experience we developed the Orasis Mapping Studio. It is a mapping tool specifically designed to work against legacy databases and also existing .NET Model / Domain objects. It takes your SQL queries and allows you to map them to your existing .NET objects by showing the metadata of the Query and the Object graphically and using drag and drop to create mappings between Object properties and Query columns. The tool automatically generates all the ADO.NET code you would need to perform your mapping to read your objects. You can then use the generated code in your DAL Layer or use the generated Assembly to retrieve and persist your data.

You can try it here: Orasis Mapping Studio. It is a tool that we believe developers really need, specially for working with legacy databases and where performance is a key requirement. It is written by developers for developers so it handles some of the intricate details like Obejct inheritance, nested objects, data type conversions, etc. Good Luck!

Answer 7

You had mentioned you are moving away from your current architecture, but are you required to stick with your legacy database design (e.g. a DBA is strictly responsible for designing and maintaining it) or is your current database design being refactored (with consideration for an O/R mapper) as well?

If it's difficult to move away from your current legacy design, you could probably consider looking into something else like iBATIS SqlMap to O/R map entities to your legacy database using plain old T-SQL queries, rather than the less-flexible Hibernate-style XML/attributes to map data.

Depending on how you want to approach the design, you could use iBATIS SqlMap and NHibernate transparently side-by-side, by making use of your own abstract DAO factory, or the iBATIS DataAccess API (which does essentially that, and is still compatible with NHibernate.)

Answer 8

I agree with what Ahmad said, I've faced similar problems in the past. Most of the trouble I had come not from technology itself, but from its adoption by developers, changing to NHibernate implies changes in working habits, to say the least, and this is not always easy.

If you are new to ORM it’s probably better to start a brand new project with it than try to convert a legacy project.

My advice to convert a legacy project to ORM would be to try to use some sort of generating tool like LLBGen or .netTiers or even Linq2SQL. This way you could get a bit more comfortable with ORM without the traumatic rewrite that NHibernate could impose.