title: How to dynamically order by certain entity properties in Entity Framework 7 (Core)
body: <p>I have a project where the front-end JavaScript specifies a list of columns to order by.</p>
  
  <p>Then in the back-end I have multi-layer application. Typical scenario</p>
  
  <ol>
  <li>Service layer (the service models' (DTO) properties match whatever the client-side wants to order by)</li>
  <li>Domain layer (it exposes repository interfaces to access persisted objects)</li>
  <li>ORM layer (it implements the repository and it uses Entity Framework 7 (a.k.a Entity Framework Core) to access a SQL Server database)</li>
  </ol>
  
  <p>Please note that <code>System.Linq.Dynamic</code> IS NOT supported for DNX Core v5.0 or .NET Platform v5.4 so I cannot use that library.</p>
  
  <p>I have the following implementation in my <code>Things</code> repository:</p>
  
  <pre><code>    public async Task&lt;IEnumerable&lt;Thing&gt;&gt; GetThingsAsync(IEnumerable&lt;SortModel&gt; sortModels)
      {
          var query = GetThingsQueryable(sortModels);
          var things = await query.ToListAsync();
          return things;
      }
  
      private IQueryable&lt;Thing&gt; GetThingsQueryable(IEnumerable&lt;SortModel&gt; sortModels)
      {
  
          var thingsQuery = _context.Things
                  .Include(t =&gt; t.Other)
                  .Where(t =&gt; 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&lt;SortModel&gt; sortModels)
      {
          IEnumerable&lt;string&gt; orderByParams = sortModels.Select(pair =&gt; { return pair.PairAsSqlExpression; });
          string orderByParamsConcat = string.Join(", ", orderByParams);
          string sqlStatement = orderByParamsConcat.Length &gt; 1 ? $" order by {orderByParamsConcat}" : string.Empty;
          return sqlStatement;
      }
  </code></pre>
  
  <p>and this is the object that contains a column name and a order by direction (asc or desc)</p>
  
  <pre><code>public class SortModel
  {
      public string ColId { get; set; }
      public string Sort { get; set; }
  
      public string PairAsSqlExpression
      {
          get
          {
              return $"{ColId} {Sort}";
          }
      }
  }
  </code></pre>
  
  <p>This approach tries to mix a SQL statement with whatever Entity creates for the previous queryable. But I get a:</p>
  
  <pre><code>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]) =&gt; AnnotateQuery(Include([t].DeparturePort)) =&gt; AnnotateQuery(Include([t].ArrivalPort)) =&gt; AnnotateQuery(Include([t].Consignments))} where (([t].CreatorBusinessId == __businessId_0) AndAlso (Convert([t].Direction) == __p_1)) select [t] =&gt; AnnotateQuery(QueryAnnotation(FromSql(value(Microsoft.Data.Entity.Query.Internal.EntityQueryable`1[MyTestProj.Data.Models.Thing]), " order by arrivalDate asc, arrivalPortCode asc", []))) =&gt; 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] =&gt; 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.&lt;&gt;c__DisplayClass19_0`1.&lt;CompileAsyncQuery&gt;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']
  </code></pre>
  
  <p>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?</p>
  
  <p>In any case, is there any example or recommendation on how to achieve what I want with Entity 7 and the core .net framework?</p>