CRM web api expand along with filter - inner join or left join?

Question

I'm trying to use web api query and without fetchxml, I was able to build this endpoint but the resultset is behaving like LEFT OUTER JOIN, but I need INNER JOIN.

new_demo is having lookup of new_currentappointment (most recent record is captured in here from a subgrid list), new_currentappointment is having new_user lookup.

I want the list of new_demo with new_currentappointment_lookup where new_user_lookup is the filter.

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>)

The result is bringing the every single new_demo in the system, but the expand filter only results null. How to eliminate the filtered null result from expanded entity in main result?

"value": [
    {
      "@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,
    }
  ]

This result explained in ASP.NET web api documentation is what I'm looking for, but I could not find one for Dynamics CRM web api. Any other easy way I'm missing?

Answer 1

From Doc feedback: CRM web api expand along with filter - inner join or left join?

This is the equivalent query to your scenario:

{{webapiurl}}incidents?$select=title
&$expand=customerid_account($select=name;
$expand=primarycontactid($select=fullname;
$filter=contactid eq '384d0f84-7de6-ea11-a817-000d3a122b89'))

The $filter just controls whether the record is expanded or not.

It is the difference between this:

{
    "@odata.etag": "W/\"31762030\"",
    "title": "Sample Case",
    "incidentid": "d3d685f9-cddd-ea11-a813-000d3a122b89",
    "customerid_account": {
        "name": "Fourth Coffee",
        "accountid": "ccd685f9-cddd-ea11-a813-000d3a122b89",
        "primarycontactid": {
            "fullname": "Charlie Brown",
            "contactid": "384d0f84-7de6-ea11-a817-000d3a122b89"
        }
    }
}

And this:

{
    "@odata.etag": "W/\"31762030\"",
    "title": "Sample Case",
    "incidentid": "d3d685f9-cddd-ea11-a813-000d3a122b89",
    "customerid_account": {
        "name": "Fourth Coffee",
        "accountid": "ccd685f9-cddd-ea11-a813-000d3a122b89",
        "primarycontactid": null
    }
}

See this example (Ctrl+F "Nested Filter in Expand") where all People are returned but only the Trips matching the $filter are expanded.

So the $filter behavior in the expansion will just control whether the detail will be returned or not. It isn't a INNER JOIN behavior. You will need to use FetchXml to achieve this.