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

[アーティクル]
07/22/2024

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

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

注意

LinkEntities プロパティは読み取り専用です。オブジェクトの初期化または QueryExpression.AddLink 算出方法を使って、LinkEntity インスタンスをこのコレクションに設定できます。

また、LinkEntities プロパティが継承する System.Collections.ObjectModel.Collection<T> 算出方法を使用することもできます。

LinkEntity プロパティ

次のテーブルは、LinkEntity プロパティに関する詳細を示します。

Property	プロパティ
LinkFromEntityName	リンクする元のエンティティの論理名。ネストされていない `LinkEntity` の場合は、 QueryExpression.EntityName プロパティと同じ値を使用します。 LinkEntity.LinkEntities コレクションにネストされた `LinkEntity` の場合、親リンクエンティティから LinkEntity.LinkToEntityName の値を使用します。
LinkToEntityName	リンクする先のエンティティの論理名。
LinkFromAttributeName	リンクする元のエンティティの属性の論理名。
LinkToAttributeName	リンクする先のエンティティの属性の論理名。
JoinOperator	結合演算子。 JoinOperator 列挙型メンバーのいずれかの値を使用します。既定値は `Inner` で、両方のテーブルで値が一致する行に結果が制限されます。その他の有効な値は以下のとおりです: - `LeftOuter` には、一致する値がない親行の結果が含まれます。 - `Natural` 等価結合操作が実行され、2 つの値が同一である場合、結合された 2 つの列のうち 1 つの値のみが返されます。これらのメンバーは高度な JoinOperator を検討しました。 - `Exists` - `In` - `MatchFirstRowUsingCrossApply` これらのメンバーは、関連レコードの値をフィルター処理するために使用されます。 - `All` - `Any` - `NotAll` - `NotAny`
EntityAlias	テーブルのエイリアス。
Columns	テーブルに含める列。 ColumnSet を使用して、これらの列を結合テーブルに追加します。 QueryExpression を使用して列を選択する方法を学ぶ
LinkCriteria	クエリの結果をフィルターする複雑な条件および論理フィルター式。 QueryExpression を使用して行をフィルタリングする方法を学ぶ
LinkEntities	ネストされたリンクを含めることができるエンティティ間のリンクのコレクション。クエリには最大 15 個のリンクを含めることができます

注意

LinkEntity.LinkFromAttributeName および LinkEntity.LinkToAttributeName プロパティの意味は、FetchXml 内の対応する from および to 属性の意味とは逆になります。 FetchXml での from および to 属性の使用について学ぶ

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 算出方法を使用してクエリを拡張し、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");

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

制限

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

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

多対一関連付け

前の例は、多くの取引先レコードが 1 つの取引先責任者レコードを参照できる多対 1 のリレーションシップです。この情報は、account_primary_contact という一対一のリレーションシップで定義されます:

Property	価値	Comment
`SchemaName`	`account_primary_contact`	リレーションシップの固有名です。
`ReferencedEntity`	`contact`	参照されるテーブルです。多対一のものです。前の例の `LinkToEntityName`例。
`ReferencedAttribute`	`contactid`	参照されるテーブルの主キーです。前の例の `LinkToAttributeName`例。
`ReferencingEntity`	`account`	他のテーブルを参照するルックアップ列を持つテーブルです。多対一の多くです。前の例の `LinkFromEntityName`例。
`ReferencingAttribute`	`primarycontactid`	ルックアップ列の表示名です。前の例の `LinkFromAttributeName`例。
`RelationshipType`	`OneToManyRelationship`	参照 (1 つの) `contact` テーブルから見た場合の 1 対多のリレーションシップ。参照 (多くの) `account` テーブルから見た場合の多対一のリレーションシップ

リレーションシップ情報の取得

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

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

次の表は、多対 1 のリレーションシップに使用する関係値を示しています。

Property	リレーションシップの値	Comment
`LinkFromEntityName`	`ReferencingEntity`	参照されるテーブルです。多対一の多くです。多対 1 の例の`account`。 AddLink 算出方法にこのプロパティのパラメーターはありません。なぜなら、`QueryExpression.EntityName` または `LinkEntity.LinkToEntityName` プロパティから取り込むことができるからです。
`LinkToEntityName`	`ReferencedEntity`	他のテーブルが参照する主キーを持つテーブル。多対一のものです。多対 1 の例の`contact`。
`LinkFromAttributeName`	`ReferencingAttribute`	ルックアップ列の表示名です。多対 1 の例の`primarycontactid`。
`LinkToAttributeName`	`ReferencedAttribute`	参照されるテーブルの主キーです。多対 1 の例の`contactid`。

一対多関連付け

