Expanding Lookup and filtering based on a value in related Entity - Web API roadblock

I had a requirement and when I wanted to achieve it using through web api, I faced a roadblock - in fact the result is not what I wanted. Instead I had to use fetchxml and achieved it. In the process - I posted my question in Stack Overflow and eventually raised a github issue. Microsoft (Jim Daly) actively support the queries in SO and github as well, they confirmed it as expected behavior.

The scenario is like this: I have to get all the Entity A records with an expected attribute value (filter) in related Entity B lookup. This should work in a way like INNER JOIN, so that the Entity A records with blank lookup will get filtered out. But the result is actually LEFT OUTER JOIN and even blank records without filter attribute value were returned.

Web API Query:

https://crmdev.crm.dynamics.com/api/data/v9.1/new_demo?$select=new_attribute_one&$expand=new_currentappointment_lookup($select=new_attribute_two;$filter=_new_user_lookup_value eq <guid>)

Expected Output:

  [
    {
      "@odata.etag": "W/\"608177550\"",
      "new_attribute_one": "Demo 1",
    
      "new_currentappointment_lookup": {
        "new_attribute_two": "testing comments",
        "_new_user_lookup_value": "guid",
      },
    }
  ]

Actual Output:

  [
    {
      "@odata.etag": "W/\"608177550\"",
      "new_attribute_one": "Demo 1",
    
      "new_currentappointment_lookup": {
        "new_attribute_two": "testing comments",
        "_new_user_lookup_value": "guid",
      },
    },
    {
      "@odata.etag": "W/\"608177790\"",
      "new_attribute_one": "Demo 2",
      
      "new_currentappointment_lookup": null,
    }
  ]

  

In a nutshell, this is working by design - we have to use fetchxml for getting this expected output. Web API endpoint cannot help you in this case.

Continue to website...