I hate piggybacking on blog responses, but occasionally the topics are just so timely that it's hard not to. If you haven't checked out Jeff Atwood's latest post and Rob's response to it, I recommend taking a look. Like Rob, I take issue with the characterization of the query engine as being wrapped in "proprietary SubSonic object noise." However, given that I am currently rewriting the query engine with a focus on righting wrongs, I am interested in any feedback on the existing model, particularly its shortcomings.
I have come to the conclusion that there is no "correct" way to structure the query syntax. In undertaking this rewrite, I attempted to identify some sort of common way of a "diagramming" SQL statements in they way students do with sentences (or would if grammar was still part of our primary school curriculum and they weren't spending all their time doing standardized test prep. No child left behind!). For simple examples, this is possible, at least to a limited degree. Take following example from the Wikipedia SQL entry:
Unfortunately, this is not terribly meaningful, and really doesn't provide much of foundation for any kind of logical class structure, let alone an intuitive syntax and grammar, even if you don't support JOINs or predicate grouping.
One of the mistakes we made with the first version of the query engine was trying too hard to hard to "port" SQL into SubSonic, to the point of including our much-maligned uppercase clause methods. (take that FxCop!) Once again, this works well for simple cases, but falls flat when it comes to more complex scenarios, or what some would call "real world."
If SQL were a true standard, providing syntactical parity might make more sense. However, having spent several days poring through specifications for SQL-92, or at least what there is of it that's freely available without shelling out big bucks for a copy of ISO 9075, I began to under why the SQL syntax landscape is such a sloppy mess.
Finally, let us not forget that SQL is no panacea. Injections attacks aside, there are good reasons why so much effort is spent trying to avoid writing it. For me, the prime example is the inclusion of spatial constructs into the core syntax, which raises this question:
Does a RIGHT JOIN become a LEFT JOIN if you're using an Arabic or Hebrew character set?