CODEWITHSUNDEEP
selectodataexpandodata-v4
SeanJ_2173
SeanJ_2173

Reputation: 35

OData V4: Is it possible to only select fields from a parent entity (i.e. navigation property)?

In OData V3, I can select just fields from parent/ancestor entities like this: http://services.odata.org/V3/Northwind/Northwind.svc/Order_Details(OrderID=10248,ProductID=11)?&$select=Product/Category/CategoryName&$expand=Product/Category

That query returns only CategoryName, it does not include any fields from Order_Details or Product. This behavior is very important to our application for performance reasons. Selecting all fields when we don't need them can have a significant impact on query performance.

There does not seem to be a way to accomplish the same in OData V4. The equivalent query returns all fields from Order_Details and Product http://services.odata.org/V4/Northwind/Northwind.svc/Order_Details(OrderID=10248,ProductID=11)?$expand=Product($expand=Category($select=CategoryName))

The closest I can get is to just select one field from each level, introduces a lot of complexity into our code, and it has been difficult to ensure that all queries (future and existing) adhere to this rule.

Upvotes: 1

Views: 2793

Answers (3)

Chris Schaller
Chris Schaller

Reputation: 16757

The syntax for this is:

https://services.odata.org/V4/Northwind/Northwind.svc/Order_Details(OrderID=10248,ProductID=11)/Product/Category?$select=CategoryName

results in:

{
    "@odata.context": "https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Categories(CategoryName)/$entity",
    "CategoryName": "Dairy Products"
}

The key OData v4 concept here is that the path, that is everything before the ? defines the resource that is being served, and by that the shape of the resulting graph. The output of $select and $expand (projections) is constrained to match the requested resource.

  • So in v3 with $select you could return a more arbitrary structure but in v4 the $select and $expand can only mask the graph by returning what is essentially a subset of $select=*&$expand=*.

To get around this but still allow similar query scenarios in v4 we can compose an entity path expression to *any resource within the parent path. So we move the resource selector path from the v3 $select Product/Cateogry' and append it the path of our resource ~/Order_Details(OrderID=10248,ProductID=11)`

NOTE: There is a strong caveat to this, whilst the OData specification describes this behaviour, not all implementations support deep resource selections like this. The specification is a guidance document on the standard protocol, not all implemenations are 100% compliant.

A simplification of this is to try selecting just the Product from the same query, notice here we do not use any query parameters at all:

~/Order_Details(OrderID=10248,ProductID=11)/Product

{
    "@odata.context": "https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products/$entity",
    "ProductID": 11,
    "ProductName": "Queso Cabrales",
    "SupplierID": 5,
    "CategoryID": 4,
    "QuantityPerUnit": "1 kg pkg.",
    "UnitPrice": 21.0000,
    "UnitsInStock": 22,
    "UnitsOnOrder": 30,
    "ReorderLevel": 30,
    "Discontinued": false
}

You can see in this response that the context or the resource that is being returned is a $metadata#Products/$entity and not an Order_Details/$entity

Once your resource is selected, normal v4 $select and $expand logic is evaluated. This is documented in the specification under 4.3 Addressing Entities

These rules are recursive, so it is possible to address a single entity via another single entity, a collection via a single entity and even a collection via a collection; examples include, but are not limited to:

  • By following a navigation from a single entity to another related entity (see rule: entityNavigationProperty)
    Example 14: http://host/service/Products(1)/Supplier

Update:

I've substantially edited this post from my original answer, at the time i misinterpreted OP's request and the structure they were expecting, but this is still relevant information in 2022 and none of the answers directly produces the desired behaviour.

Upvotes: 1

Kaleb Luse
Kaleb Luse

Reputation: 28

The closest I can get is to just select one field from each level, introduces a lot of complexity into our code, and it has been difficult to ensure that all queries (future and existing) adhere to this rule.

Looks something like this:

http://services.odata.org/V4/Northwind/Northwind.svc/Order_Details(OrderID=10248,ProductID=11)?$expand=Product($select=Category;$expand=Category($select=CategoryName))&$select=Product

There is certainly a bit of added complexity here, but this was acceptable in my case.

Upvotes: 1

st35ly
st35ly

Reputation: 1255

The simplest solutions would be to create View with required schema on your db server and try to fetch data from this datasource with filters and column name(s) instead.

Especially when facing issues with performance.

The best way would be to register this class to your IoC as singleton

public class InternalODataEdmModelBuilder
{
    private readonly ODataConventionModelBuilder _oDataConventionModelBuilder = new ODataConventionModelBuilder();
    private IEdmModel _edmModel;

    public InternalODataEdmModelBuilder()
    {
        ODataEntitySetsConfigInternal.Register(_oDataConventionModelBuilder);
    }

    // cache
    public IEdmModel GetEdmModel()
    {
        return _edmModel ?? (_edmModel = _oDataConventionModelBuilder.GetEdmModel());
    }
}

internal static class ODataEntitySetsConfigInternal
{
    public static void Register(ODataConventionModelBuilder oDataModelBuilder)
    {
        if (oDataModelBuilder == null)
        {
            throw new Exception("'ODataConventionModelBuilderWebApi' cannot be null");
        }

        oDataModelBuilder.EntitySet<YourView>("YourView").EntityType.HasKey(x => x.YourKey);
    }
}

And then inject this registered object in your API controller and build your query from URL like this: 

        ODataQueryContext queryContext = new ODataQueryContext(_oDataConventionModel, typeof(YourViewEFType), null);
        var option = new ODataQueryOptions(queryContext, Request);
        var data = option.ApplyTo(data, new ODataQuerySettings { EnsureStableOrdering = false });

And then map data into your POCO (API EDM model shown to the public world).

Hope this helps.

Upvotes: 0

Related Questions