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, Dictionary
2 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()" } } }
