What is so great about ORM?

Question

So I'm having a head against the wall moment and hoping somebody can come help either remove the wall or stop my head from moving!!

Over the last 3/4 weeks I've been investigating ORM's in readyness for a new project. The ORM must map to an existing, large and ageing SQL database.

So I tried Subsonic. I really liked v2 and v3 after modding to work nicely with VB and named schemas in SQL was running OK. However, its lack of flexibility of having separate entity properties names vs column names had me pulling my hair out (sorry Rob).

I tried Entity Framework but I found like others it lacking in certain areas.

So I bit the bullet and tried nHibernate but after a week or so getting it working how I liked (with help from Codesmith to generate classes/hbms for me) I'm frustrated with the time it takes to startup (build a config object), despite trying a number of tricks to reduce this time.

I'm essentially after building a DAL class that I can share between apps and websites. Am I barking up the wrong tree? For a legacy project with 100s of tables should I go back to ado.net and use DTOs? Aarrgh!

Sorry for the ranty style of question. I don't have much hair left and I'd like to keep what I have!!

Thanks in advance, Ed

PS. I should add that I know SQL very well and not scared of getting my hands dirty to write fast queries. If anything I don't need to be hid from SQL

Answer 1

ORM let's you:

1. To map table rows to objects, that are the the workable pieces of object oriented programming.
2. To automatically navigate through object relationships
3. To easily add, edit and remove table rows
4. To query the database in a more intuitive way as you don't have to think of joins (this one will depend on the ORM and the query method)
5. To transparently handle L1 and L2 cache.

All of the above would have to be handled by hand if you werent using ORM.

PS: I agree to Dmitry as to the startup time of NHibernate (see question comments). Besides, did you try Fluent NHibernate? Fluent NHibernate is impressively easy. I couldn't believe my eyes when I first mapped a database. It's even easier than proprietary ORMs like DevExpress XPO.

Answer 2

The biggest benefit of an ORM tool is that it will help you layer your application correctly. Most project nowadays use a Data Layer to connect to the database. You start from the ORM tool to produce classes that correspond to your database objects. Then you define an interface using these methods. All persistence code uses the methods of this interface. This way the business logic layer is only coupled to this higher-layer interface and needs to know nothing about the database. In fact there should be no dependency on ADO.NET or even NHibernate.

Another advantage of ORM tools is that you de-couple your application from the database server. You could change the db engine and still use the same code. Also there isn't only the complexity of the SQL that the ORM hides from you. It can also help you with transactions logic and connection pooling.

I'd say that for new projects an ORM tool is a necessity. For legacy projects it isn't so much beneficial, unless of course you have the time/money to start from scratch.