How to: Define FetchPlans Per Query

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

FetchPlans allow you to load references and fields in addition to what needs to be loaded by a single operation with the goal to eliminate the additional round-trips to the database server. As you learnt in the Getting Started with FetchPlans API topic, defining a fetch plan is supported in Telerik Data Access on a context level by using the FetchStrategy property.

It is also possible to define a fetch plan on a query level, i.e. the fetch plan can be defined as a part of the LINQ statement by using either the Include or LoadWith extension methods.

The Include and LoadWith<T> extension methods are from the Telerik.OpenAccess namespace. So the first step before using them is to add the Telerik.OpenAccess using (Import) statement.

using Telerik.OpenAccess;

Imports Telerik.OpenAccess

It is not possible to mix the Include and LoadWith methods in one query.

Suppose, you have the following domain model containing the Cars, Categories and RentalOrders tables from the SofiaCarRental database.

Using the Include Method

The following example shows how to load all the Cars and RentalOrders for all the Categories in one query using the Include method. As a result, successive access to the Cars property on a Category object does not trigger a new database query. The same is valid for the RentalOrders property on a Car object.

using (EntitiesModel dbContext = new EntitiesModel())
{
   IQueryable<Category> categories = 
        from category in dbContext.Categories.
            Include(cat => cat.Cars.Select(car => car.RentalOrders))
        select category;

   // Only one database query is performed.
   foreach (Category category in categories)
   {
       Console.WriteLine("Category=" + category.CategoryName + " CarsCount=" +
            category.Cars.Count + " RentalOrdersCount=" + 
            category.Cars.Sum(car => car.RentalOrders.Count));
   }
}

Using dbContext As New EntitiesModel()
 Dim categories As IQueryable(Of Category) = 
    From category In dbContext.Categories. _
        Include(Function(cat) cat.Cars.Select(Function(car) car.RentalOrders))
    Select category

 ' Only one database query is performed.
 For Each _category As Category In categories
  Console.WriteLine("Category=" & _category.CategoryName & " CarsCount=" &  _
    _category.Cars.Count & " RentalOrdersCount=" & _
    _category.Cars.Sum(Function(car) car.RentalOrders.Count))
 Next _category
End Using

The following example shows how to load the Category and all the RentalOrders for all the Cars in one query. Note that the Include method is used twice.

using (EntitiesModel dbContext = new EntitiesModel())
{
   IQueryable<Car> cars = 
        from car in dbContext.Cars.
            Include(c => c.RentalOrders).Include(c => c.Category)
        select car;

   // Only one database query is performed.
   foreach (Car car in cars)
   {
       Console.WriteLine("Car=" + car.Model + " Category=" + 
                         car.Category.CategoryName +
                         " Orders Count=" + car.RentalOrders.Count);
   }
}

Using dbContext As New EntitiesModel()
 Dim cars As IQueryable(Of Car) = 
        From car In dbContext.Cars.Include(Function(c) c.RentalOrders). _
            Include(Function(c) c.Category)
        Select car

 ' Only one database query is performed.
 For Each _car As Car In cars
  Console.WriteLine("Car=" & _car.Model & " Category=" &  _
        _car.Category.CategoryName & " Orders Count=" &  _
        _car.RentalOrders.Count)
 Next _car
End Using

The following example shows how to load the Car and Category objects for a RentalOrder in one query by using the Include method.

using (EntitiesModel dbContext = new EntitiesModel())
{
   IQueryable<RentalOrder> orders = 
        from rentalOrder in dbContext.RentalOrders.
            Include(order => order.Car.Category)
        select rentalOrder;

   // Only one database query is performed.
   foreach (RentalOrder rentalOrder in orders)
   {
       Console.WriteLine("RentalOrder=" + rentalOrder.RentalOrderID + 
                         " Car=" + rentalOrder.Car.Model + " Category=" +
                         rentalOrder.Car.Category.CategoryName);
   }
}

Using dbContext As New EntitiesModel()
 Dim orders As IQueryable(Of RentalOrder) = 
        From rentalOrder In dbContext.RentalOrders. _
            Include(Function(order) order.Car.Category)
        Select rentalOrder

 ' Only one database query is performed.
 For Each _rentalOrder As RentalOrder In orders
  Console.WriteLine("RentalOrder=" & _rentalOrder.RentalOrderID & " Car=" & _
                    _rentalOrder.Car.Model & " Category=" & _
                    _rentalOrder.Car.Category.CategoryName)
 Next _rentalOrder
End Using

Using the LoadWith<T> Method

All scenarios demonstrated in the previous section could be achieved by using the LoadWith<T> method.

The following example shows how to load all the Cars and RentalOrders for all the Categories in one query using the LoadWith<T> method.

