Joindre des tables à l’aide de QueryExpression

Utilisez la propriété QueryExpression.LinkEntities pour décrire les données des tables associées à retourner avec votre requête. Cette propriété contient une collection d’instances LinkEntity qui décrivent :

• Quelles lignes de table associées renvoyer
• Quelles valeurs de colonne baser la jointure sur
• Quelles colonnes de ces enregistrements renvoyer
• Des filtres à appliquer avec l'opération de jointure

Propriétés de l'entité de lien

Le tableau suivant fournit des détails sur les propriétés LinkEntity :

Exemple d'Entité de Lien

La requête suivante retourne jusqu’à cinq enregistrements à partir des tables de compte et de contact en fonction de la colonne de recherche PrimaryContactId dans l’enregistrement du compte. Cela représente une relation plusieurs-à-un :

QueryExpression query = new("account")
{
      TopCount = 5,
      ColumnSet = new ColumnSet("name"),
      LinkEntities = {
            new LinkEntity()
            {
                  LinkFromEntityName = "account",
                  LinkToEntityName = "contact",
                  LinkFromAttributeName = "primarycontactid",
                  LinkToAttributeName = "contactid",
                  JoinOperator = JoinOperator.Inner,
                  EntityAlias = "contact",
                  Columns = new ColumnSet("fullname")
            }
      }
};

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

Méthodes AddLink

Vous pouvez composer l’intégralité de la requête à l’aide de l’initialisation d’objet comme indiqué, mais nous vous recommandons d’utiliser les méthodes QueryExpression.AddLink et LinkEntity.AddLink . Ces méthodes retournent une référence au lien créé afin que vous puissiez facilement accéder à la requête et la modifier dans la collection. Par exemple, si vous composez la requête de cette façon à l’aide de la méthode QueryExpression.AddLink :

var query = new QueryExpression("account")
{
      TopCount = 5,
      ColumnSet = new ColumnSet("name"),
};

// Link to primary contact
LinkEntity linkedPrimaryContact = query.AddLink(
      linkToEntityName: "contact",
      linkFromAttributeName: "primarycontactid",
      linkToAttributeName: "contactid",
      joinOperator: JoinOperator.Inner);

linkedPrimaryContact.EntityAlias = "primarycontact";
linkedPrimaryContact.Columns = new ColumnSet("fullname");

Vous pouvez étendre la requête à l’aide de la méthode LinkEntity.AddLink pour inclure des informations sur l’utilisateur propriétaire du contact lié via l’instance linkedPrimaryContactLinkEntity :

// Link to contact owning user
LinkEntity linkedContactOwner = linkedPrimaryContact.AddLink(
      linkToEntityName: "systemuser",
      linkFromAttributeName: "owninguser",
      linkToAttributeName: "systemuserid",
      joinOperator: JoinOperator.Inner);

linkedContactOwner.EntityAlias = "owner";
linkedContactOwner.Columns = new ColumnSet("fullname");

De cette façon, vous pouvez accéder plus facilement aux différentes parties de la requête pour effectuer des ajustements.

Limites

Vous pouvez ajouter jusqu’à 15 instances LinkEntity à une requête. Chacun LinkEntity ajoute une jointure à 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 LinkEntity instances à QueryExpression.LinkEntities, vous obtenez cette erreur d’exécution :

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

Relations plusieurs-à-un

L’exemple précédent est une relation plusieurs-à-un où 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 :

Récupérer les informations de relation

Vous pouvez utiliser d’autres outils et API pour rechercher les données de relation appropriées pour les valeurs LinkToEntityName, LinkToAttributeName, LinkFromEntityName, et LinkFromAttributeName à utiliser. Pour plus d’informations, consultez :

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

Exemple de relation plusieurs-à-un

Le tableau suivant présente les valeurs de relation à utiliser pour une relation plusieurs-à-un :

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 l’exemple précédent de la table de contacts à l’aide de la même relation, sauf du côté de la contact table. 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.

var query = new QueryExpression("contact")
{
      TopCount = 5,
      ColumnSet = new ColumnSet("fullname"),
};

// Link to related account
var linkedPrimaryContact = query.AddLink(
      linkToEntityName: "account",
      linkFromAttributeName: "contactid",
      linkToAttributeName: "primarycontactid",
      joinOperator: JoinOperator.Inner);

linkedPrimaryContact.EntityAlias = "account";
linkedPrimaryContact.Columns = new ColumnSet("name");

Pour une relation un-à-plusieurs, utilisez ces valeurs de relation :

Les résultats incluent les mêmes enregistrements et données que l’exemple précédent utilisant la relation plusieurs-à-un, à l’exception de l' « entité parente » est maintenant 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 QueryExpression :

var query = new QueryExpression("systemuser")
{
      TopCount = 2,
      ColumnSet = new ColumnSet("fullname"),
};

LinkEntity linkedTeamMemberShip = query.AddLink(
      linkToEntityName: "teammembership",
      linkFromAttributeName: "systemuserid",
      linkToAttributeName: "systemuserid");

LinkEntity linkedTeam = linkedTeamMemberShip.AddLink(
      linkToEntityName: "team",
      linkFromAttributeName: "teamid",
      linkToAttributeName: "teamid");

linkedTeam.EntityAlias = "team";
linkedTeam.Columns = new ColumnSet("name");

Il existe deux instances LinkEntity .

• linkedTeamMemberShip se connecte systemuser à la teammembership table d’intersection où systemuserid = systemuserid.
• linkedTeam se connecte teammembership à la table d’intersection vers 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 propriétés LinkFromAttributeName et LinkToAttributeName à l’aide de colonnes qui ne font pas partie d’une relation pré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.

