How can I expose Querable API using OData for EF Core Keyless Entity(Database view)?

Question

I am using https://www.nuget.org/packages/Microsoft.AspNetCore.OData/7.3.0 to expose OData 4 based Querable API using ASP.NET Core 3.1 API

I have EF Data context with keyless entity(Database view), When I try to expose it with OData Convention based model

   modelBuilder
   .Entity<Student>(eb =>
    {
        eb.HasNoKey();
        eb.ToView("vw_students", "public");
    });

And here is my OData EDM Model mapping with EF Core entity type

var edmBuilder = new ODataConventionModelBuilder();
edmBuilder.EntitySet<Student>("Students");       
return edmBuilder.GetEdmModel();

I get an error on this line edmBuilder.GetEdmModel() The entity set 'Students' is based on type 'ODataCore31.Student' that has no keys defined.

My questions- 1) Are Keyless entity types are natively supported by OData model? 2) Is there any workaround?

Answer 1

To expose a Keyless View or custom query via OData you must use an Unbound Function instead of a full OData Controller, which is configured via the EntitySet<T>() expression in the conventional builder.

This makes sense because an EntitySet Controller allows item based operations but without a specific Key there is no conventional way to address a specific Item as its own Resource.

A keyless type is therefore a ComplexType and not an Entity, by definition an Entity MUST have a key so that OData can support by item or key based navigation.

The OData way to expose a Keyless collection as an Unbound Function involves 3 important steps:

1. Declare your query in a controller so that it returns an IQueryable<T>

A specific quirk about an Unbound Function or Action is that they are not specifically bound to any controller, so you can declare your unbound endpoints in any controller of your choosing.

I prefer to create a specific controller to host all unbound components, the name is not important as long as it does not clash with other controllers or entity types in your domain.

As with the default collection endpoints, we need to make sure our endpoint returns an IQueryable<Student>, you can still use the IActionResult response type if you need that level of abstraction, but the implementation should be queryable.

2. Set the specific route for the method using ODataAttributeRouting.

This is a crucial element, in usual convention-based implementations we do not declare specific routes on each method, each controller's route prefix is resolved from the controller name and the endpoints are resolved from the method names.

It helps to follow conventions as much as possible to keep your implementation transparent and configurable, if you always declare specific routes then it is important that the routes you configure match the registration with the ODataConventionModelBuilder.

In the specific case for an unbound endpoint, there is no controller element in the route at all, which has two implications: you cannot use a route prefix specified at the controller level because leaving the prefix empty in an ODataRouteComponent will trigger the default logic which will use the name of the controller class; and you must declare the route on the endpoint itself as a full route because all the convention based routing depends on the controller element in the route to map to the controller to resolve the possible endpoints, so the runtime won't know which controller to inspect.

You can write your own route resolution logic but that is out of scope for this issue.

This is a minimal example controller implementation:
NOTE: "odata" is the routePrefix registered to the OData service in this example

[ODataAttributeRouting]
public partial class ViewsController : ODataController
{
    protected AppDbContext _db;
    public ViewsController(AppDbContext context)
    {
        _db = context;
    }

    [HttpGet("odata/" + nameof(Students))]
    [EnableQuery]
    public IQueryable<Student> Students(ODataQueryOptions<Student> _queryOptions)
    {
        return this._db.Students;
    }
}

3. Register/Map the route in the ODataConventionModelBuilder

This step is often overlooked, if you skip this step the endpoint can be queried via HTTP but this is a standard Web API implementation and not a conventional OData implementation, so the endpoint will not be documented in the CSDL ($metadata) or some swagger implementations and as a result will not be available to OData client generators.

var edmBuilder = new ODataConventionModelBuilder();
edmBuilder.Function("Students").ReturnsCollection<Student>();     
return edmBuilder.GetEdmModel();

An inline services registration would look like this, notice the "odata" routePrefix declared in the AddRouteComponents method, this MUST match the custom route declaration prefix.

services.AddControllers().AddOData(opt =>
{
    var edmBuilder = new ODataConventionModelBuilder();
    edmBuilder.Function("Students").ReturnsCollection<Student>();   

    opt.AddRouteComponents("odata", edmBuilder.GetEdmModel()).EnableQueryFeatures();
}

---

This solution was tested and written using OData 8.0 (for .Net 5.0), the concepts are fully compatible with OP's requested ASP.NET Core 3.1 and OData Lib v7.3 however some of the namespaces are different. 7.3 can easily be upgraded to 8 or 9 and I encourage you to do so as there are significant performance and functional improvements in the latest releases.