Introduction

The use of an O/RM framework is not dictated by law. You should not feel forced to use an O/RM and drop your beloved, maybe old-fashioned, but still terribly effective ADO.NET code or stored procedures. Like many other things in life, you enjoy an O/RM when you actually need it. So when are you really going to need one?

In my humble opinion, you never need an O/RM per se. There are cases in which you need an O/RM as a tool that significantly help with the implementation of particular application architecture. If you are not following the Domain Model pattern (including variations of it) and, more importantly, are not neatly separating separate data processing logic (DAL) from business logic (BLL), you don't even see the point of O/RM frameworks. The primary reason for keeping DAL and BLL neatly separated is the will of modeling the solution of the problem using real world entities. At some point, these real-world entities need to store or read some raw data. Here's where the business logic gets in touch with the DAL and passes its own objects whose contents will be persisted or loaded. Architecturally speaking, that's all you need.

How would you write the DAL? Frankly, you can use any technology you feel comfortable with, including stored procedures, ADO.NET, and O/RM frameworks. I wouldn't call this a plain implementation detail as switching from one option to the next will have deep repercussions. However, an O/RM is simply an effective (and highly productive) way of implementing the DAL in a truly layered architecture.

When you get to evaluating the various options for the DAL, you see that stored procedures and plain ADO.NET objects give you total control over the SQL being used. When an O/RM is used, instead, you trade speed of development for opaque SQL. The SQL that actually hits the database, in fact, can be inspected but not replaced. For this reason, people with a strong database background tend to feel a bit uneasy about O/RMs. While the control you can exercise over the SQL that an O/RM generates is never total, there are a few things you can do to control the final SQL.

This article is about fetch plans - a recognized and common way for developers to instruct the O/RM about the structure of the SQL you desire.

What's a Fetch Plan, Anyway?

A fetch plan is the strategy that an O/RM uses for retrieving related objects in the context of an association. By specifying the fetch plan you let the O/RM know a bit more about how you intend to use the objects being retrieved. In this way, you provide the O/RM with the possibility to fine-tune the fetch operation. This normally results in reduced database load and likely better performance. Let's see a concrete example of how (and why) a fetch plan can be helpful.

Given a Customers->Orders relationship, a fetch plan sets the strategy for retrieving orders associated with a given customer. Imagine you run the following code. The code snippet is for Entity Framework, but the concept behind it holds true in general and is not specific of the Entity Framework product.

The code will examine a bunch of orders and then decides about sending a notification to the customer who placed it. If you let it go as is in the context of Entity Framework, you get a first query for all orders placed past 1997 followed by a new roundtrip for each new customer discovered.

How would you write such a query yourself? It's a tradeoff between the estimated number of queries per customer you actually have and the amount of data and time it would cost you making a JOIN between all customers and orders. The point is that the preceding code leaves the O/RM without guidance on how to arrange the roundtrips to the database. The O/RM sees individual operations and tries to optimize the SQL for that operation. It doesn't necessarily get a wider scope and can't guess how you are using the retrieved data. By specifying a fetch plan you share your future plans with the O/RM and give it more information to generate more effective SQL code.

A fetching strategy is based on two aspects: the time at which the data is fetched and what kind of SQL code is used to fetch data. Specifying a fetch strategy is ultimately equivalent to deciding whether you want that navigation properties (like Customer or Orders in the previous example) are eager-loaded or lazy-loaded.

Let's see how fetch plan capabilities are offered in NHibernate and Entity Framework. As you'll see in a moment, differences in the API revolve around the granularity of control the O/RM API offers on properties and code.

Fetch Plans with Entity Framework

In Entity Framework, you specify the fetch plan by using the Include method at the query level. By using the Include method you tell the O/RM engine which associations you intend to expand in the context of the query. More often than not, an Include call results in a JOIN being performed in the SQL code. Here's you could rewrite the previous code snippet:

The Include method gets a dot-separated string indicating the name of the objects to retrieve in the query. By simply adding an Include call to the collection being queried, you radically modify the SQL being used. Here's what the SQL Profiler tool now captures: a single query with a left outer join between orders and customers.

Other methods such IsLoaded and Load give you more control over the operation and enable you to programmatically determine which property to load and when. When you use Include, it is said you're using eager loading. It is called on-demand loading when you use Load. You can use the Load method to extend via custom code the capabilities of lazy loading.

Fetch Plans with NHibernate

In NHibernate, the use of the SetFetchMode method on the query object enables you to configure navigation properties so that they are retrieved in advance. Assume you hold a criteria object that expresses a query in NHibernate; the following code snippet shows how to manipulate it in order to get orders and order details loaded in advance.

The SetFetchMode method can accept two values: Select and Join. The FetchMode.Join value indicates that an outer JOIN will be used beforehand to retrieve all involved data. In this way, you get all you need in a single query to the database. The FetchMode.Select value, instead, indicates that a separate SELECT statement will be issued on demand to retrieve the associated entity or collection. Unless you explicitly set lazy="false" in the configuration of the entity, the second SELECT will be executed in a lazy manner, that is not until you effectively access the data.

In addition to setting the fetch mode for any particular HQL or criteria query, NHibernate also allows you to express your fetch strategies in the O/R mapping metadata. The preceding code defines an NHibernate set with a JOIN fetch mode.

Speaking of smart ways to fetch your data, a couple of specific features you find in NHibernate must be mentioned: multi-queries and futures. Although not strictly related to fetch plans, both tools can help you writing fetch code that meets expectations and optimizes the database load.

In brief, a multi-query consists in multiple queries grouped together to form a single command that hits the database in a single roundtrip. The canonical example of a multi-query is joining the query for a page of data with the query that calculates the total number of pages or total number of records to page through. For a multi-query to work, it is required that the database have the ability to return multiple result sets. You can use the multi-query syntax to express both HQL queries and criteria-based queries. Here's an example of a criteria multi- query:

The method List runs all chained queries and returns an IList object. Each element in the list represents the results of one of the queries. The order is the same order in which queries have been added. Let's get to the Future feature.

When you call the List method on a criteria object in NHibernate the query executes immediately. If you resort to the Future method, instead, execution is delayed. Queries are grouped into a multi-query which executes as soon as one of the result sets is actually accessed.

As mentioned, both multi-queries and futures can't be catalogued as aspects of the fetch strategy which remains an attribute of navigation properties defined on entities. However, at the very end of the day what you need is delineating an effective strategy for fetching the data you need in the smallest possible number of roundtrip and load. In this regard, multi-queries and futures do help.

Summary

By looking at fetch strategies you probably figure out the real difference that exists between Entity Framework and NHibernate beyond hype and personal bias. NHibernate is about at the end of its technological growth. It has all of its mistakes fixed and all of the needs set. Entity Framework is younger and is catching up. For what we can say today, the set of features an O/RM needs is large but quite defined. Any O/RM, if backed by a strongly committed vendor or community, can eventually cover most of it. Your choice depends on the time you need to make the choice, your actual needs and the current position of the various O/RM tools.

As far as fetch plans are concerned, NHibernate offers more options and more control over the overall fetch strategy. But Entity Framework offers an effective solution for just what you need to do most of the time.

About Dino Esposito

Dino Esposito is one of the world's authorities on Web technology and software architecture. Dino published an array of books, most of which are considered state-of-the-art in their respective areas. His most recent books are “Microsoft ® .NET: Architecting Applications for the Enterprise” and “... This author has published 53 articles on DotNetSlackers. View other articles or the complete profile here.