During the summer weeks, I was a little less active on stackoverflow. But now that the weather is becoming less and less friendly, I've reactivated myself and come across this question:

https://stackoverflow.com/questions/45999489/entity-framework-interceptor-default-sorting/46001440#46001440

So basically, OP wanted to find a way to automatically add an OrderBy() clause to every LInQ-to-Entities query. Nice :)

Probably the best-way to do this is creating an interceptor. Here is a good post by Rowan Miller, former manager of the EF team, that describes how to create and use these interceptors.

You simply have to implement an interface which has one method and then register the interceptor. When a command tree is created, this method is invoked in the interceptor and you have the option the mutate the created command tree using a visitor. I solved the question with a rudimentary solution, but I decided I should implement a more structured solution.

Design a way to designate sort properties and sort orders

So first, I need a way to somehow define the property which the sorting should be based on, and somehow also indicate the sort order (ascending or descending). I decided that custom annotations are probably the best way, so I created some extension methods to define the sort properties.

public static class DefaultSortExtensions
{
        public static PrimitivePropertyConfiguration IsDefaultSort(this PrimitivePropertyConfiguration property)
{
  property.HasColumnAnnotation(OrderConstants.OrderProperty, OrderConstants.Ascending);
  return property;
}

public static PrimitivePropertyConfiguration IsDefaultSortDescending(this PrimitivePropertyConfiguration property)
{          
   property.HasColumnAnnotation(OrderConstants.OrderProperty, OrderConstants.Descending);
  return property;
}

These will be used to define the sorting properties:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<Person>().Property(p =>p.OrderProp)
                               .IsDefaultSort();            
}

I believe that this is again a requirement that should be handled transparently to the datamodel. But just in case someone likes the attributes, I created two of them that can be applied to a property:

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public abstract class DefaultSortAtrributeBase : Attribute { }

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public sealed class DefaultSortPropertyAttribute : DefaultSortAtrributeBase { }

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public sealed class DefaultDescendingSortPropertyAttribute : DefaultSortAtrributeBase { }

And created a custom convention that applies the same custom annotations to properties that have these attributes:

 public sealed class DefaultSortConvention : Convention
 {
   public DefaultSortConvention()
   {
      this.Properties()
          .Having(x => x.GetCustomAttribute<DefaultSortAtrributeBase>(true))
          .Configure((config, att) => ConfigureDefaultSort(config, att));
  }

  private void ConfigureDefaultSort(ConventionPrimitivePropertyConfiguration config, DefaultSortAtrributeBase att)
  {
    var attType = att.GetType();
    if (attType == typeof(DefaultSortPropertyAttribute))
    {
      config.HasColumnAnnotation(OrderConstants.OrderProperty, OrderConstants.Ascending);
    }
    else if (attType == typeof(DefaultDescendingSortPropertyAttribute))
    {
      config.HasColumnAnnotation(OrderConstants.OrderProperty, OrderConstants.Descending);
    }
    else
    {
      throw new InvalidOperationException($"Unknown order attribute type {attType.FullName}");
    }
  }
}

If you apply this configuration to your context, you can use the attributes to denote the sorting property.

Implement the visitor

Next step is to implement the visitor. The visitor has basically two jobs:

• Find the sorting property.
• Apply an ordering clause.

And here's the code:

 public class DefaultSortVisitor : DefaultExpressionVisitor
 {
   public override DbExpression Visit(DbScanExpression expression)
   {
     var table = (EntityType)expression.Target.ElementType;
     var binding = expression.Bind();
     var orderProps = table.Properties.Where(p => p.MetadataProperties.Any(mp => mp.Name == "http://schemas.microsoft.com/ado/2013/11/edm/customannotation:"+OrderConstants.OrderProperty)).ToList();
     if (orderProps.Count == 0)
     {
       return expression;
     }
     if (orderProps.Count > 1)
     {
        var propNames = string.Join(",", orderProps.Select(p => p.Name));
        throw new InvalidOperationException($"Multiple default sort properties specified: {propNames}");
     }

     var orderProp = orderProps.Single();
     var sortingColumn = orderProp.Name;
     
     var sortKey = binding.VariableType.Variable(binding.VariableName).Property(sortingColumn);
    var sortProps = orderProp.MetadataProperties.Where(mp => mp.Name == "http://schemas.microsoft.com/ado/2013/11/edm/customannotation:"+OrderConstants.OrderProperty).ToList();
    if (sortProps.Count > 1)
    {
      throw new InvalidOperationException($"Property {orderProp.Name} is specified more than once as a default sorting property");
    }
    var sortDir = (string)sortProps.Single().Value;

    Func<DbExpression, DbSortClause> sortClauseCreator = sortDir == OrderConstants.Ascending ? new Func<DbExpression, DbSortClause>(DbExpressionBuilder.ToSortClause) : sortDir == OrderConstants.Descending ? new Func<DbExpression, DbSortClause>(DbExpressionBuilder.ToSortClauseDescending) : throw new InvalidOperationException("Unknown sort order");

    var sortClause = sortClauseCreator(sortKey);
    return DbExpressionBuilder.Sort(binding, new[] { sortClause });
  }
}

Most of the code deals with finding and validating the sort property. If there are no sort properties, nothing is applied. If there are more than one, an exception is thrown. If there is only one, and it is specified only once as a sorting property, then the sortorder and the sorting property is extracted. No magic.

The magic happens when the query must be amended. You have to use the DbExpressionBuilder.Sort() method, pass in the original command tree as the first parameter and add the sorting clause. The sorting clause can be created with the DbExpressionBuilder.ToSortClause() or the DbExpressionBuilder.ToSortClauseDescending(), and pass in the sorting key expression. This was the magic:

var sortKey = binding.VariableType.Variable(binding.VariableName).Property(sortingColumn);

And with that, the visitor is done. The last thing is the interceptor.

Implementing the interceptor

Here's the code:

public class DefaultSortInterceptor : IDbCommandTreeInterceptor
{
  public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
  {
    if (interceptionContext.OriginalResult.DataSpace != DataSpace.SSpace)
    {
      return;
    }

    var queryCommand = interceptionContext.Result as DbQueryCommandTree;
    if (queryCommand != null)
    {
       interceptionContext.Result = HandleQueryCommand(queryCommand);
     }
  }

  private static DbCommandTree HandleQueryCommand(DbQueryCommandTree queryCommand)
  {
    var newQuery = queryCommand.Query.Accept(new DefaultSortVisitor());
    return new DbQueryCommandTree(queryCommand.MetadataWorkspace,
                                  queryCommand.DataSpace,
                                  newQuery);
  }
}

There is really not much to explain: there are some extra checks to see if the command really is a query, and then the visitor is applied.

And finally, you have to register the interceptor (perhaps in the static ctor of your context):

DbInterception.Add(new DefaultSortInterceptor());

You can find the whole thing here on Github. If you feel like it, go ahead, fork and improve on it (maybe you could use a custom SQL generator and add an index to the sort column automatically...)