2

Problem

I'm trying to query records from Dynamics 365 using the Web API where a key attribute is null.

Approach

For testing purposes I created an entity with a string attribute, an integer attribute, and a decimal attribute. I then created an alternate key and put those three attributes in it, making this combination of attributes unique. I then created some records.

Querying the data using the Web API works as expected. I created a record with those values:

{ "my_string_key": "s1", "my_integer_key": 1, "my_decimal_key": 1.23 }

And query it like this:

/my_entities(my_string_key='s1',my_integer_key=1,my_decimal_key=1.23)

This returns the desired record.

However I can't get it to work when any of the key fields is empty, it always returns a 400 with message "Bad Request - Error in query syntax". Just for clarification: I purposely created records where one of the key attributes is empty, like this:

{ "my_integer_key": 1, "my_decimal_key": 1.23 } { "my_string_key": "s1", "my_decimal_key": 1.23 } { "my_string_key": "s1", "my_integer_key": 1 }

Notice how each record is missing one of the key attributes, which effectively leaves it at null.

I tried using =null, =empty, ='', = and =Microsoft.OData.Core.ODataNullValue but nothing works. I tried to query them like this:

/my_entities(my_string_key=null,my_integer_key=1,my_decimal_key=1.23) /my_entities(my_string_key='s1',my_integer_key=null,my_decimal_key=1.23) /my_entities(my_string_key='s1',my_integer_key=1,my_decimal_key=null)

I also tried omitting the attribute with null in it, like this:

/my_entities(my_integer_key=1,my_decimal_key=1.23) /my_entities(my_string_key='s1',my_decimal_key=1.23) /my_entities(my_string_key='s1',my_integer_key=1)

None of this works. Can anyone provide a solution?

Final Words

I know that key attributes shouldn't be empty because that's not the point of key attributes. My scenario however requires to handle this specific case.

I also know that I could just use the $filter parameter, but I'd like a solution without having to resort to this "workaround". After all, it is a key and I'd like to treat it as one.

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
Daniel Schmid
  • 362
  • 1
  • 5
  • 20

1 Answers1

0

I think your efforts prove that key attributes cannot be empty. So, time for another approach.

Create a custom attribute and fill it with the combined values of the three key attributes. You can do this in a plugin registered in the pre operation stage of the Create and eventually Update messages of your entity.

Use the custom attribute as the key attribute.

Henk van Boeijen
  • 7,357
  • 6
  • 32
  • 42
  • I don't think that this proves that key attributes cannot be empty, it rather proves that I don't know of a way to query empty attributes. Either I want a way to query empty key attributes, or I'd like a documentation stating that this is not possible. Anything else is a workaround and doesn't answer my question. Thanks for replying though. – Daniel Schmid Apr 26 '18 at 07:06
  • In that case you can specify a filter, can't you? – Henk van Boeijen Apr 26 '18 at 14:24
  • As I said: I also know that I could just use the $filter parameter, but I'd like a solution without having to resort to this "workaround". – Daniel Schmid Apr 27 '18 at 06:53