using (EntitiesModel dbContext = new EntitiesModel())
{
   Telerik.OpenAccess.FetchOptimization.FetchStrategy fetchStrategy = 
        new Telerik.OpenAccess.FetchOptimization.FetchStrategy();
   fetchStrategy.LoadWith<Category>(c => c.Cars);
   fetchStrategy.LoadWith<Car>(c => c.RentalOrders);

   IQueryable<Category> categories = 
        from category in dbContext.Categories.LoadWith(fetchStrategy)
        select category;

   // Only one database query is performed.
   foreach (Category category in categories)
   {
       Console.WriteLine("Category=" + category.CategoryName + 
                         " CarsCount=" + category.Cars.Count + 
                         " RentalOrdersCount=" + category.Cars.
                                Sum(car=>car.RentalOrders.Count));
   }
}

Using dbContext As New EntitiesModel()
 Dim fetchStrategy As New Telerik.OpenAccess.FetchOptimization.FetchStrategy()
 fetchStrategy.LoadWith(Of Category)(Function(c) c.Cars)
 fetchStrategy.LoadWith(Of Car)(Function(c) c.RentalOrders)

 Dim categories As IQueryable(Of Category) = 
    From category In dbContext.Categories.LoadWith(fetchStrategy)
    Select category

 ' Only one database query is performed.
 For Each _category As Category In categories
  Console.WriteLine("Category=" & _category.CategoryName & " CarsCount=" &  _
                    _category.Cars.Count & " RentalOrdersCount=" &  _
                    _category.Cars.Sum(Function(car) car.RentalOrders.Count))
 Next _category
End Using

The following example shows how to load the Category and all the RentalOrders for all the Cars in one query.

using (EntitiesModel dbContext = new EntitiesModel())
{
   Telerik.OpenAccess.FetchOptimization.FetchStrategy fetchStrategy = 
        new Telerik.OpenAccess.FetchOptimization.FetchStrategy();
   fetchStrategy.LoadWith<Car>(c => c.Category);
   fetchStrategy.LoadWith<Car>(c => c.RentalOrders);

   IQueryable<Car> cars = from car in dbContext.Cars.LoadWith(fetchStrategy)
                          select car;

   // Only one database query is performed.
   foreach (Car car in cars)
   {
       Console.WriteLine("Car=" + car.Model + " Category=" + 
                         car.Category.CategoryName + " Orders Count=" + 
                         car.RentalOrders.Count);
   }
}

Using dbContext As New EntitiesModel()
 Dim fetchStrategy As New Telerik.OpenAccess.FetchOptimization.FetchStrategy()
 fetchStrategy.LoadWith(Of Car)(Function(c) c.Category)
 fetchStrategy.LoadWith(Of Car)(Function(c) c.RentalOrders)

 Dim cars As IQueryable(Of Car) = 
    From car In dbContext.Cars.LoadWith(fetchStrategy)
    Select car

 ' Only one database query is performed.
 For Each _car As Car In cars
  Console.WriteLine("Car=" & _car.Model & " Category=" &  _
                    _car.Category.CategoryName & " Orders Count=" & _
                    _car.RentalOrders.Count)
 Next _car
End Using

The final example shows how to load the Car and Category objects for a RentalOrder in one query.

using (EntitiesModel dbContext = new EntitiesModel())
{
   Telerik.OpenAccess.FetchOptimization.FetchStrategy fetchStrategy = 
        new Telerik.OpenAccess.FetchOptimization.FetchStrategy();
   fetchStrategy.LoadWith<RentalOrder>(r => r.Car);
   fetchStrategy.LoadWith<Car>(c => c.Category);

   IQueryable<RentalOrder> orders = 
        from order in dbContext.RentalOrders.LoadWith(fetchStrategy)
        select order;

   // Only one database query is performed.
   foreach (RentalOrder rentalOrder in orders)
   {
       Console.WriteLine("RentalOrder=" + rentalOrder.RentalOrderID + 
                         " Car=" + rentalOrder.Car.Model + " Category=" + 
                         rentalOrder.Car.Category.CategoryName);
   }
}

Using dbContext As New EntitiesModel()
 Dim fetchStrategy As New Telerik.OpenAccess.FetchOptimization.FetchStrategy()
 fetchStrategy.LoadWith(Of RentalOrder)(Function(r) r.Car)
 fetchStrategy.LoadWith(Of Car)(Function(c) c.Category)

 Dim orders As IQueryable(Of RentalOrder) = 
        From order In dbContext.RentalOrders.LoadWith(fetchStrategy)
        Select order

 ' Only one database query is performed.
 For Each _rentalOrder As RentalOrder In orders
  Console.WriteLine("RentalOrder=" & _rentalOrder.RentalOrderID & " Car=" & _
                    _rentalOrder.Car.Model & " Category=" &  _
                    _rentalOrder.Car.Category.CategoryName)
 Next _rentalOrder
End Using