FetchXML - Find records not linked to specific record via N:N

Question

There are numerous posts for finding "not-in" to find records of type a that have no associations to record type b.

I want to extend this in my scenario I have a Database record type and a Server Upgrade record type with an N:N between them. (there is an N:N between database and server but that's not part of this query)

I want to find all database records that are not already linked to the specific server upgrade I am working on. My attempts are failing because the database can be linked to other server upgrade records

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
  <entity name="dsg_databases" >
    <attribute name="dsg_databasesid" />
    <filter type="and" >
      <condition entityname="ae" attribute="dsg_serverupgradeid" operator="neq" value="25dbe565-f435-e911-a976-000d3a2bcd97" />
    </filter>
    <link-entity name="dsg_dsg_serverupgrade_dsg_databases" from="dsg_databasesid" to="dsg_databasesid" link-type="outer" intersect="true" >
      <link-entity name="dsg_serverupgrade" from="dsg_serverupgradeid" to="dsg_serverupgradeid" link-type="outer" alias="ae" />
    </link-entity>
  </entity>
</fetch>

The reason being, in a plugin I then associate these databases to the server upgrade record but get an error Cannot insert duplicate key if they are already linked.

For reference and in case there's a better way, I take the entity collection returned by the FetchXML, convert to an EntityReferenceCollection ercDatabases and use service.Associate(targetEntity.LogicalName, targetEntity.Id, relationship, ercDatabases);

Edit - I'm trying to avoid cycling through each database record returned and checking whether they're associated. I'd rather do it in the single query for performance.

Answer 1

Moving the filter condition of the recordid you're trying to exclude to the intersect entity (ensuring it's an outer join) and ignoring the second join to the actual server upgrade record, then having a condition in the main entity filter pointing to the join record checking for null appears to work

<fetch top="50" >
  <entity name="dsg_databases" >
    <attribute name="dsg_databasesid" />
    <attribute name="dsg_name" />
    <filter type="and" >
      <condition entityname="ae" attribute="dsg_serverupgradeid" operator="null" />
    </filter>
    <link-entity name="dsg_dsg_databases_dsg_server" from="dsg_databasesid" to="dsg_databasesid" visible="false" intersect="true" >
      <link-entity name="dsg_server" from="dsg_serverid" to="dsg_serverid" alias="ad" >
        <filter type="and" >
          <condition attribute="dsg_serverid" operator="eq" value="98f46447-7f7b-e811-a95a-000d3a22cba0" />
        </filter>
      </link-entity>
    </link-entity>
    <link-entity name="dsg_dsg_serverupgrade_dsg_databases" from="dsg_databasesid" to="dsg_databasesid" link-type="outer" intersect="true" alias="ae" >
      <filter type="and" >
        <condition attribute="dsg_serverupgradeid" operator="eq" value="25dbe565-f435-e911-a976-000d3a2bcd97" />
      </filter>
    </link-entity>
  </entity>
</fetch>