.NET Core 3 InvalidOperationException on OrderBy with dynamic field name

c# ef-core-3.0 entity-framework-core linq

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!

1
3
12/27/2019 12:58:07 AM

Accepted Answer

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 :) ).

1
12/27/2019 2:52:49 AM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow