CODEWITHSUNDEEP
c#apirestsortingsql-order-by
Anna Boyko
Anna Boyko

Reputation: 25

An unhandled exception occurred while processing the request.(Rest api sorting on asp.net)

InvalidOperationException: The LINQ expression 'DbSet() .OrderBy(s => s.GetType().GetProperty(__sort_by_0).GetValue(s))' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

This error occurres when I try to sort my rest api by property.enter image description here

Upvotes: 1

Views: 726

Answers (1)

Leaky
Leaky

Reputation: 3646

Someone linked a previous answer in a comment, which makes me unsure if I should be answering this, but well, it was already written. :)

Basically sorting dynamically is a bit more involved than what you're trying to do. That GetType(), GetProperty(), etc. reflection code cannot be executed in the database.

You have basically two options:

1) Use the Dynamic Linq package

With System.Linq.Dynamic.Core package (see documentation) you can provide string references to the members of entities, and it will be translated to expressions.

Which means you can simply write this:

_context.SwiftTransfers.OrderBy(sort_by).ToListAsync();

2) Use a custom solution to translate strings to expressions

You can build an expression yourself. For example the following method supports applying multiple ascending or descending ordering to an IQueryable, based on the provided string member name (which can reference nested properties with the pattern "User/Name/FirstName"):

static IQueryable<T> ApplyOrdering(IQueryable<T> query, string propertyPath, bool isAscending = true, bool firstOrdering = true)
{
    var param = Expression.Parameter(typeof(T), "p");
    var member = (MemberExpression)propertyPath.Split('/').Aggregate((Expression)param, Expression.Property);
    var exp = Expression.Lambda(member, param);
    string methodName = isAscending switch
    {
        true => firstOrdering ? "OrderBy" : "ThenBy",
        false => firstOrdering ? "OrderByDescending" : "ThenByDescending"
    };
    Type[] types = new Type[] { query.ElementType, exp.Body.Type };
    var orderByExpression = Expression.Call(typeof(Queryable), methodName, types, query.Expression, exp);
    return query.Provider.CreateQuery<T>(orderByExpression);
}

// Then call it like this:
ApplyOrdering(_context.SwiftTransfers.AsQueryable(), sort_by).ToListAsync();

This can be used as an extension method on IQueryable to access it more comfortably.

Tips

  • If you want to implement a standard-compliant way of sorting/filtering, you can look into OData. It has ready-to-use packages you can apply on your endpoints in ASP.NET Core. I personally don't really like their implementation suggestion, though, so I usually implement OData-compliant filtering and sorting manually.

  • If you use DTOs that change the shape of the data, and the client will send you sort parameters based on the DTO, you can run into issues where the given property name doesn't exist on the actual entity that you're trying to sort. But if you use AutoMapper for mapping, and if you map via ProjectTo<TDto>(), that will automatically translate the expressions back so that they reference the correct entity property. So you don't have to do manual property name mapping.

Upvotes: 2

Related Questions