How to: Use Projections to Create Custom Queries and Optimize Performance
- 13 minutes to read
Entity Framework provides the DbSet.Local property that can be used to bind a visual control to data. However, use of this property is limited:
- You cannot specify which properties are loaded to the client if you don’t need most of them in your UI and wish to optimize the query execution time.
- You cannot add custom calculated properties to the client data. This properties should be executed on the server side to avoid loading excessive data to the client.
Collection view models generated by the Scaffolding Wizard allow using custom queries for CRUD operations. With projections, you can use all the power of the System.Linq.Queryable class and LINQ Queries in the application generated via the Scaffolding Wizard.
This tutorial describes how to use projections to solve the following tasks:
- change order and filter items that are loaded to the client;
- include navigation properties to the items that are loaded to the client;
- use custom projection types to specify which properties are loaded to the client to optimize performance;
- saving changes when custom projection type is used;
- add properties that are calculated on the server;
- use additional queries to load necessary data on demand;
- use projections in detail collection view models.
The DevExpress.OutlookInspiredApp and DevExpress.HybridApp projects from the Building Outlook Inspired and Hybrid Applications tutorial are used in this tutorial.
Step 1 - Projections Mechanism Overview
In general, a projection is a function that converts an IRepositoryQuery object parameterized by an entity type to an IQueryable object parameterized by the same type, or another type:
Func<IRepositoryQuery<TEntity>, IQueryable<TEntity>> projection
or
Func<IRepositoryQuery<TEntity>, IQueryable<TProjection>> projection
The IRepositoryQuery interface is an IQueryable descendant that allows you to include navigation properties in the query. It also provides the Where method that is used by the internal view model infrastructure instead of the IQueryable.Where method:
public interface IRepositoryQuery<T> : IQueryable<T> {
IRepositoryQuery<T> Include<TProperty>(Expression<Func<T, TProperty>> path);
IRepositoryQuery<T> Where(Expression<Func<T, bool>> predicate);
}
The CollectionViewModel class accepts projection as one of its constructor parameters. This is an optional parameter, so there is no need to specify projections in simple cases.
public partial class CollectionViewModel<TEntity, TPrimaryKey, TUnitOfWork> : CollectionViewModel<TEntity,
TEntity, TPrimaryKey, TUnitOfWork>
where TEntity : class
where TUnitOfWork : IUnitOfWork {
protected CollectionViewModel(
IUnitOfWorkFactory<TUnitOfWork> unitOfWorkFactory,
Func<TUnitOfWork, IRepository<TEntity, TPrimaryKey>> getRepositoryFunc,
Func<IRepositoryQuery<TEntity>, IQueryable<TEntity>> projection = null,
Action<TEntity> newEntityInitializer = null,
bool ignoreSelectEntityMessage = false
) : base(unitOfWorkFactory, getRepositoryFunc, projection, newEntityInitializer, ignoreSelectEntityMessage) {
}
}
There is also a version of the CollectionViewModel class that can be parameterized by a separate projection type:
public partial class CollectionViewModel<TEntity, TProjection, TPrimaryKey, TUnitOfWork> :
CollectionViewModelBase<TEntity, TProjection, TPrimaryKey, TUnitOfWork>
where TEntity : class
where TProjection : class
where TUnitOfWork : IUnitOfWork {
protected CollectionViewModel(
IUnitOfWorkFactory<TUnitOfWork> unitOfWorkFactory,
Func<TUnitOfWork, IRepository<TEntity, TPrimaryKey>> getRepositoryFunc,
Func<IRepositoryQuery<TEntity>, IQueryable<TProjection>> projection,
Action<TEntity> newEntityInitializer = null,
bool ignoreSelectEntityMessage = false
) : base(unitOfWorkFactory, getRepositoryFunc, projection, newEntityInitializer, ignoreSelectEntityMessage) {
}
}
The CollectionViewModel class uses a projection to transform a query before using it to query data from the server.
Step 2 - Change Order and Filter Items Loaded to the Client
The ProductCollectionViewModel class from the DevExpress.OutlookInspiredApp project uses projections to order items by the Category property value:
Note
To learn more about using projections for sorting data, see the Step 4 of the Customize layout of the collection views tutorial.
protected ProductCollectionViewModel(IUnitOfWorkFactory<IDevAVDbUnitOfWork> unitOfWorkFactory = null)
: base(unitOfWorkFactory ?? UnitOfWorkSource.GetUnitOfWorkFactory(), x => x.Products, query => query.OrderBy(x => x.Category)) {
}
As an another example, you can filter items that will be loaded to the client:
projection: query => query.OrderBy(x => x.InvoiceNumber).Where(x => x.OrderDate > new DateTime(2014, 1, 1))
Step 3 - Include Navigation Properties to the Items Loaded to the Client
The Step 5 - Optimizing the Data Query from the Customize layout of the collection views lesson describes how to diagnose problems related to lazy navigation properties loading and include them in the data query.
In short, use the IRepositoryQuery.Include method to specify which navigation properties should be loaded to the client when the query is executed (DevExpress.OutlookInspiredApp project):
protected OrderCollectionViewModel(IUnitOfWorkFactory<IDevAVDbUnitOfWork> unitOfWorkFactory = null)
: base(unitOfWorkFactory ?? UnitOfWorkSource.GetUnitOfWorkFactory(), x => x.Orders, query => query.Include(x => x.Store).Include(x => x.Customer).OrderBy(x => x.InvoiceNumber)) {
}
Step 4 - Using Custom projection Types to Specify Which Properties are Loaded to the Client to Optimize the Performance
By default, all data properties are loaded to the client even if the client uses only a few of them in the UI. You may significantly reduce the query execution time by creating a custom projection type that contains only necessary properties.
The Step 2 - Create custom data query from the Show sparkline charts in the grid cells lesson describes how to solve this problem in detail (the CustomerCollectionViewModel class from the DevExpress.HybridApp project).
Create a custom type (CustomerInfo) that contain only necessary properties (it should also contain the primary key property from the original entity with the same name) and a helper method that creates the IQueryable<CustomerInfo> projection based on IQueryable<Customer>:
public class CustomerInfo {
public long Id { get; set; }
public string Name { get; set; }
public string AddressLine { get; set; }
}
public static class QueriesHelper {
public static IQueryable<CustomerInfo> GetCustomerInfo(IQueryable<Customer> customers) {
return customers.Select(x => new CustomerInfo
{
Id = x.Id,
Name = x.Name,
AddressLine = x.AddressLine,
});
}
}
To make the CustomerCollectionViewModel work with CustomerInfo as a projection type, specify CustomerInfo as an additional generic parameter in a base class and use the GetCustomerInfo helper method in a base constructor invocation:
public partial class CustomerCollectionViewModel : CollectionViewModel<Customer, CustomerInfo, long, IDevAVDbUnitOfWork> {
//...
protected CustomerCollectionViewModel(IUnitOfWorkFactory<IDevAVDbUnitOfWork> unitOfWorkFactory = null)
: base(unitOfWorkFactory ?? UnitOfWorkSource.GetUnitOfWorkFactory(), x => x.Customers, query => QueriesHelper.GetCustomerInfo(query)) {
}
}
Step 5 - Saving Changes When Custom Projection Type is Used
Collection view model can save changes made to the entity and notify other view models that the entity has been changed. The common scenario of using this feature is enabling the inplace editing feature for the GridControl inside the collection view. For more information, see Step 5 - Implementing Interaction section in the Creating a Table View tutorial.
When using the custom projection mechanism, you should manually apply properties of the modified projection object to the original entity, so it can be saved to the database:
protected override void ApplyProjectionPropertiesToEntity(CustomerInfo projectionEntity, Customer entity) {
entity.Name = projectionEntity.Name;
entity.AddressLine = projectionEntity.AddressLine;
entity.AddressCity = projectionEntity.AddressCity;
entity.AddressState = projectionEntity.AddressState;
entity.AddressZipCode = projectionEntity.AddressZipCode;
entity.Phone = projectionEntity.Phone;
entity.Fax = projectionEntity.Fax;
}
If the ApplyProjectionPropertiesToEntity method has not been overridden, the NotImplementedException will be thrown in runtime on an attempt to call SaveCommand for the collection view model that uses projections.
Step 6 - Adding Properties that are Calculated on the Server
To learn more, see the Step 2 - Create custom data query from the Show sparkline charts in the grid cells lesson (the CustomerCollectionViewModel class from the DevExpress.HybridApp project).
Add the following properties to the CustomerInfo class created in the previous step.
public decimal? TotalSales { get; set; }
public IEnumerable<decimal> MonthlySales { get; set; }
Assign these properties in the QueriesHelper.GetCustomerInfo method.
TotalSales = x.Orders.Sum(orderItem => orderItem.TotalAmount),
MonthlySales = x.Orders.GroupBy(o => o.OrderDate.Month).Select(g => g.Sum(i => i.TotalAmount)),
Step 7 - Using Additional Queries to Load Necessary Data on Demand
To learn more, see the Step 5 - Adding Lazy Detail Collection to the Projection Type from the Show sparkline charts in the grid cells lesson (the CustomerCollectionViewModel class from the DevExpress.HybridApp project).
Assume that you want to show customer stores for the selected customer under the grid. If you use the original Customer object in the CustomerCollectionViewModel class, the solution is straightforward: just bind to the Customer.CustomerStores navigation collection. This is a lazy property, meaning that stores for the customer are loaded only when the customer is selected in the grid and the CustomerStores property for this customer is accessed.
To implement lazy loading when using the custom projection type, add the lazy CustomerInfo.CustomerStores property.
public class CustomerInfo {
...
Lazy<IEnumerable<CustomerStore>> customerStores;
public IEnumerable<CustomerStore> CustomerStores { get { return customerStores.Value; } }
public void SetDeferredStores(Func<IEnumerable<CustomerStore>> getStores) {
this.customerStores = new Lazy<IEnumerable<CustomerStore>>(getStores);
}
}
Provide a method that assigns a function that can be used to add customer stores to the collection of CustomerInfo objects.
public static class QueriesHelper {
...
public static void UpdateCustomerInfoStores(IEnumerable<CustomerInfo> entities, IQueryable<Customer> customers) {
foreach(var item in entities) {
item.SetDeferredStores(() => customers.First(x => x.Id == item.Id).CustomerStores.ToArray());
}
}
}
The CollectionViewModel class provides a special virtual method that is called when a single entity, or many entities are loaded from the database.
partial class CustomerCollectionViewModel {
protected override void OnEntitiesLoaded(IDevAVDbUnitOfWork unitOfWork, IEnumerable<CustomerInfo> entities) {
base.OnEntitiesLoaded(unitOfWork, entities);
QueriesHelper.UpdateCustomerInfoStores(entities, unitOfWork.Customers);
}
}
Now, you can use the CustomerInfo.CustomerStores collection in the UI and it will be loaded on demand.
Step 8 - Using Projections in Detail Collection View Models and Lookup Collections
Detail collection view models are used to edit detail navigation collection in single object editing forms. For example, add or remove tasks from the Employee.AssignedTasks collection.
Lookup collections models are used to edit detail entity in the single object editing forms and choose a master object for it, for example, to select the employee to which the task is assigned.
All approaches that have been described above can be used with detail collection view models and lookup collections.
For example, you want to use a custom projection type for the EmployeeViewModel.EmployeeAssignedTasksDetails detail collection property.
public partial class EmployeeViewModel : SingleObjectViewModel<Employee, long, IDevAVDbUnitOfWork> {
//...
public CollectionViewModel<EmployeeTask, long, IDevAVDbUnitOfWork> EmployeeAssignedTasksDetails {
get { return GetDetailsCollectionViewModel((EmployeeViewModel x) => x.EmployeeAssignedTasksDetails, x => x.Tasks, x => x.AssignedEmployeeId, (x, key) => x.AssignedEmployeeId = key); }
}
}
Create a custom projection type for EmployeeTask.
public class EmployeeTaskInfo {
public static IQueryable<EmployeeTaskInfo> GetEmployeeTaskInfo(IQueryable<EmployeeTask> query) {
return query.Select(x => new EmployeeTaskInfo {
Id = x.Id,
Subject = x.Subject,
});
}
public long Id { get; set; }
public string Subject { get; set; }
}
Use this type in the EmployeeViewModel.EmployeeAssignedTasksDetails property.
public CollectionViewModel<EmployeeTask, EmployeeTaskInfo, long, IDevAVDbUnitOfWork> EmployeeAssignedTasksDetails {
get { return GetDetailProjectionsCollectionViewModel((EmployeeViewModel x) => x.EmployeeAssignedTasksDetails, x => x.Tasks, x => x.AssignedEmployeeId,
(x, key) => x.AssignedEmployeeId = key, projection: query => EmployeeTaskInfo.GetEmployeeTaskInfo(query)); }
}
Apply a custom projection type to for the EmployeeTaskViewModel.LookUpEmployees lookup collection (DevExpress.HybridApp).
public partial class EmployeeTaskViewModel : SingleObjectViewModel<EmployeeTask, long, IDevAVDbUnitOfWork> {
//...
public IEntitiesViewModel<Employee> LookUpEmployees {
get { return GetLookUpEntitiesViewModel((EmployeeTaskViewModel x) => x.LookUpEmployees, x => x.Employees); }
}
}
Create a custom projection type for Employee.
public class EmployeeInfo {
public static IQueryable<EmployeeInfo> GetEmployeeInfo(IQueryable<Employee> query) {
return query.Select(x => new EmployeeInfo {
Id = x.Id,
FirstName = x.FirstName,
});
}
public long Id { get; set; }
public string FirstName { get; set; }
}
Use this type in the EmployeeTaskViewModel.LookUpEmployees property.