Unir tablas usando QueryExpression

Utilice la propiedad QueryExpression.LinkEntities para describir los datos de las tablas relacionadas que se devolverán con su consulta. Esta propiedad contiene una colección de instancias de LinkEntity que describen:

• Qué filas de la tabla relacionadas devolver
• En qué valores de columna basar la combinación
• Qué columnas de esos registros devolver
• Cualquier filtro para aplicar con la combinación

Propiedades de LinkEntity

La siguiente tabla proporciona detalles sobre las propiedades de LinkEntity:

Ejemplo de LinkEntity

La siguiente consulta devuelve hasta cinco registros de las tablas cuenta y contacto basadas en la columna de búsqueda PrimaryContactId en el registro de cuenta. Esto representa una relación de varios a uno:

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")
            }
      }
};

Los resultados tienen este aspecto:

 -----------------------------------------------------------------
 | 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étodos de AddLink

Puede redactar la consulta completa utilizando la inicialización de objetos como se muestra, pero recomendamos usar los métodos QueryExpression.AddLink y LinkEntity.AddLink. Estos métodos devuelven una referencia al vínculo creado para que pueda tener acceso y modificar fácilmente la consulta dentro de la colección. Por ejemplo, si redacta la consulta de esta manera utilizando el método 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");

Puede ampliar la consulta utilizando el método LinkEntity.AddLink para incluir información sobre usuario propietario para el contacto vinculado a través de la instancia linkedPrimaryContact LinkEntity:

// 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 esta manera podrá tener acceso más fácilmente a las diferentes partes de la consulta para realizar ajustes.

Limitaciones

Puede agregar hasta 15 instancias LinkEntity a una consulta. Cada LinkEntity agrega un JOIN a la consulta y aumenta el tiempo para ejecutar la consulta. Este límite es para proteger el rendimiento. Si agrega más de 15 instancias de LinkEntity a QueryExpression.LinkEntities, obtendrá este error de tiempo de ejecución:

Nombre: TooManyLinkEntitiesInQuery Código: 0x8004430D
Número: -2147204339
Mensaje: Number of link entities in query exceeded maximum limit.

Relaciones de varios a uno

El ejemplo anterior es una relación de muchos a uno donde muchos registros de cuentas pueden hacer referencia a un registro de contacto. Esta información se define en la relación de muchos a uno Relación de cuenta account_primary_contact, que tiene los siguientes valores:

Recuperar información de la relación

Puede utilizar otras herramientas y API para buscar datos de relaciones para los valores LinkToEntityName, LinkToAttributeName, LinkFromEntityName y LinkFromAttributeName a utilizar. Para obtener más información, consulte:

• Examinar las definiciones de tablas en su entorno
• Consultar definiciones de esquema

Ejemplo de relación varios a uno

La siguiente tabla muestra los valores de relación que se utilizarán para una relación de varios a uno:

Relaciones de uno a varios

Las relaciones de muchos a uno y de uno a muchos son como ver las dos caras de una moneda. La relación existe entre las tablas, por lo que la forma en que la use depende de qué tabla es la tabla base para su consulta.

Puede recuperar los mismos datos que en el ejemplo anterior de la tabla contacto usando la misma relación, excepto en la lado de la tabla contact. Utilice los datos de la misma relación de cuenta account_primary_contact one-to-many, pero ajuste los valores para la vista diferente de la relación.

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

Para una relación de uno a varios, utilice estos valores de relación:

Los resultados incluyen los mismos registros y datos que el ejemplo anterior usando la relación de varios a uno, excepto que ahora la 'entidad principal' es contact en lugar 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)      |
 -----------------------------------------------------------------

Relaciones de varios a varios

Las relaciones de muchos a muchos dependen de una tabla de intersección. Una tabla de intersección normalmente tiene solo cuatro columnas, pero solo dos de ellas son importantes. Las dos columnas importantes coinciden con las columnas de clave principal de las tablas participantes.

Por ejemplo, la tabla de intersección TeamMembership admite la relación de muchos a muchos teammembership_association entre SystemUser y Team . Permite a los usuarios unirse a varios equipos y a los equipos tener varios usuarios. TeamMembership tiene estas columnas: systemuserid, teamid.

Si desea recuperar información sobre los usuarios y los equipos a los que pertenecen utilizando la relación teammembership_association de varios a varios, puede utilizar esta consulta 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");

Hay dos instancias de LinkEntity.

