QueryExpression を使用してテーブルを結合する

QueryExpression.LinkEntities プロパティを使用して、クエリで返される関連テーブルのデータを記述します。 このプロパティには、以下を記述する LinkEntity インスタンスのコレクションが含まれています。

• どの関連テーブルの行を返すか
• 結合の基となる列の値
• 返されるレコードの列
• 結合時に適用するフィルター

LinkEntity プロパティ

次の表に、 LinkEntity プロパティの詳細を示します。

LinkEntity の例

次のクエリでは、取引先企業レコードの PrimaryContactId 参照列に基づいて、取引先企業テーブルと連絡先テーブルから最大 5 つのレコードが返されます。 多対一のリレーションシップを表します。

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

結果はこのようになります:

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

AddLink メソッド

次に示すように、オブジェクトの初期化を使用してクエリ全体を作成できますが、 QueryExpression.AddLink メソッドと LinkEntity.AddLink メソッドを使用することをお勧めします。 これらのメソッドは、コレクション内のクエリに簡単にアクセスして変更できるように、作成されたリンクへの参照を返します。 たとえば、 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");

LinkEntity.AddLink メソッドを使用してクエリを拡張し、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");

これにより、クエリのさまざまな部分に簡単にアクセスして調整を行うことができます。

制限事項

最大 15 個の LinkEntity インスタンスをクエリに追加できます。 各 LinkEntity は、クエリに JOIN を追加し、クエリの実行時間を長くします。 この制限は、パフォーマンスを保護することです。 LinkEntity に 15 個を超える インスタンスを追加すると、次のランタイム エラーが発生します。

名前: TooManyLinkEntitiesInQuery コード: 0x8004430D
数： -2147204339
メッセージ: Number of link entities in query exceeded maximum limit.

多対一リレーションシップ

前の例は、多くの取引先レコードが 1 つの取引先責任者レコードを参照できる多対 1 のリレーションシップです。 この情報は、アカウント account_primary_contact の多対一リレーションシップで定義されており、次の値があります。

リレーションシップ情報を取得する

他のツールと API を使用して、使用する適切な LinkToEntityName、 LinkToAttributeName、 LinkFromEntityName、 LinkFromAttributeName 値のリレーションシップ データを検索できます。 詳細については、以下を参照してください。

• 環境内のテーブル定義を参照する
• クエリ スキーマ定義

多対一リレーションシップの例

次の表に、多対一リレーションシップに使用するリレーションシップ値を示します。

一対多関連付け

多対一と一対多の関係は、コインの両側を見ているようなものです。 リレーションシップはテーブル間に存在するため、使用する方法は、クエリのベース テーブルであるテーブルによって異なります。

前の例と同じデータは、 テーブルの側を除き、同じリレーションシップを使用してcontactテーブルから取得できます。 同じ Contact account_primary_contact一対多リレーションシップのデータを使用しますが、リレーションシップのさまざまなビューの値を調整します。

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

一対多リレーションシップの場合は、次のリレーションシップ値を使用します。

結果には、多対一リレーションシップを使用する前の例と同じレコードとデータが含まれますが、の代わりに contact が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)      |
 -----------------------------------------------------------------

多対多のリレーションシップ

多対多リレーションシップは交差テーブルに依存します。 通常、交差テーブルには 4 つの列しかありませんが、重要なのはそのうちの 2 つだけです。 2 つの重要な列は、参加テーブルの主キー列と一致します。

たとえば、TeamMembership 交差テーブルは SystemUser テーブルと Team テーブルの間にある teammembership_association の多対多リレーションシップをサポートします。 これにより、ユーザーは複数のチームに参加でき、チームは複数のユーザーを持つことができます。 TeamMembership には、 systemuserid、 teamidの列があります。

teammembership_association多対多リレーションシップを使用して、ユーザーと所属するチームに関する情報を取得する場合は、次の 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");

LinkEntity インスタンスは 2 つあります。

