如何在 Entity Framework 7 (Core) 中按某些实体属性动态排序 [英] How to dynamically order by certain entity properties in Entity Framework 7 (Core)

问题描述

我有一个项目，其中前端 JavaScript 指定要排序的列列表.

I have a project where the front-end JavaScript specifies a list of columns to order by.

然后在后端我有多层应用程序.典型场景

Then in the back-end I have multi-layer application. Typical scenario

1. 服务层(服务模型的 (DTO) 属性匹配客户端想要订购的任何内容)
2. 域层(它公开存储库接口以访问持久化对象)
3. ORM 层(它实现存储库并使用 Entity Framework 7(又名实体框架核心)访问 SQL Server 数据库)

请注意，System.Linq.Dynamic 不支持 DNX Core v5.0 或 .NET Platform v5.4，因此我无法使用该库.

Please note that System.Linq.Dynamic IS NOT supported for DNX Core v5.0 or .NET Platform v5.4 so I cannot use that library.

我的 Things 存储库中有以下实现:

I have the following implementation in my Things repository:

    public async Task<IEnumerable<Thing>> GetThingsAsync(IEnumerable<SortModel> sortModels)
    {
        var query = GetThingsQueryable(sortModels);
        var things = await query.ToListAsync();
        return things;
    }

    private IQueryable<Thing> GetThingsQueryable(IEnumerable<SortModel> sortModels)
    {

        var thingsQuery = _context.Things
                .Include(t => t.Other)
                .Where(t => t.Deleted == false);

        // this is the problematic area as it does not return a valid queryable
        string orderBySqlStatement = GetOrderBySqlStatement(sortModels);
        thingsQuery = thingsQuery.FromSql(orderBySqlStatement);
        return thingsQuery ;
    }

    /// this returns something like " order by thingy1 asc, thingy2 desc"
    private string GetOrderBySqlStatement(IEnumerable<SortModel> sortModels)
    {
        IEnumerable<string> orderByParams = sortModels.Select(pair => { return pair.PairAsSqlExpression; });
        string orderByParamsConcat = string.Join(", ", orderByParams);
        string sqlStatement = orderByParamsConcat.Length > 1 ? $" order by {orderByParamsConcat}" : string.Empty;
        return sqlStatement;
    }

这是包含列名和按方向排序的对象(asc 或 desc)

and this is the object that contains a column name and a order by direction (asc or desc)

public class SortModel
{
    public string ColId { get; set; }
    public string Sort { get; set; }

    public string PairAsSqlExpression
    {
        get
        {
            return $"{ColId} {Sort}";
        }
    }
}

这种方法试图将 SQL 语句与实体为前一个可查询创建的任何内容混合.但我得到一个:

This approach tries to mix a SQL statement with whatever Entity creates for the previous queryable. But I get a:

