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 )

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?

user_4669344 · Answer

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.

user_38425 · Answer

It may not have been possible before, but it IS now possible to get INNER JOIN behavior via the Web API for a relationship one level deep by adding a top level filter against the navigation property. For your example, it would look like this:

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

A great place for help creating Web API queries is the FetchXML Builder tool in the XrmToolbox. Create your query in fetch and then in the View menu enable the OData 4.0 option and it will show you the equivalent Web API query (if it is possible).

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

2 Answers