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.

C#


    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"));
               }
           }
       }
    }

VB


    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:

    C#

    
        using (NorthwindDbContext dbContext = new NorthwindDbContext())
        {
        }

    VB

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

    C#

    
        Telerik.OpenAccess.FetchOptimization.FetchStrategy fetchStrategy = new Telerik.OpenAccess.FetchOptimization.FetchStrategy();

    VB

    
        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:

    C#

    
        fetchStrategy.LoadWith<Customer>(c => c.Orders);

    VB

    
        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:

    C#

    
        fetchStrategy.LoadWith<Order>(c => c.OrderDetails);

    VB

    
        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:

    C#

    
        dbContext.FetchStrategy = fetchStrategy;

    VB

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

    C#

    
        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"));
               }
           }
        }

    VB

    
        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:

C#


    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"));
               }
           }
       }
    }

VB


    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: