Web API OData custom query issue

Question

I am new to Web API, Entity Framework and OData. I asked a similar question in another forum but haven't gotten a relevant response.

We have a OData compliant web api service for use in Salesforce. We have a custom complex query in Oracle that we need to expose.

I am not sure how to use a custom query like we want to also allow for odata parameter filtering to occur? ($filter, $top, $skip, etc) For example, when a $filter is used i want to apply that filter to the custom query and then send it back to the database to have it return the result set. How can i do this?

The issue i seem to have is that I can see the parameters as they come in but they are not translating to the query being passed to oracle. It seems that it will fire the query returning the full result set and then apply the parameters. This is very slow as the result set is very large.

I am hoping 2 figure out 2 things 1. How can i use custom sql and apply odata parameters to the underlying query? 2. When using EF or a custom query, how can i apply odata parameters to the query so that when the query is sent to the database that the $filter parameter, for example, is included in the query? I don't want the full result returned then apply the filter.

Can anyone give me some pointers on how to make this 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 the 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  from po_headers_all where 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

Answer 1

I don't think this is possible.

1. How can i use custom sql and apply odata parameters to the underlying query?

As far as I'm aware, you can't. The whole point of the OData library is that it needs to work off an IQueryable. By using custom SQL in a string like you have in your example, you can't combine it with the OData parameters that are being passed in.

One approach would be to have your custom SQL in a SQL view, then add the SQL view to your EF model in the same way as you would add a table - it will be represented as a DbSet just like tables are.

You can then get an IQueryable to represent the dataset and then apply the OData parameters as follows:

public IHttpActionResult GetOrders(ODataQueryOptions<OrdersView> queryOptions)
{
    IQueryable<OrdersView> allData = // ... get the DbSet from entity framework...

    // this will apply the OData query to the data set and only pull the data you want from the database
    var filteredResults = queryOptions.ApplyTo(allData) as IQueryable<OrdersView>;


    return Ok<IQueryable<OrdersView>>(filteredResults);
}