Summary
This blog post is overdue, but I’d like to talk about my database experiences with and without ORMs.
Before ORMs
I have been writing database driven software for a long time. My early days include the use of Borland C++ with Paradox and FoxPro databases. I have also written Borland C++ and PHP code with Oracle 8i. I’ve also written C++, then C# to access MS SQL Server. I’ve used ODBC and ADO to access database servers. They all have their pros and cons, but they are all the same when it gets down to the actual coding. The basic coding includes opening and closing a database connection, performing a select or execute query and dealing with the results usually with some sort of reader that only goes forward. PHP had the ability to select into an array and I have used the Enterprise library with C# to select into a DataSet (which can be treated as an array). These are convenient because you can go forward and backwards through the result set, but they suffer from the fact that all records must be read before you can access the data.
There are two big headaches with non-ORM data access. First the query is a string that is passed to the database. One of the easiest placed to get a syntax bug. The second headache is the return set, which also uses a string to name the field name used (unless you use the field number relying on ordering…OMG). This is where the promise of ORMs caught my attention.
ORMs
Object Relational Models looked so cool when they first came out. The LINQ language didn’t match SQL, but it had built-in syntax checking. Of course, you can get a query syntactically correct and still get the wrong results, but at least you didn’t misspell “SELECT” or “INSERT” or something trivial like that (I once debugged a program for an hour only to discover that I had spelled “UPDATE” as “UDPATE”). The really exciting part of the ORM to me was the fact that the result set came back as an object or list of objects. That makes things really easy on the result end of the query. With context sensitive programming tools (i.e. Visual Studio), hitting the dot causes a list of the fields to pop-up and voila! You don’t need to memorize the exact spelling of each field.
My first experience with ORMs was Linq-to-SQL. This ORM suffers from the fact that you can’t easily regenerate the tables when you add a field in the database. I switched to Entity Framework because of this headache. Entity Framework turned out to have some serious performance issues not to mention the massive XML file that stores the data configuration that contains unique keys that are renumbered every time the edmx file is regenerated. That causes big problems with version control systems.
So I moved on to NHibernate, specifically Fluent NHibernate. I wasn’t too crazy about NHibernate because of the XML files. Too much work! The Fluent add-on makes it nice because all the mappings are in C#. I did a lot of research up-front and decided that NHibernate was a winner. After using this product for the past 8 or 9 months, I have to admit it has it’s own set of flaws. There is a lot of Linq support that is missing from NHibernate such as outer joins. Some of the field mappings with SQL server has quirks like the 4000 character max problem that crops up everywhere. Fortunately there are work arounds for this stuff, but there shouldn’t be such glaring problems.
The next issue with ORMs is that I’m not convinced that I have gained any productivity from the use of an ORM (any ORM). It seems that I spend a ton of time trying to translate the SQL that I want to write (which I’ve done for so many years that I can do complex queries in my head), into Linq. You also have to be careful about the way you write your Linq code, because ultimately there is a lexer that converts the Linq code into SQL that is transmitted back to the server. This may end up as really bad (read: inefficient) code. Before I used ORMs I would workout my complex queries inside Microsoft SQL Server Management Studio and then just copy the text into my code. Can’t do that with Linq, because it’s not the same syntax.
Conclusion
Am I willing to give up on ORMs and go back to ADO? Not really. I like the object list result sets too much to throw in the towel. I’m also smart enough to know how to profile and test queries that are generated by an ORM. Finally, I can revert to sending a raw query to SQL and get an object list back, making NHibnernate behave like a micro-ORM (until NHibernate or another ORM comes by and fixes the issues). I think Entity Framework could be THE killer product if Microsoft focused on it’s two major flaws: Performance and the massive edmx file that is used. The Entity Framework development team blog indicates to me that they are oblivious to these weaknesses or that these are low on their priority list. Microsoft suffers from a priority scheme involving features first, performance and reliability last.
OK, I’m going to stop ranting now!