Getting Started with FetchPlans API

In this topic, you will understand the round trip issues of Telerik Data Access and how you can overcome them by using the FetchStrategy class. When you query for an object, you actually retrieve only the object you requested. The related objects are not automatically fetched at the same time. The FetchStrategy class provides you with immediate loading of related data. The FetchStrategy class provides two methods to achieve immediate loading of specified related data. The LoadWith and LoadWith<T> methods allow immediate loading of data related to the main target.

Consider the database diagram below, where three tables from the Northwind database (Customers, Orders and OrderDetails) are represented.

Suppose that you have a generated Telerik Data Access Domain Model. To fill the entity objects with data from the database, several steps are required. In the first step, you need an OpenAccessContext instance. Then you need a LINQ query to load Customer objects from the database. Finally, start iterating through the Customer, Order and OrderDetail objects.

using (NorthwindDbContext dbContext = new NorthwindDbContext())
{
   IQueryable<Customer> query = from c in dbContext.Customers
                                where c.Country == "Germany"
                                select c;
   foreach (Customer customer in query)
   {
       Console.WriteLine("Customer Id: {0}", customer.CustomerID);
       foreach (Order order in customer.Orders)
       {
           Console.WriteLine("===Order date: {0}", order.OrderDate.ToString());
           foreach (OrderDetail orderDetail in order.OrderDetails)
           {
               Console.WriteLine("======Unit price: {0}", orderDetail.UnitPrice.ToString("c"));
           }
       }
   }
}
Using dbContext As New NorthwindDbContext()
 Dim query As IQueryable(Of Customer) = From c In dbContext.Customers
                                        Where c.Country = "Germany"
                                        Select c
 For Each customer_Renamed As Customer In query
  Console.WriteLine("Customer Id: {0}", customer_Renamed.CustomerID)
  For Each order_Renamed As Order In customer_Renamed.Orders
   Console.WriteLine("===Order date: {0}", order_Renamed.OrderDate.ToString())
   For Each orderDetail_Renamed As OrderDetail In order_Renamed.OrderDetails
    Console.WriteLine("======Unit price: {0}", orderDetail_Renamed.UnitPrice.ToString("c"))
   Next orderDetail_Renamed
  Next order_Renamed
 Next customer_Renamed
End Using

If you analyze the previous code-snippet, you should note the following things:

  • After the LINQ query is created by the user, it is converted to a representation that is compatible with Telerik Data Access, which is then executed against the data source by the Telerik Data Access engine. At query execution time, all query expressions (or components of the query) are evaluated on the client or on the server. This includes expressions that are used in result materialization or projections. The time in which a query expression is executed can vary. LINQ queries are executed each time the query variable is iterated over, not when the query variable is created.
  • The second important thing is that for every entity, a separate query is fired to the SQL Server. For instance, for Customer one query is fired and then separate queries for Orders and OrderDetails are fired to flourish the entity object.

You can instruct Telerik Data Access to load all objects by using the FetchStrategy class. Below are the steps involved to enable this behavior:

  1. Create a new instance of the OpenAccessContext class:

    using (NorthwindDbContext dbContext = new NorthwindDbContext())
    {
    }
    
    Using dbContext As New NorthwindDbContext()
    End Using
    
  2. Create a new instance of the FetchStrategy class:

    Telerik.OpenAccess.FetchOptimization.FetchStrategy fetchStrategy = 
        new Telerik.OpenAccess.FetchOptimization.FetchStrategy();
    
    Dim fetchStrategy As New Telerik.OpenAccess.FetchOptimization.FetchStrategy()
    
  3. Using the LoadWith<T> method, you need to define that you want to load Customer with Orders in one SQL:

    fetchStrategy.LoadWith<Customer>(c => c.Orders);
    
    fetchStrategy.LoadWith(Of Customer)(Function(c) c.Orders)
    
  4. Every Order object has OrderDetails, so you can say that the OrderDetail objects should be loaded for every Order in one SQL:

    fetchStrategy.LoadWith<Order>(c => c.OrderDetails);
    
    fetchStrategy.LoadWith(Of Order)(Function(c) c.OrderDetails)
    
  5. Whatever fetch strategy you have defined, you need to set the same one to the OpenAccessContext's FetchStrategy
    property:

    dbContext.FetchStrategy = fetchStrategy;
    
    dbContext.FetchStrategy = fetchStrategy
    
  6. Finally, prepare your query and start iterating through the objects:

    IQueryable<Customer> query = from c in dbContext.Customers
                                 where c.Country == "Germany"
                                 select c;
    foreach (Customer customer in query)
    {
       Console.WriteLine("Customer Id: {0}", customer.CustomerID);
       foreach (Order order in customer.Orders)
       {
           Console.WriteLine("===Order date: {0}", order.OrderDate.ToString());
           foreach (OrderDetail orderDetail in order.OrderDetails)
           {
               Console.WriteLine("======Unit price: {0}", orderDetail.UnitPrice.ToString("c"));
           }
       }
    }
    
    Dim query As IQueryable(Of Customer) = From c In dbContext.Customers
                                           Where c.Country = "Germany"
                                           Select c
    For Each c As Customer In query
     Console.WriteLine("Customer Id: {0}", c.CustomerID)
     For Each o As Order In c.Orders
      Console.WriteLine("===Order date: {0}", o.OrderDate.ToString())
      For Each od As OrderDetail In o.OrderDetails
       Console.WriteLine("======Unit price: {0}", od.UnitPrice.ToString("c"))
      Next od
     Next o
    Next c
    

Below is the complete source code:

using (NorthwindDbContext dbContext = new NorthwindDbContext())
{
   Telerik.OpenAccess.FetchOptimization.FetchStrategy fetchStrategy = 
        new Telerik.OpenAccess.FetchOptimization.FetchStrategy();
   fetchStrategy.LoadWith<Customer>(c => c.Orders);
   fetchStrategy.LoadWith<Order>(c => c.OrderDetails);
   dbContext.FetchStrategy = fetchStrategy;

   IQueryable<Customer> query = from c in dbContext.Customers
                                where c.Country == "Germany"
                                select c;

   foreach (Customer customer in query)
   {
       Console.WriteLine("Customer Id: {0}", customer.CustomerID);
       foreach (Order order in customer.Orders)
       {
           Console.WriteLine("===Order date: {0}", order.OrderDate.ToString());
           foreach (OrderDetail orderDetail in order.OrderDetails)
           {
               Console.WriteLine("======Unit price: {0}", orderDetail.UnitPrice.ToString("c"));
           }
       }
   }
}
Using dbContext As New NorthwindDbContext()
 Dim fetchStrategy As New Telerik.OpenAccess.FetchOptimization.FetchStrategy()
 fetchStrategy.LoadWith(Of Customer)(Function(c) c.Orders)
 fetchStrategy.LoadWith(Of Order)(Function(c) c.OrderDetails)
 dbContext.FetchStrategy = fetchStrategy

 Dim query As IQueryable(Of Customer) = From c In dbContext.Customers
        Where c.Country = "Germany"
        Select c

 For Each c As Customer In query
  Console.WriteLine("Customer Id: {0}", c.CustomerID)
  For Each o As Order In c.Orders
   Console.WriteLine("===Order date: {0}", o.OrderDate.ToString())
   For Each od As OrderDetail In o.OrderDetails
    Console.WriteLine("======Unit price: {0}", od.UnitPrice.ToString("c"))
   Next od
  Next o
 Next c
End Using

For more information about the FetchPlan API provided by Telerik Data Access, consider the following topics: