In this blog post we will introduce the new intuitive and powerful Fetch Optimization API. Users familiar with the LINQ to SQL data load options will feel very comfortable with our API. If you don’t have a clue what this is all about, here we will give an overview of the basic concepts behind the usage of fetch strategies.

In short, fetch strategies help you optimize your retrieval of objects from the database. They allow you to specify properties in a persistent object that are retrieved together whenever you invoke a call to the persistent object.

Let’s consider the following example which uses the ubiquitous Northwind database. Suppose that we have orders which are served by different employees. We want to access the names of the employee related to an order as soon as we get an order object from the database. OpenAccess uses lazy loading by default in such scenarios which means that the employee will not be retrieved earlier than the first time some of its members are accessed (in our example the first and last names). The catch here is that an individual query will be sent to the database for each first-time access of the employee name through the order reference. Now, imagine that we have 10000 orders served by 1000 different employees.  Besides the query to retrieve the orders, we will have 1000 additional queries when we access the employee names. Can you imagine what an overhead this simple scenario causes? The good news is OpenAccess provides flexible tools to save the day in the form of the new fetch optimization API.

To enable eager loading of employees when we load orders, we can take a look at the following code snippet:

NorthwindContext context = new NorthwindContext();
 
FetchStrategy fetchStrategy = new FetchStrategy();
fetchStrategy.LoadWith<Order>(o => o.Employee);               
// or use the overload:
// fetchStrategy.LoadWith((Order o) => o.Employee);
context.FetchStrategy = fetchStrategy;
 
IQueryable<Order> orders = context.Orders;
foreach (Order order in orders)
{
    Console.WriteLine("Order {0} served by {1} {2}",
          order.OrderID, order.Employee.FirstName, order.Employee.LastName);
}

 

The FetchStartegy is a class that includes fetch definitions for each persistent type. Each persistent type can have at most one fetch definition per FetchStrategy. The strategy is bound to a defined context and determines the fetching rules whenever retrieval through the same context occurs . Once the fetch strategy is set to a context it cannot be modified. In order to change the fetch strategy of the context, you need to create a new one and assign it to this context. 

The LoadWith method of the FetchStrategy class describes a fetch definition for a persistent type. In the above example, the result is that when you access the employee names from an order reference there will be no additional queries in the database. You can track the generated SQL with a tool such as SQL Server Profiler for MS SQL.

Now, let’s consider a little more complicated example. We would like to display the following information for products: the product name, the category name of the product and the supplier name of the product. We can define a fetch strategy which loads the category and supplier at the same time we load the product.

 

NorthwindContext context = new NorthwindContext()
             
FetchStrategy fetchStrategy = new FetchStrategy();
fetchStrategy.LoadWith<Product>(p => p.Category, p => p.Supplier);
// or use the overload:
// fetchStrategy.LoadWith((Product p) => p.Category, (Product p) => p.Supplier);
             
context.FetchStrategy = fetchStrategy;
 
IQueryable<Product> products = context.Products;
foreach (Product product in products)
{
    Console.WriteLine("\nProduct info: ");
    Console.WriteLine("\t name: {0}", product.ProductName);
    Console.WriteLine("\t category: {0}", product.Category.CategoryName);
    Console.WriteLine("\t supplier: {0}", product.Supplier.CompanyName);
}

You can achieve immense flexibility in the way you define your fetch optimization when using the different overloads of the LoadWith method. Nevertheless, the parameterized overloads ensure type safety and in most cases are the preferred choice.  

It is worth pointing out that you cannot define cycles with the FetchStrategy. For instance, you cannot load employees with orders and at the same time load orders with employees.

fetchStrategy.LoadWith<Order>(o => o.Employee);
fetchStrategy.LoadWith<Employee>(e => e.Orders);

To sum up, fetch optimization strategies are a powerful tool which can be utilized in whatever way you see fit. Yet, with the great power comes responsibility for the performance of your application. Loading many objects eagerly together can slow the execution of your program if the fetch strategies are used inappropriately.

What’s next? We will include some exciting additional features to this API. One of them is a fetch rule defined on a per-query basis. This allows you to use different load combinations for each query to the database and does not constrain you to global context settings. The other fantastic feature is fetch paths, which means that you can load related objects to a reference within a persistent type. An illustrative example for this is loading Order Details with Orders and Products with Order Details. However, bear in mind that this is different from invoking two calls to the LoadWith method. In the former case accessing the Product via an OrderDetail reference in the Order object results in one query to the database. In the latter case there will be two queries for this scenario because the Product reference is not accessed directly through an OrderDetail object.

Last but not least, we will introduce collection filtering and users will be able to narrow the range of loaded objects to only those that are absolutely necessary. For example, it will be possible to load only shipped orders in the last year by providing the right lambda criteria.


Comments

Comments are disabled in preview mode.