Microsoft.Data.Entity.Query.Internal.SqlServerQueryCompilationContextFactory:Verbose: Compiling query model: 'from Thing t in {value(Microsoft.Data.Entity.Query.Internal.EntityQueryable`1[MyTestProj.Data.Models.Thing]) => AnnotateQuery(Include([t].DeparturePort)) => AnnotateQuery(Include([t].ArrivalPort)) => AnnotateQuery(Include([t].Consignments))} where (([t].CreatorBusinessId == __businessId_0) AndAlso (Convert([t].Direction) == __p_1)) select [t] => AnnotateQuery(QueryAnnotation(FromSql(value(Microsoft.Data.Entity.Query.Internal.EntityQueryable`1[MyTestProj.Data.Models.Thing]), " order by arrivalDate asc, arrivalPortCode asc", []))) => Count()'
Microsoft.Data.Entity.Query.Internal.SqlServerQueryCompilationContextFactory:Verbose: Optimized query model: 'from Thing t in value(Microsoft.Data.Entity.Query.Internal.EntityQueryable`1[MyTestProj.Data.Models.Thing]) where (([t].CreatorBusinessId == __businessId_0) AndAlso (Convert([t].Direction) == __p_1)) select [t] => Count()'
Microsoft.Data.Entity.Query.Internal.QueryCompiler:Error: An exception occurred in the database while iterating the results of a query.
System.InvalidOperationException: The Include operation is not supported when calling a stored procedure.
   at Microsoft.Data.Entity.Query.ExpressionVisitors.RelationalEntityQueryableExpressionVisitor.VisitEntityQueryable(Type elementType)
   at Microsoft.Data.Entity.Query.ExpressionVisitors.EntityQueryableExpressionVisitor.VisitConstant(ConstantExpression constantExpression)
   at System.Linq.Expressions.ConstantExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.Data.Entity.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression expression)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.ReplaceClauseReferences(Expression expression, IQuerySource querySource, Boolean inProjection)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CompileMainFromClauseExpression(MainFromClause mainFromClause, QueryModel queryModel)
   at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.CompileMainFromClauseExpression(MainFromClause mainFromClause, QueryModel queryModel)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitMainFromClause(MainFromClause fromClause, QueryModel queryModel)
   at Remotion.Linq.Clauses.MainFromClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.Data.Entity.Query.EntityQueryModelVisitor.CreateAsyncQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.Data.Entity.Storage.Database.CompileAsyncQuery[TResult](QueryModel queryModel)
   at Microsoft.Data.Entity.Query.Internal.QueryCompiler.<>c__DisplayClass19_0`1.<CompileAsyncQuery>b__0()
   at Microsoft.Data.Entity.Query.Internal.CompiledQueryCache.GetOrAddAsyncQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.Data.Entity.Query.Internal.QueryCompiler.CompileAsyncQuery[TResult](Expression query)
   at Microsoft.Data.Entity.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
Exception thrown: 'System.InvalidOperationException' in EntityFramework.Core.dll
Exception thrown: 'System.InvalidOperationException' in mscorlib.ni.dll
Exception thrown: 'System.InvalidOperationException' in mscorlib.ni.dll
Microsoft.AspNet.Diagnostics.Entity.DatabaseErrorPageMiddleware:Verbose: System.InvalidOperationException occurred, checking if Entity Framework recorded this exception as resulting from a failed database operation.
Microsoft.AspNet.Diagnostics.Entity.DatabaseErrorPageMiddleware:Verbose: Entity Framework recorded that the current exception was due to a failed database operation. Attempting to show database error page.
Microsoft.Data.Entity.Storage.Internal.SqlServerConnection:Verbose: Opening connection 'Server=(localdb)mssqllocaldb;Database=SpeediCargo;Trusted_Connection=True;MultipleActiveResultSets=true'.
Microsoft.Data.Entity.Storage.Internal.SqlServerConnection:Verbose: Closing connection 'Server=(localdb)mssqllocaldb;Database=SpeediCargo;Trusted_Connection=True;MultipleActiveResultSets=true'.
Microsoft.Data.Entity.Storage.Internal.SqlServerConnection:Verbose: Opening connection 'Server=(localdb)mssqllocaldb;Database=SpeediCargo;Trusted_Connection=True;MultipleActiveResultSets=true'.
Microsoft.Data.Entity.Storage.Internal.SqlServerConnection:Verbose: Closing connection 'Server=(localdb)mssqllocaldb;Database=SpeediCargo;Trusted_Connection=True;MultipleActiveResultSets=true'.
Microsoft.Data.Entity.Storage.Internal.SqlServerConnection:Verbose: Opening connection 'Server=(localdb)mssqllocaldb;Database=SpeediCargo;Trusted_Connection=True;MultipleActiveResultSets=true'.
Microsoft.Data.Entity.Storage.Internal.RelationalCommandBuilderFactory:Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'__EFMigrationsHistory');
Microsoft.Data.Entity.Storage.Internal.SqlServerConnection:Verbose: Closing connection 'Server=(localdb)mssqllocaldb;Database=SpeediCargo;Trusted_Connection=True;MultipleActiveResultSets=true'.
Microsoft.Data.Entity.Storage.Internal.SqlServerConnection:Verbose: Opening connection 'Server=(localdb)mssqllocaldb;Database=SpeediCargo;Trusted_Connection=True;MultipleActiveResultSets=true'.
Microsoft.Data.Entity.Storage.Internal.RelationalCommandBuilderFactory:Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

似乎不可能将订单的 SQL 与 linq 查询的其余部分混合使用?或者问题是我没有在列前面加上 [t] 前缀，实体无法理解这些列是什么?

It seems it's not possible to mix SQL for the order by part with the rest of the linq query? Or the problem is that I am not prefixing the columns with the [t] and Entity is unable to understand what are those columns?

无论如何，是否有关于如何使用 Entity 7 和核心 .net 框架实现我想要的目标的示例或建议?

In any case, is there any example or recommendation on how to achieve what I want with Entity 7 and the core .net framework?

推荐答案

FromSql 绝对不能用于混合 SQL.因此，与动态查询一样，您必须求助于 System.Linq.Expressions.

FromSql definitely cannot be used to mix SQL. So as usual with dynamic queries, you have to resort to System.Linq.Expressions.

例如，像这样:

public static class QueryableExtensions
{
    public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, IEnumerable<SortModel> sortModels)
    {
        var expression = source.Expression;
        int count = 0;
        foreach (var item in sortModels)
        {
            var parameter = Expression.Parameter(typeof(T), "x");
            var selector = Expression.PropertyOrField(parameter, item.ColId);
            var method = string.Equals(item.Sort, "desc", StringComparison.OrdinalIgnoreCase) ?
                (count == 0 ? "OrderByDescending" : "ThenByDescending") :
                (count == 0 ? "OrderBy" : "ThenBy");
            expression = Expression.Call(typeof(Queryable), method,
                new Type[] { source.ElementType, selector.Type },
                expression, Expression.Quote(Expression.Lambda(selector, parameter)));
            count++;
        }
        return count > 0 ? source.Provider.CreateQuery<T>(expression) : source;
    }
}

然后:

var thingsQuery = _context.Things
        .Include(t => t.Other)
        .Where(t => t.Deleted == false)
        .OrderBy(sortModels);

这篇关于如何在 Entity Framework 7 (Core) 中按某些实体属性动态排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！