How do I expose a table valued function as a property of an entity set within a Web Api 2 OData v4 service?

Question

Please I need help figuring out how to expose a table valued function as a property of an entity set in a Web Api 2 OData v4 service.

My simplified schema has three tables, Structures, Locations, and LocationLinks. A structure contains a graph with nodes (Locatons) and edges (LocationLinks). I access with an Entity Framework 6 Database first model.

Simplified Schema

  Structure:
      ID

  Locations:
      ID
      ParentID -> Structure

  LocationLinks
      A -> Location
      B -> Location

The goal is to access a structures collection of LocationLinks the same way I access a structure's location. i.e. To request structure #180's graph:

http://.../OData/Structures(180)/LocationLinks
http://.../OData/Structures(180)/Locations

The Locations query works automatically but I can't figure out how to add the right route to enable the LocationLinks query. Thinking it would make my task easier I have added a Table Valued Function to my SQL server. The function is present in my EF model and returns a collection of LocationLink entities:

StructureLocationLinks(@StructureID) -> LocationLinks

Unfortunately no matter what I try I cannot seem to make the Structure(180)\LocationLinks URL functional. This is my latest attempt:

StructuresController.cs snippet:

        // GET: odata/Structures(5)/Locations
        [EnableQuery]
        public IQueryable<Location> GetLocations([FromODataUri] long key)
        {
            return db.Structures.Where(m => m.ID == key).SelectMany(m => m.Locations);
        }

        // GET: odata/Structures(5)/LocationLinks
        [EnableQuery]        
        //[System.Web.OData.Routing.ODataRoute("Structures({key})")]
        public IQueryable<LocationLink> GetLocationLinks([FromODataUri] long key)
        {
            return db.StructureLocationLinks(key);
        }

WebApi.cs snippet:

public static void Register(HttpConfiguration config)
        {
            // Web API configuration and services
            var json = GlobalConfiguration.Configuration.Formatters.JsonFormatter;
            json.UseDataContractJsonSerializer = true;
            //json.SerializerSettings.PreserveReferencesHandling = Newtonsoft.Json.PreserveReferencesHandling.All;

            //var cors = new System.Web.Http.Cors.EnableCorsAttribute("*", "*", "*");
            //config.EnableCors(cors);

            // Web API routes 
            config.MapHttpAttributeRoutes();

            ODataConventionModelBuilder builder = GetModel();

            config.MapODataServiceRoute(routeName: "odata",
                routePrefix: null,
                model: builder.GetEdmModel());

            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );
        }

    public static ODataConventionModelBuilder GetModel()
        {
            ODataConventionModelBuilder builder = new ODataConventionModelBuilder();

            builder.Namespace = typeof(Structure).Namespace;

            AddLocationLinks(builder);
            AddStructures(builder);
            builder.EntitySet<Location>("Locations");

            return builder;
        }

        public static void AddStructures(ODataModelBuilder builder)
        {
            var structSetconfig = builder.EntitySet<Structure>("Structures");
            var structConfig = structSetconfig.EntityType;

            var functionConfig = structConfig.Collection.Function("StructureLocationLinks");
            functionConfig.Parameter<long>("StructureID");
            functionConfig.Returns<LocationLink>();
        }


        public static void AddLocationLinks(ODataModelBuilder builder)
        {
            var type = builder.EntityType<LocationLink>();
            type.HasKey(sl => sl.A);
            type.HasKey(sl => sl.B);
            builder.EntitySet<LocationLink>("LocationLinks");
        }

The error I recieve is:

{ "error":{ "code":"","message":"No HTTP resource was found that matches the request URI 'http://.../OData/Structures(180)/LocationLinks'.","innererror":{ "message":"No routing convention was found to select an action for the OData path with template '~/entityset/key/unresolved'.","type":"","stacktrace":"" } } }

Based on some searching I attempted to add an ODataRoute attribute to the controller:

// GET: odata/Structures(5)/LocationLinks
[EnableQuery]        
[System.Web.OData.Routing.ODataRoute("Structures({key})/LocationLinks")]
public IQueryable<LocationLink> GetLocationLinks([FromODataUri] long key)
{
    return db.StructureLocationLinks(key);
}

Which results in this error:

The path template 'Structures({key})/LocationLinks' on the action 'GetLocationLinks' in controller 'Structures' is not a valid OData path template. Found an unresolved path segment 'LocationLinks' in the OData path template 'Structures({key})/LocationLinks'.

How can I make expose the LocationLinks from the Structures collection? Thank you for your time.

Edit:

I managed to get this working after finding this question: Adding a custom query backed Navigation Property to ODataConventionModelBuilder

I had to call .GetEdmModel on my ODataConventionBuilder object, then add the navigation property to the model with this function:

