我使用这个Pull Request中的示例作为嵌套属性链的引用。不幸的是，类似的东西
Sql.Property<object>(x, "SomeClass.SomeProperty")
不起作用。
假设我有一个模型SortDescriptor(定义如下)，它定义了我们如何动态地对代码中的项进行排序。有没有一种方法可以动态创建一个表达式树来生成嵌套的Sql.Property调用，比如下面显示的ToPropertyExpression方法？
如果给定以下筛选器，则应从SortBy函数输出以下SQL：
ORDER BY Category.EntityId, StackRank, Id
(Category是CategoryProperty上的导航属性)
var filter1 = new PagedFilter
{
Take = 25,
Sort = new List<SortDescriptor>
{
new SortDescriptor { Selector = "Category.EntityId" },
new SortDescriptor { Selector = "StackRank" },
new SortDescriptor { Selector = "Id" },
}
};
以下是我正在使用的功能和对象：
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; }
}
每当我尝试执行类似于上面的内容时，我都会收到一条错误消息，说它正在尝试评估客户端的Sql.Property表达式链：
异常消息：
LinqToDB.Linq.LinqException："Property"只是服务器端方法。堆栈跟踪：
LinqToDB.Sql.Property[T](Object实体，String propertyName)
LinqToDB.Linq.QueryRunner.SetParameters(查询查询、表达式表达式、IDataContext参数Context、Object[]参数、Int32 queryNumber、SqlParameterValues参数值)
在LinqToDB2.QueryRunnerBase.SetCommand(布尔clearQueryHints)LinqToDB.Data.DataConnection.QueryRunner。<gt；n__0(布尔clearQueryHints)
LinqToDB.Data.DataConnection.QueryRunner.ExecuteReaderAsync(CancellationToken cancellionToken)
LinqToDB2.LinqQueryRunner.AsyncEnumeratorImpl1.MoveNextAsync() at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1源，CancellationToken取消令牌)
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at LinqToDB.AsyncExtensions.ToListAsync[TSource](IQueryable1源，CancellationToken令牌)
Experlogix.Api.DesignStudio.DataAccess.CategoryPropertyRepository.GetPageIndexById(CategoryPropertyFilter筛选器，Guid-id)D： \Dev\repos\Experlogix.Api.DesignStudio\src\Experlogix.Api.Design Studio.DataAccess\CategoryPropertyRepository.cs:line 116
Experlogix.Core.DataAccess.Service6.GetPageIndexById(TFilter filter, TId id) at Experlogix.Api.DesignStudio.Controllers.CategoryPropertyController.GetPageIndexAsync(Guid id, CategoryPropertyFilter filter) in D:DevreposExperlogix.Api.DesignStudiosrcExperlogix.Api.DesignStudioControllersCategoryPropertyController.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)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g_Await|10_0(ControllerActionInvoker调用程序，Task lastTask，State next，Scope Scope，Object State，Boolean isCompleted)
在Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed上下文)
在Microsoft.AspNetCore.Mvc.InfrasterActionInvokeR.Next(State&Next，Scope&Scope，Object&State，Boolean&isCompleted)(ControllerActionInvoker调用程序，Task lastTask，State next，Scope Scope，Object State，Boolean isCompleted(ResourceInvoker调用程序，任务任务，IDisposable作用域)
Microsoft.AspNetCore.Routing.EndpointMiddleware.g_AwaitRequestTask|6_0(端点，任务请求任务，ILogger记录器)
Experlogix.Api.DesignStudio.Middleware.DatabaseConnectionMiddleware.IInvoke(HttpContext HttpContext，IUserContext userContext，IConnectionContext connectionContext，IDatabaseClient databaseClient)D： \Dev\repos\Experlogix.Api.DesignStudio\src\Experlogix.Api.Design Studio\Middleware\DatabaseConnectionMiddleware.cs:line 65
在Microsoft.AspNetCore.Authorization.AuthenticationMiddleware.Invoke(HttpContext上下文)
位于Serilog.AspNetCore.RequestLoggingMiddleware.Unvoke/>位于Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.IInvoke(HttpContext HttpContext，ISwakerProvider swaggerProvider)
Experlogix.Core.AspNet.Middleware.ApiExceptionHandlerMiddleware.UInvoke(HttpContext context)
如有任何关于如何实现类似目标的指导，我们将不胜感激！
环境详细信息
- linq2db版本：linq2db.EntityFrameworkCore 5.1.0
- 数据库服务器：SQL server
- 操作系统：Windows 10
- .NET Framework：.NET 5.0
我能够通过更改ToPropertyExpression使查询按需工作，如下所示：
public static Expression ToPropertyExpression(this SortDescriptor sort, Expression entity)
{
return sort.Selector.Split('.').Aggregate(entity, Expression.Property);
}
所有其他代码保持不变，并生成以下SQL查询：
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]
我提出了更通用的分页解决方案。它可以处理任何查询并生成正确的SQL。它还可以向数据库返回一次往返的TotalCount。
实现不是琐碎的，但可以作为参考源来使用表达式树。
用法示例：
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)});
实施：
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
}
}