Joindre des tables à l’aide de FetchXml

  • Article

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

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

Attribut Brève description Trouvez plus de détails dans la référence de l’élément link-entity
name Le nom logique de la table associée.
from Le nom logique de la colonne de la table associée qui correspond à la colonne spécifiée dans l’attribut to.
to Le nom logique de la colonne dans l’élément parent à mettre en correspondance avec la colonne de table associée spécifiée dans l’attribut from.
link-type Le type de lien utilisé. Le comportement par défaut est inner, qui restreint les résultats aux lignes avec des valeurs correspondantes dans les deux tables.
Les autres valeurs valides sont :
- outer
- any
- not any
- all
- not all
- exists
- in
- matchfirstrowusingcrossapply
En savoir plus sur les options link-type
alias Représente le nom de la table associée dans les résultats.
intersect Indique que l’élément link-entity est utilisé pour joindre des tables et ne renvoyer aucune colonne

Par exemple, la requête suivante renvoie jusqu’à 5 enregistrements à partir des tables compte et contact basées sur la colonne de recherche PrimaryContactId dans l’enregistrement de 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 à ce qui suit :

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

Limitations

Vous pouvez ajouter jusqu’à 15 éléments link-entity à 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 sert à protéger les performances. Si vous ajoutez plus de 15 éléments link-entity à 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 dans l’élément parent pour :

Relations plusieurs-à-un

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

Propriété Valeur Commentaire
SchemaName account_primary_contact Nom unique de la relation.
ReferencedEntity contact La table référencée. Le un dans plusieurs-à-un.
ReferencedAttribute contactid La clé primaire de la table référencée.
ReferencingEntity account La table avec une colonne de recherche qui fait référence à l’autre table. Le plusieurs dans plusieurs-à-un.
ReferencingAttribute primarycontactid Le nom de la colonne de recherche.
RelationshipType OneToManyRelationship Une relation un-à-plusieurs lorsqu’elle est affichée à partir de la table référencée (une).
Une relation plusieurs-à-un lorsqu’elle est affichée à partir de la table de référence (plusieurs)

Récupérer les informations sur la relation

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

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

Relations 1 à N (un-à-plusieurs)

Les relations plusieurs-à-un et un-à-plusieurs sont comme regarder des deux faces d’une pièce de monnaie. La relation existe entre les tables ; par conséquent, la façon dont vous l’utilisez dépend de quelle table est la table de base pour votre requête.

Vous pouvez récupérer les mêmes données que l’exemple précédent de la table de contacts en utilisant la même relation, sauf du 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 de l’attribut link-entity dans cet exemple :

Attribut Valeur Description
name account Le nom logique de la table de référence.
from primarycontactid Le nom de la colonne de recherche dans la table de comptes de référence
to contactid La clé primaire de la table de contacts référencée.
alias account Une valeur est recommandée pour l’élément link-entity avec une relation un-à-plusieurs. Si aucun alias n’est pas fourni, un alias par défaut est généré. Dans cet exemple, si aucun alias n’est fourni, les données sont renvoyées avec une colonne nommée account1.name.
link-type Non défini Lorsqu’aucune valeur n’est définie, la valeur par défaut est inner

Les résultats incluent les mêmes enregistrements et données que la requête précédente qui utilise 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 de la clé primaire 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. Elle permet aux utilisateurs de joindre plusieurs équipes et aux équipes d’avoir plusieurs utilisateurs. TeamMembership comporte 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 relation plusieurs-à-plusieurs teammembership_association, 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 éléments link-entity imbriqués.

  • 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 devraient ressembler à ce qui suit :

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

Aucune relation

Il est possible de spécifier les attributs from et to à l’aide de colonnes qui ne font pas partie d’une relation définie.

Par exemple, cette requête recherche des paires d’enregistrements dans lesquels la colonne Name d’un enregistrement de compte correspond à la Colonne FullName d’un enregistrement de contact, qu’ils se référencent ou non 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>

Notes

