Objects, Tables and ORMs – part deux
Interesting times!
In this short series I want to examine exactly what I need out of a DAL and how I plan to approach solving this problem in a somewhat different way that Steve, Mark or Doug have taken . . .
In particular, one pointed out that you can't escape the complexity. I've programmed basic SQL for years without ever understanding how queries are executed and how to optimize them, but you'll be lucky to use an ORM for a week without getting into trouble unless you understand how the ORM operates and the implications of your choices in terms of the queries generated. This goes even for low traffic sites. It only takes a few users calling a page where you have an n+1 query problem with 500 records to completely ruin your day!
It includes posts on the downside of ORM (along with a good rebuttal and an example).
There is also a good post giving a sense of how much work it is to create a comprehensive ORM a la Hibernate (or in this case nHibernate), but I think Doug and Mark have shown that a modest subset of Hibernates functionality can still have value.
Also an interesting look at how you handle n+1 query problems in nHibernate.
If I want to order by FirstName, all I need to do is pick up the table name and alias. In the database the FirstName attribute might be persisted as tbl_AdminUser.FirstName or tbl_User.Horribly_long_and_bizarre_Name_for_firstname_here. To do this I need to replace |space|Attribute_Name|space| with |space|Attribute_TableSQLName.Attribute_FieldName |space|. That is pretty straightforward with just the minor inconvenience that operators such as LIKE become reserved words so you can’t have an attribute name called like. Annoying, but acceptable to me.