Unplanned
Last Updated: 16 Mar 2022 14:07 by Greg
Greg
Created on: 16 Mar 2022 07:38
Category: Grid
Type: Feature Request
2
Grid Support For Any/All OData Filtering on Sub Property Collections

Does the Blazor grid have any support for any or all queries on sub-property collections? I would like to have the grid OnRead be able to generate a query against a sub-entity collection.

E.g.

GET serviceRoot/People?$filter=Emails/any(s:endswith(s, 'contoso.com'))

From what I can tell the Column.FieldName property only will generate a valid query for scalar properties. Is there any way to make this work?

Given the Northwind OData sample https://demos.telerik.com/kendo-ui/service-v4/odata. I would like to display a grid of all customers. In the grid, I would like to have a column that provides filtering for the orders shipper column as in the query below.

https://demos.telerik.com/kendo-ui/service-v4/odata/Customers?$expand=Orders&$filter=Orders/any(d: contains(d/Shipper/CompanyName,'Speedy Express'))

There does not seem to be a way for the in-built filter mechanism to use a lambda and it seems like there should be.

1 comment
Greg
Posted on: 16 Mar 2022 14:07

I would like to propose a solution for this problem by allowing for some custom syntax in the property field and extending the Telerik.Blazor.Extensions.DataSourceExtensions to process that new syntax. Essentially lambda's have an inner and outer part. My suggestion is to allow the property to define the lambda as the inner and outer part with a delimiter in this case a pipe.

For example: Products/any(p: {0})|p.ProductName

This syntax can be extended to handle multiple nested lambdas in the outer pipe. Of course using this syntax would require the column be templated to actually handle the display of the resulting data but I think that is a reasonable trade off.

Here are the proposed changes to the DataSource extensions to support this. I believe this code would be fully backward compatible with the existing implementation and would not constitute a breaking change. Looking forward to the communities thoughts.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using Telerik.DataSource;

namespace Telerik.Blazor.Extensions
{
    /// <summary>
    /// Data source extensions which support custom lambda syntax. This class was adapted from <see cref="Telerik.Blazor.Extensions.DataSourceExtensions"/>.
    /// </summary>
    public static class DataSourceExtensions
    {
        /// <summary>
        /// Serializes a Telerik component data request to a string suitable for OData v4.
        /// <para>You can also use OData lambdas with the following syntax {Outer Expressions {0}|{Inner Expression}. The filter will be applied to the inner expression.</para>
        /// <para>E.g. Products/any(p: {0})|p.ProductName</para>
        /// </summary>
        /// <param name="request">the Telerik DataSourceRequest object you get from the component.</param>
        /// <returns>an OData v4 formatted string with the request parameters.</returns>
        public static string ToODataStringWithLambdaSupport(this DataSourceRequest request)
        {
            var queryString = "$count=true";

            queryString += SerializeFilters(request.Filters);

            queryString += SerializeSorts(request.Sorts);

            queryString += SerializeSkip(request);

            if (request.PageSize > 0)
            {
                queryString += $"&$top={request.PageSize}";
            }

            return EncodeUrl(queryString);
        }

        private static string SerializeFilters(IList<IFilterDescriptor> filters)
        {
            string result = string.Empty;

            if (filters.Count > 0)
            {
                List<string> serializedFilters = new List<string>();

                foreach (var filter in filters)
                {
                    serializedFilters.Add(SerializeFilter(filter));
                }

                result += $"&$filter=({string.Join(" and ", serializedFilters)})";
            }

            return result;
        }

        private static string SerializeFilter(IFilterDescriptor filter)
        {
            if (filter is CompositeFilterDescriptor)
            {
                return SerializeCompositeFilter(filter as CompositeFilterDescriptor);
            }

            return SerializeSingleFilter(filter as FilterDescriptor);
        }

        private static string SerializeCompositeFilter(CompositeFilterDescriptor compositeFilter)
        {
            string logicOperator = compositeFilter.LogicalOperator.ToString().ToLowerInvariant();
            List<string> serializedFilters = new List<string>();

            foreach (var filter in compositeFilter.FilterDescriptors)
            {
                serializedFilters.Add(SerializeFilter(filter));
            }

            return $"({string.Join($" {logicOperator} ", serializedFilters)})";
        }

