Web API OData with Stored Procedures

asp.net-web-api entity-framework odata


Is it possible to use OData's power without EF?

I'm utilizing an existing database and using stored procedures to retrieve data to build my models.

AFAIK, utilizing$filter for instance, the filter will be applied at the DB layer when using OData with EF.

Currently, the filter is applied after all results have been retrieved from the database.

I'd like to know how to modify it so that only the results I need will be returned because the processing time and server load are wasted in this manner.

Maybe working with EF would be simpler?

The database is quite complicated; most model objects are constructed using joins from numerous separate columns.

10/28/2013 2:35:13 PM

Accepted Answer

Your stored procedures can be imported using Entity Framework, and you can use the entities based on those stored procedures with the Web API OData EntitySetController / ODataController.

10/29/2013 5:29:03 PM

Popular Answer

This response focuses more on filtering and how everything functions in general, which might be useful to someone.

You stated:

The way it works now is that all results are brought back from the DB and only then the filter is applied.

Actually, there are two ways to retrieve data from OData (that I have run into). Having the database server deliver all data and then filtering is one option (highly inefficient - which I believe that is what you are alluding to). The second is to use EF to filter the OData arguments at the database level. The framework will provide a total number of records for things like paging (two queries made).

For instance, a filtered data request might appear as follows to show a list of people who are members of a particular group:

url: "/api/Users?$filter=USERGROUPS/any(usergroup: usergroup/ID eq '" + groupData.ID + "')"

You can do the following to make sure that the OData settings are applied to your EF database context:

public IEnumerable<USER> Get(ODataQueryOptions<USER> options)
    var unitOfWork = new ATMS.Repository.UnitOfWork(_dbContext);

    var users = options.ApplyTo(unitOfWork.Repository<USER>().Queryable
                                            .Include(u => u.USERGROUPS)
                                            .OrderBy(order => order.USERNAME))

    unitOfWork.Save();      // includes Dispose()

    return users;

Analyzing the information returned

enter image description here

In your situation, you might swap out theUSER the sproc entity that EF generates. Through the EDMX interface, I think it recognizes these as "complex objects," though I'm not positive.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow