Office 2016 constructs incorrect OData request, when requesting data from remote server

Question

While playing with Excel 2016 I came across strange behavior while trying to get the data from remote OData service. It seems like the OData query created by excel is not well constructed.

the query looks like this:

http://[host]/[odataroot]/[entitysetname]?$filter=id eq null and ' 1bd07c16-d344-4d87-8622-0211710c2ad1 ' eq null

To provide you with some more info I created a simplest edml as possible, the metadata xml looks like this:

<?xml version="1.0" encoding="utf-8"?>

<Edmx Version="4.0" xmlns="http://docs.oasis-open.org/odata/ns/edmx">

  <DataServices>

    <Schema Namespace="MyNamespace" xmlns="http://docs.oasis-open.org/odata/ns/edm">

      <ComplexType Name="Reference">

        <Property Name="target" Type="Edm.String" />

        <Property Name="name" Type="Edm.String" />

      </ComplexType>

      <EntityType Name="MyEntity">

        <Key>

          <PropertyRef Name="id" />

        </Key>

        <Property Name="id" Type="Edm.Guid" />

        <Property Name="lookup" Type="MyNamespace.Reference" />

      </EntityType>

      <EntityContainer Name="DefaultContainer">

        <EntitySet Name="MyEntity" EntityType="MyNamespace.MyEntity" />

      </EntityContainer>

    </Schema>

  </DataServices>

</Edmx>

As you can see there is one single entity that contains one primary key. the type of the id property is Guid. (this seems to be a problem, if the type of id is string, the query is constructed as requested)

If I connect from excel 2016 to the remote server (provider of the odata) the entity collection is displayed and by clicking on the sample EntitySet the single row of data is displayed:

Clicking on the MyEntity brings the content of that entity:

Until now everything was fine. If user clicks on the Record link, excel sends request to OData server that seems broken. $filter clause looks like this:

$filter=id eq null and '1bd07c16-d344-4d87-8622-0211710c2ad1' eq null

I think that expected query looks like this:

$filter=id eq ' 1bd07c16-d344-4d87-8622-0211710c2ad1 '

One more note: If the type of the id property is string the filter query is constructed as expected.

Can you confirm this strange behavior?

The data sent from server to excel are in JSON format:

{ "@odata.context":"http://localhost:6738/odata/v4/rohel/$metadata#MyEntity", "value": [{"@odata.id":"http://localhost:6738/odata/v4/rohel/MyEntity(1bd07c16-d344-4d87-8622-0211710c2ad1)", "id":"1bd07c16-d344-4d87-8622-0211710c2ad1", "lookup": { "target":"targetValue", "name":"nameValue" } }] }

Answer 1

Hi Martin,

I'm on the engineering team behind Excel's OData support, no need to go to TechNet.

Thanks for the all the helpful information in your post!

I was able to reproduce the same problem with broken $filter on GUID keys.

We're tracking the bug now and we'll work to get a fix out ASAP!

As for the Guid vs. string:

M doesn't have a datatype for Guid, so we show it as string.

Generally that doesn't cause any problems, but interestingly that does seem to be the exact problem here.

Thanks,

Carl

Answer 2

Hi Martin,

Thank you for posting your query in Microsoft Community.

I suggest you to post this query in following TechNet forum for better suggestion.

https://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel

If you have any questions related to Office products, you can always reply and I’ll be happy to assist you.

Thank you.