• linkedTeamMemberShipは、systemuserをteammembershipsystemuserid = systemuserid交差テーブルに接続します。
• linkedTeam は、teammembership 交差テーブルをチーム (teamid = teamid) に接続します。

結果は次のようになります。

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

リレーションシップなし

定義されたリレーションシップの一部ではない列を使用して、 LinkFromAttributeName プロパティと LinkToAttributeName プロパティを指定できます。

たとえば、このクエリでは、取引先レコードのName 列が連絡先レコードのFullName 列と一致するレコードのペアを、参照列でお互いに参照しているかどうかに関係なく、検索します。

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

次の 列の種類 は、 LinkFromAttributeName プロパティと LinkToAttributeName プロパティでは使用できません。

• File
• イメージ
• MultiSelect フィールド
• パーティーリスト

一部の列は、 LinkFromAttributeName プロパティと LinkToAttributeName プロパティで使用できますが、パフォーマンスが低下する可能性があります。

• 複数行のテキスト 型の列
• 最大長が 850 を超える 単一行テキスト型の 列
• 数式の列
• 計算列
• 論理 列

セット内にないレコードを検索する

QueryExpression を使用すると、左外部結合を使用してセット内にないレコードを返すクエリを作成できます。 左外部結合では、2 番め入力で最初の入力の結合を満たす各列を返します。 また、2 番目入力で一致する列がない最初の入力列を返します。 2 番目の入力の不一致行は null 値として返されます。

QueryExpressionを使用して、で左外部結合を実行できます。 EntityName プロパティは、条件、フィルター、入れ子になったフィルターで有効です。 LinkEntity のフィルターの詳細を確認する

たとえば、次のクエリは、連絡先のないすべてのアカウントレコードを返します。

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

高度な JoinOperator を使用する

次の JoinOperator メンバー は、T-SQL JOIN 演算子 の型に直接対応せず、代わりに サブクエリ を使用します。 これらの型は、クエリのパフォーマンスを向上させ、より複雑なクエリを定義するために使用できる、より高度な機能を提供します。

JoinOperator.Exists または JoinOperator.In を使用する

Exists Inは、親行の複数のコピーが結果に返されないように、Inner句で異なる条件 (EXISTS と IN) を使用するwhereのバリアントです。 ExistsとInの両方で、関連するエンティティ行の列値は返されません。

JoinOperator.ExistsまたはJoinOperator.Inを使用すると、中間または最終的なクエリ結果のサイズを小さくできます。特に、同じ親行に対して多数の一致するリンク行が存在する場合や、同じ親で複数のリンク エンティティが使用されている場合です。 JoinOperator.ExistsまたはJoinOperator.Inを使用すると、親行ごとに異なるリンクされたエンティティから可能なすべての行の順列を含むデカルト積を返す必要がないため、JoinOperator.Innerと比較してクエリのパフォーマンスを向上させることができます。

これらの JoinOperator メンバーは、Dataverse が親行ごとに最初に一致するリンク されたエンティティ行のみを検索することを許可する場合もあります。これは、 JoinOperator.Innerを持つリンク エンティティ内のすべての一致する行を検索するよりも効率的です。

JoinOperator.Exists 例

これらの QueryExpression と SQL の例は、 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 例

これらの QueryExpression と SQL の例は、 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)

JoinOperator.MatchFirstRowUsingCrossApply を使用する

JoinOperator.MatchFirstRowUsingCrossApplyは、このパターンに従って、を使用してサブクエリを持つ top 1 演算子を生成します。

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"

これは、親行を最大 1 回だけ返す点を除き、 JoinOperator.LeftOuter と同じです。 JoinOperator.InやJoinOperator.Existsとは異なり、一致する行が存在する場合、関連するテーブル内の一致する行の 1 つから列値が返されますが、関連するテーブルに一致する行がない場合でも親行が返されます。 これは、関連するテーブルの一致する行の 1 つの例だけで十分であり、結果内の親行の複数のコピーが必要ない場合に使用します。

次のステップ

行を並べ替える方法について説明します。