Skip and Take not working for an IQueryable datasource

Question

I have a common method across all objects in my service layer named "GetBaseEntity". This basically has a definition of:

public IQueryable<TEntity> GetBaseEntity(bool includeNavigation = true, bool isAdmin = false)
{
    var objBase = _context.EntityName;

    if (isAdmin)
    {
        return objBase;
    }

    return objBase.Where(x => x.IsActive == true && x.IsDeleted == false);
}

This returns an IQueryable of type TEntity. I want to dynamically pass the pagination options in a method so I implemented it this way:

public async Task<IEnumerable<EntityDto>> LoadResources(PagingOptions pagingOptions)
{
    var baseQuery = GetBaseEntity();

    if (pagingOptions != null)
    {
        baseQuery
            .Skip(pagingOptions.Page.Value * pagingOptions.Limit.Value)
            .Take(pagingOptions.Limit.Value);
    } 

    // I can actually return from this part but I just
    // set it to a variable to see how many rows the query will return
    var query = await baseQuery
        .ProjectTo<EntityDto>(_mappingConfiguration)
        .ToListAsync();

        return query;
}

However, the query still returns the whole set. My assumption is that the GetBaseEntity() just set-up the query but the execution applies on the LoadResourcesMethod where I applied the ToListAsync() in the LoadResources method.

I have tried the following but it didn't work:

1) not chaining the query (IQueryable with Entity Framework - order, where, skip and take have no effect)

if (pagingOptions != null) {
    baseQuery.Skip(pagingOptions.Page.Value * pagingOptions.Limit.Value);
    baseQuery.Take(pagingOptions.Limit.Value);
}

2) adding an "OrderBy" (WEB API IQueryable skip() take())

    var query = await baseQuery
    .OrderBy(x => x.Id)
    .ProjectTo<EntityDto>(_mappingConfiguration)
    .ToListAsync();

    return query;

Any help on building a dynamic pagination from an IQueryable source?

Answer 1

You are not updating your query in case of pagination. Skip and Take return a new IEnumerable as a result, instead of modifying the existing one in place. So, you should replace this line:

baseQuery
   .Skip(pagingOptions.Page.Value * pagingOptions.Limit.Value)
   .Take(pagingOptions.Limit.Value);

with:

baseQuery = baseQuery
   .Skip(pagingOptions.Page.Value * pagingOptions.Limit.Value)
   .Take(pagingOptions.Limit.Value);

This way, you assign the new query value to the baseQuery and then when you enumerate it, it will return the expected entities.