Linq2DB (SQL Server): chaining Sql.Property calls dynamically

Question

I am using the examples from this Pull Request as a reference for nested property chains. Unfortunately something like

Sql.Property<object>(x, "SomeClass.SomeProperty") 

doesn't work.

Say I have a model, SortDescriptor (defined below), which defines how we sort items in our code dynamically. Is there a way to dynamically create an expression tree to generate nested Sql.Property calls, like in the ToPropertyExpression method shown below?

The following SQL should be output from the SortBy function if given the following filter:

ORDER BY Category.EntityId, StackRank, Id

(Category is a navigation property on CategoryProperty)

        var filter1 = new PagedFilter
        {
            Take = 25,
            Sort = new List<SortDescriptor>
            {
                new SortDescriptor { Selector = "Category.EntityId" },
                new SortDescriptor { Selector = "StackRank" },
                new SortDescriptor { Selector = "Id" },
            }
        };

Below are the functions and objects I am using:

public class CategoryPropertyRepository 
{
    public async Task<long> GetPageIndexById(PagedFilter filter, Guid id)
    {
        var entity = await DbContext.Set<Entities.CategoryProperty>()
            .Select(x => new
            {
                x.Id,
                RowNumber = Sql.Ext.RowNumber().Over().SortBy(x, filter.Sort).ToValue()
            }).FirstOrDefaultAsyncLinqToDB(x => x.Id == id);
        var rowNumber = entity.RowNumber;

        return rowNumber / filter.Take.Value;
    }
}

public static class IOrderExtensions
{
    [Sql.Extension("ORDER BY {entity}{filter}", TokenName = "order_by_clause", ServerSideOnly = true, BuilderType = typeof(SortByBuilder))]
    public static AnalyticFunctions.IOrderedReadyToFunction<T> SortBy<T, TEntity>(
        this AnalyticFunctions.IOverMayHavePartitionAndOrder<T> over, TEntity entity, IPagedFilter filter) => throw new InvalidOperationException("SortBy is server-side only.");

    public class SortByBuilder : Sql.IExtensionCallBuilder
    {
        public void Build(Sql.ISqExtensionBuilder builder)
        {
            var entity = builder.Arguments[1];
            var filter = builder.GetValue<IPagedFilter>("filter");
            var index = 0;
            var expression = $"ORDER BY {string.Join(", ", filter.Sort.Select(x => $"{{{index++}}}{(x.Descending ? " DESC" : string.Empty)}"))}";

            List<ISqlExpression> parameters = new List<ISqlExpression>();

            foreach (var sort in filter.Sort)
            {
                var sqlExpr = builder.ConvertExpressionToSql(sort.ToPropertyExpression(entity));
                parameters.Add(sqlExpr);
            }

            builder.ResultExpression = new SqlExpression(expression, Precedence.Primary, parameters.ToArray());
        }
    }

    public static Expression ToPropertyExpression(this SortDescriptor sort, object entity)
    {
        var nameParts = sort.Selector.Split('.');

        // x.SomeClass.SomeProperty should yield something like Sql.Property<object>(Sql.Property<object>(x, "SomeClass"), "SomeProperty);
        var propertyMethod = typeof(Sql).GetMethod("Property", BindingFlags.Public | BindingFlags.Static);
        propertyMethod = propertyMethod!.MakeGenericMethod(typeof(object));
        Expression exp = null;

        for (int i = nameParts.Length - 1; i >= 0; i--)
        {
            exp = Expression.Call(null, propertyMethod, Expression.Constant(exp ?? entity),
                Expression.Constant(nameParts[i]));
        }

        return exp;
    }
}

public class PagedFilter : IPagedFilter
{
    public virtual int? Skip { get; set; }
    public virtual int? Take { get; set; }
    public virtual IList<SortDescriptor> Sort { get; set; }
}

public class SortDescriptor
{
    public string Selector { get; set; }
    public bool Descending { get; set; }
}

Whenever I try to execute something similar to what's above, I get this error message saying that it's trying to evaluate Sql.Property expression chain on the client side:

Exception message:
LinqToDB.Linq.LinqException: 'Property' is only server-side method.

