oracle - Web API OData custom query issue -

i new web api, entity framework , odata. asked similar question in forum haven't gotten relevant response.

we have odata compliant web api service use in salesforce. have custom complex query in oracle need expose.

i not sure how use custom query want allow odata parameter filtering occur? ($filter, $top, $skip, etc) example, when $filter used want apply filter custom query , send database have return result set. how can this?

the issue seem have can see parameters come in not translating query being passed oracle. seems fire query returning full result set , apply parameters. slow result set large.

i hoping 2 figure out 2 things 1. how can use custom sql , apply odata parameters underlying query? 2. when using ef or custom query, how can apply odata parameters query when query sent database $filter parameter, example, included in query? don't want full result returned apply filter.

can give me pointers on how make happen?

private static odatavalidationsettings _validationsettings = new odatavalidationsettings();      //public ihttpactionresult getname()     //{ }      // get: odata/sharedata     [odataroute("orders")]     [enablequery(pagesize = 50)]     public ihttpactionresult getorders(odataqueryoptions<orders> queryoptions)     {         // validate query.         try         {             queryoptions.validate(_validationsettings);         }         catch (odataexception ex)         {             return badrequest(ex.message);         }          try         {             string connectionstring = configurationmanager.connectionstrings["dnatestconnectionstring"].connectionstring;             var items = getdataitems(connectionstring);             return ok<ienumerable<orders>>(items);         }         catch (exception ex)         {             return statuscode(httpstatuscode.internalservererror);         }     }        #region load data methods     private static list<orders> getdataitems(string connectionstring)     {         list<orders> items = new list<orders>();          using (oracleconnection con = new oracleconnection(connectionstring))         {             con.open();              using (oraclecommand cmd = con.createcommand())             {                 cmd.commandtext = "select po_header_id, segment1, vendor_id, vendor_site_id  po_headers_all vendor_id=4993";                 using (oracledatareader rdr = cmd.executereader())                 {                     while (rdr.read())                         items.add(toorders(rdr));                 }             }         }          return items;     }      private static orders toorders(oracledatareader rdr)     {         orders data = new orders();          data.vendor_id = toint32(rdr, "vendor_id");         data.vendor_site_id = toint32(rdr, "vendor_site_id");         data.po_header_id = toint32(rdr, "po_header_id");         data.segment1 = convert.tostring(rdr["segment1"]);          return data;     }      private static int toint32(oracledatareader rdr, string name)     {         int index = rdr.getordinal(name);         return rdr.isdbnull(index) ? 0 : convert.toint32(rdr[index]);     }     #endregion 

i don't think possible.

1. how can use custom sql , apply odata parameters underlying query?

as far i'm aware, can't. whole point of odata library needs work off iqueryable. using custom sql in string have in example, can't combine odata parameters being passed in.

one approach have custom sql in sql view, add sql view ef model in same way add table - represented dbset tables are.

you can iqueryable represent dataset , apply odata parameters follows:

public ihttpactionresult getorders(odataqueryoptions<ordersview> queryoptions) {     iqueryable<ordersview> alldata = // ... dbset entity framework...      // apply odata query data set , pull data want database     var filteredresults = queryoptions.applyto(alldata) iqueryable<ordersview>;       return ok<iqueryable<ordersview>>(filteredresults); }