.NET Core 3 InvalidOperationException on OrderBy with dynamic field name

Question

I'm migrating an existing web API from .NET Core 2 o 3 version. After several problems, I manage to make it work, with the exception of Dynamic OrderBy by column name.

This is my code, that worked great with .net core 2:

public async Task<IEnumerable<Clientes_view>> GetClientes(int bActivos, int nRegistroInic, int nRegistros, string sOrdenar, 
        int nSentido, string sFiltro, int nTipo = -1, int idCliente = -1)
    {
        var clientes = this.context.Set<Clientes_view>()
           .Where(e => e.RazonFantasia.Contains(sFiltro) || e.RazonFantasia.Contains(sFiltro)
               || e.Cuit.Contains(sFiltro) || e.Mail.StartsWith(sFiltro) || string.IsNullOrEmpty(sFiltro))
           .Where(e => (e.Activo && bActivos == 1) || bActivos == -1 || (!e.Activo && bActivos == 0))
           .Where(e => e.IdTipoCliente == nTipo || nTipo == -1)
           .Where(e => e.IdCliente == idCliente || idCliente == -1);

        if (!string.IsNullOrEmpty(sOrdenar))
        {
            var propertyInfo = this.context.Set<Clientes_view>().First().GetType().GetProperty(sOrdenar, 
                BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);

            if (propertyInfo != null) if (nSentido == -1) clientes = clientes.OrderByDescending(e => propertyInfo.GetValue(e, null));
                else clientes = clientes.OrderBy(e => propertyInfo.GetValue(e, null));
        }

        clientes = clientes.Skip(nRegistroInic).Take(nRegistros);

        return await clientes.ToListAsync();
    }

And the error I'm getting is the following:

System.InvalidOperationException: The LINQ expression 'DbSet .Where(c => True) .Where(c => c.Activo && True || False || False) .Where(c => True) .Where(c => True) .OrderBy(c => __propertyInfo_3.GetValue( obj: c, index: null))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

Any thoughts? Thanks!

Answer 1

You need to actually generate the member access expression, all you've done was used reflection to get the value of some object, and provided that as the expression. That will not work, the query provider will not be able to translate that.

You need to do something like this:

if (!String.IsNullOrEmpty(sOrdenar))
{
    var type = typeof(Clientes_view);
    var prop = type.GetProperty(sOrdenar);
    if (prop != null)
    {
        var param = Expression.Parameter(type);
        var expr = Expression.Lambda<Func<Clientes_view, object>>(
            Expression.Convert(Expression.Property(param, prop), typeof(object)),
            param
        );
        if (nSentido == -1)
            clientes = clientes.OrderByDescending(expr);
        else
            clientes = clientes.OrderBy(expr);
    }
}

Answer 2

Your problem is that you are using reflection inside of order by, while probably you should use sorting string. One of the options

Install-Package System.Linq.Dynamic
using System.Linq.Dynamic;

then you can sort

query.OrderBy("item.item_id DESC")

Other option without any library in case you dont have many sort options would be:

switch(sOrdenar){
   case "Field1"
     clientes = nSentido == -1 ? clientes.OrderBy(entity=> entity.Field1) : clientes.OrderByDescending(entity=> entity.Field1);
     break;
   case "OtherField"
          clientes = nSentido == -1 ? clientes.OrderBy(entity=> entity.OtherField) : clientes.OrderByDescending(entity=> entity.OtherField);
     break;
}

Personally I prefer second option better, because then I can be sure that user is able to sort only on allowed fields otherwise you can have performance issues in case you have large tables and users start sorting on wrong fields (Never trust your users :) ).

Answer 3

EF Core attempts to translate as much of your query to a server-side query (i.e. SQL) as possible. In versions before 3.0, any code that could not be converted was silently run on the client - however, this can cause massive and often unintuitive performance issues, so from 3.0 the decision was made that if any query code cannot be translated, an exception would immediately be thrown.

Reference: https://learn.microsoft.com/en-us/ef/core/querying/client-eval#previous-versions

The end result is that you either need to rearchitect your code to separate the parts that can and can't be run on the server, or alternatively force everything to be run on the client. The referenced document explains how to achieve the latter, but note that doing so will likely have significant performance impact.

In your case, the stuff inside the if (!string.IsNullOrEmpty(sOrdenar)) block is what is causing the problem. You should be aware that this implies that whenever that block has been executed, the paging that follows it (Skip and Take) has not been executed on the server, always the client - so if you've ever had performance problems with this method, now you know why!

Answer 4

It's pretty obvious that calling properties via reflection can't be automatically translated into SQL query.

The only ways it could have worked before was either that this branch was never taken, or the whole query was processed by your application instead of on the database side.

To fix this, do as the error message suggests: break the query into DB and application parts, e.g.

if (!string.IsNullOrEmpty(sOrdenar))
{
    IEnumerable<Clientes_view> list = await clientes.AsAsyncEnumerable();
    list = list.Where(.....); //here you may use everything you like
    return list;
}

If you are searching for a way to generate the OrderBy part dynamically on the server side, take a look at this answer; apparently it's written for classic EF, but should probably work in EF Core with minor adjustments.