private static Microsoft.OData.Edm.IEdmModel AddStructureLocationLinks(IEdmModel edmModel)
        { 

            var structures = edmModel.EntityContainer.FindEntitySet("Structures") as EdmEntitySet;
            var locationLinks = edmModel.EntityContainer.FindEntitySet("LocationLinks") as EdmEntitySet;
            var structType = structures.EntityType() as EdmEntityType;
            var locLinksType = locationLinks.EntityType() as EdmEntityType;

            var structLocLinksProperty = new EdmNavigationPropertyInfo();
            structLocLinksProperty.TargetMultiplicity = Microsoft.OData.Edm.EdmMultiplicity.Many;
            structLocLinksProperty.Target = locLinksType;
            structLocLinksProperty.ContainsTarget = true; 
            structLocLinksProperty.OnDelete = Microsoft.OData.Edm.EdmOnDeleteAction.None;
            structLocLinksProperty.Name = "LocationLinks";

            var navigationProperty = structType.AddUnidirectionalNavigation(structLocLinksProperty);
            structures.AddNavigationTarget(navigationProperty, locationLinks);

            return edmModel; 
        }

The issue I have now is that I have limited ability to access the navigation property in queries. For example this link works:

http://.../OData/Structures(180)/Children?$expand=Locations

While this does not.

http://.../OData/Structures(180)/Children?$expand=LocationLinks

The error returned is

{ "error": { "code":"","message":"An error has occurred.","innererror": { "message":"Instance property 'LocationLinks' is not defined for type 'ConnectomeDataModel.Structure'", "type":"System.ArgumentException","stacktrace":" at System.Linq.Expressions.Expression.Property(Expression expression, String propertyName)\r\n at System.Web.OData.Query.Expressions.SelectExpandBinder.CreatePropertyValueExpressionWithFilter(IEdmEntityType elementType, IEdmProperty property, Expression source, FilterClause filterClause)\r\n at System.Web.OData.Query.Expressions.SelectExpandBinder.BuildPropertyContainer(IEdmEntityType elementType, Expression source, Dictionary2 propertiesToExpand, ISet1 propertiesToInclude, ISet1 autoSelectedProperties, Boolean isSelectingOpenTypeSegments)\r\n at System.Web.OData.Query.Expressions.SelectExpandBinder.ProjectElement(Expression source, SelectExpandClause selectExpandClause, IEdmEntityType entityType)\r\n at System.Web.OData.Query.Expressions.SelectExpandBinder.Bind(IQueryable queryable)\r\n at System.Web.OData.Query.ODataQueryOptions.ApplySelectExpand[T](T entity, ODataQuerySettings querySettings)\r\n at System.Web.OData.Query.ODataQueryOptions.ApplyTo(IQueryable query, ODataQuerySettings querySettings)\r\n at System.Web.OData.EnableQueryAttribute.ExecuteQuery(Object response, HttpRequestMessage request, HttpActionDescriptor actionDescriptor)\r\n at System.Web.OData.EnableQueryAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)\r\n at System.Web.Http.Filters.ActionFilterAttribute.OnActionExecutedAsync(HttpActionExecutedContext actionExecutedContext, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter1.GetResult()\r\n at System.Web.Http.Controllers.ActionFilterResult.d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()\r\n at System.Web.Http.Dispatcher.HttpControllerDispatcher.d__1.MoveNext()" } } }

Answer 1

Xycor

As mentioned from you, LocationLinks should be same as Locations for Structure. So, What you have done for controller and action, I think, is correct. I have a test based on your description and it seems Web API OData can route the GetLocationLinks by convention.

Let me show my metadata document, please ignore the namespace and compare it with yours and let me know any difference. Thanks.

<Schema Namespace="ODataConsoleSample" xmlns="http://docs.oasis-open.org/odata/ns/edm">
  <EntityType Name="LocationLink">
    <Key>
      <PropertyRef Name="A" />
      <PropertyRef Name="B" />
    </Key>
    <Property Name="A" Type="Edm.Int64" Nullable="false" />
    <Property Name="B" Type="Edm.Int64" Nullable="false" />
  </EntityType>
  <EntityType Name="Structure">
    <Key>
      <PropertyRef Name="Id" />
    </Key>
    <Property Name="Id" Type="Edm.Int64" Nullable="false" />
    <NavigationProperty Name="Locations" Type="Collection(ODataConsoleSample.Location)" />
    <NavigationProperty Name="LocationLinks" Type="Collection(ODataConsoleSample.LocationLink)" />
  </EntityType>
  <EntityType Name="Location">
    <Key>
      <PropertyRef Name="Id" />
    </Key>
    <Property Name="Id" Type="Edm.Int64" Nullable="false" />
    <Property Name="ParentId" Type="Edm.Int64" Nullable="false" />
  </EntityType>
  <Function Name="StructureLocationLinks" IsBound="true">
    <Parameter Name="bindingParameter" Type="Collection(ODataConsoleSample.Structure)" />
    <Parameter Name="StructureID" Type="Edm.Int64" Nullable="false" />
    <ReturnType Type="ODataConsoleSample.LocationLink" />
  </Function>
  <EntityContainer Name="Container">
    <EntitySet Name="LocationLinks" EntityType="ODataConsoleSample.LocationLink" />
    <EntitySet Name="Structures" EntityType="ODataConsoleSample.Structure">
      <NavigationPropertyBinding Path="Locations" Target="Locations" />
      <NavigationPropertyBinding Path="LocationLinks" Target="LocationLinks" />
    </EntitySet>
    <EntitySet Name="Locations" EntityType="ODataConsoleSample.Location" />
  </EntityContainer>
</Schema>