Web API OData with Stored Procedures

Question

Can I leverage the power of OData without EF?

I'm using an existing DB and creating my models using stored procedures to retrieve from DB.

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

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

Since the processing time and server load are gone to waste this way, I'd like to know how I can customize it so that only the results that I need will get returned.

Maybe it would be easier to work with EF?

The DB is pretty complex - most model objects are built from many different tables using joins.

Answer 1

You can use Entity Framework to import your stored procedures and then use the entities based on those stored procs with the Web API OData ODataController / EntitySetController.

Answer 2

This reply is more about filtering and how it all works under the hood, which may be of benefit to someone.

You said:

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

There are actually two ways you can get data back from OData (that I have run into). One is to have the database server return all data, then filter (highly inefficient - which I believe that is what you are alluding to). The second is to pass the OData parameters through EF to filter at the database level. For things like paging, the framework will return a total number of records (two queries made).

For example, to display a list of users belonging to a specific group, a filtered data request would look something like:

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

What you want to ensure is that the OData options are applied to your EF database context, and can be done as follows:

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))
                                            .Cast<USER>().ToList();

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

    return users;
}

Looking at the data returned:

In your case, you could replace the USER entity with the sproc entity that EF creates. I believe it recognizes these as "complex objects" through the EDMX interface, but can't recall for certain.