Joindre des tables à l’aide de FetchXml

Comme décrit dans Les données de requête à l’aide de FetchXml, démarrez votre requête en sélectionnant une table à l’aide de l’élément d’entité.

Utilisez l’élément link-entity pour décrire les données des tables associées à retourner avec votre requête avec les attributs suivants :

Par exemple, la requête suivante retourne jusqu’à 5 enregistrements du compte et des tables de contacts en fonction de la colonne de recherche PrimaryContactId dans l’enregistrement du compte :

<fetch top='5'>
   <entity name='account'>
      <attribute name='name' />
      <link-entity name='contact'
         from='contactid'
         to='primarycontactid'
         link-type='inner'
         alias='contact'>
         <attribute name='fullname' />
      </link-entity>
   </entity>
</fetch>

Les résultats ressemblent à ceci :

 -----------------------------------------------------------------
 | name                             | contact.fullname           |
 -----------------------------------------------------------------
 | Litware, Inc. (sample)           | Susanna Stubberod (sample) |
 -----------------------------------------------------------------
 | Adventure Works (sample)         | Nancy Anderson (sample)    |
 -----------------------------------------------------------------
 | Fabrikam, Inc. (sample)          | Maria Campbell (sample)    |
 -----------------------------------------------------------------
 | Blue Yonder Airlines (sample)    | Sidney Higa (sample)       |
 -----------------------------------------------------------------
 | City Power & Light (sample)      | Scott Konersmann (sample)  |
 -----------------------------------------------------------------

Limites

Vous pouvez ajouter jusqu’à 15 link-entity éléments à une requête. Chaque élément link-entity ajoute un JOIN à la requête et augmente le temps d’exécution de la requête. Cette limite consiste à protéger les performances. Si vous ajoutez plus de 15 éléments d’entité de lien à une requête, vous obtenez cette erreur :

Code : 0x8004430D
Nombre : -2147204339
Message : Number of link entities in query exceeded maximum limit.

Éléments enfants

Dans l’élément link-entity, vous pouvez ajouter des éléments enfants comme vous le feriez sur l’élément parent pour :

• Sélectionner des colonnes dans la table associée
• Filtrer les lignes de la table associée
• Joindre une autre table associée

Relations plusieurs-à-un

L’exemple précédent illustre une relation de plusieurs à un, où de nombreux enregistrements de compte peuvent faire référence à un seul enregistrement de contact. Ces informations sont définies dans la relation plusieurs-à-un account_primary_contact du compte, qui a les valeurs suivantes :

Récupérer les informations de relation

Si vous utilisez le XrmToolBoxGénérateur FetchXML, vous pouvez voir comment cet outil vous permet de sélectionner la relation pour définir les valeurs d’attribut appropriées pour name, from et to.

Vous pouvez également utiliser d’autres outils et API pour rechercher les données de relation appropriées pour les valeurs d’attribut name, from et to à utiliser. Découvrez comment récupérer ces données :

• Parcourir les définitions de table dans votre environnement
• Rechercher les définitions de schéma

Relations 1 à N (un-à-plusieurs)

Les relations de plusieurs-à-un et de un-à-plusieurs sont comme regarder les deux côtés d'une même pièce. La relation existe entre les tables, de sorte que la façon dont vous l’utilisez dépend de la table de base de votre requête.

Vous pouvez récupérer les mêmes données que dans l'exemple précédent à partir de la table de contacts, en utilisant la même relation, mais cette fois depuis le côté de la table de contacts. Utilisez les données de la même relation un-à-plusieurs account_primary_contact du contact, mais ajustez les valeurs pour la vue différente de la relation.

<fetch top='5'>
   <entity name='contact'>
      <attribute name='fullname' />
      <link-entity name='account'
         from='primarycontactid'
         to='contactid'
         alias='account'>
         <attribute name='name' />
      </link-entity>
   </entity>
</fetch>

Le tableau suivant présente les valeurs d’attribut d’entité de lien dans cet exemple :

Les résultats incluent les mêmes enregistrements et données que la requête précédente à l’aide de la relation plusieurs-à-un, sauf que l’entité « parente » est maintenant contact au lieu de account.

 -----------------------------------------------------------------
 | fullname                   | account.name                     |
 -----------------------------------------------------------------
 | Susanna Stubberod (sample) | Litware, Inc. (sample)           |
 -----------------------------------------------------------------
 | Nancy Anderson (sample)    | Adventure Works (sample)         |
 -----------------------------------------------------------------
 | Maria Campbell (sample)    | Fabrikam, Inc. (sample)          |
 -----------------------------------------------------------------
 | Sidney Higa (sample)       | Blue Yonder Airlines (sample)    |
 -----------------------------------------------------------------
 | Scott Konersmann (sample)  | City Power & Light (sample)      |
 -----------------------------------------------------------------