var query = new QueryExpression("account")
{
      ColumnSet = new ColumnSet("name"),
};

LinkEntity linkedContact = query.AddLink(
      linkToEntityName: "contact", 
      linkFromAttributeName: "name", 
      linkToAttributeName: "fullname");
linkedContact.EntityAlias = "contact";
linkedContact.Columns = new ColumnSet("fullname");

Les types de colonnes suivants ne peuvent pas être utilisés dans les propriétés LinkFromAttributeName et LinkToAttributeName :

• File
• Image
• Champ MultiSelect
• PartyList

Certaines colonnes peuvent être utilisées dans les propriétés LinkFromAttributeName et LinkToAttributeName, 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 QueryExpression pour créer une requête visant à renvoyer des enregistrements qui ne sont pas dans un ensemble à l’aide d’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 qui ne correspondent pas dans la deuxième entrée sont retournées sous forme de valeurs Null.

Vous pouvez effectuer une jointure externe gauche dans QueryExpression en utilisant la propriété ConditionExpression.EntityName. La EntityName propriété est valide dans des conditions, des filtres et des filtres imbriqués. En savoir plus sur les filtres sur LinkEntity

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

var query = new QueryExpression(entityName: "account");
query.ColumnSet.AddColumn("name");
query.AddOrder(
      attributeName: "name", 
      orderType: OrderType.Descending);
query.Criteria.AddCondition(
      entityName: "contact",
      attributeName: "parentcustomerid",
      conditionOperator: ConditionOperator.Null);

LinkEntity linkedContact = query.AddLink(
      linkToEntityName: "contact",
      linkFromAttributeName: "accountid",
      linkToAttributeName: "parentcustomerid",
      joinOperator: JoinOperator.LeftOuter);
linkedContact.EntityAlias = "contact";
linkedContact.Columns.AddColumn("fullname");

Utiliser des JoinOperators avancés

Les membres JoinOperator suivants ne correspondent pas directement aux types d’opérateurs T-SQL JOIN 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.

Utilisez JoinOperator.Exists ou JoinOperator.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. Les deux Exists et In ne retournent pas les valeurs de colonne des lignes d’entité associées.

L’utilisation de JoinOperator.Exists ou JoinOperator.In peut réduire la taille des résultats de requête intermédiaires ou finaux, notamment lorsque de nombreuses lignes liées correspondantes existent pour les mêmes lignes parentes, ou lorsque plusieurs entités de liaison sont utilisées avec le même parent. L’utilisation de JoinOperator.Exists ou JoinOperator.In peut améliorer les performances de la requête par rapport à JoinOperator.Inner, car cela 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 JoinOperator membres peuvent également permettre à Dataverse de ne trouver que la première ligne d’entité liée correspondante pour chaque ligne parente, ce qui est plus efficace que de chercher toutes les lignes correspondantes dans l’entité liée avec JoinOperator.Inner.

JoinOperator.Exists exemple

Ces exemples QueryExpression et SQL montrent les modèles appliqués avec JoinOperator.Exists.

QueryExpression query = new("contact");
query.ColumnSet.AddColumn("fullname");

LinkEntity linkedAccount = query.AddLink(
      linkToEntityName: "account",
      linkFromAttributeName: "contactid",
      linkToAttributeName: "primarycontactid",
      joinOperator: JoinOperator.Exists);

linkedAccount.EntityAlias = "account";

linkedAccount.LinkCriteria.AddCondition(
      entityName:"account", 
      attributeName: "statecode", 
      conditionOperator: ConditionOperator.Equal,
      values: 1);

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)

JoinOperator.In exemple

Ces exemples QueryExpression et SQL montrent les modèles appliqués avec JoinOperator.In.

QueryExpression query = new("contact");
query.ColumnSet.AddColumn("fullname");

LinkEntity linkedAccount = query.AddLink(
      linkToEntityName: "account",
      linkFromAttributeName: "contactid",
      linkToAttributeName: "primarycontactid",
      joinOperator: JoinOperator.In);

linkedAccount.EntityAlias = "account";

linkedAccount.LinkCriteria.AddCondition(
      entityName: "account",
      attributeName: "statecode",
      conditionOperator: ConditionOperator.Equal,
      values: 1);

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

Utilisez JoinOperator.MatchFirstRowUsingCrossApply.

JoinOperator.MatchFirstRowUsingCrossApply produit un opérateur CROSS APPLY avec une sous-requête à l’aide de top 1 ce modèle :

QueryExpression query = new("contact");
query.ColumnSet.AddColumn("fullname");

LinkEntity linkedAccount = query.AddLink(
      linkToEntityName: "account",
      linkFromAttributeName: "contactid",
      linkToAttributeName: "primarycontactid",
      joinOperator: JoinOperator.MatchFirstRowUsingCrossApply);

linkedAccount.EntityAlias = "account";
linkedAccount.Columns = new ColumnSet("accountid", "name");

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"

Cela équivaut à JoinOperator.LeftOuter : il retourne cependant uniquement la ligne parente au maximum une fois. Contrairement JoinOperator.In à et JoinOperator.Exists, elle retourne des valeurs de colonne de l’une des lignes correspondantes dans la table associée lorsque des lignes correspondantes existent, mais la ligne parente est retournée même s’il n’y a pas de lignes correspondantes dans la table associée. Utilisez cette option lorsqu’un seul exemple de ligne correspondante de la table associée est suffisant et que plusieurs copies de la ligne parente dans les résultats ne sont pas nécessaires.

Étapes suivantes

Découvrez comment trier des lignes.