WebApi with Odata-V4 and Dapper - Serverside filtering

Question

we use ASP.Net Webapi with OData and Dapper as ORM. For the GET requests we use the options parameter object respectively the filter parameter of it to build the SQL String for the Dapper query. This works nice for column eq value etc.

But now I wanted to do some serverside paging. This means I make an request with two filters ($top and $skip). E.g. "https://api.server.com/Orders?$skip=100&$top=50. Dapper makes the correct request to the db and I get a result containing 50 entries as response from dapper.

Then I give this result into the return statement of the webapi controller and the webapi seems to do the filtering on its own. So it executes to skip 100 from a result of 50 which result in 0 entries.

Did anyone have the same issue and found a way to prevent the webapi from filtering but to delegate the filtering to the ORM? Writing ApiControllers instead of ODataControllers is no alternative because I really like to use the odata syntax for filtering.

Thanks for your answers!

Answer 1

We fixed it in the way of the snippet below, this offered the solution to renounce on the [EnableQuery] Attribute:

public async Task<IHttpActionResult> Get(ODataQueryOptions<vwABC> options) 
    { 
        if(options != null && options.SelectExpand != null)
        {
            options.Request.ODataProperties().SelectExpandClause = options.SelectExpand.SelectExpandClause; 
        }
        if(options != null && options.Count != null && options.Count.Value == true)
        {
            options.Request.ODataProperties().TotalCount = await base.GetCount("vwABC", options);
        }
        return await base.Get(options, "vwABC"); 
    }