asp.net mvc - How to limit the amount of data from an OData request? -

i have users table of 76 users , usergroups table.

using mvc, odata, generic repository , ef, trying optimize data retrieval when filtering based on user group:

/api/users?$filter=usergroups/any(usergroup: usergroup/id eq 'group1') 

on client side, right number of users - 71 (as odata filtering based on result), want limit number of records being returned form actual query - ie. not want return records filter (not optimal large data sets).

my api controller method follows:

    [queryable(allowedqueryoptions = allowedqueryoptions.all)]     public iqueryable<user> get()     {         var unitofwork = new atms.repository.unitofwork(_dbcontext);          var users = unitofwork.repository<user>()                               .query()                               .include(u => u.usergroups)                               .get()                               .orderby(order => order.username);          unitofwork.save();      // includes dispose()          return users.asqueryable();     } 

i read in this post that:

entity framework takes care of building dynamic query based on request.

however, using sql server profiler, query executed requesting records, rather filtered query.

adding .take() query not accomplish desired result, need actual number of records returned paging purposes.

i thinking of using grabbing properties through odataqueryoptions, doesn't seem quite right either.

is implementation of unit of work , repository incorrect, in relation trying accomplish, , if so, how can corrected?

simple - set page size queryable atrribute [queryable(pagesize=10)]

http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/supporting-odata-query-options#server-paging

if you'd tell ef apply options, work. :

    //[queryable(allowedqueryoptions = allowedqueryoptions.all)]     public iqueryable<user> get(odataqueryoptions<user> options)     {          var users = options.applyto(_dbcontext.set<user>()                               .query()                               .include(u => u.usergroups)                               .get()                               .orderby(order => order.username));          return users;     } 

your code didn't work, because tried apply options onto last line "users.asqueryable()", happened, ef pull full dataset, , applied query onto last line (that being in memory collection). , that's why didn't see "filter" not being passed sql.

the mechanics such, ef tries apply query, iqueryable collection finds in code (there's still question how find correct line).