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/

Yi Ding - MSFT · Answer 1 · 2014-10-22T07:01:41.490

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.

You are correct in that "EnableQuery" does work with the expand. I should have explained my problem better. The "EnableQuery" does not seem to be working when using a StoredProcedure along with paging. Like "PageResult"... — goroth, Oct 20 '14 at 19:14
@goroth Why would you using PageResult as you described that the return value will be a single "Product" record. But if you are returning a collection of Products along with paging, you can have the return type as "IQueryable" as use "[EnableQuery(PageSize = 10)]" to do it. — Yi Ding - MSFT, Oct 21 '14 at 11:21
The key word is "StoredProcedure". Since a stored procedure will always return all records, I think I have to use "PageResults" to first get the "total count" and next set the "take / skip" on the return result. — goroth, Oct 21 '14 at 12:48

score 1 · Accepted Answer · answered Oct 21 '14 at 18:40

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

Webapi odata expand with entity framework functions

2 Answers2

Linked