Search code examples
c#odataasp.net-core-webapiodata-v4

How to POST OData entity and link it to multiple existing entities at the same time?

How do I post an entity to an OData endpoint while at the same time associating it with other existing entities in the body?

Consider the following class structure (sample):

class Invoice
{
    public int Id { get; set; }

    public Person Issuer { get; set; }

    public Person Recipient { get; set; }

    // other properties
}

class Person
{
    public int Id { get; set; }

    // other properties
}

Both Invoice and Person are entities in my domain (thus the Id property). Imagine that both are exposed in their own entitysets, so:

  • GET http://host/odata/People(1)

    returns Person with Id = 1

  • GET http://host/odata/Invoices(2)?$expand='Issuer, Recipient'

    returns Invoice with Id = 2 and both Issuer and Recipient expanded in the payload

Now consider the following requirement:

I want to create a new invoice in the system that will be associated to an existing issuer and recipient

How do I "tell" the OData framework that I want to associate a given navigation property to an existing entity? How would my controller action be notified that this is the intention?

Ideally, I'd like a POST body to look like this:

  • POST http://host/odata/Invoices

    { "Issuer": "/odata/People(1)", "Recipient": "/odata/People(2)", "Property1": "someValue", "Property2": "100", ... }

Once the server receives this payload, it should:

  1. Load the required "people(1)" Person for the Issuer property. If it does not exist, a bad request should be returned.
  2. Load the required "people(2)" Person for the Recipient property. If it does not exist, a bad request should be returned.
  3. Create a new Invoice instance and assign the Issuer and Recipient from above, then save it to the database.

I know OData has support for configuring the relationships after-the-fact with special PUT/POST URLs using the entity/relation/$ref syntax. With such syntax, I'd be able to do something like this:

  1. POST http://host/odata/Invoices

    { "Property1": "someValue", "Property2": "100" }

  2. PUT http://host/odata/Invoices(x)/Issuer/$ref

    {"@odata.id":"http://host/odata/People(1)"}

  3. PUT http://host/odata/Invoices(x)/Recipient/$ref

    {"@odata.id":"http://host/odata/People(2)"}

However, I want to be able to perform this all in a single POST operation that should atomically create the instance.

I tried a few ideas to see what the server would accept, and this seemed to go through:

{
    "Issuer": { "@odata.id": "/odata/People(1)" },
    "Recipient": { "@odata.id": "/odata/People(2)" },
    "Property1": "someValue",
    "Property2": "100",
    ...
}

But I have no idea how I'd be able to read/parse the IDs from this (like how it is done in a dedicated Ref method), or even if this is supported in the OData standard.

For now, I'll resort to just passing the ID property in the model and in the server assuming this will always mean an existing relationship, but that's far from ideal as it is not general-purpose enough and would make my API inflexible.

Solution

  • The simplest solution is to expose the ForeignKey properties in your Model directly. Even the Models used in the MS Doc Entity Relations in OData v4 Using ASP.NET Web API 2.2 explaining $ref expose the FKs.

    class Invoice
{
    public int Id { get; set; }

    public int Issuer_Person_Id { get; set; }
    [ForeignKey(nameof(Issuer_Person_Id)]
    public Person Issuer { get; set; }

    public int Recipient_Person_Id { get; set; }
    [ForeignKey(nameof(Recipient_Person_Id)]
    public Person Recipient { get; set; }

    // other properties
}

    This doesn't make your API inflexible, rather it makes your API MORE flexible. This also grants you greater control over the DataBase Implementation of your Model, whilst still being database engine agnostic.

    In environments where Lazy Loading is enabled, including FKs has some added performance benefits if you need to check the existence of a related entity without requiring it to be loaded into memory.

    NOTE: By including the FKs in the Model the $ref syntax and batching can still be used, but we now have access to the more practical FK Id values that can be easily validated in the server side code, just as it is easier to send the values.

    Now in the PATCH or POST we can simply use the Ids directly to link the Person records.

    The same level of information/understanding is required at both the client and server sides to achieve this, so it is still general purpose, the $metadata document fully describes which FK fields link the related entities but a good naming convention as demonstrated here can help

    {
    "Issuer_Person_Id": 1,
    "Recipient_Person_Id": 2,
    "Property1": "someValue",
    "Property2": "100",
    ...
}

    Be Careful:
    One of the reasons that many Model designers choose NOT to expose ForeignKey properties is that ambiguity exists when or if you try to send or process both the ForeignKey and the related Entity.
    For a PATCH there is no confusion, the v4.0 specification tells use specifically to ignore the related entity and that it shouldn't be sent at all.

    11.4.3 Update an Entity
    If an update specifies both a binding to a single-valued navigation property and a dependent property that is tied to a key property of the principal entity according to the same navigation property, then the dependent property is ignored and the relationship is updated according to the value specified in the binding.

    For a POST however if the related entity is provided in the request as well as the FK, the related entity is assumed to be a deep insert and the FK is ignored.

    11.4.2.2 Create Related Entities when Creating an Entity
    Each included related entity is processed observing the rules for creating an entity as if it was posted against the original target URL extended with the navigation path to this related entity.

    With FKs enabled My advice therefore is to take steps on the client side to make sure you don't try to send both the FK and the related entities in requests back to the API.

    I agree that the @odata.id in the post as you have suggested is a logical conclusion, however it raises other potential implementation issues which is why the protocol provides the concept of direct CRUD operations against the $ref endpoint that represents the ForeignKey reference.

    OData V4.0 was specifically declarative and designed such that operations against a single resource should only affect that resource. This is why we cannot PATCH related properties in a single query, as with this referencing issue, there are too many potential implementation variations and interpretations of how it might work that they kept the specification concise and constrained in the way that it is.

    Basically a consensus between interested parties could not be reached on the protocol specifics and guidance on how to handle deep updates before the specification was drafted. The ASP.Net FX and Core implementations (as of this post) are only OData 4.0 Minimal Conformance Level OOTB. There is a lot you need to do to increase the level of conformance.

    Batching is the preferred mechanism to perform operations that "might" affect multiple resources in a single transacted query, however it is a more involved solution than if you just expose the FKs!

    Whilst it's nice that we can use complicated syntax and batching to achieve this in other ways, there are many other practical benefits to exposing the FK Ids in the Model and making them accessible to the client side, not just in the server logic, IMO these can be really big benefits in the right scenarios:

    • Optimised data retrieval in Grids
      If many rows have a link to the same record in another table you only need to download the linked value from the common table once. For some types of data it will be more efficient to download all the possible lookup values from the common tables and then in your presentation layer you can join in the results based on the Ids. These lookup values may only need to be downloaded once in the whole session in some use cases.

    • ComboBox relationship assignments
      There is a time and a place, but by including the FKs in your Model it is very simple to bind to ComboBox or DropDownList implemenations in the presentation layer to change or assign the related entities, the implementation is almost identical to the grid presentation, bind the control the FK, and show the related entities in the drop down list.

    UPDATE 2022

    OData v4.01 Minimal Conformance level SHOULD support deep updates
    But the current version of ODataLib (v8) used by the .Net 5 runtime does not support this feature OOTB and is still only minimally compliant to v4.0, albeit with some of the more advanced features than before.

    11.4.3.1 Update Related Entities When Updating an Entity
    Payloads with an OData-Version header with a value of 4.01 or greater MAY include nested entities and entity references that specify the full set of to be related entities, or a nested delta payload representing the related entities that have been added, removed, or changed. Such a request is referred to as a “deep update”. If the nested collection is represented identical to an expanded navigation property, then the set of nested entities and entity references specified in a successful update request represents the full set of entities to be related according to that relationship and MUST NOT include added links, deleted links, or deleted entities.

    The json payload implementation is similar to your suggestion:

    {
    "@type":"#container.Invoice",
    "Issuer": { "@id": "People(1)" },
    "Recipient": { "@id": "People(2)" },
    "Property1": "someValue",
    "Property2": "100",
    ...
}

    There is now also a nested delta representation to Add, Remove or Update links as well as nested values in a single request, but these advanced mechanisms are not yet implemented in the ODataLib runtimes.