Object Relational Mappers Series - Entity Navigation vs Entity Query

This is the next post on my series on ORMs.  

ORM vendors state there are generally more "entity navigations" (given entity X...I want to explore X's object graph by looking at various properties) than "entity queries" (which entities occurred last year, which entities fullfill some query).  Entity navigation is similar to starting at the parent table and navigating down the child tables (given a customer, show me his orders, then show me a specific order, then show me a specific item on that order).  

Entity queries usually expose root objects that can then be explored using entity navigation.  Entity queries are sometimes difficult to express in the ORM, but entity navigation is easy.  Sometimes the ratio of navigations to queries in an application is 10 or 20 times. 

The problem, in my opinion, is that ORMs handle entity queries in a kludgey fashion.  All will allow some type of pass-through query...but most will *want* you to use their own specific query language.  I covered this in my previous post on ORMs...[[Object Relational Mappers Series - Goofy Syntax]].  The vendors will tell you their query language is powerful, business-object aware, easy to use, strongly typed, portable, etc.  Bottom line is that it still needs to generate SQL under the covers so the RDBMS can handle the request.  So, do you want to learn something proprietary or do you want to learn industry standard SQL?  Furthermore these entity queries tend to perform poorly because the proprietary language can never be as performant and robust as SQL.  Just remember, if their language was so much better than SQL (or vendor specified stored procedures that are optimized to the hilt) than THEY would be the de facto industry standard and NOT Structured Query Language. 

Let's revisit stored procedures for a second.  Again, ORM tools want to steer you away from them...mentioned earlier in a previous post.  Navigation queries are another reason why.  The ORM can map the tables, and hence entities, to each other based on the foreign keys declared or based on your custom mapping.  This is much, much more difficult with a stored procedure.  The ORM just won't know how to relate the stored procedures, nor how to call them with the correct parameters.