How to: Retrieve Data from a Telerik® Data Access Model
This topic discusses various techniques for retrieving data from an Telerik Data Access Model with the help of the OpenAccessDataSource component. The provided examples and code snippets assume an existing Telerik Data Access Model of the Product schema from the Adventure Works sample database.
The most simple approach to extract entities from an Telerik Data Access Model is to bind the OpenAccessDataSource component directly to a property of the model, as shown in the sample code below:
Telerik.Reporting.OpenAccessDataSource openAccessDataSource =
new Telerik.Reporting.OpenAccessDataSource();
openAccessDataSource.ObjectContext = typeof(AdventureWorksEntities);
openAccessDataSource.ObjectContextMember = "Products";
Telerik.Reporting.Report report = new Telerik.Reporting.Report();
report.DataSource = openAccessDataSource;
Dim openAccessDataSource As Telerik.Reporting.OpenAccessDataSource =
New Telerik.Reporting.OpenAccessDataSource()
openAccessDataSource.ObjectContext = GetType(AdventureWorksEntities)
openAccessDataSource.ObjectContextMember = "Products"
Dim report As Telerik.Reporting.Report = New Telerik.Reporting.Report()
report.DataSource = openAccessDataSource
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 OpenAccessContext 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();
}
}
Partial Class AdventureWorksEntities
Public Function GetProducts(ByVal color As String, ByVal price As Decimal) As _
System.Collections.Generic.List(Of Product)
Return Me.Products.Where(Function(product) product.Color = color And _
product.ListPrice <= price).ToList()
End Function
End Class
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:
Telerik.Reporting.OpenAccessDataSource openAccessDataSource =
new Telerik.Reporting.OpenAccessDataSource();
openAccessDataSource.ObjectContext = typeof(AdventureWorksEntities);
openAccessDataSource.ObjectContextMember = "GetProducts";
openAccessDataSource.Parameters.Add("color", typeof(string), "Black");
openAccessDataSource.Parameters.Add("price", typeof(decimal), 100);
Telerik.Reporting.Report report = new Telerik.Reporting.Report();
report.DataSource = openAccessDataSource;
Dim openAccessDataSource As Telerik.Reporting.OpenAccessDataSource =
New Telerik.Reporting.OpenAccessDataSource()
openAccessDataSource.ObjectContext = GetType(AdventureWorksEntities)
openAccessDataSource.ObjectContextMember = "GetProducts"
openAccessDataSource.Parameters.Add("color", GetType(String), "Black")
openAccessDataSource.Parameters.Add("price", GetType(Decimal), 100)
Dim report As Telerik.Reporting.Report = New Telerik.Reporting.Report()
report.DataSource = openAccessDataSource
Another common problem is related to the lazy loading feature of the Telerik Data Access. For example, let's 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 need to query all entities needed for the report at once. In this way lazy loading is completely avoided and all the data is retrieved in a single round-trip from the database. The most flexible and efficient method for retrieving data from the Telerik Data Access 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();
}
}
Public Class ReportData
Public Property CategoryName As String
Public Property SubcategoryName As String
Public Property ProductName As String
Public Property ListPrice As Decimal
End Class
Partial Class AdventureWorksEntities
Public Function GetProducts(ByVal category As String, ByVal subcategory As String) As _
System.Collections.Generic.List(Of ReportData)
Dim result = From productCategory In Me.ProductCategories
Where productCategory.Name.StartsWith(category)
From productSubcategory In productCategory.ProductSubcategories
Where productCategory.Name.StartsWith(subcategory)
From product In productSubcategory.Products
Select New ReportData With
{
.CategoryName = productCategory.Name,
.SubcategoryName = productSubcategory.Name,
.ProductName = product.Name,
.ListPrice = product.ListPrice
}
Return result.ToList()
End Function
End Class
The sample code that binds the OpenAccessDataSource component to that method is shown here:
Telerik.Reporting.OpenAccessDataSource openAccessDataSource =
new Telerik.Reporting.OpenAccessDataSource();
openAccessDataSource.ObjectContext = typeof(AdventureWorksEntities);
openAccessDataSource.ObjectContextMember = "GetProducts";
openAccessDataSource.Parameters.Add("category", typeof(string), "Bike");
openAccessDataSource.Parameters.Add("subcategory", typeof(string), "Road");
Telerik.Reporting.Report report = new Telerik.Reporting.Report();
report.DataSource = openAccessDataSource;
Dim openAccessDataSource As Telerik.Reporting.OpenAccessDataSource =
New Telerik.Reporting.OpenAccessDataSource()
openAccessDataSource.ObjectContext = GetType(AdventureWorksEntities)
openAccessDataSource.ObjectContextMember = "GetProducts"
openAccessDataSource.Parameters.Add("category", GetType(String), "Bike")
openAccessDataSource.Parameters.Add("subcategory", GetType(String), "Road")
Dim report As Telerik.Reporting.Report = New Telerik.Reporting.Report()
report.DataSource = openAccessDataSource