Stack trace:
at LinqToDB.Sql.Property[T](Object entity, String propertyName)
at LinqToDB.Linq.QueryRunner.SetParameters(Query query, Expression expression, IDataContext parametersContext, Object[] parameters, Int32 queryNumber, SqlParameterValues parameterValues)
at LinqToDB.Linq.QueryRunnerBase.SetCommand(Boolean clearQueryHints)
at LinqToDB.Data.DataConnection.QueryRunner.<>n__0(Boolean clearQueryHints)
at LinqToDB.Data.DataConnection.QueryRunner.ExecuteReaderAsync(CancellationToken cancellationToken)
at LinqToDB.Linq.QueryRunner.AsyncEnumeratorImpl1.MoveNextAsync() at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at LinqToDB.AsyncExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken token)
at Experlogix.Api.DesignStudio.DataAccess.CategoryPropertyRepository.GetPageIndexById(CategoryPropertyFilter filter, Guid id) in D:\Dev\repos\Experlogix.Api.DesignStudio\src\Experlogix.Api.DesignStudio.DataAccess\CategoryPropertyRepository.cs:line 116
at Experlogix.Core.DataAccess.Service6.GetPageIndexById(TFilter filter, TId id) at Experlogix.Api.DesignStudio.Controllers.CategoryPropertyController.GetPageIndexAsync(Guid id, CategoryPropertyFilter filter) in D:\Dev\repos\Experlogix.Api.DesignStudio\src\Experlogix.Api.DesignStudio\Controllers\CategoryPropertyController.cs:line 46 at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Experlogix.Api.DesignStudio.Middleware.DatabaseConnectionMiddleware.Invoke(HttpContext httpContext, IUserContext userContext, IConnectionContext connectionContext, IDatabaseClient databaseClient) in D:\Dev\repos\Experlogix.Api.DesignStudio\src\Experlogix.Api.DesignStudio\Middleware\DatabaseConnectionMiddleware.cs:line 65
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Serilog.AspNetCore.RequestLoggingMiddleware.Invoke(HttpContext httpContext)
at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
at Experlogix.Core.AspNet.Middleware.ApiExceptionHandlerMiddleware.Invoke(HttpContext context)

Any guidance on how something similar to this can be achieved would be greatly appreciated!

Environment details

• linq2db version: linq2db.EntityFrameworkCore 5.1.0
• Database server: SQL Server
• Operating system: Windows 10
• .NET Framework: .Net 5.0

Answer 1

I was able to get the query to work as desired by changing ToPropertyExpression like so:

    public static Expression ToPropertyExpression(this SortDescriptor sort, Expression entity)
    {
        return sort.Selector.Split('.').Aggregate(entity, Expression.Property);
    }

All of the other code remained the same, and produced the following SQL Query:

SELECT
    [f].[Id],
    ROW_NUMBER() OVER(ORDER BY [a_Category].[EntityId], [f].[StackRank], [f].[Id])
FROM
    [ds].[CategoryProperty] [f]
        INNER JOIN [ds].[Category] [a_Category] ON [f].[CategoryId] = [a_Category].[Id]

Answer 2

I'm proposing more general solution for pagination. It can handle any query and build correct SQL. Also it can return TotalCount with single roundtrip to databse.

Implementation is not trivial but can be used as reference source how to work with Expression Tree.

Example of usage:

var pageSize = 20;

var query = table.Where(x => x.Id % 2 == 0).OrderBy(x => x.Id).ThenByDescending(x => x.Value);
var pagination1 = query.Paginate(1, pageSize);
var pagination2 = query.Paginate(2, pageSize, true); // with total count

// extensions which accepts predicate to find page. Query must be ordered.
var byKey = query.GetPageByCondition(pageSize, x => x.Id == someId);

// returns page number. Query must be ordered.
var pageNumber = query.GetPageNumberByCondition(pageSize, x => x.Id == someId);

// dynamic ordering
var query = table.Where(x => x.Id % 2 == 0)
   .ApplyOrderBy(new []{Tuple.Create("Id", false), Tuple.Create("Value", true)});

Implementation:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Threading;
using System.Threading.Tasks;
using LinqToDB;
using LinqToDB.Async;
using LinqToDB.Expressions;

namespace Tests.Playground
{
    public static class PaginationExtensions
    {
        public class PaginationResult<T>
        {
            public PaginationResult(int totalCount, int page, int pageSize, List<T> items)
            {
                TotalCount = totalCount;
                Page = page;
                PageSize = pageSize;
                Items = items;
            }

            public int TotalCount { get; }
            public List<T> Items { get; }
            public int Page { get; }
            public int PageSize { get; }
        }

        public static PaginationResult<T> Paginate<T>(this IQueryable<T> query, int page, int pageSize, bool includeTotalCount = false)
        {
            return ProcessPaginationResult(EnvelopeQuery(query, page, pageSize, includeTotalCount), pageSize);
        }

