Thursday, February 24, 2011

Why not ORM: Trusting SQL, Part 2

ORM-RobotIn part 1, we examined and busted 5 common ORM myths. Actually, we busted 4.5. We agreed that one myth, that you can't trust the SQL an ORM produces, is plausible. Let's bust that myth, at least bust it for OpenAccess ORM, today.


One of the biggest barriers to adopting any time saving development tool is trust. You have to trust that the work the tool is doing to save you time is of equal or better quality than the same work you would otherwise do by hand. A development tool is in many ways like a co-worker or contractor. You have to get to know the results they produce before you can stop worrying about including their output in your project.

This is true for refactoring tools (like JustCode), UI components (like the RadControls), and especially true for data tools like OpenAccess ORM.

But how do you go about "trusting" a tool like OpenAccess ORM? Transparency.


With ORMs, many fears root in the perceived "black box" nature of what's happening under the covers. Many developers are familiar with hand crafting EACH-and-EVERY-sql-statement, and it can be unsettling to let some ORM robot start automagically creating your SQL based on abstracted LINQ. What if the SQL is inefficient? What if way too much SQL is being generated? What if the ORM "select *" my database?!

The result of this fear is often cut-and-run. Dump (or don't start using) the ORM and stick with time-wasting, but 100% transparent hand coding.

What developers need is an easy way to open the ORM black box and see what's really happening. Cut-out the "what ifs," and replace them with "when I do X, the ORM will do Y."


While ORMs like OpenAccess are unbelievably sophisticated, giving you the power to map database objects to code and even update schemas from code-based models, at the very root of the implementation is code you know. Standard (best practice) .NET for moving data between various databases. In the case of SQL Server, you'll find ADO.NET and SqlCommands, Transactions, and Connections.

The important thing to know is that this "standard" code is built by super-smart developers that are 100% focused on best practice data programming. Think about that. When you use a well built ORM, it's like adding a .NET data programming expert to your team, freeing you to really focus and be an expert on other layers of your app.

Then there's the SQL. And here it's worth noting, not all ORMs are created equal. The way an ORM converts LINQ or OQL or whatever in to SQL, and the ability on top of that to optimize the SQL through configuration, is often what makes one ORM better than another. With a quality ORM, though, you get SQL generated by programmers that are again 100% focused on SQL programming.

Now, much like you must "speak Google" to get the search results you expect, you usually need to learn how to "speak ORM" to get the SQL output you expect. That means you need to form an understanding of how the abstracted data languages (usually LINQ) produce SQL so you know when extra optimization configurations are needed. Do that, and there is no reason the SQL created by an ORM will be any different than the SQL you would create by hand (it may even be better!).

A good ORM is more than a tool. It's a .NET data programming and SQL expert. But how do you prove that to yourself? And how do you learn to speak ORM?


You should never blindly trust any tool. Just as I'd expect you to inspect the HTML produced by the Telerik Extensions for ASP.NET MVC, I'd expect you to inspect the SQL produced by OpenAccess. Doing so not only helps you learn when and how to optimize ORM programming, but it helps you build the trust in your essential data tool.

Fortunately, OpenAccess provides a couple ways to open the "black box" out of the box:

  1. Logging
    If you've never used OpenAccess logging, you may be amazed at what can be easily logged with the simple change to a couple of config settings. All SQL statements, parameter values, database connections, and even returned rows can be captured by OpenAccess logging for your thorough review. Logs can be output to the console, a text file, or even directly to a in-memory StringWriter, and volume of data that is captured depends on your logging level. More on that in the next post.
  2. IntelliTrace
    For added runtime debugging transparency, OpenAccess (as of Q3 2010) provides deep insight with integrated IntelliTrace support in Visual Studio 2010. As you step and debug your app, IntelliTrace can reveal the SQL OpenAccess is producing and executing.

Both of these approaches are useful, but they do require you to manually "parse" the logs or trace stacks to understand what's happening when your program runs. Is there an easier way to see the bigger picture?


Taking advantage of the OpenAccess logging output via a custom TraceListener (along with a few other tricks for newer versions of OpenAccess), I've created a simple tool designed to help you better visualize what OpenAccess is doing when your application runs.


The advantages of this tool:

  • Easier to visualize, syntax-highlighted SQL
  • Convenient log sorting, filtering, and grouping (provided by RadGridView)
  • Summary values for key behaviors (total connections, total queries, total transactions, etc.)
  • Real-time visualization of OpenAccess output

In part 3, I'll provide a more complete overview of this new tool and provide a basic working version that you can try with your own OpenAccess-powered applications. The main idea behind this tool: make it dead simple to open the OpenAccess black box and visualize the "real" SQL.

Whether you use built-in logging output or a visualization tool like this, take the time to get to know your ORM. If you can build a trust with a powerful ORM, like OpenAccess, you'll leave hand-coding data access in the past where it belongs and never look back.

Can you trust the SQL produced by an ORM? Definitely! You just need to take a look. It's not in a book…it's in a log file.


Unknown said...

Great post, Todd. I've been wondering when someone would sit down and crank out a tool like this for OpenAccess. I'm looking forward to seeing it in action!

Litzo said...

I'd always resisted ORMs of old, but I'm really enjoying using Open Access. Great tool too, and an excellent follow up on the last Open Access blog post.

Robbo said...

Let me just start by saying I am a HUGE fan of Telerik products. However, we've been using EntitySpaces for years and I haven't seen anything in OpenAccess that would make me want to switch. In fact, everything appears to be more difficult in OpenAccess (OQL? really?). I feel like I'm missing something, because OpenAccess almost looks worse than raw SQL to me (see I'm open to be converted though!

Unknown said...
This comment has been removed by the author.
Unknown said...

@Robbo, you were referencing the old OpenAccess Classic that is being discontinued. The new domain model OpenAccess uses LINQ, very easy to use, see here
I was a former EntitySpaces dev, switched to OA and been happy with it.

Steve said...

I have been using OA for a few years, never ever needed to write OQL for any reason.

It's quite elegant

var program = context.Programs.FirstOrDefault();
program.Name = "Whatever";

@Todd, am I missing the DL link for this tool somewhere?

Unknown said...

Great article! I'm very happy with OpenAccess, but I never dived into the generated SQL (I always simply trust Telerik :-)). It would be good though to learn to use OA even better. Can't wait for the tool to visualize the generated SQL!

Ed Charbeneau said...

Excellent post. There is so much about OA ORM to learn. I've spent the better part of a year working with OA and can't imagine ever doing with out it now. It saves me so much time and effort.

Now that I'm very familiar with OA ORM I've been speaking and writing about it:

I recently upgraded to the Telerik Ultimate collection and it's empowering to say the least. OA, UI and WebAi test are the way to go.

Todd Anglin said...

@Robbo- To be honest, in my years of using OpenAccess, I've -never- used OQL. I agree that it feels too much like SQL (plus, it's string-based and thus less able to catch errors at build.)

I always use LINQ. OpenAcess supports LINQ strongly-typed, fluent querying. As I remember it (it's been a while), EntitySpaces is similar, it just has it's own proprietary fluent querying API. Telerik just chose to use the .NET "standard."

@Steve- DL link coming in the next post. (Today if I can get it out...)

@Ed- Keep-up the good community work!