CODEWITHSUNDEEP
c#asp.net-web-apiodatadapper
Hejo
Hejo

Reputation: 293

WebApi with Odata-V4 and Dapper - Serverside filtering

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!

Upvotes: 3

Views: 6548

Answers (2)

Vinit
Vinit

Reputation: 2607

Assuming the return type of your API action is IQueryable, then the framework will apply the query filters on whatever data returned from database, so lets wrap the query result into the PageResult and return it, it won't apply the filters again . Sample code is as below -

public PageResult<Orders> GetOrdersPage(ODataQueryOptions<Orders> queryOptions)
{
    // Parse queryOptions to get Top and Skip
    var top = queryOptions.Top.RawValue;
    var skip = queryOptions.Skip.RawValue;

    //Call the dataaccess method and then get Querable result
    var queryResults = dataAccess.GetOrders(top,skip).AsQuerable<Orders>();

    //Return Page result 
    return new PageResult<Orders>(queryResults, new URI("Next page URI"), 1234); //1234 - is total count of records in table
}

Upvotes: 2

Hejo
Hejo

Reputation: 293

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"); 
    }

Upvotes: 1

Related Questions