        public static Task<PaginationResult<T>> PaginateAsync<T>(this IQueryable<T> query, int page, int pageSize, bool includeTotalCount = false, CancellationToken cancellationToken = default)
        {
            return ProcessPaginationResultAsync(EnvelopeQuery(query, page, pageSize, includeTotalCount), pageSize, cancellationToken);
        }

        public static Expression ApplyOrderBy(Type entityType, Expression queryExpr, IEnumerable<Tuple<string, bool>> order)
        {
            var param = Expression.Parameter(entityType, "e");
            var isFirst = true;
            foreach (var tuple in order)
            {
                var lambda = Expression.Lambda(MakePropPath(param, tuple.Item1), param);
                var methodName =
                    isFirst ? tuple.Item2 ? nameof(Queryable.OrderByDescending) : nameof(Queryable.OrderBy)
                    : tuple.Item2 ? nameof(Queryable.ThenByDescending) : nameof(Queryable.ThenBy);

                queryExpr = Expression.Call(typeof(Queryable), methodName, new[] { entityType, lambda.Body.Type }, queryExpr, lambda);
                isFirst = false;
            }

            return queryExpr;
        }

        public static PaginationResult<T> GetPageByCondition<T>(this IQueryable<T> query, int pageSize,
            Expression<Func<T, bool>> predicate, bool includeTotal = false)
        {
            return ProcessPaginationResult(GetPageByConditionInternal(query, pageSize, predicate, includeTotal), pageSize);
        }

        public static int GetPageNumberByCondition<T>(this IQueryable<T> query, int pageSize,
            Expression<Func<T, bool>> predicate, bool includeTotal = false)
        {
            return GetPageNumberByConditionInternal(query, pageSize, predicate, includeTotal).FirstOrDefault();
        }

        public static Task<int> GetPageNumberByConditionAsync<T>(this IQueryable<T> query, int pageSize,
            Expression<Func<T, bool>> predicate, bool includeTotal = false, CancellationToken cancellationToken = default)
        {
            return GetPageNumberByConditionInternal(query, pageSize, predicate, includeTotal).FirstOrDefaultAsync(cancellationToken);
        }

        public static Task<PaginationResult<T>> GetPageByConditionAsync<T>(this IQueryable<T> query, int pageSize,
            Expression<Func<T, bool>> predicate, bool includeTotal = false, CancellationToken cancellationToken = default)
        {
            return ProcessPaginationResultAsync(GetPageByConditionInternal(query, pageSize, predicate, includeTotal), pageSize, cancellationToken);
        }

        public static IQueryable<T> ApplyOrderBy<T>(this IQueryable<T> query, IEnumerable<Tuple<string, bool>> order)
        {
            var expr = ApplyOrderBy(typeof(T), query.Expression, order);
            return query.Provider.CreateQuery<T>(expr);
        }

        #region Helpers

        static Expression? Unwrap(Expression? ex)
        {
            if (ex == null)
                return null;

            switch (ex.NodeType)
            {
                case ExpressionType.Quote:
                case ExpressionType.ConvertChecked:
                case ExpressionType.Convert:
                    return ((UnaryExpression)ex).Operand.Unwrap();
            }

            return ex;
        }

        static MethodInfo? FindMethodInfoInType(Type type, string methodName, int paramCount)
        {
            var method = type.GetRuntimeMethods()
            .FirstOrDefault(m => m.Name == methodName && m.GetParameters().Length == paramCount);
            return method;
        }

        static MethodInfo FindMethodInfo(Type type, string methodName, int paramCount)
        {
            var method = FindMethodInfoInType(type, methodName, paramCount);

            if (method != null)
                return method;

            method = type.GetInterfaces().Select(it => FindMethodInfoInType(it, methodName, paramCount))
                .FirstOrDefault(m => m != null);

            if (method == null)
                throw new Exception($"Method '{methodName}' not found in type '{type.Name}'.");

            return method;
        }


        static Expression ExtractOrderByPart(Expression query, List<Tuple<Expression, bool>> orderBy)
        {
            var current = query;
            while (current.NodeType == ExpressionType.Call)
            {
                var mc = (MethodCallExpression)current;
                if (typeof(Queryable) == mc.Method.DeclaringType)
                {
                    var supported = true;
                    switch (mc.Method.Name)
                    {
                        case "OrderBy":
                        case "ThenBy":
                        {
                            orderBy.Add(Tuple.Create(mc.Arguments[1], false));
                            break;
                        }
                        case "OrderByDescending":
                        case "ThenByDescending":
                        {
                            orderBy.Add(Tuple.Create(mc.Arguments[1], true));
                            break;
                        }
                        default:
                            supported = false;
                            break;
                    }
                    if (!supported)
                        break;

                    current = mc.Arguments[0];
                }
                else
                    break;
            }

            return current;
        }