Relations plusieurs à plusieurs

Les relations plusieurs-à-plusieurs dépendent d’une table d'intersection. Une table d’intersection ne comporte généralement que quatre colonnes, mais seules deux d’entre elles sont importantes. Les deux colonnes importantes correspondent aux colonnes clés primaires des tables participantes.

Par exemple, la table d’intersection TeamMembership prend en charge la relation plusieurs-à-plusieurs teammembership_association entre les tables SystemUser et Team. Il permet aux utilisateurs de rejoindre plusieurs équipes et d’avoir plusieurs utilisateurs. TeamMembership a ces colonnes : systemuserid, teamid.

Si vous souhaitez récupérer des informations sur les utilisateurs et les équipes auxquelles ils appartiennent à l’aide de la teammembership_association relation plusieurs-à-plusieurs, vous pouvez utiliser cette requête fetchXML :

<fetch top='2'>
   <entity name='systemuser'>
      <attribute name='fullname' />
      <link-entity name='teammembership'
         from='systemuserid'
         to='systemuserid'
         intersect='true' >
         <link-entity name='team'
            from='teamid'
            to='teamid'
            link-type='inner'
            alias='team'>
            <attribute name='name' />
         </link-entity>
      </link-entity>
   </entity>
</fetch>

Il existe deux entités de liaison imbriquées.

• Le premier connecte systemuser à la table d’intersection teammembership, où systemuserid = systemuserid.
• Le second connecte la table d’intersection teammembership à l’équipe, où teamid = teamid.

Les résultats doivent ressembler à ceci :

 --------------------------------------
 | fullname             | team.name   |
 --------------------------------------
 | FirstName LastName   | org26ed931d |
 --------------------------------------
 | # PpdfCDSClient      | org26ed931d |
 --------------------------------------

Aucune relation

Il est possible de spécifier les attributs from et to en utilisant des colonnes qui ne font pas partie d'une relation définie.

Par exemple, cette requête recherche des paires d’enregistrements où la colonne Name d’un enregistrement de compte correspond à la colonne FullName d’un enregistrement de contact , qu’elle se référence l’une ou l’autre dans l’une des colonnes de recherche.

<fetch>
   <entity name='account'>
     <attribute name='name' />
     <link-entity name='contact'
       from='fullname'
       to='name'
       link-type='inner'
       alias='contact'>
       <attribute name='fullname' />
     </link-entity>
   </entity>
 </fetch>

Les types de colonnes suivants ne peuvent pas être utilisés dans les attributs from et to.

• File
• Image
• Champ MultiSelect
• PartyList

Certaines colonnes peuvent être utilisées dans les attributs from et to, mais cela peut entraîner des performances médiocres :

• Colonnes du type Plusieurs lignes de texte
• Colonnes du type Ligne de texte unique avec une longueur maximale supérieure à 850
• Colonnes de formule
• Colonnes calculées
• Colonnes logiques

Rechercher des enregistrements qui ne sont pas dans un ensemble

Vous pouvez utiliser FetchXml pour créer une requête afin de retourner des enregistrements qui ne sont pas dans un ensemble en utilisant une jointure externe gauche. Une jointure externe gauche renvoie chaque ligne qui satisfait la jointure de la première entrée avec la deuxième entrée. Elle retourne également toutes les lignes de la première entrée qui n’avaient aucune ligne correspondante dans la deuxième entrée. Les lignes non correspondantes de la deuxième entrée sont retournées sous forme de valeurs Null.

Vous pouvez effectuer une jointure externe gauche dans FetchXML à l’aide de l’attribut entityname d’un élément de condition. L’attribut entityname est valide dans les conditions, les filtres et les filtres imbriqués. En savoir plus sur les filtres sur l’entité de lien.

Par exemple, la requête suivante retourne tous les enregistrements de compte sans contacts.

<fetch>
   <entity name='account'>
      <attribute name='name' />
      <order attribute='name' />
      <link-entity name='contact'
         from='parentcustomerid'
         to='accountid'
         link-type='outer'
         alias='contact' />
      <filter type='and'>
         <condition entityname='contact'
            attribute='parentcustomerid'
            operator='null' />
      </filter>
   </entity>
</fetch>

Utiliser des types de liens avancés

Les types d’entités de liaison suivants ne correspondent pas directement aux types d’opérateurs T-SQL JOIN et utilisent plutôt des sous-requêtes . Ces types offrent des fonctionnalités plus avancées que vous pouvez utiliser pour améliorer les performances des requêtes et définir des requêtes plus complexes.

Utiliser les types de liens exists ou in

