How to create a custom bound OData function

Sometimes you have to create a custom function in your OData WebAPI project to return custom logic or call a stored procedure to return a result set that doesn't come out of the box with OData. In order to do this, create a custom OData function. Specifically a bound function which is a function that is associated with a specific entity type or entity set.

Define function

Start by defining the bound function in Program.cs where the other OData entities are defined. In this example and the rest of the example I'm going to call my bound class or entity TEntity. This could be any of your entity types. The function GetRelatedEntities is the name of my custom function. It's bound to the TEntity type by the ReturnsCollectionFromEntitySet<TEntity>("TEntities"). This tells OData that the return type of the function is going to be of type TEntities.

var entityType = builder.EntityType<TEntity>();
        entityType.Collection.Function("GetRelatedEntities").ReturnsCollectionFromEntitySet<TEntity>("TEntities");

Implement logic in the controller

Create a new method for the custom function in your controller for TEntity. This logic executes a custom stored procedure that returns data in the same format as the TEntity model. In order to execute a stored procedure the results must match the type of entity the SQL is called from. In this case, TEntities.

public class TEntitiesController : ODataController
{
    private readonly YourDbContext _context;

    public TEntitiesController(YourDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    [EnableQuery]
    public IQueryable<TEntity> GetRelatedEntities()
    {
        var entities = _context.TEntities
                       .FromSqlRaw("Exec GetEntities")
                       .AsQueryable();
 
        return entities;
    }
}

That's all that is needed to create a custom bound OData function. To view the results of this function you'd call it at the end of the url like:
https://localhost:5001/odata/TEntities/GetRelatedEntities()