After reviewing all the major ORM technologies out there and participating in a project that was a classic ORM nightmare, I have come to the following conclusion: ORM is a good acronym but not a good concept. But, I must give credit where credit is due; ORMs have moved us forward in thinking of Objects and making our applications more Object Oriented. Gone are the days when binding a grid to a DataTable or some other data structure directly from the data provider was considered a viable solution.

Here is the bad part about ORMs, and we must learn from the negatives to come up with a positive solution. They couple or glue your Objects to your Database Schema in some form, directly or indirectly. How about if we mapped SQL Results to Objects instead of mapping relational schemas to Objects? One couples your Relational Schema to your Objects while the other maps to SQL which is the natural language of any Relational Database. In this way the abstraction is at a higher level and not at the database's schema level.

The reason why ORMs are problematic is that they introduce their own query languages that query your Objects instead of tables. They say they hide the "complexity" of SQL, but in reality it's the only way you can get objects out of an ORM. It's a simple fact that Objects are not designed to be queried. They are not inherently relational structures, they are hierarchical. Database Tables, views, etc are all relational tuples designed for storage and queries. So which one would you query against?

Aside from the query, ORMs let you map data directly into objects without having to write mapping code. That is a great convenience, I admit. In the process they take care of type conversions, null checks, nullable types, data lengths, etc. all which are quite painful to write for developers. Now remember, that you do take a performance hit because ORMs use reflection to do this work for you which is slower than native object instantiation and population, but for most cases it is negligible.

So the lesson learnt is that if SQL queries were brought back into the picture and somehow the mapping of results, and parameters from the Query to the Objects was automated and managed and the Objects were allowed to be decoupled from the Database that would be a better solution than an ORM.

The only problem is that you would need a tool to manage all this work and possibly generate the code for mapping each query to an Object.

So why not try a SQL to Object mapping solution? It seems, at least to me, to be a more viable solution than using an ORM solution. I'm sure many of you may have already tried this. Let me know your degree of success if you did.

You can also try the Orasis Mapping Studio which was built from the ground up to do SQL based O/R Mapping at http://www.orasissoftware.com/download.aspx