        private static string SerializeSingleFilter(FilterDescriptor filter)
        {
            var result = string.Empty;

            string filterValue = filter.Value?.ToString() ?? string.Empty;

            string lambdaFilter = null;
            string filterMember;
            if (filter.Member.Contains("|"))
            {
                var split = filter.Member.Split('|', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries);
                Debug.Assert(split.Length == 2);
                lambdaFilter = split[0];
                filterMember = GetMemberName(split[1]);
            }
            else
            {
                filterMember = GetMemberName(filter.Member);
            }

            if (filter.Value != null)
            {
                if (filter.MemberType == null || filter.MemberType.Equals(typeof(string)) || filter.MemberType.IsEnum)
                {
                    filterValue = $"'{filterValue.Replace("'", "''")}'";
                }
                else if (filter.MemberType.Equals(typeof(bool)))
                {
                    filterValue = filterValue.ToLowerInvariant();
                }
                else if (filter.MemberType.Equals(typeof(DateTime)) || filter.MemberType.Equals(typeof(DateTimeOffset)))
                {
                    filterValue = DateTime.SpecifyKind((DateTime)filter.Value, DateTimeKind.Utc).ToString("o");
                }
            }

            switch (filter.Operator)
            {
                case FilterOperator.IsLessThan:
                    result = $"{filterMember} lt {filterValue}";
                    break;
                case FilterOperator.IsLessThanOrEqualTo:
                    result = $"{filterMember} le {filterValue}";
                    break;
                case FilterOperator.IsEqualTo:
                    result = $"{filterMember} eq {filterValue}";
                    break;
                case FilterOperator.IsNotEqualTo:
                    result = $"{filterMember} ne {filterValue}";
                    break;
                case FilterOperator.IsGreaterThanOrEqualTo:
                    result = $"{filterMember} ge {filterValue}";
                    break;
                case FilterOperator.IsGreaterThan:
                    result = $"{filterMember} gt {filterValue}";
                    break;
                case FilterOperator.StartsWith:
                    result = $"startswith({filterMember},{filterValue})";
                    break;
                case FilterOperator.EndsWith:
                    result = $"endswith({filterMember},{filterValue})";
                    break;
                case FilterOperator.Contains:
                    result = $"contains({filterMember},{filterValue})";
                    break;
                case FilterOperator.IsContainedIn:
                    result = $"contains({filterValue},'{filterMember}')";
                    break;
                case FilterOperator.DoesNotContain:
                    result = $"indexof({filterMember},{filterValue}) eq -1";
                    break;
                case FilterOperator.IsNull:
                    result = $"{filterMember} eq null";
                    break;
                case FilterOperator.IsNotNull:
                    result = $"{filterMember} ne null";
                    break;
                case FilterOperator.IsEmpty:
                    result = $"{filterMember} eq ''";
                    break;
                case FilterOperator.IsNotEmpty:
                    result = $"{filterMember} ne ''";
                    break;
                case FilterOperator.IsNullOrEmpty:
                    result = $"({filterMember} eq null or {filterMember} eq '')";
                    break;
                case FilterOperator.IsNotNullOrEmpty:
                    result = $"({filterMember} ne null and {filterMember} ne '')";
                    break;
                default:
                    break;
            }

            if (!string.IsNullOrEmpty(lambdaFilter))
            {
                return string.Format(lambdaFilter, result);
            }

            return result;
        }

        private static string SerializeSorts(IList<SortDescriptor> sorts)
        {
            var result = string.Empty;

            if (sorts.Count > 0)
            {
                var serializedSorts = new List<string>();

                foreach (var sortDescriptor in sorts)
                {
                    var sortDirection = sortDescriptor.SortDirection == ListSortDirection.Ascending ? string.Empty : " desc";
                    var memberName = GetMemberName(sortDescriptor.Member);
                    serializedSorts.Add($"{memberName}{sortDirection}");
                }

                result = $"&$orderby={string.Join(',', serializedSorts)}";
            }

            return result;
        }

        private static string SerializeSkip(DataSourceRequest request)
        {
            string result = string.Empty;

            if (request.Skip != 0)
            {
                result += $"&$skip={Math.Max(request.Skip, 0)}";
            }
            else if (request.Page > 0)
            {
                var skip = (request.Page - 1) * request.PageSize;

                result += $"&$skip={skip}";
            }

            return result;
        }

        private static string EncodeUrl(string url)
        {
            return url.Replace(" ", "%20").Replace("'", "%27");
        }

        private static string GetMemberName(string member)
        {
            return member.Replace(".", "/");
        }
    }
}