[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Collapse]WPF Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Expand]MVVM Framework
 [Expand]Controls and Libraries
 [Collapse]Scaffolding Wizard
   Getting Started
  [Expand]Overview
  [Expand]Data Access Layer
   UI Generation
  [Collapse]Examples
    How to: Create a Custom Filter
    How to: Add a New Detail Collection
    How to: Show Validation Errors when IDataErrorInfo is Used
    How to: Show Detail Views in Separate Windows
    How to: Implement Many-to-many Relations Editing
    Entity Names Autogeneration
    How to: Use Projections to Create Custom Queries and Optimize Performance
    How To: Improve Performance by Using the Instant Feedback Mode
  [Expand]Tutorials
 [Expand]Localization
  Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

How to: Use Projections to Create Custom Queries and Optimize Performance

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.

Expanded 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:

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.

There is also a version of the CollectionViewModel class that can be parameterized by a separate projection type:

The CollectionViewModel class uses a projection to transform a query before using it to query data from the server.

Expanded 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.

As an another example, you can filter items that will be loaded to the client:

Expanded 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):

Expanded 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>:

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:

Expanded 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:

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.

Expanded 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.

Assign these properties in the QueriesHelper.GetCustomerInfo method.

Expanded 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.

Provide a method that assigns a function that can be used to add customer stores to the collection of CustomerInfo objects.

The CollectionViewModel class provides a special virtual method that is called when a single entity, or many entities are loaded from the database.

Now, you can use the CustomerInfo.CustomerStores collection in the UI and it will be loaded on demand.

Expanded 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.

Create a custom projection type for EmployeeTask.

Use this type in the EmployeeViewModel.EmployeeAssignedTasksDetails property.

Apply a custom projection type to for the EmployeeTaskViewModel.LookUpEmployees lookup collection (DevExpress.HybridApp).

Create a custom projection type for Employee.

Use this type in the EmployeeTaskViewModel.LookUpEmployees property.

Is this topic helpful?​​​​​​​