Aspnetboilerplate application gives runtime error while applying sort on a query for ISoftDelete entity

Question

I want to write CRUD services for an entity named Location . Location has a composite primary key, so I couldn't use AsyncCrudAppService and copied the methods that I wanted from AsyncCrudAppService.

When I use GetAll Service without ApplySorting method, it works fine. But when I add sorting, I get this runtime error:

[35] ERROR BookingSystem.middlewares.HttpGlobalExceptionFilter [(null)] - The LINQ expression 'DbSet .Where(l => __ef_filter__p_0 || !(((ISoftDelete)l).IsDeleted)) .OrderByDescending(l => l.Id)' 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(). S ee https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

public class Location : IEntity<int>, IPassivable, IFullAudited<User> {
    public int Id { get; set; }
    public int IdLocation { get; set; }         //primary key 0
    public LocationType TypeId { get; set; }    //primary key 1
    public string Name { get; set; }
}

public interface ILocationService : IApplicationService
{
    Task<PagedResultDto<LocationDto>> GetAllAsync(PagedLocationResultRequestDto input);
}

public class LocationService : AbpServiceBase, ILocationService, IPerWebRequestDependency
{
    private readonly IRepository<Location, int> _repository;
    private IAsyncQueryableExecuter AsyncQueryableExecuter { get; set; }

    public LocationService(IRepository<Location> repository)
    {
        _repository = repository;
        AsyncQueryableExecuter = NullAsyncQueryableExecuter.Instance;
    }


    public async Task<PagedResultDto<LocationDto>> GetAllAsync(PagedLocationResultRequestDto input)
    {
        if (input.MaxResultCount > _appSettings.Value.MaxResultCount)
        {
            throw new BookingSystemUserFriendlyException(BookingSystemExceptionCode.InputNotValid,
                nameof(input.MaxResultCount));
        }

        var query = CreateFilteredQuery(input);

        var totalCount = await AsyncQueryableExecuter.CountAsync(query);

        query = ApplySorting(query, input);
        query = ApplyPaging(query, input);

        var entities = await AsyncQueryableExecuter.ToListAsync(query);

        return new PagedResultDto<LocationDto>(
            totalCount,
            entities.Select(MapToEntityDto).ToList()
        );
    }

    protected virtual IQueryable<Location> ApplySorting(
        IQueryable<Location> query, PagedLocationResultRequestDto input)
    {
        if (input is ISortedResultRequest sortInput &&
            !sortInput.Sorting.IsNullOrWhiteSpace())
        {
            return query.OrderBy(sortInput.Sorting);
        }

        return query.OrderByDescending(e => e.Id);
    }

    protected virtual IQueryable<Location> ApplyPaging(
        IQueryable<Location> query, PagedLocationResultRequestDto input)
    {
        if (input is IPagedResultRequest pagedInput)
        {
            return query.PageBy(pagedInput);
        }

        return query;
    }

    private IQueryable<Location> CreateFilteredQuery(PagedLocationResultRequestDto input)
    {
        return _repository.GetAll()
            .WhereIf(!string.IsNullOrWhiteSpace(input.Name),
                location => location.Name.ToLower().Contains(input.Name.Trim().ToLower()));
    }

    private LocationDto MapToEntityDto(Location entity)
    {
        return ObjectMapper.Map<LocationDto>(entity);
    }
}

Abp package version: 5.1.0

Base framework: .Net Core

Answer 1

Well, I asked the same question in the project's GitHub and got the answer. In ApplySorting, the default sorting is based on Id, which doesn't exist in my database table.

If you are using composite PK, then you probably don't have the Id field in the database, right? Then you should not sort by Id.

https://github.com/aspnetboilerplate/aspnetboilerplate/issues/5274#issuecomment-583946065