gpt4 book ai didi

linq - 如何在 Entity Framework 7 (Core) 中按某些实体属性动态排序

转载 作者:行者123 更新时间:2023-12-04 13:06:54 26 4
gpt4 key购买 nike

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

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

  • 服务层(服务模型 (DTO) 属性匹配客户端想要订购的任何东西)
  • 域层(它公开存储库接口(interface)以访问持久对象)
  • ORM 层(它实现存储库并使用 Entity Framework 7(也称为 Entity Framework Core)来访问 SQL Server 数据库)

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

    我的 Things 中有以下实现存储库:
        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)的对象
    public class SortModel
    {
    public string ColId { get; set; }
    public string Sort { get; set; }

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

    这种方法尝试将 SQL 语句与 Entity 为之前的可查询对象创建的任何内容混合。但我得到一个:
    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] 并且实体无法理解这些列是什么?

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

    最佳答案

    FromSql绝对不能用来混SQL。因此,像往常一样使用动态查询,您必须求助于 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);

    关于linq - 如何在 Entity Framework 7 (Core) 中按某些实体属性动态排序,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36298868/

    26 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com