Object Relational Mappers Series - More On Proprietary Query Languages

This is the next post on my series on ORMs.  This is a continuation of the last post...[[Object Relational Mappers Series - Goofy Syntax]].  

Most ORM vendors admit that their proprietary language cannot handle every type of query.  That's a start.  They'll tell you that any query that doesn't map to a business object can't be expressed in their language.  That will eliminate any query doing a calculation, aggregation, grouping, DISTINCT, UNION, etc. 

Suprisingly that will also eliminate, in most ORM tools, queries that specifically are looking for a subset of properties/columns, but not the entire business entity.  So, if you have a partitioned object (data resides in many tables) and wish, for performance, to only show a few key cols/properties from only one underlying table, it likely won't be expressable in the ORM language without mapping an entirely new entity that will be a functionally similar subset of an existing entity. 

Again, most ORM vendors will admit to these shortcomings.  Their solution is usually some form of pass-through query that will require you to write the query in SQL or the RDBMS dialect of SQL if you need something really complex.  Stored procedures are usually implemented as a type of pass-through query in these tools. 

Here's the issues I have with using a pass-through query:  

• why use the ORM's proprietary language at all if I have to resort to old-fashioned SQL when things are non-trivial?  Just learn SQL!!! 
• Even though you can use a pass-through query you are usually required to map it to a custom business object to follow the ORM paradigm.  Seems like extra work to me.  What if I want just an arbitrary set of data to display?  Like a set of log entries.  Do I really need a business object for this?  So now I'm probably going to write this in ADO.NET anyway to avoid the ORM overhead. Otherwise I have lots of small business objects that are really just copies of other business objects, save a few minor changes.  

Hibernate Query Language

I have a little bit of experience with HQL and it just does not feel right to me.  It seems too much like SQL.  It's definitely not intuitive, there is a learning curve.  If I'm going to learn a new tool/language, I don't want it to feel like the tool/language I'm replacing, otherwise, where is the return on learning?  I might as well learn something that is much more flexible than HQL and is ubiquitous (SQL).  HQL is also stored entirely in XML.  This is a supportability concern.  If I'm merging my code then XML is a hassle.  It's also frustrating when I need to do refactoring.