Webapi odata expand with entity framework functions

Question

I have a Product odata controller and a Product Category odata controller.
They are both using entity framework entities and have navigation methods used for odata expand.
The expand for both is working fine.
Now I added a stored procedure in entity framework to manipulate the data returned from the database and still return a "Product" record.
I set the entity stored procedure function return type to "Product" and created a new function in the Product odata controller to call the entity function and return "Product".
I can call the function from a url and this is returning a Product entity / json correctly.
Now I need to call the expand on the url to get the "Product Category" entity but this fails.

I looked into this article but this is based on non-entity models. My entities are all correct and functioning fine.
http://beyondtheduck.com/projecting-and-the-odata-expand-query-option-possible-at-last-kinda/

Answer 1

According to your description, it seems that you need to add the [EnableQuery] attribute to the controller method for the stored procedure.

The following implementation works for me:

In WebApiConfig.cs:

builder.EntityType<Product>().Function("SomeFunction").ReturnsFromEntitySet<Product>("Products");

In ProductsController.cs:

[HttpGet]
[EnableQuery]
public IHttpActionResult SomeFunction()
{
    return Ok(products.FirstOrDefault(c => c.ID == 1));
}

In browser:

GET http://localhost:54017/Products(1)/Default.SomeFunction()?$expand=Categories

gives

{
    @odata.context: "http://localhost:54017/$metadata#Products",
    value: [
    {
        ID: 1,
        Name: "Some",
        Categories: [
        {
            ID: 1,
            Name: "Some"
        }
        ]
    }
    ]
}

Updated on 10/22/2014:

I've modified the code you attached and attach it down below. Would you try if it works?

[HttpPost]
[EnableQuery(PageSize=10)]
public IHttpActionResult SomeFunction()
{
    var results = db.SomeStoredProc().ToList();
    return Ok(results);
}

Similar function worked in my tests. The reason that this would work is that Web API OData handles the $skip, $top, and paging for you automatically. You don't need to worry about applying them to your result. The query options from the client will be applied to the whole set you return.

Answer 2

Here is the code I used to fix the problem.
By no means is it "correct" code.
For example: ODataQueryOptions.Top / Skip will be null if used on an Action that contains ODataActionParameters.
ODataActionParameters will contain the Top / Skip as a parameter? Very odd.
So I added both in the hopes that Microsoft or someone else can fix this issue in the future.

Controller:

[HttpPost]
[EnableQuery]
public PageResult<SomeObject> SomeFunction(ODataQueryOptions<SomeObject> options, ODataActionParameters parameters)
{
    // Get the paging settings from ODataActionParameters since they are not shown on the ODataQueryOptions. Maybe there will be some fix for this in the future.
    int pageSize = (int)parameters["pageSize"];
    int take = (int)parameters["take"];
    int skip = (int)parameters["skip"];
    int page = (int)parameters["page"];

    // Apply page size settings
    ODataQuerySettings settings = new ODataQuerySettings();

    // Create a temp result set to hold the results from the stored procedure
    var tempResults = db.SomeStoredProc().ToList(); // ToList is required to get the "real" total count before paging

    // Apply the query options. For now this is only needed to get the correct count since the options does not seem to contain the TOP / SKIP when using OData parameters.
    IQueryable results = options.ApplyTo(tempResults.AsQueryable(), settings);

    // This was needed for custom paging. EXAMPLE: http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/supporting-odata-query-options
    return new PageResult<SomeObject>(tempResults.Skip(skip).Take(take),
                            Request.ODataProperties().NextLink,
                            Request.ODataProperties().TotalCount);
}

Then WebApiConfig:

var SomeFunction = builder.Entity<SomeObject>().Collection.Action("SomeFunction");
SomeFunction.Parameter<int>("take");
SomeFunction.Parameter<int>("skip");
SomeFunction.Parameter<int>("page");
SomeFunction.Parameter<int>("pageSize");
SomeFunction.ReturnsCollectionFromEntitySet<SomeObject>("SomeObjects");