        static Expression FinalizeFunction(Expression functionBody)
        {
            var toValueMethodInfo = FindMethodInfo(functionBody.Type, "ToValue", 0);
            functionBody = Expression.Call(functionBody, toValueMethodInfo);
            return functionBody;
        }

        static Expression GenerateOrderBy(Expression entity, Expression functionBody, List<Tuple<Expression, bool>> orderBy)
        {
            var isFirst = true;

            for (int i = orderBy.Count - 1; i >= 0; i--)
            {
                var order = orderBy[i];
                string methodName;
                if (order.Item2)
                    methodName = isFirst ? "OrderByDesc" : "ThenByDesc";
                else
                    methodName = isFirst ? "OrderBy" : "ThenBy";
                isFirst = false;

                var currentType = functionBody.Type;
                var methodInfo = FindMethodInfo(currentType, methodName, 1).GetGenericMethodDefinition();

                var arg = ((LambdaExpression)Unwrap(order.Item1)!).GetBody(entity);

                functionBody = Expression.Call(functionBody, methodInfo.MakeGenericMethod(arg.Type), arg);
            }

            return functionBody;
        }

        static Expression GeneratePartitionBy(Expression functionBody, Expression[] partitionBy)
        {
            if (partitionBy.Length == 0)
                return functionBody;

            var method = FindMethodInfo(functionBody.Type, "PartitionBy", 1);

            var partitionsExpr = Expression.NewArrayInit(typeof(object), partitionBy);

            var call = Expression.Call(functionBody, method, partitionsExpr);

            return call;
        }

        static Expression MakePropPath(Expression objExpression, string path)
        {
            return path.Split('.').Aggregate(objExpression, Expression.PropertyOrField);
        }

        private class Envelope<T>
        {
            public int TotalCount { get; set; }
            public T Data { get; set; } = default!;
            public int Page { get; set; }
        }

        static IQueryable<Envelope<T>> EnvelopeQuery<T>(IQueryable<T> query, int page, int pageSize, bool includeTotalCount)
        {
            var withCount = includeTotalCount
                ? query.Select(q =>
                    new Envelope<T> {TotalCount = Sql.Ext.Count().Over().ToValue(), Page = page, Data = q})
                : query.Select(q => new Envelope<T> {TotalCount = -1, Page = page, Data = q});

            return withCount.Skip((page - 1) * pageSize).Take(pageSize);
        }

        static PaginationResult<T> ProcessPaginationResult<T>(IQueryable<Envelope<T>> query, int pageSize)
        {
            int totalRecords;
            int page = 0;

            using (var enumerator = query.GetEnumerator())
            {
                List<T> result;
                if (!enumerator.MoveNext())
                {
                    totalRecords = 0;
                    result = new List<T>();
                }
                else
                {
                    totalRecords = enumerator.Current.TotalCount;
                    page = enumerator.Current.Page;
                    result = new List<T>(pageSize);
                    do
                    {
                        result.Add(enumerator.Current.Data);
                    } while (enumerator.MoveNext());
                }

                return new PaginationResult<T>(totalRecords, page, pageSize, result);
            }
        }

        static async Task<PaginationResult<T>> ProcessPaginationResultAsync<T>(IQueryable<Envelope<T>> query, int pageSize, CancellationToken cancellationToken)
        {
            var items = query.AsAsyncEnumerable();
            int totalRecords;
            int page = 0;

            await using (var enumerator = items.GetAsyncEnumerator(cancellationToken))
            {
                List<T> result;
                if (!await enumerator.MoveNextAsync())
                {
                    totalRecords = 0;
                    result = new List<T>();
                }
                else
                {
                    totalRecords = enumerator.Current.TotalCount;
                    page = enumerator.Current.Page;
                    result = new List<T>(pageSize);
                    do
                    {
                        result.Add(enumerator.Current.Data);
                    } while (await enumerator.MoveNextAsync());
                }

                return new PaginationResult<T>(totalRecords, page, pageSize, result);
            }
        }

        class RownNumberHolder<T>
        {
            public T Data = default!;
            public long RowNumber;
            public int TotalCount;
        }

        static Expression<Func<int>> _totalCountTemplate = () => Sql.Ext.Count().Over().ToValue();
        static Expression _totalCountEmpty = Expression.Constant(-1);

