How to limit web api results per each call

Question

How to limit rest api result? I set up a basic API but total result is 800k from database. I was reading on CMS website document and they set up a size & offset so each api call return at max 5000 rows. How can I do this?

web api url call examples:

localhost2343/api/dataset/data?First_Name=Dave
localhost2343/api/dataset/data?Last_Name=Dave&First_Name=Ram
localhost2343/api/dataset/data?size=1&offset=50
localhost2343/api/dataset/data?First_Name=Dave&size=1&offset=50

my web api

    [Route("data")]
    [HttpGet]
     public async Task<IQueryable<My_Model>> Get(My_Model? filter)
    {
        IQueryable<My_Model> Query = (from x in _context.My_DbSet
                                    select x);
        // add filters
        if (filter.Id != 0)
        {
            Query = Query.Where(x => x.Id == filter.Id);
        }
        if (!string.IsNullOrEmpty(filter.First_Name))
        {
            Query = Query.Where(x => x.First_Name == filter.First_Name);
        }
         ...

        return Query;
    }

What I tried: I think I have to do something like below but not sure. I am also not sure how to pass these values in URL becuase user enter 0 or 2 filters in different orders.

Query = Query.Skip((offset - 1) * size).Take(size);

Your question regarding limiting DB requests is answered here: https://stackoverflow.com/questions/3344493/limiting-query-size-with-entity-framework Your question regarding HttpGet URL parameters is answered here: https://stackoverflow.com/questions/36280947/how-to-pass-multiple-parameters-to-a-get-method-in-asp-net-core — DSander, Jan 08 '23 at 05:25

score 1 · Accepted Answer · answered Jan 08 '23 at 05:30

You can use pagination, It's essential if you’re dealing with a lot of data and endpoints. Pagination automatically implies adding order to the query result. You can do something like this. And you can Take() and Skip()

[Route("data")]
[HttpGet]
public async Task<IQueryable<My_Model>> Get(My_Model? filter, int pageSize = 50, int page = 1)
{
    IQueryable<My_Model> Query = (from x in _context.My_DbSet
                                select x);
    // add filters
    if (filter.Id != 0)
    {
        Query = Query.Where(x => x.Id == filter.Id);
    }
    if (!string.IsNullOrEmpty(filter.First_Name))
    {
        Query = Query.Where(x => x.First_Name == filter.First_Name);
    }
    
    return Query.Skip((page - 1) * pageSize).Take(pageSize);
}

To call the API, you can use query parameters like.

/data?filter.Id=1&filter.First_Name=John&pageSize=50&page=1

How to limit web api results per each call

1 Answers1