Queries

Asp.net Core Mvc Controls Toolkit has the capability to handle queries passed in the query string in OData format, through the MvcControlsToolkit.Core.OData nuget package, that is a dependency of the MvcControlsToolkit.ControlsCore package.
It has also TagHelpers and JavaScript tools to create these queries through user friendly interfaces.
More specifically, the mvcct-odata bower/npm package contains classes to build query trees and then for transforming them either in OData strings or in Javascript functions that apply those queries to JavaScript arrays. While, the mvcct.controls.query.js module of the mvcct-controlsBower/npm package extracts query trees in the mvcct-odata format from user friendly interfaces, and send them to the server.
User friendly interfaces are generated by the query tag helper that generates query windows the user may open with toolbar buttons, and by the query-inline tag helper that generates in-line query forms.

Enabling queries on the server side

  1. Add the following code in your ConfigureServices in Startup.cs:
    services.AddODataQueries();
                
    The AddODataQueries extension method is contained in the MvcControlsToolkit.Core.Extensions namespace and must be placed after your services.AddMvcControlsToolkitControls. It enables the middleware that extracts OData queries from the url.
  2. Decorate all ViewModels properties that must appear in your queries with the QueryAttribute (MvcControlsToolkit.Core.DataAnnotations namespace). The QueryAttribute has properties to specify wich operations to allow on the property. You may use also the FilterLayoutAttribute to specify that a property might have several filter conditions constraining it, and to specify wich filter operations may be selected in each of the conditions.
    public class FoodViewModel
    {
        public int? Id { getset; }
     
        [QueryStringLength(64, MinimumLength = 2) Required, 
            Display(Name ="name")]
        public string ProductName { getset; }
     
        [QueryStringLength(32, MinimumLength = 2), Required,
            Display(Name = "package type")]
        public string Package { getset; }
        [Range(0, 1000), Query,
            Display(Name = "unit price")]
        public decimal UnitPrice { getset; }
        [Display(Name = "discontinued")]
        public bool IsDiscontinued { getset; }
        [Query,
            Display(Name = "supplier")]
        public int SupplierId { getset; }
        [Query,
            Display(Name = "supplier")]
        public string SupplierCompanyName { getset; }
     
    }
  3. In case you need to count occurrences of values in your aggregations, create a grouping ViewModel that inherits from your item ViewModel and that for each property PropertyN whose occurrences might be counted in an aggregation, adds a new property called PropertyNCount.
    [RunTimeType]
    public class FoodViewModelGroupingFoodViewModel
    {
        public int SupplierIdCount { getset; }
        public int PackageCount { getset; }
     
     
    }
    The grouping subclass must be decorated with the RunTimeTypeAttribute as for all subclasses that might substitute the main item ViewModel in any tag helper that allows subclassing (as for instance the grid).
  4. Inject IWebQueryProvider (MvcControlsToolkit.Core.OData namespace) in any controller you would like to enrich with OData queries:
    public class FoodController: 
        ServerCrudController<FoodViewModelFoodViewModelint?>
    {
        public FoodController(FoodRepository repository, 
                IStringLocalizerFactory factory, IHttpContextAccessor accessor, 
                IWebQueryProvider queryProvider) :
            base(factory, accessor)
        {
                
            Repository = repository;
            this.queryProvider = queryProvider;
        }
     
        public IWebQueryProvider queryProvider { getprivate set; }
  5. Get the OData query in your action methods by calling the IWebQueryProvider.Parse method. It needs your item ViewModel in its generic argument:
    var query = queryProvider.Parse<FoodViewModel>();
    The code above yields a QueryDescription object you may use to get all query components in LinQ format.
  6. Call the following methods of the QueryDescription object to get all query components in LinQ format:
    QueryDescription.Page
    To get the current page
    QueryDescription.GetFilterExpression()
    To get the LinQ expression to pass to a LinQ Where method.
    QueryDescription.GetSorting()/QueryDescription.GetSorting<M>()
    To get the current sorting. It returns a function from IQueryable<T> to IOrderedQueryable<T> that you must apply to your IQueryable. In case the query contains grouping, and the ViewModel M used for the grouping items is different from the item ViewModel you must call the overload of GetSorting that has a generic argument for M.
    QueryDescription.GetGrouping()/QueryDescription.GetGrouping<M>()
    To get the current grouping/aggregations. It returns a function from IQueryable<T> to IQueryable<M> that you must apply to your IQueryable. Where M is the ViewModel used fro the grouping items that must be a subclass of the item model T.
    int pg = (int)query.Page;
    var grouping = query.GetGrouping<FoodViewModelGrouping>();
     
    var model = new FoodListViewModel
    {
        Query = query,
        Products =
            grouping == null ?
                await Repository.GetPage(
                    query.GetFilterExpression(),
                    query.GetSorting() ??
                        (q => q.OrderBy(m => m.ProductName)),
                    pg, 5)
                :
                await Repository.GetPageExtended(
                    query.GetFilterExpression(),
                    query.GetSorting<FoodViewModelGrouping>() ??
                        (q => q.OrderBy(m => m.ProductName)),
                    pg, 5,
                    query.GetGrouping<FoodViewModelGrouping>())
    };

    In the example above, since the ViewModel used for the grouping items is different from the items ViewModel, as a first step we verify if the query contains a not null grouping, in wich case we call the overloads of all methods having a generic argument for the grouping ViewModel. Please, notice that also the GetPage method of the ICRUDRepository interface has an overload that accepts the grouping ViewModel.

Adding queries to your Views

Query forms may be associated to toolbars of controls like the grid, or may be rendered in-line in your View. Each query window is specific for filtering, or sorting, or grouping. Below the procedure to add query buttons to a grid:
  1. Add the following attributes to the grid tag helper:
    query-for="Query"
    sorting-clauses="nClauses"
    enable-query="true"
    query-grouping-type="typeof(FoodViewModelGrouping)"

    Where query-grouping-type declares the type of the ViewModel for the grouping items, enable-query enables queries, query-for declares the property containing the current QueryDescription object, and sorting-clauses declares the number of sorting conditions to show in the sorting window.
    query-for is needed to render the grid in grouping mode when the query contains grouping, but it is also inherited by all query buttons contained in toolbars.
  2. If there are grouping windows and the grouping ViewModel differs from the item ViewModel the grid must contain a RowType that declares how to render grid rows when the grid is in grouping mode.
    <row-type asp-for="Products.Data
                .SubInfo<FoodViewModelGrouping>().Model" from-row="0">
        <column asp-for="Products.Data
                .SubElement<FoodViewModelGrouping>().SupplierIdCount" />
        <column asp-for="Products.Data
                .SubElement<FoodViewModelGrouping>().PackageCount" />
    </row-type>

    As shown in the example above, the RowType definitions usually inherits from the grid main RowType (from-row="0").
    Important: When in grouping mode the grid renders just the columns that are involved in grouping and aggregation operations in the current query.
  3. If we would like query windows to appear by clicking query buttons, it is enough to add the required buttons to any grid toolbar:
    <toolbar zone-name="@LayoutStandardPlaces.Header">
        <pager 
                class="pagination pagination-sm"
                max-pages="4"
                page-size-default="5"
                total-pages="Products.TotalPages" />
        &nbsp;
        <query type="Filtering" />
        <query type="Sorting" />
        <query type="Grouping" />
    </toolbar>
    The pager must be configured in query mode. All settings needed by the pager and by the query buttons to render properly the query window are inherited by the grid tag helper, but may be overriden filling their attributes.
  4. If we would like to render some query window in-line, it is enough to place a query-inline tag helper where the query window must appear.
    </grid>
     
    <query-inline type="Filtering" asp-for="Query"
                  collection-for="Products.Data"
                  row-collection-name="export-for-query" />

    Since, in this case information can't be inherited by the grid, the query-inline must be passed the property containing the QueryDescription object, the collection to be queried, and the grid RowType definitions (row-collection-name="export-for-query"), that the grid must export with the attribute:
    rows-cache-key="export-for-query"
    

Fork me on GitHub