多対 1 と 1 対多のリレーションシップは、コインの表裏のようなものです。リレーションシップはテーブル間に存在するため、その使用方法は、どのテーブルがクエリのベーステーブルであるかによって異なります。

注意

ベーステーブルに参照列が含まれている場合、それは多対 1 のリレーションシップになります。それ以外の場合は、1対多のリレーションシップになります。

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

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

一対多のリレーションシップに対しては、これらのリレーションシップの値を使用します。

Property	リレーションシップの値	Comment
`LinkFromEntityName`	`ReferencedEntity`	参照されるテーブルです。多対一のものです。 1 対多の例の `contact`。 AddLink 算出方法にこのプロパティのパラメーターはありません。なぜなら、`QueryExpression.EntityName` または `LinkEntity.LinkToEntityName` プロパティから取り込むことができるからです。
`LinkToEntityName`	`ReferencingEntity`	他のテーブルを参照するルックアップ列を持つテーブルです。多対一の多くです。 1 対多の例の `account`。
`LinkFromAttributeName`	`ReferencedAttribute`	参照されるテーブルの主キーです。 1 対多の例の `contactid`。
`LinkToAttributeName`	`ReferencingAttribute`	ルックアップ列の表示名です。 1 対多の例の `primarycontactid`。

結果には、多対 1 のリレーションシップを使用した前の例と同じレコードとデータが含まれますが、 「親エンティティ」 が 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");

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

linkedTeamMemberShip は、systemuser を teammembership 交差テーブル (systemuserid = 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 プロパティで指定された列は、リレーションシップに関係がない場合でも同じタイプであることが重要です。異なるタイプの列を使用すると、パフォーマンスに影響を与える可能性のあるタイプ変換が必要になり、一部の列値では失敗する可能性があります。

次の列タイプは LinkFromAttributeName および LinkToAttributeName プロパティで使用できません。

ファイル
Image
MultiSelect フィールド
PartyList

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

複数行テキスト タイプの列
最大長 850 を超える 1 行テキストタイプ の列
数式列
計算列
論理列

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

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

ConditionExpression.EntityName property を使用することにより、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");

高度な JoinOperators を使用する

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

件名	プロパティ
`Exists`	`Inner` のバリアントで、パフォーマンス上の利点があります。 `where` 句で EXISTS 条件を使用します。結果に親行の複数のコピーが必要ない場合、`Exists` を使用します。 `Exists` と `In` の詳細。
`In`	`Inner` のバリアントで、パフォーマンス上の利点があります。 where 句内では、IN 条件を使用します。結果に親行の複数のコピーが必要ない場合、`In` を使用します。 `Exists` と `In` の詳細。
`MatchFirstRowUsingCrossApply`	`Inner` のバリアントで、パフォーマンス上の利点があります。このタイプは、リンクされたエンティティの一致する行の例が 1 つだけで十分で、結果に親行の複数のコピーが必要ない場合に使用します。 `MatchFirstRowUsingCrossApply` を使うことに関する詳細

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

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

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

これら JoinOperator メンバーによりまた、Dataverse で各親行に対して最初に一致するリンクエンティティ行のみを検索できるようにするため、JoinOperator.Inner とリンクされたエンティティ内の一致する行をすべて検索するよりも効率的です。

`JoinOperator.Exists` の例

これらの QueryExpression および SQL 例は、JoinOperator.Exists で適用されたパターンを示しています。

QueryExpression
SQL

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
SQL

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 を使用して、サブクエリを含む CROSS APPLY 演算子を生成します。

QueryExpression
SQL

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"

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

次の手順

列の注文方法について解説します。

行を並べ替える

注意

ドキュメントの言語設定についてお聞かせください。簡単な調査を行います。 (この調査は英語です)

この調査には約 7 分かかります。個人データは収集されません (プライバシーステートメント)。

次の方法で共有

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

LinkEntity プロパティ

LinkEntity の例

AddLink 算出方法

制限

多対一関連付け

リレーションシップ情報の取得

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

一対多関連付け

多対多関連付け

リレーションシップがない

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

高度な JoinOperators を使用する

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

`JoinOperator.Exists` の例

`JoinOperator.In` の例

`JoinOperator.MatchFirstRowUsingCrossApply` の使用

次の手順

その他のリソース

次の方法で共有

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

LinkEntity プロパティ

LinkEntity の例

AddLink 算出方法

制限

多対一関連付け

リレーションシップ情報の取得

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

一対多関連付け

多対多関連付け

リレーションシップがない

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

高度な JoinOperators を使用する

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

JoinOperator.Exists の例

JoinOperator.In の例

JoinOperator.MatchFirstRowUsingCrossApply の使用

次の手順

その他のリソース

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

`JoinOperator.Exists` の例

`JoinOperator.In` の例

`JoinOperator.MatchFirstRowUsingCrossApply` の使用