At my previous company we created a “data access layer” above all data access layers. One guy mainly built and maintained our templates for generating code. Another guy build complicated views for retrieving and updating data which applied business logic, etc. The database became a repository for metadata about the system. From then on, the data access layer took over and produced marvels.
It was quite an amazing system, but like any amazing system it had its down-sides. You really needed to be an expert to work with it, logic was many layers deep and it needed to load a lot of metadata before the system would start to work. Basically there was a lot of overhead to get stuff done.
When I started at my current job I’d realised the benefits of automating data access, but foolishly tried to build my own data access layer. For code to stand between the developer and a database, there’s a lot it must do before it starts to become an integral part of the system. Unfortunately writing one from scratch, and writing it well is just too much trouble for most people (myself included). The requirements are many, but most notably it needs to do two things:
- It needs to make the developers life easier
- It needs to impose standards
Unfortunately those often end up at odds, and even worse often rule one is lost completely. Slightly more forgivable, rule two often is just really hard to maintain because each organisation has different standards so having one system that works everywhere is most likely unachievable.
So what’s a developer to do? Recently we decided to revisit our chosen tool for generating our data access layer. We did some research, gathered the names of the most “popular” ORM tools and set about testing each one. We have a fairly large legacy of code that we need to work with, so there’s a lot of customisation we need to make it work well and have the code look reasonably neat/legible.
We looked at Subsonic, NHibernate, Entity Framework, Linq to SQL, LLBLGen Pro, NetTiers, PLinqo and a few others. It boiled down to cost vs customisability vs time and effort to setup. Cost wise we were down to Subsonic, NHibernate, Entity Framework, Linq to SQL, Plinqo and NetTiers. We dropped NetTiers because it hasn’t been updated in about a year, and dropped Plinqo because it was basically just a wrapper around Linq to SQL (plus we’d need to buy more licenses of CodeSmith). So we were down to Subsonic, NHibernate, EF, and Linq to SQL.
The first framework we tried was Subsonic, took 5 minutes to setup, a quick tutorial video and we were off (I read some documentation too). Within 30 minutes I’d generated a data access layer, and within an hour I was customising the templates to do little things I wanted them to do. It was simple, quick and efficient.
NHibernate was an eye opener. Many config files, odd documentation, no easy “do x, y and z and you’ll have a DAL”. I resorted to using a copy of CodeSmith with some NHibernate templates to generate the classes and config files for me and even then it was just too complicated. I know it can do miracles, but we just need something simple and this much config is just too much trouble.
EF was easier than NHibernate, but still had its downfalls. It offers the world, which means it has extra complications, models, etc. There wasn’t an easy way to simply add a table to the database and have it generate the code for us in the custom ways we want it to. The only real benefit it gave us was complicated ORM functionality that we didn’t need.
Linq to SQL is great, but again not much customisation, and really Subsonic and other frameworks provide better features.
So in the end it our decision was clear … Subsonic was the easiest. Even before we’d made the final decision we had some of our developers asking “can we use Subsonic in project X?”
The only down side so far to Subsonic is that submitting changes to their code/templates requires you to use Git, which so far is proving to be a real pain in the ass. I guess you can’t have everything you want in life, but at least I have a nice ORM now.
