How to: Define FetchPlans Per Query

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 are consuming a fluent model based on the SofiaCarRental database.

You can find a tutorial for creating the SofiaCarRental fluent model in the Creating The SofiaCarRental Model article.

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 (FluentModel dbContext = new FluentModel())
{
   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 FluentModel()
 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 (FluentModel dbContext = new FluentModel())
{
   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 FluentModel()
 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 (FluentModel dbContext = new FluentModel())
{
   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 FluentModel()
 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 (FluentModel dbContext = new FluentModel())
{
   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 FluentModel()
 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 (FluentModel dbContext = new FluentModel())
{
   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 FluentModel()
 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 (FluentModel dbContext = new FluentModel())
{
   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 FluentModel()
 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