• linkedTeamMemberShip se conecta systemuser a la tabla de intersección de teammembership donde systemuserid = systemuserid.
• linkedTeam conecta la tabla de intersección de teammembership al equipo en el que teamid = teamid.

El resultado deberá ser ahora similar a:

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

No existe relación

Es posible especificar propiedades LinkFromAttributeName y LinkToAttributeName usando columnas que no forman parte de una relación definida.

Por ejemplo, esta consulta encuentra pares de registros donde la Columna de nombre de un registro de cuenta coincide con la columna FullName de un registro de contacto independientemente de si se hacen referencia entre sí en cualquiera de las columnas de búsqueda.

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

Los siguientes tipos de columnas no se pueden usar en las propiedades LinkFromAttributeName y LinkToAttributeName:

• Archivo
• Image
• Campo MultiSelect
• PartyList

Algunas columnas se pueden utilizar en las propiedades LinkFromAttributeName y LinkToAttributeName, pero pueden provocar un rendimiento deficiente:

• Columnas del tipo Varias líneas de texto
• Columnas del tipo Línea única de texto con una longitud máxima superior a 850
• Columnas de Fórmula
• Columnas de Calculado
• Columnas Lógicas

Buscar registros que no estén en un conjunto

Puede usar QueryExpression para crear una consulta para devolver registros que no están en un conjunto usando una combinación externa izquierda. Una combinación externa izquierda devuelve todas las filas que satisfacen la combinación de la primera entrada con la segunda entrada. También devuelve las filas de la primera entrada que no tenían ninguna fila coincidente en la segunda entrada. Las filas no coincidentes de la segunda entrada se devuelven como valores nulos.

Puede realizar una combinación externa izquierda en QueryExpression usando la propiedad ConditionExpression.EntityName. La propiedad EntityName es válida en condiciones, filtros y filtros anidados. Obtener más información sobre los filtros en LinkEntity

Por ejemplo, la siguiente consulta devuelve todos los registros de cuentas sin contactos.

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

Usar JoinOperators avanzados

Los siguientes miembros JoinOperator no corresponden directamente a tipos operador JOIN de T-SQL y usan subconsultas en su lugar. Estos tipos proporcionan capacidades más avanzadas que puede utilizar para mejorar el rendimiento de las consultas y definir consultas más complejas.

Use JoinOperator.Exists o JoinOperator.In

Exists e In son variantes de Inner que utilizan diferentes condiciones ( EXISTS e IN respectivamente) en la cláusula where para que no se devuelvan varias copias de la fila principal en los resultados. Exists y In no devuelven los valores de las columnas de las filas de la entidad relacionada.

El uso de JoinOperator.Exists o JoinOperator.In puede reducir el tamaño de los resultados de la consulta intermedia o final, especialmente cuando existen muchas filas vinculadas coincidentes para las mismas filas principales, o cuando se utilizan varias entidades de vínculo con el mismo padre. El uso de JoinOperator.Exists o JoinOperator.In puede mejorar el rendimiento de la consulta en comparación con JoinOperator.Inner porque no requiere devolver un producto cartesiano que contenga todas las permutaciones posibles de filas de diferentes entidades vinculadas para cada fila principal.

Estos miembros de JoinOperator también podrían permitir a Dataverse buscar solo la primera fila de entidad vinculada coincidente para cada fila principal, lo cual es más eficiente que buscar todas las filas coincidentes en la entidad vinculada con JoinOperator.Inner.

Ejemplo de JoinOperator.Exists

Estos ejemplos de QueryExpression y SQL muestran los patrones aplicados con 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)

Ejemplo de JoinOperator.In

Estos ejemplos de QueryExpression y SQL muestran los patrones aplicados con 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)

Usar JoinOperator.MatchFirstRowUsingCrossApply

JoinOperator.MatchFirstRowUsingCrossApply produce un operador CROSS APPLY con una subconsulta usando top 1 siguiendo este patrón:

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"

Esto es equivalente a JoinOperator.LeftOuter excepto que solo devuelve la fila principal como máximo una vez. A diferencia de JoinOperator.In y JoinOperator.Exists, devuelve valores de columna de una de las filas coincidentes en la tabla relacionada cuando existen filas coincidentes, pero la fila principal se devuelve incluso si no hay filas coincidentes en la tabla relacionada. Utilícelo cuando solo sea suficiente un único ejemplo de una fila coincidente de la tabla relacionada y no sean necesarias varias copias de la fila principal en los resultados.

Pasos siguientes

Aprenda a filtrar las filas de pedidos.