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);
>Solution :
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