Telerik Reporting R1 2017

Retrieving data from an Entity Data Model with the EntityDataSource component

This section discusses various techniques for retrieving data from an Entity Data Model with the help of the EntityDataSource component. The provided examples and code snippets assume an existing Entity Data Model of the Adventure Works sample database with the following structure:

The simplest approach to extract entities from an Entity Data Model is to bind the EntityDataSource component directly to an auto-generated property of the model, as shown in the sample code below:

var entityDataSource = new Telerik.Reporting.EntityDataSource();

entityDataSource.Context = typeof(AdventureWorksEntities);
entityDataSource.ContextMember = "Products";

var report = new Report1();

report.DataSource = entityDataSource;

You can specify expressions to the data item to group, sort or filter the selected entities. The expressions are evaluated on the application level by the reporting engine after all entities are downloaded from the database. Sometimes it is preferable to offload certain tasks on the database level instead. To do this you need to define a custom method in the ObjectContext/DbContext class that performs the required business logic. For example, the following method uses the Where extension method to filter the Product entities:

partial class AdventureWorksEntities
{
    public System.Collections.Generic.List<Product> GetProducts(string color, decimal price)
    {
        return this.Products.Where(product => product.Color == color && product.ListPrice <= price).ToList();
    }
}

Using a method instead of a property has the additional benefit that you can pass data source parameters to it, as illustrated in the following code snippet:

var entityDataSource = new Telerik.Reporting.EntityDataSource();

entityDataSource.Context = typeof(AdventureWorksEntities);
entityDataSource.ContextMember = "GetProducts";
entityDataSource.Parameters.Add("color", typeof(string), "Black");
entityDataSource.Parameters.Add("price", typeof(decimal), 100);

var report = new Report1();

report.DataSource = entityDataSource;

Another common problem is related to the lazy loading feature of the ADO.NET Entity Framework. For example, let us consider the following expression that obtains the category of a given product

=Fields.ProductSubcategory.ProductCategory.Name

The above expression relies upon the built-in lazy loading mechanism to obtain the ProductSubcategory entity for the current Product entity via the corresponding relation property, and then the ProductCategory entity for the current ProductSubcategory entity. While convenient, lazy loading requires additional round-trips to the database for the entities that are not present in memory. If this happens frequently it might significantly impact the performance of the report. To overcome this you can try performing eager loading of the entities instead. For example, the following statement uses the Include method to preload the ProductSubcategory and the ProductCategory entities while retrieving the Product entities:

this.Products.Include("ProductSubcategory").Include("ProductSubcategory.ProductCategory").ToList()

However in certain scenarios eager loading might be costly too. Given the previous example, we materialize all ProductSubcategory and ProductCategory entities only to show the category name of each product. This means a lot of unnecessary data is downloaded from database just to be discarded later. The most flexible and efficient method for retrieving data from the Entity Data Model is to execute a custom query against the entities. The following sample method uses a LINQ query to obtain only the necessary data for the report and then packs it into a collection of POCOs:

public class ReportData
{
    public string CategoryName { get; set; }
    public string SubcategoryName { get; set; }
    public string ProductName { get; set; }
    public decimal ListPrice { get; set; }
}

partial class AdventureWorksEntities
{
    public System.Collections.Generic.List<ReportData> GetProducts(string category, string subcategory)
    {
        var result = from productCategory in this.ProductCategories
                     where productCategory.Name.StartsWith(category)
                     from productSubcategory in productCategory.ProductSubcategories
                     where productSubcategory.Name.StartsWith(subcategory)
                     from product in productSubcategory.Products
                     select new ReportData
                     {
                         CategoryName = productCategory.Name,
                         SubcategoryName = productSubcategory.Name,
                         ProductName = product.Name,
                         ListPrice = product.ListPrice
                     };

        return result.ToList();
    }
}

The sample code that binds the EntityDataSource component to that method is shown here:

var entityDataSource = new Telerik.Reporting.EntityDataSource();

entityDataSource.Context = typeof(AdventureWorksEntities);
entityDataSource.ContextMember = "GetProducts";
entityDataSource.Parameters.Add("category", typeof(string), "Bike");
entityDataSource.Parameters.Add("subcategory", typeof(string), "Road");

var report = new Report1();

report.DataSource = entityDataSource;