        static Expression GetRowNumberQuery<T>(Expression queryWithoutOrder, List<Tuple<Expression, bool>> orderBy, bool includeTotal)
        {
            if (orderBy.Count == 0)
                throw new InvalidOperationException("OrderBy for query is not specified");

            Expression<Func<T, AnalyticFunctions.IOverMayHavePartitionAndOrder<long>>> overExpression =
                t => Sql.Ext.RowNumber().Over();

            Expression<Func<IQueryable<T>, long, int, IQueryable<RownNumberHolder<T>>>> selectExpression =
                (q, rn, tc) => q.Select(x => new RownNumberHolder<T> {Data = x, RowNumber = rn, TotalCount = tc});


            Expression totalCountExpr = includeTotal ? _totalCountTemplate.Body : _totalCountEmpty;

            var entityParam = ((LambdaExpression)((MethodCallExpression)selectExpression.Body).Arguments[1].Unwrap())
                .Parameters[0];

            var windowFunctionBody = overExpression.Body;
            windowFunctionBody = GenerateOrderBy(entityParam, windowFunctionBody, orderBy);
            windowFunctionBody = FinalizeFunction(windowFunctionBody);

            var queryExpr = selectExpression.GetBody(queryWithoutOrder, windowFunctionBody, totalCountExpr);

            return queryExpr;
        }

        static IQueryable<Envelope<T>> GetPageByConditionInternal<T>(IQueryable<T> query, int pageSize, Expression<Func<T, bool>> predicate, bool includeTotal)
        {
            Expression<Func<IQueryable<RownNumberHolder<T>>, IQueryable<RownNumberHolder<T>>>> cteCall = q => q.AsCte("pagination_cte");

            var queryExpr = query.Expression;

            var orderBy = new List<Tuple<Expression, bool>>();
            var withoutOrder = ExtractOrderByPart(queryExpr, orderBy);

            var rnQueryExpr = GetRowNumberQuery<T>(withoutOrder, orderBy, includeTotal);
            rnQueryExpr = cteCall.GetBody(rnQueryExpr);

            Expression<Func<IQueryable<RownNumberHolder<T>>, Expression<Func<RownNumberHolder<T>, bool>>, int,
                IQueryable<Envelope<T>>>> dataTemplate =
                includeTotal
                    ? (q, f, ps) =>
                        q
                            .Where(f).Take(1).Select(x => (int)(x.RowNumber - 1) / ps + 1)
                            .SelectMany(page => q.Where(x => x.RowNumber.Between((page - 1) * ps + 1, page * ps))
                                .OrderBy(x => x.RowNumber)
                                .Select(x =>
                                    new Envelope<T>
                                    {
                                        Data = x.Data, Page = page, TotalCount = (int)x.TotalCount
                                    }))
                    : (q, f, ps) =>
                        q
                            .Where(f).Take(1).Select(x => (int)(x.RowNumber - 1) / ps + 1)
                            .SelectMany(page => q.Where(x => x.RowNumber.Between((page - 1) * ps + 1, page * ps))
                                .OrderBy(x => x.RowNumber)
                                .Select(x =>
                                    new Envelope<T> {Data = x.Data, Page = page, TotalCount = -1}));



            var param = Expression.Parameter(typeof(RownNumberHolder<T>), "h");
            var newPredicate = Expression.Lambda(predicate.GetBody(Expression.PropertyOrField(param, "Data")), param);

            var resultExpr = dataTemplate.GetBody(rnQueryExpr, newPredicate, Expression.Constant(pageSize));
            return query.Provider.CreateQuery<Envelope<T>>(resultExpr);
        }

        static IQueryable<int> GetPageNumberByConditionInternal<T>(IQueryable<T> query, int pageSize, Expression<Func<T, bool>> predicate, bool includeTotal)
        {
            var queryExpr = query.Expression;

            var orderBy = new List<Tuple<Expression, bool>>();
            var withoutOrder = ExtractOrderByPart(queryExpr, orderBy);

            var rnQueryExpr = GetRowNumberQuery<T>(withoutOrder, orderBy, includeTotal);

            Expression<Func<IQueryable<RownNumberHolder<T>>, Expression<Func<RownNumberHolder<T>, bool>>, int,
                IQueryable<int>>> dataTemplate =
                (q, f, ps) =>
                    q.AsSubQuery().Where(f).Select(x => (int)((x.RowNumber - 1) / ps + 1));

            var param = Expression.Parameter(typeof(RownNumberHolder<T>), "h");
            var newPredicate = Expression.Lambda(predicate.GetBody(Expression.PropertyOrField(param, "Data")), param);

            var resultExpr = dataTemplate.GetBody(rnQueryExpr, newPredicate, Expression.Constant(pageSize));
            return query.Provider.CreateQuery<int>(resultExpr);
        }

        #endregion
    }
}