Il est important que les colonnes spécifiées dans les attributs from et to soient du même type même si elles ne sont pas impliquées dans une relation. L’utilisation de colonnes de types différents nécessitera une conversion de type qui peut avoir un impact sur les performances et peut échouer pour certaines valeurs de colonne.

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

  • Fichier
  • Image
  • Champ à sélection multiple
  • PartyList

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

  • Colonnes de type Plusieurs lignes de texte
  • Colonnes de type Une seule ligne de texte d’une longueur maximale supérieure à 850
  • Colonnes de formule
  • Colonnes calculées
  • Colonnes logiques

Rechercher des enregistrements qui ne font pas partie d’un ensemble

Vous pouvez utiliser FetchXml pour créer une requête pour renvoyer les enregistrements qui ne font pas partie d’un ensemble à l’aide d’une jointure externe gauche. Une jointure externe gauche renvoie chaque ligne qui répond à la jointure de la première entrée avec la seconde entrée. Elle renvoie également toute ligne de la première entrée qui n’avait pas de lignes correspondantes dans la seconde entrée. Les lignes sans correspondance de la seconde entrée sont renvoyées comme valeurs nulles.

Vous pouvez effectuer une jointure externe gauche dans FetchXML en utilisant l’attribut entityname dans un élément condition. L’attribut entityname est valide en termes de conditions, de filtres et de filtres imbriqués. En savoir plus sur les filtres sur link-entity.

Par exemple, la requête suivante renvoie tous les enregistrements de contact 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>

Les types d’entités de lien suivants ne correspondent pas directement aux types d’opérateur JOIN T-SQL et utilisent des sous-requêtes à la place. 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.

Nom  Description
exists Une variante de inner qui peut offrir des avantages en termes de performances. Utilise une condition EXISTS dans la clause where. Utilisez-la lorsque plusieurs copies de la ligne parente ne sont pas nécessaires dans les résultats. En savoir plus sur exists et in
in Une variante de inner qui peut offrir des avantages en termes de performances. Utilise une condition IN dans la clause where. Utilisez-la lorsque plusieurs copies de la ligne parente ne sont pas nécessaires dans les résultats. En savoir plus sur exists et in
matchfirstrowusingcrossapply Une variante de inner qui peut offrir des avantages en termes de performances. Utilisez ce type 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. En savoir plus sur matchfirstrowusingcrossapply

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

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>

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>

L’utilisation des types de liens exists ou in peut réduire la taille des résultats de requête intermédiaires ou finaux, en particulier lorsqu’il existe de nombreuses lignes liées correspondantes 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 il n’est pas nécessaire de renvoyer un produit cartésien contenant toutes les permutations possibles de lignes de différentes entités liées pour chaque ligne parente.

Ces types de liens peuvent également permettre à Dataverse de 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 de l’entité liée dans une jointure inner.

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>

Ce type de lien matchfirstrowusingcrossapply est équivalent au type inner, sauf qu’il ne renvoie la ligne parente au plus qu’une fois. La ligne parente n’est renvoyée que s’il existe des lignes correspondantes dans l’entité liée mais, contrairement aux types in et exists, elle renvoie les valeurs de colonne de l’une des lignes correspondantes dans l’entité liée. Utilisez ce type 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.

Lors de l’utilisation du type de lien matchfirstrowusingcrossapply, les noms des propriétés renvoyées à l’aide de l’API Web ou les valeurs Keys de la collection Entity.Attributes du SDK pour les colonnes de la table associée sont différents des autres types de jointures. Habituellement, ceux-ci suivront le format <tablealias>.<logicalname>. Cependant, pour le type de lien matchfirstrowusingcrossapply, les valeurs SchemaName sont utilisées sans le préfixe d’alias de table.

En utilisant l’exemple de requête précédent avec n’importe quel autre type de lien, les propriétés ou les clés devraient porter ces noms :

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

Mais avec le type de lien matchfirstrowusingcrossapply, les propriétés ou les clés portent ces noms :

  • fullname
  • contactid
  • AccountId
  • Name

Étapes suivantes

Découvrez comment trier des lignes.

Trier des lignes

Notes

Pouvez-vous nous indiquer vos préférences de langue pour la documentation ? Répondez à un court questionnaire. (veuillez noter que ce questionnaire est en anglais)

Le questionnaire vous prendra environ sept minutes. Aucune donnée personnelle n’est collectée (déclaration de confidentialité).