Object Relational Mappers Series - Goofy Syntax

This is the next post on my series on ORMs.  ORM vendors state that our developers should be focused on solving business problems, not on data access issues.  I agree.  So, instead of learning SQL, we should learn the ORM vendor's language? That seems insane to me.  Is the ORM language vendor-neutral?  Is there an ANSI standard?  Is OQL the compatible with HQL?  Nope.  But forgetting those issues, let's look at how we need to express very basic queries in Structured Query Language versus just about every ORM language I've ever seen.  

SQL is an algebraic language and ORMs I've worked with are object-oriented.  This means that what is easy to express in relational algrebra can actually be MORE difficult (or at least less intuitive) in an ORM. 

Example:  I need all rows where ColA and ColB are True.  The SQL will look something like

...WHERE ColA = 'True' AND ColB = 'True'

Now the ORM (this is just pseudo-code...each implementation is different...but most follow this pattern): 

query.AddClause (TableA.ColA, query.QueryOp.Equals, "True")
query.AddClause(TableA.ColB, query.QueryOp.Equals, "True")
query.AddOperator (query.QueryOp.EntityBooleanOp.And)
return query

You're probably thinking that syntax is a little weird, but it's still maintainable.  It isn't algebra, like SQL, it is closer to Reverse Polish Notation.  I'm not making this up...RPN it is a language where the operator follows the list of operands.  It is a postfix notation that is actually easy to use...but it requires learning for most people...even those exposed to it in high school. It is counter-intuitive for most people.  Algebraic SQL is not.  

Let's assume some tricky examples that may throw you off unless you truly understand RPN (or are an ORM zealot).  Example A...I want to see every row with a colA value not equal to "123" or any row with a colB value of True.  Example B...I want to see every row with a colA value of 123 and not having a col B value of 123. 

Tell me if SQL or reverse polish notation is easier to understand? 

 

 
Example A
Example B
Question
every row with colA <> '123' or any row with a colB = TRUE
every row with a colA value of 123 and not having a colB value of 123
Algebraic equivalent
(not A...) or B
A and (not B)
RPN equivalent
A not B or
AB not
SQL
WHERE colA <> '123' or colB = 'TRUE'
colA = '123' and NOT colB = '123'
ORM
query.AddClause (ColA, query.QueryOp.NotEquals, "123")
query.AddOperator (query.QueryOp.AddOperator.Not)
query.AddClause(TableA.ColB, query.QueryOp.Equals, "True")
query.AddOperator (query.QueryOp.EntityBooleanOp.Or)
return query
query.AddClause (TableA.ColA, query.QueryOp.Equals, "123")
query.AddClause(TableA.ColB, query.QueryOp.NotEquals, "123")
query.AddOperator (query.QueryOp.EntityBooleanOp.Not)
return query

This is starting to get a little more complicated and order of operations is becoming an issue.  Seems like a lot of thought being expended to answer simple questions.  Now what happens when we introduce nulls?  SQL is 3 valued logic but is not always implemented intuitively to start with...how will the ORM tool handle it?  Seems like a lot of extra testing to me. 

This is a debugging nightmare.  

CONTENT
data architecture orm

Archive