How to dynamically arrange specific properties of an object in EntityFramework 7 (Core)

Question

I have a project in which Foreground JavaScript sets up a list of columns to order.

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

• Service level parameters (properties of the service model (DTO) correspond to those that the client wants to order)
• Domain level (it provides repository interfaces for accessing persistent objects)
• ORM level (it implements a repository and uses Entity Framework 7 (aka Entity Framework Core) to access the SQL Server database)

Please note that System.Linq.Dynamic is not supported for DNX Core v5.0 or .NET Platform v5.4, so I cannot use this library

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 queyable 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; } 

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

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

This approach attempts to mix an SQL statement with any generated Entity for a previous query. But I get:

 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'] 

It seems impossible to mix SQL to order in parts with the rest of the linq query? Or is the problem that I am not prefixing the columns with [t], and Entity cannot understand what these columns are?

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

Answer 1

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

For example, something like this:

 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; } } 

And then:

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