Accessing data with dynamic query can be quite challenging. Crosslight provides a powerful data access component called QueryDescriptor which encapsulate dynamic query information that can be easily passed to server for data retrieval.
The QueryDescriptor component supports any dynamic query format such as OData query, SQLite query and many more.
Understanding Query Descriptor Model
QueryDescriptor stores query information in five different properties as shown in the following figure.
As you can see in the above figure, there are five main components that represent a specific query information.
- The FilterDescriptors property contains a collection of FilterDescriptor that hold filtering information.
- The SortDescriptors property contains a collection of SortDescriptor that hold sorting information.
- The PageDescriptor property contains information about data paging.
- The Includes property contains collection of navigation property names that will be included in the query.
- The Selects property contains collection of select descriptor that will be included in the query.
To filter data, you need to define the filter expression in FilterDescriptors property. There are several type of filters that you can choose from such as follows.
The most basic type of filter descriptor is FilterDescriptor, this model represents a simple conditional query as follows.
This translates to UnitPrice >= 10.
Composite filter descriptor acts as a parentheses for your filter expression. It groups one or more filter expressions with the specific logical filter operator.
This translates to (UnitPrice >= 0 AND UnitPrice <= 50).
CompositeFilterDescriptor may contain another CompositeFilterDescriptor to create a nested grouping. With this capability you can achieve any complex filter expression. For example, let's try creating filter descriptor for the following query.
((CategoryId == 1 Or CategoryID == 5 or CategoryID == 10) AND (UnitInStock >= 10 AND UnitInStock <= 50)) OR (UnitsInOrder >= 100)
AnyFilterDescriptor is a new filter descriptor introduced in Crosslight 4, which allows you to create a dynamic query based on collection property or navigation list property.
Let's say you want to retrieve all customers who buy certain products in the following table structure.
In LINQ you can do something like:
You can achieve this any query using AnyFilterDescriptor as follows.
To perform data sorting, you can define the sort expression in SortDescriptors property. You can apply subsequence sorts by adding another items in SortDescriptors. The following code shows how to sort data using QueryDescriptor.
To retrieve paged data, you define the PageSize and PageIndex property of PageDescriptor property. The following code shows how to page data using QueryDescriptor.
Including Related Entities
When selecting a record, you can also select its related entity by specifying the related navigation property in the Includes property. The following code shows you how to select Customers and includes all its Orders entities.
Selecting Specific Column
By default selecting an entity will retrieve all the columns from that entities. If you only want to select specific column, you can use the SelectDescriptors as follows.
Using SelectDescriptors as ViewProjection
You can also select navigation property using SelectDescriptors and use it as the view projection for your select query. To do this, first you need to create the placeholder property to holds your view projection value. If you're using entity designer extensions, your entity classes are auto-generated classes. To add placeholder property in these classes, you can utilize the partial class capability such as follows:
Now you can use select descriptors to retrieve Product.Category.Name and store it in CategoryName property and also Customers.Orders.Count to TotalOrders property.
QueryDescriptor Data Access
In the above sections, you have learnt the basic concept of query descriptor and how you can construct a dynamic query using the provided API. However, query descriptor is just an intermediary component that encapsulates the query information. For the query descriptor to be useful, it needs a data access end point that can support and process the provided query information.
Currently, there are two data access end points that fully support the query descriptor features such as described in the following sections.
Intersoft WebApi Service
Building on ASP.NET 4.5, Intersoft WebApi Service supports OData v4 protocol for creating and consuming data APIs. In Crosslight 4, Intersoft WebAPI has been dramatically enhanced to support advanced view projection and aggregation features to match all the query capabilities available in query descriptor.
For more information about Intersoft WebApi, see Enterprise Data Access. To learn how to use query descriptor to perform sorting, filtering and paging using Crosslight App Framework, see Using Query Definition.
Serving as the primary local storage for Crosslight apps, Intersoft SQLite supports query descriptor component equally to the WebApi counterpart. This allows you to build complex client-server data apps and consistently performing query without concerning the actual data end point. More importantly, it opens up many advanced capabilities such as introduced in Crosslight Data Synchronization Framework.
The following code snippet shows you a simple example how the query descriptor is leveraged by the SQLite API.
For more information, see Querying SQLite Data.
In addition to Intersoft's proprietary data access components, you can also use the query descriptor component in a LINQ query. The following code example shows you how.
To learn more about query descriptor and see how to implement it with best practice, please check out the advanced data samples here: http://git.intersoftpt.com/projects/CROS-SUPP/repos/view-projection-samples/browse.