Telerik OpenAccess ORM	Send comments on this topic.
How to: Define FetchPlans Per Query

Programmer's Guide > Developer's Guide > CRUD Operations > Defining Fetch Plans > How to: Define FetchPlans Per Query

Glossary Item Box

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 OpenAccess ORM 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.

C#	Copy Code
using Telerik.OpenAccess;

VB.NET	Copy Code
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.

Copy Code

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

VB.NET

Copy Code

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.

Copy Code

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

VB.NET	Copy Code
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.

Copy Code

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

VB.NET

Copy Code

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.

Copy Code

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

VB.NET

Copy Code

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.

Copy Code

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

VB.NET

Copy Code

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.

Copy Code

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

VB.NET

Copy Code

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