exists et in sont des variantes de inner qui utilisent des conditions différentes (EXISTS et IN respectivement) dans la clause where afin d'éviter que plusieurs copies de la ligne parente ne soient retournées dans les résultats. Aucun de ces types ne retourne les valeurs de colonne des lignes des entités liées.

exists

Ces exemples FetchXml et SQL montrent les modèles appliqués avec exists.

<fetch>
   <entity name='contact'>
      <attribute name='fullname' />
      <link-entity name='account'
         from='primarycontactid'
         to='contactid'
         link-type='exists'>
         <filter type='and'>
            <condition attribute='statecode'
               operator='eq'
               value='1' />
         </filter>
      </link-entity>
   </entity>
</fetch>

select 
    "contact0".fullname as "fullname" 
from Contact as "contact0" 
where exists (
    select "account1".primarycontactid
    from Account as "account1"
    where "account1".statecode = 1
        and "contact0".contactid = "account1".primarycontactid)

in

Ces exemples FetchXml et SQL montrent les modèles appliqués avec in.

<fetch>
   <entity name='contact'>
      <attribute name='fullname' />
      <link-entity name='account'
         from='primarycontactid'
         to='contactid'
         link-type='in'>
         <filter type='and'>
            <condition attribute='statecode'
               operator='eq'
               value='1' />
         </filter>
      </link-entity>
   </entity>
</fetch>

select 
    "contact0".fullname as "fullname" 
from Contact as "contact0" 
where "contact0".contactid in (
    select "account1".primarycontactid
    from Account as "account1"
    where "account1".statecode = 1)

L'utilisation des types de liens exists ou in peut réduire la taille des résultats de requête intermédiaires ou finaux, surtout lorsque de nombreuses lignes liées correspondantes existent pour les mêmes lignes parentes, ou lorsque plusieurs entités de lien sont utilisées avec le même parent. L'utilisation des types de liens exists ou in peut améliorer les performances de la requête par rapport au type inner, car elle ne nécessite pas de retourner un produit cartésien contenant toutes les permutations possibles de lignes provenant d'entités liées différentes pour chaque ligne parente.

Ces types de liens peuvent également autoriser Dataverse à rechercher uniquement la première ligne d’entité liée correspondante pour chaque ligne parente, ce qui est plus efficace que de rechercher toutes les lignes correspondantes dans l’entité liée dans une inner jointure.

Utiliser le matchfirstrowusingcrossapply type de lien

Ce type de lien produit un opérateur CROSS APPLY avec une sous-requête qui utilise top 1 en suivant ce modèle :

<fetch>
   <entity name='contact'>
      <attribute name='fullname' />
      <link-entity name='account'
         from='primarycontactid'
         to='contactid'
         link-type='matchfirstrowusingcrossapply'>
         <attribute name='accountid' />
         <attribute name='name' />
      </link-entity>
   </entity>
</fetch>

select 
    "contact0".fullname as "fullname",
    "account1".accountid as "accountid",
    "account1".name as "name" 
from Contact as "contact0"
cross apply (
    select top 1
        "account1".accountid as "accountid",
        "account1".name as "name"
    from Account as "account1"
    where "contact0".contactid = "account1".primarycontactid
  ) "account1"

Le matchfirstrowusingcrossapply type de lien est équivalent au inner type, sauf qu’il retourne uniquement la ligne parente au plus une fois. La ligne parente est retournée uniquement s’il existe des lignes correspondantes dans l’entité liée, mais, contrairement in aux exists types , elle retourne des valeurs de colonne à partir de l’une des lignes correspondantes de l’entité liée. Utilisez cette option lorsqu’un seul exemple de ligne correspondante de l’entité liée est suffisant et que plusieurs copies de la ligne parente dans les résultats ne sont pas nécessaires.

Les noms de propriétés/attributs de table connexes sont incohérents

Lorsque vous utilisez le matchfirstrowusingcrossapply type de lien, les noms des propriétés retournées en utilisant l’API Web, ou les valeurs de la collection Keys du SDK pour les colonnes de table associées, sont différents des autres types de jointures. En règle générale, ceux-ci suivent le <tablealias>.<logicalname> format. Toutefois, pour le type de matchfirstrowusingcrossapply lien, les valeurs SchemaName sont utilisées sans préfixe d’alias de table.

À l’aide de l’exemple de requête précédent avec n’importe quel autre type de lien, vous pouvez vous attendre à ce que les propriétés ou les clés aient ces noms :

• fullname
• contactid
• account1.accountid
• account1.name

Toutefois, avec le type de matchfirstrowusingcrossapply lien, les propriétés ou les clés ont les noms suivants :

• fullname
• contactid
• AccountId
• Name

Étapes suivantes

Découvrez comment trier des lignes.