ADO EF - TPHでの派生型間の関連付けのエラー

c# entity-framework orm

質問

バックグラウンド

.NET Framework 3.5 SP1を使用して、Visual Studio 2008 SP1のADO Entity Frameworkを使用してデータアクセスライブラリを作成しています。どちらも抽象型から派生した2つのエンティティ間の関連付けを作成しようとしています。 Table Per Hierarchy (TPH)を使用して両方のエンティティ継承階層を表しています。つまり、エンティティ継承階層ごとに1つずつ、合計2つのテーブルしかありません。

:この問題を回避するためにTable Per Type(TPT)を使用することができますが、それ自体の欠点があります。継承永続モデルを選択する場合の詳細については、 こちらこちらを参照してください。

これは、エンティティモデルのデザイナビューのスクリーンショットです。

Visual Studio 2008 SP1のエンティティモデルのデザインビュー

これがデータベーススキーマのスクリーンショットです。

データベーススキーマ

仮定

ADO Entity Framework Designerで、Visual Studio 2008 SP1と.NET Framework 3.5 SP1を使用してTPHでモデル化された派生型の間に関連付けを作成すると、「エラー3034:異なるキーを持つ2つのエンティティが同じマップされていますこれら2つのマッピングフラグメントが、重複するキーを持つエンティティの2つのグループを同じ行のグループにマッピングしないようにしてください。」

私がオンライン読んだことに基づいて、この問題を解決するためには、外部キーの関連付けに次のように条件を追加する必要があります。

<Condition ColumnName="Endpoint1" IsNull="false" />

これがPersonPersonToPerson1アソシエーションのこの編集のスクリーンショットです。

Visual Studio 2008 SP1 GraphModel.edmxの編集

制約

  • 各階層の基本クラス(すなわちNodeとLink) 抽象的でなければなりません。
  • 2つの派生型間の関連付けからのナビゲーションプロパティは、リンク型(PersonToPersonとPersonToLocationなど)によって区別できる必要あります。つまり、Link抽象基本クラスとNode抽象基本クラスの間に関連付けを作成することはできません

問題:

上記のようにEntity Modelを作成し、上記のAssumptionsに記載されているようにAssociationMappingsにConditionsを追加すると、モデルを作成/検証するときに "Error 3023"が表示されます。

Error   1   Error 3023: Problem in Mapping Fragments starting at lines 146, 153, 159, 186, 195, 204, 213: Column Link.Endpoint1 has no default value and is not nullable. A column value is required to store entity data.
An Entity with Key (PK) will not round-trip when:
((PK is NOT in 'LinkSet' EntitySet OR PK does NOT play Role 'PersonToPerson' in AssociationSet 'PersonPersonToPerson1') AND (PK is in 'LinkSet' EntitySet OR PK plays Role 'PersonToPerson' in AssociationSet 'PersonPersonToPerson1' OR PK plays Role 'PersonToPerson' in AssociationSet 'PersonPersonToPerson'))
C:\Documents and Settings\Demo\My Documents\Visual Studio 2008\Projects\GraphExample2.BusinessEntities\GraphExample2.BusinessEntities\GraphModel.edmx   147 15  GraphExample2.BusinessEntities

上記のシナリオでEntity Frameworkがハングアップしているのは、同じ外部キーにマッピングされている2つのプロパティがあるということです。たとえば、Endpoint1の列と外部キーは、PersonToLocation派生型のPersonプロパティにマッピングされ、PersonToPerson派生型のLeaderプロパティにマッピングされます。

なぜこれが問題なのかわかりません。 Leader / Followerプロパティは、PersonToPerson派生型にのみ存在し(他の派生型や基本型には存在しない)、Person / Locationプロパティにも同じことが当てはまるため、EFがTypeDiscriminatorフィールドでどれを判断するのに十分でないか特定の行が属する行を設定しますか?

TypeDiscriminator = 1のオブジェクトを扱う場合は、Endpoint1をLeaderに、Endpoint2をFollowerに配置します。同様に、TypeDiscriminator = 2のオブジェクトを扱う場合は、Endpoint1をPersonに、Endpoint2をLocationに配置します。

質問:

どのようにこれらの関連付けが起こるようにするためにエラー3023を解決しますか。

または

上記のADO Entity Frameworkで、どのような種類の関連付けを作成しますか。

参考文献:

コード

SQL:

USE [GraphExample2]
GO
/****** Object:  Table [dbo].[Node]    Script Date: 02/17/2009 14:36:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Node](
    [NodeID] [int] NOT NULL,
    [NodeTypeDiscriminator] [int] NOT NULL,
    [Name] [varchar](255) NOT NULL,
    [Description] [varchar](1023) NULL,
 CONSTRAINT [PK_Node] PRIMARY KEY CLUSTERED 
(
    [NodeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Link]    Script Date: 02/17/2009 14:36:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Link](
    [LinkID] [int] NOT NULL,
    [LinkTypeDiscriminator] [int] NOT NULL,
    [Endpoint1] [int] NOT NULL,
    [Endpoint2] [int] NOT NULL,
    [Name] [varchar](255) NULL,
    [Description] [varchar](1023) NULL,
 CONSTRAINT [PK_Link] PRIMARY KEY CLUSTERED 
(
    [LinkID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  ForeignKey [FK_Link_Node_Endpoint1]    Script Date: 02/17/2009 14:36:12 ******/
ALTER TABLE [dbo].[Link]  WITH CHECK ADD  CONSTRAINT [FK_Link_Node_Endpoint1] FOREIGN KEY([Endpoint1])
REFERENCES [dbo].[Node] ([NodeID])
GO
ALTER TABLE [dbo].[Link] CHECK CONSTRAINT [FK_Link_Node_Endpoint1]
GO
/****** Object:  ForeignKey [FK_Link_Node_Endpoint2]    Script Date: 02/17/2009 14:36:12 ******/
ALTER TABLE [dbo].[Link]  WITH CHECK ADD  CONSTRAINT [FK_Link_Node_Endpoint2] FOREIGN KEY([Endpoint2])
REFERENCES [dbo].[Node] ([NodeID])
GO
ALTER TABLE [dbo].[Link] CHECK CONSTRAINT [FK_Link_Node_Endpoint2]
GO

EDMX:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="GraphModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
        <EntityContainer Name="GraphModelStoreContainer">
          <EntitySet Name="Link" EntityType="GraphModel.Store.Link" store:Type="Tables" Schema="dbo" />
          <EntitySet Name="Node" EntityType="GraphModel.Store.Node" store:Type="Tables" Schema="dbo" />
          <AssociationSet Name="FK_Link_Node_Endpoint1" Association="GraphModel.Store.FK_Link_Node_Endpoint1">
            <End Role="Node" EntitySet="Node" />
            <End Role="Link" EntitySet="Link" />
          </AssociationSet>
          <AssociationSet Name="FK_Link_Node_Endpoint2" Association="GraphModel.Store.FK_Link_Node_Endpoint2">
            <End Role="Node" EntitySet="Node" />
            <End Role="Link" EntitySet="Link" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="Link">
          <Key>
            <PropertyRef Name="LinkID" />
          </Key>
          <Property Name="LinkID" Type="int" Nullable="false" />
          <Property Name="LinkTypeDiscriminator" Type="int" Nullable="false" />
          <Property Name="Endpoint1" Type="int" Nullable="false" />
          <Property Name="Endpoint2" Type="int" Nullable="false" />
          <Property Name="Name" Type="varchar" MaxLength="255" />
          <Property Name="Description" Type="varchar" MaxLength="1023" />
        </EntityType>
        <EntityType Name="Node">
          <Key>
            <PropertyRef Name="NodeID" />
          </Key>
          <Property Name="NodeID" Type="int" Nullable="false" />
          <Property Name="NodeTypeDiscriminator" Type="int" Nullable="false" />
          <Property Name="Name" Type="varchar" Nullable="false" MaxLength="255" />
          <Property Name="Description" Type="varchar" MaxLength="1023" />
        </EntityType>
        <Association Name="FK_Link_Node_Endpoint1">
          <End Role="Node" Type="GraphModel.Store.Node" Multiplicity="1" />
          <End Role="Link" Type="GraphModel.Store.Link" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="Node">
              <PropertyRef Name="NodeID" />
            </Principal>
            <Dependent Role="Link">
              <PropertyRef Name="Endpoint1" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
        <Association Name="FK_Link_Node_Endpoint2">
          <End Role="Node" Type="GraphModel.Store.Node" Multiplicity="1" />
          <End Role="Link" Type="GraphModel.Store.Link" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="Node">
              <PropertyRef Name="NodeID" />
            </Principal>
            <Dependent Role="Link">
              <PropertyRef Name="Endpoint2" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
      </Schema>
    </edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema xmlns="http://schemas.microsoft.com/ado/2006/04/edm" Namespace="GraphModel" Alias="Self">
        <EntityContainer Name="GraphModelContainer" >
          <EntitySet Name="NodeSet" EntityType="GraphModel.Node" />
          <EntitySet Name="LinkSet" EntityType="GraphModel.Link" />
          <AssociationSet Name="PersonPersonToPerson" Association="GraphModel.PersonPersonToPerson">
            <End Role="Person" EntitySet="NodeSet" />
            <End Role="PersonToPerson" EntitySet="LinkSet" />
          </AssociationSet>
          <AssociationSet Name="PersonPersonToPerson1" Association="GraphModel.PersonPersonToPerson1">
            <End Role="Person" EntitySet="NodeSet" />
            <End Role="PersonToPerson" EntitySet="LinkSet" />
          </AssociationSet>
          <AssociationSet Name="Person_PersonToLocation" Association="GraphModel.Person_PersonToLocation">
            <End Role="Person" EntitySet="NodeSet" />
            <End Role="PersonToLocation" EntitySet="LinkSet" />
          </AssociationSet>
          <AssociationSet Name="Location_PersonToLocation" Association="GraphModel.Location_PersonToLocation">
            <End Role="Location" EntitySet="NodeSet" />
            <End Role="PersonToLocation" EntitySet="LinkSet" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="Node" Abstract="true">
          <Key>
            <PropertyRef Name="NodeId" />
          </Key>
          <Property Name="NodeId" Type="Int32" Nullable="false" />
          <Property Name="Name" Type="String" Nullable="false" />
          <Property Name="Description" Type="String" Nullable="true" />
        </EntityType>
        <EntityType Name="Person" BaseType="GraphModel.Node" >
          <NavigationProperty Name="Leaders" Relationship="GraphModel.PersonPersonToPerson" FromRole="Person" ToRole="PersonToPerson" />
          <NavigationProperty Name="Followers" Relationship="GraphModel.PersonPersonToPerson1" FromRole="Person" ToRole="PersonToPerson" />
          <NavigationProperty Name="Locations" Relationship="GraphModel.Person_PersonToLocation" FromRole="Person" ToRole="PersonToLocation" />
        </EntityType>
        <EntityType Name="Location" BaseType="GraphModel.Node" >
          <NavigationProperty Name="Visitors" Relationship="GraphModel.Location_PersonToLocation" FromRole="Location" ToRole="PersonToLocation" />
        </EntityType>
        <EntityType Name="Link" Abstract="true">
          <Key>
            <PropertyRef Name="LinkId" />
          </Key>
          <Property Name="LinkId" Type="Int32" Nullable="false" />
          <Property Name="Name" Type="String" Nullable="true" />
          <Property Name="Description" Type="String" Nullable="true" />
        </EntityType>
        <EntityType Name="PersonToPerson" BaseType="GraphModel.Link" >
          <NavigationProperty Name="Leader" Relationship="GraphModel.PersonPersonToPerson" FromRole="PersonToPerson" ToRole="Person" />
          <NavigationProperty Name="Follower" Relationship="GraphModel.PersonPersonToPerson1" FromRole="PersonToPerson" ToRole="Person" />
        </EntityType>
        <EntityType Name="PersonToLocation" BaseType="GraphModel.Link" >
          <NavigationProperty Name="Person" Relationship="GraphModel.Person_PersonToLocation" FromRole="PersonToLocation" ToRole="Person" />
          <NavigationProperty Name="Location" Relationship="GraphModel.Location_PersonToLocation" FromRole="PersonToLocation" ToRole="Location" />
        </EntityType>
        <Association Name="PersonPersonToPerson">
          <End Type="GraphModel.Person" Role="Person" Multiplicity="1" />
          <End Type="GraphModel.PersonToPerson" Role="PersonToPerson" Multiplicity="*" />
        </Association>
        <Association Name="PersonPersonToPerson1">
          <End Type="GraphModel.Person" Role="Person" Multiplicity="1" />
          <End Type="GraphModel.PersonToPerson" Role="PersonToPerson" Multiplicity="*" />
        </Association>
        <Association Name="Person_PersonToLocation">
          <End Type="GraphModel.Person" Role="Person" Multiplicity="1" />
          <End Type="GraphModel.PersonToLocation" Role="PersonToLocation" Multiplicity="*" />
        </Association>
        <Association Name="Location_PersonToLocation">
          <End Type="GraphModel.Location" Role="Location" Multiplicity="1" />
          <End Type="GraphModel.PersonToLocation" Role="PersonToLocation" Multiplicity="*" />
        </Association>
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS" Space="C-S">
        <Alias Key="Model" Value="GraphModel" />
        <Alias Key="Target" Value="GraphModel.Store" />
        <EntityContainerMapping CdmEntityContainer="GraphModelContainer" StorageEntityContainer="GraphModelStoreContainer">
          <EntitySetMapping Name="LinkSet">
            <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Link)">
              <MappingFragment StoreEntitySet="Link">
                <ScalarProperty Name="Description" ColumnName="Description" />
                <ScalarProperty Name="Name" ColumnName="Name" />
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(GraphModel.PersonToPerson)">
              <MappingFragment StoreEntitySet="Link" >
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
                <Condition ColumnName="LinkTypeDiscriminator" Value="1" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(GraphModel.PersonToLocation)">
              <MappingFragment StoreEntitySet="Link" >
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
                <Condition ColumnName="LinkTypeDiscriminator" Value="2" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <EntitySetMapping Name="NodeSet">
            <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Node)">
              <MappingFragment StoreEntitySet="Node">
                <ScalarProperty Name="Description" ColumnName="Description" />
                <ScalarProperty Name="Name" ColumnName="Name" />
                <ScalarProperty Name="NodeId" ColumnName="NodeID" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Person)">
              <MappingFragment StoreEntitySet="Node" >
                <ScalarProperty Name="NodeId" ColumnName="NodeID" />
                <Condition ColumnName="NodeTypeDiscriminator" Value="1" />
              </MappingFragment>
            </EntityTypeMapping>
            <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Location)">
              <MappingFragment StoreEntitySet="Node" >
                <ScalarProperty Name="NodeId" ColumnName="NodeID" />
                <Condition ColumnName="NodeTypeDiscriminator" Value="2" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <AssociationSetMapping Name="PersonPersonToPerson1" TypeName="GraphModel.PersonPersonToPerson1" StoreEntitySet="Link">
            <EndProperty Name="Person">
              <ScalarProperty Name="NodeId" ColumnName="Endpoint1" />
            </EndProperty>
            <EndProperty Name="PersonToPerson">
              <ScalarProperty Name="LinkId" ColumnName="LinkID" />
            </EndProperty>
            <Condition ColumnName="Endpoint1" IsNull="false" />
          </AssociationSetMapping>
          <AssociationSetMapping Name="PersonPersonToPerson" TypeName="GraphModel.PersonPersonToPerson" StoreEntitySet="Link">
            <EndProperty Name="Person">
              <ScalarProperty Name="NodeId" ColumnName="Endpoint2" />
            </EndProperty>
            <EndProperty Name="PersonToPerson">
              <ScalarProperty Name="LinkId" ColumnName="LinkID" />
            </EndProperty>
            <Condition ColumnName="Endpoint2" IsNull="false" />
          </AssociationSetMapping>
          <AssociationSetMapping Name="Person_PersonToLocation" TypeName="GraphModel.Person_PersonToLocation" StoreEntitySet="Link">
            <EndProperty Name="Person">
              <ScalarProperty Name="NodeId" ColumnName="Endpoint1" />
            </EndProperty>
            <EndProperty Name="PersonToLocation">
              <ScalarProperty Name="LinkId" ColumnName="LinkID" />
            </EndProperty>
            <Condition ColumnName="Endpoint1" IsNull="false" />
          </AssociationSetMapping>
          <AssociationSetMapping Name="Location_PersonToLocation" TypeName="GraphModel.Location_PersonToLocation" StoreEntitySet="Link">
            <EndProperty Name="Location">
              <ScalarProperty Name="NodeId" ColumnName="Endpoint2" />
            </EndProperty>
            <EndProperty Name="PersonToLocation">
              <ScalarProperty Name="LinkId" ColumnName="LinkID" />
            </EndProperty>
            <Condition ColumnName="Endpoint2" IsNull="false" />
          </AssociationSetMapping>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2007/06/edmx">
    <edmx:Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </edmx:Connection>
    <edmx:Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
      </DesignerInfoPropertySet>
    </edmx:Options>
    <!-- Diagram content (shape and connector positions) -->
    <edmx:Diagrams>
      <Diagram Name="GraphModel" ZoomLevel="114" >
        <EntityTypeShape EntityType="GraphModel.Node" Width="1.5" PointX="5.875" PointY="1.375" Height="1.427958984375" />
        <EntityTypeShape EntityType="GraphModel.Person" Width="1.5" PointX="5.875" PointY="3.25" Height="1.4279589843749996" />
        <EntityTypeShape EntityType="GraphModel.Location" Width="1.5" PointX="7.75" PointY="4.625" Height="1.0992643229166665" />
        <InheritanceConnector EntityType="GraphModel.Location">
          <ConnectorPoint PointX="7.375" PointY="2.0889794921875" />
          <ConnectorPoint PointX="8.5" PointY="2.0889794921875" />
          <ConnectorPoint PointX="8.5" PointY="4.625" />
        </InheritanceConnector>
        <EntityTypeShape EntityType="GraphModel.Link" Width="1.5" PointX="2.875" PointY="1.375" Height="1.427958984375" />
        <EntityTypeShape EntityType="GraphModel.PersonToPerson" Width="1.75" PointX="2.625" PointY="3.125" Height="0.9349169921875" />
        <InheritanceConnector EntityType="GraphModel.PersonToPerson">
          <ConnectorPoint PointX="3.625" PointY="2.802958984375" />
          <ConnectorPoint PointX="3.625" PointY="3.125" />
        </InheritanceConnector>
        <InheritanceConnector EntityType="GraphModel.Person">
          <ConnectorPoint PointX="6.625" PointY="2.802958984375" />
          <ConnectorPoint PointX="6.625" PointY="3.25" />
        </InheritanceConnector>
        <EntityTypeShape EntityType="GraphModel.PersonToLocation" Width="1.875" PointX="0.75" PointY="4.625" Height="1.2636116536458326" />
        <InheritanceConnector EntityType="GraphModel.PersonToLocation">
          <ConnectorPoint PointX="2.875" PointY="2.0889794921875" />
          <ConnectorPoint PointX="1.65625" PointY="2.0889794921875" />
          <ConnectorPoint PointX="1.65625" PointY="4.625" />
        </InheritanceConnector>
        <AssociationConnector Association="GraphModel.PersonPersonToPerson">
          <ConnectorPoint PointX="5.875" PointY="3.8193058268229163" />
          <ConnectorPoint PointX="4.375" PointY="3.8193058268229163" />
        </AssociationConnector>
        <AssociationConnector Association="GraphModel.PersonPersonToPerson1">
          <ConnectorPoint PointX="5.875" PointY="3.4721529134114579" />
          <ConnectorPoint PointX="4.375" PointY="3.4721529134114579" />
        </AssociationConnector>
        <AssociationConnector Association="GraphModel.Person_PersonToLocation">
          <ConnectorPoint PointX="6.625" PointY="4.677958984375" />
          <ConnectorPoint PointX="6.625" PointY="5.1875" />
          <ConnectorPoint PointX="2.625" PointY="5.1875" />
        </AssociationConnector>
        <AssociationConnector Association="GraphModel.Location_PersonToLocation">
          <ConnectorPoint PointX="7.75" PointY="5.4791666666666661" />
          <ConnectorPoint PointX="2.625" PointY="5.4791666666666661" />
        </AssociationConnector>
      </Diagram>
    </edmx:Diagrams>
  </edmx:Designer>
</edmx:Edmx>

受け入れられた回答

考えられる回避策

  1. 派生型間の関連ごとに別々の列を作成し、これらの各列をNULL可能にします。
  2. これらの新しい列と主キーテーブルの間に外部キーを作成します。
  3. Entity Modelの各関連付けを特定の一意の列と外部キーにマッピングして、各列と外部キーが1回だけ使用されるようにします。

問題

これは、必要な列数を増やすため、非常に望ましくない解決策です。

  • More Columns - 派生型間の各関連付けに対して列を追加すると、列数が急増します。
  • 空の列 TPHの場合は、テーブルに空の列がたくさんあることを意味します。
  • SQL JOIN - 空の列の数を避けるためにTPHからTPTに切り替えると、EFがJOINを使用する必要が生じます。これは非常に頻繁に発生する必要があります(派生型を扱うたびに)。
  • リファクタリング将来派生型を追加する場合は、Entityモデル(* .edmx)とそのマッピングを更新するだけでなく、列を追加してデータベーススキーマを変更する必要があります。

上記のLink / Nodeの例では、結果として得られるデータベーススキーマは次のようになります。

GraphExample回避策データベーススキーマhttp://img230.imageshack.us/img230/1628/graphexampledatabasewor.th.png


コード

SQL:

USE [GraphExample2]
GO
/****** Object:  Table [dbo].[Node]    Script Date: 02/26/2009 15:45:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Node](
    [NodeID] [int] IDENTITY(1,1) NOT NULL,
    [NodeTypeDiscriminator] [int] NOT NULL,
    [Name] [varchar](255) NOT NULL,
    [Description] [varchar](1023) NULL,
 CONSTRAINT [PK_Node] PRIMARY KEY CLUSTERED 
(
    [NodeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Link]    Script Date: 02/26/2009 15:45:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Link](
    [LinkID] [int] IDENTITY(1,1) NOT NULL,
    [LinkTypeDiscriminator] [int] NOT NULL,
    [LeaderID] [int] NULL,
    [FollowerID] [int] NULL,
    [PersonID] [int] NULL,
    [LocationID] [int] NULL,
    [Name] [varchar](255) NULL,
    [Description] [varchar](1023) NULL,
 CONSTRAINT [PK_Link] PRIMARY KEY CLUSTERED 
(
    [LinkID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  ForeignKey [FK_Link_Node_Follower]    Script Date: 02/26/2009 15:45:53 ******/
ALTER TABLE [dbo].[Link]  WITH CHECK ADD  CONSTRAINT [FK_Link_Node_Follower] FOREIGN KEY([FollowerID])
REFERENCES [dbo].[Node] ([NodeID])
GO
ALTER TABLE [dbo].[Link] CHECK CONSTRAINT [FK_Link_Node_Follower]
GO
/****** Object:  ForeignKey [FK_Link_Node_Leader]    Script Date: 02/26/2009 15:45:53 ******/
ALTER TABLE [dbo].[Link]  WITH CHECK ADD  CONSTRAINT [FK_Link_Node_Leader] FOREIGN KEY([LeaderID])
REFERENCES [dbo].[Node] ([NodeID])
GO
ALTER TABLE [dbo].[Link] CHECK CONSTRAINT [FK_Link_Node_Leader]
GO
/****** Object:  ForeignKey [FK_Link_Node_Location]    Script Date: 02/26/2009 15:45:53 ******/
ALTER TABLE [dbo].[Link]  WITH CHECK ADD  CONSTRAINT [FK_Link_Node_Location] FOREIGN KEY([LocationID])
REFERENCES [dbo].[Node] ([NodeID])
GO
ALTER TABLE [dbo].[Link] CHECK CONSTRAINT [FK_Link_Node_Location]
GO
/****** Object:  ForeignKey [FK_Link_Node_Person]    Script Date: 02/26/2009 15:45:53 ******/
ALTER TABLE [dbo].[Link]  WITH CHECK ADD  CONSTRAINT [FK_Link_Node_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Node] ([NodeID])
GO
ALTER TABLE [dbo].[Link] CHECK CONSTRAINT [FK_Link_Node_Person]
GO

EDMX:

<?xml version="1.0" encoding="utf-8"?>
  <edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
    <!-- EF Runtime content -->
    <edmx:Runtime>
      <!-- SSDL content -->
      <edmx:StorageModels>
        <Schema Namespace="GraphModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
          <EntityContainer Name="GraphModelStoreContainer">
            <EntitySet Name="Link" EntityType="GraphModel.Store.Link" store:Type="Tables" Schema="dbo" />
            <EntitySet Name="Node" EntityType="GraphModel.Store.Node" store:Type="Tables" Schema="dbo" />
            <AssociationSet Name="FK_Link_Node_Follower" Association="GraphModel.Store.FK_Link_Node_Follower">
              <End Role="Node" EntitySet="Node" />
              <End Role="Link" EntitySet="Link" />
            </AssociationSet>
            <AssociationSet Name="FK_Link_Node_Leader" Association="GraphModel.Store.FK_Link_Node_Leader">
              <End Role="Node" EntitySet="Node" />
              <End Role="Link" EntitySet="Link" />
            </AssociationSet>
            <AssociationSet Name="FK_Link_Node_Location" Association="GraphModel.Store.FK_Link_Node_Location">
              <End Role="Node" EntitySet="Node" />
              <End Role="Link" EntitySet="Link" />
            </AssociationSet>
            <AssociationSet Name="FK_Link_Node_Person" Association="GraphModel.Store.FK_Link_Node_Person">
              <End Role="Node" EntitySet="Node" />
              <End Role="Link" EntitySet="Link" />
            </AssociationSet>
          </EntityContainer>
          <EntityType Name="Link">
            <Key>
              <PropertyRef Name="LinkID" />
            </Key>
            <Property Name="LinkID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
            <Property Name="LinkTypeDiscriminator" Type="int" Nullable="false" />
            <Property Name="LeaderID" Type="int" />
            <Property Name="FollowerID" Type="int" />
            <Property Name="PersonID" Type="int" />
            <Property Name="LocationID" Type="int" />
            <Property Name="Name" Type="varchar" MaxLength="255" />
            <Property Name="Description" Type="varchar" MaxLength="1023" />
          </EntityType>
          <EntityType Name="Node">
            <Key>
              <PropertyRef Name="NodeID" />
            </Key>
            <Property Name="NodeID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
            <Property Name="NodeTypeDiscriminator" Type="int" Nullable="false" />
            <Property Name="Name" Type="varchar" Nullable="false" MaxLength="255" />
            <Property Name="Description" Type="varchar" MaxLength="1023" />
          </EntityType>
          <Association Name="FK_Link_Node_Follower">
            <End Role="Node" Type="GraphModel.Store.Node" Multiplicity="0..1" />
            <End Role="Link" Type="GraphModel.Store.Link" Multiplicity="*" />
            <ReferentialConstraint>
              <Principal Role="Node">
                <PropertyRef Name="NodeID" />
              </Principal>
              <Dependent Role="Link">
                <PropertyRef Name="FollowerID" />
              </Dependent>
            </ReferentialConstraint>
          </Association>
          <Association Name="FK_Link_Node_Leader">
            <End Role="Node" Type="GraphModel.Store.Node" Multiplicity="0..1" />
            <End Role="Link" Type="GraphModel.Store.Link" Multiplicity="*" />
            <ReferentialConstraint>
              <Principal Role="Node">
                <PropertyRef Name="NodeID" />
              </Principal>
              <Dependent Role="Link">
                <PropertyRef Name="LeaderID" />
              </Dependent>
            </ReferentialConstraint>
          </Association>
          <Association Name="FK_Link_Node_Location">
            <End Role="Node" Type="GraphModel.Store.Node" Multiplicity="0..1" />
            <End Role="Link" Type="GraphModel.Store.Link" Multiplicity="*" />
            <ReferentialConstraint>
              <Principal Role="Node">
                <PropertyRef Name="NodeID" />
              </Principal>
              <Dependent Role="Link">
                <PropertyRef Name="LocationID" />
              </Dependent>
            </ReferentialConstraint>
          </Association>
          <Association Name="FK_Link_Node_Person">
            <End Role="Node" Type="GraphModel.Store.Node" Multiplicity="0..1" />
            <End Role="Link" Type="GraphModel.Store.Link" Multiplicity="*" />
            <ReferentialConstraint>
              <Principal Role="Node">
                <PropertyRef Name="NodeID" />
              </Principal>
              <Dependent Role="Link">
                <PropertyRef Name="PersonID" />
              </Dependent>
            </ReferentialConstraint>
          </Association>
        </Schema>
      </edmx:StorageModels>
      <!-- CSDL content -->
      <edmx:ConceptualModels>
        <Schema xmlns="http://schemas.microsoft.com/ado/2006/04/edm" Namespace="GraphModel" Alias="Self">
          <EntityContainer Name="GraphModelContainer" >
            <EntitySet Name="NodeSet" EntityType="GraphModel.Node" />
            <EntitySet Name="LinkSet" EntityType="GraphModel.Link" />
            <AssociationSet Name="PersonPersonToPerson_Leader" Association="GraphModel.PersonPersonToPerson_Leader">
              <End Role="Person" EntitySet="NodeSet" />
              <End Role="PersonToPerson" EntitySet="LinkSet" />
            </AssociationSet>
            <AssociationSet Name="PersonPersonToPerson_Follower" Association="GraphModel.PersonPersonToPerson_Follower">
              <End Role="Person" EntitySet="NodeSet" />
              <End Role="PersonToPerson" EntitySet="LinkSet" />
            </AssociationSet>
            <AssociationSet Name="Person_PersonToLocation" Association="GraphModel.Person_PersonToLocation">
              <End Role="Person" EntitySet="NodeSet" />
              <End Role="PersonToLocation" EntitySet="LinkSet" />
            </AssociationSet>
            <AssociationSet Name="Location_PersonToLocation" Association="GraphModel.Location_PersonToLocation">
              <End Role="Location" EntitySet="NodeSet" />
              <End Role="PersonToLocation" EntitySet="LinkSet" />
            </AssociationSet>
          </EntityContainer>
          <EntityType Name="Node" Abstract="true">
            <Key>
              <PropertyRef Name="NodeId" />
            </Key>
            <Property Name="NodeId" Type="Int32" Nullable="false" />
            <Property Name="Name" Type="String" Nullable="false" />
            <Property Name="Description" Type="String" Nullable="true" />
          </EntityType>
          <EntityType Name="Person" BaseType="GraphModel.Node" >
            <NavigationProperty Name="Leaders" Relationship="GraphModel.PersonPersonToPerson_Leader" FromRole="Person" ToRole="PersonToPerson" />
            <NavigationProperty Name="Followers" Relationship="GraphModel.PersonPersonToPerson_Follower" FromRole="Person" ToRole="PersonToPerson" />
            <NavigationProperty Name="Locations" Relationship="GraphModel.Person_PersonToLocation" FromRole="Person" ToRole="PersonToLocation" />
          </EntityType>
          <EntityType Name="Location" BaseType="GraphModel.Node" >
            <NavigationProperty Name="Visitors" Relationship="GraphModel.Location_PersonToLocation" FromRole="Location" ToRole="PersonToLocation" />
          </EntityType>
          <EntityType Name="Link" Abstract="true">
            <Key>
              <PropertyRef Name="LinkId" />
            </Key>
            <Property Name="LinkId" Type="Int32" Nullable="false" />
            <Property Name="Name" Type="String" Nullable="true" />
            <Property Name="Description" Type="String" Nullable="true" />
          </EntityType>
          <EntityType Name="PersonToPerson" BaseType="GraphModel.Link" >
            <NavigationProperty Name="Leader" Relationship="GraphModel.PersonPersonToPerson_Leader" FromRole="PersonToPerson" ToRole="Person" />
            <NavigationProperty Name="Follower" Relationship="GraphModel.PersonPersonToPerson_Follower" FromRole="PersonToPerson" ToRole="Person" />
          </EntityType>
          <EntityType Name="PersonToLocation" BaseType="GraphModel.Link" >
            <NavigationProperty Name="Person" Relationship="GraphModel.Person_PersonToLocation" FromRole="PersonToLocation" ToRole="Person" />
            <NavigationProperty Name="Location" Relationship="GraphModel.Location_PersonToLocation" FromRole="PersonToLocation" ToRole="Location" />
          </EntityType>
          <Association Name="PersonPersonToPerson_Leader">
            <End Type="GraphModel.Person" Role="Person" Multiplicity="1" />
            <End Type="GraphModel.PersonToPerson" Role="PersonToPerson" Multiplicity="*" />
          </Association>
          <Association Name="PersonPersonToPerson_Follower">
            <End Type="GraphModel.Person" Role="Person" Multiplicity="1" />
            <End Type="GraphModel.PersonToPerson" Role="PersonToPerson" Multiplicity="*" />
          </Association>
          <Association Name="Person_PersonToLocation">
            <End Type="GraphModel.Person" Role="Person" Multiplicity="1" />
            <End Type="GraphModel.PersonToLocation" Role="PersonToLocation" Multiplicity="*" />
          </Association>
          <Association Name="Location_PersonToLocation">
            <End Type="GraphModel.Location" Role="Location" Multiplicity="1" />
            <End Type="GraphModel.PersonToLocation" Role="PersonToLocation" Multiplicity="*" />
          </Association>
        </Schema>
      </edmx:ConceptualModels>
      <!-- C-S mapping content -->
      <edmx:Mappings>
        <Mapping xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS" Space="C-S">
          <Alias Key="Model" Value="GraphModel" />
          <Alias Key="Target" Value="GraphModel.Store" />
          <EntityContainerMapping CdmEntityContainer="GraphModelContainer" StorageEntityContainer="GraphModelStoreContainer">
            <EntitySetMapping Name="LinkSet">
              <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Link)">
                <MappingFragment StoreEntitySet="Link">
                  <ScalarProperty Name="Description" ColumnName="Description" />
                  <ScalarProperty Name="Name" ColumnName="Name" />
                  <ScalarProperty Name="LinkId" ColumnName="LinkID" />
                </MappingFragment>
              </EntityTypeMapping>
              <EntityTypeMapping TypeName="IsTypeOf(GraphModel.PersonToPerson)">
                <MappingFragment StoreEntitySet="Link" >
                  <ScalarProperty Name="LinkId" ColumnName="LinkID" />
                  <Condition ColumnName="LinkTypeDiscriminator" Value="1" />
                </MappingFragment>
              </EntityTypeMapping>
              <EntityTypeMapping TypeName="IsTypeOf(GraphModel.PersonToLocation)">
                <MappingFragment StoreEntitySet="Link" >
                  <ScalarProperty Name="LinkId" ColumnName="LinkID" />
                  <Condition ColumnName="LinkTypeDiscriminator" Value="2" />
                </MappingFragment>
              </EntityTypeMapping>
            </EntitySetMapping>
            <EntitySetMapping Name="NodeSet">
              <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Node)">
                <MappingFragment StoreEntitySet="Node">
                  <ScalarProperty Name="Description" ColumnName="Description" />
                  <ScalarProperty Name="Name" ColumnName="Name" />
                  <ScalarProperty Name="NodeId" ColumnName="NodeID" />
                </MappingFragment>
              </EntityTypeMapping>
              <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Person)">
                <MappingFragment StoreEntitySet="Node" >
                  <ScalarProperty Name="NodeId" ColumnName="NodeID" />
                  <Condition ColumnName="NodeTypeDiscriminator" Value="1" />
                </MappingFragment>
              </EntityTypeMapping>
              <EntityTypeMapping TypeName="IsTypeOf(GraphModel.Location)">
                <MappingFragment StoreEntitySet="Node" >
                  <ScalarProperty Name="NodeId" ColumnName="NodeID" />
                  <Condition ColumnName="NodeTypeDiscriminator" Value="2" />
                </MappingFragment>
              </EntityTypeMapping>
            </EntitySetMapping>
            <AssociationSetMapping Name="PersonPersonToPerson_Follower" TypeName="GraphModel.PersonPersonToPerson_Follower" StoreEntitySet="Link">
              <EndProperty Name="Person">
                <ScalarProperty Name="NodeId" ColumnName="FollowerID" />
              </EndProperty>
              <EndProperty Name="PersonToPerson">
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
              </EndProperty>
            </AssociationSetMapping>
            <AssociationSetMapping Name="PersonPersonToPerson_Leader" TypeName="GraphModel.PersonPersonToPerson_Leader" StoreEntitySet="Link">
              <EndProperty Name="Person">
                <ScalarProperty Name="NodeId" ColumnName="LeaderID" />
              </EndProperty>
              <EndProperty Name="PersonToPerson">
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
              </EndProperty>
            </AssociationSetMapping>
            <AssociationSetMapping Name="Person_PersonToLocation" TypeName="GraphModel.Person_PersonToLocation" StoreEntitySet="Link">
              <EndProperty Name="Person">
                <ScalarProperty Name="NodeId" ColumnName="PersonID" />
              </EndProperty>
              <EndProperty Name="PersonToLocation">
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
              </EndProperty>
            </AssociationSetMapping>
            <AssociationSetMapping Name="Location_PersonToLocation" TypeName="GraphModel.Location_PersonToLocation" StoreEntitySet="Link">
              <EndProperty Name="Location">
                <ScalarProperty Name="NodeId" ColumnName="LocationID" />
              </EndProperty>
              <EndProperty Name="PersonToLocation">
                <ScalarProperty Name="LinkId" ColumnName="LinkID" />
              </EndProperty>
            </AssociationSetMapping>
          </EntityContainerMapping>
        </Mapping>
      </edmx:Mappings>
    </edmx:Runtime>
    <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
    <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2007/06/edmx">
      <edmx:Connection>
        <DesignerInfoPropertySet>
          <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
        </DesignerInfoPropertySet>
      </edmx:Connection>
      <edmx:Options>
        <DesignerInfoPropertySet>
          <DesignerProperty Name="ValidateOnBuild" Value="true" />
        </DesignerInfoPropertySet>
      </edmx:Options>
      <!-- Diagram content (shape and connector positions) -->
      <edmx:Diagrams>
        <Diagram Name="GraphModel" ZoomLevel="114" >
          <EntityTypeShape EntityType="GraphModel.Node" Width="1.5" PointX="5.875" PointY="1.375" Height="1.427958984375" />
          <EntityTypeShape EntityType="GraphModel.Person" Width="1.5" PointX="5.875" PointY="3.25" Height="1.4279589843749996" />
          <EntityTypeShape EntityType="GraphModel.Location" Width="1.5" PointX="7.75" PointY="4.625" Height="1.0992643229166665" />
          <InheritanceConnector EntityType="GraphModel.Location">
            <ConnectorPoint PointX="7.375" PointY="2.4176741536458342" />
            <ConnectorPoint PointX="8.5" PointY="2.4176741536458342" />
            <ConnectorPoint PointX="8.5" PointY="4.625" />
          </InheritanceConnector>
          <EntityTypeShape EntityType="GraphModel.Link" Width="1.5" PointX="2.875" PointY="1.375" Height="1.427958984375" />
          <EntityTypeShape EntityType="GraphModel.PersonToPerson" Width="1.75" PointX="2.75" PointY="3.25" Height="1.2636116536458326" />
          <InheritanceConnector EntityType="GraphModel.PersonToPerson" ManuallyRouted="false">
            <ConnectorPoint PointX="3.625" PointY="2.802958984375" />
            <ConnectorPoint PointX="3.625" PointY="3.25" />
          </InheritanceConnector>
          <InheritanceConnector EntityType="GraphModel.Person">
            <ConnectorPoint PointX="6.625" PointY="3.4603483072916683" />
            <ConnectorPoint PointX="6.625" PointY="3.25" />
          </InheritanceConnector>
          <EntityTypeShape EntityType="GraphModel.PersonToLocation" Width="1.875" PointX="0.75" PointY="4.625" Height="1.2636116536458326" />
          <InheritanceConnector EntityType="GraphModel.PersonToLocation">
            <ConnectorPoint PointX="2.875" PointY="2.4176741536458342" />
            <ConnectorPoint PointX="1.65625" PointY="2.4176741536458342" />
            <ConnectorPoint PointX="1.65625" PointY="4.625" />
          </InheritanceConnector>
          <AssociationConnector Association="GraphModel.PersonPersonToPerson_Leader">
            <ConnectorPoint PointX="5.875" PointY="3.8818058268229163" />
            <ConnectorPoint PointX="4.5" PointY="3.8818058268229163" />
          </AssociationConnector>
          <AssociationConnector Association="GraphModel.PersonPersonToPerson_Follower">
            <ConnectorPoint PointX="5.875" PointY="3.5034029134114579" />
            <ConnectorPoint PointX="4.5" PointY="3.5034029134114579" />
          </AssociationConnector>
          <AssociationConnector Association="GraphModel.Person_PersonToLocation">
            <ConnectorPoint PointX="6.625" PointY="4.677958984375" />
            <ConnectorPoint PointX="6.625" PointY="5.0078214863281243" />
            <ConnectorPoint PointX="2.625" PointY="5.0078214863281243" />
          </AssociationConnector>
          <AssociationConnector Association="GraphModel.Location_PersonToLocation">
            <ConnectorPoint PointX="7.75" PointY="5.40018798828125" />
            <ConnectorPoint PointX="2.625" PointY="5.40018798828125" />
          </AssociationConnector>
        </Diagram>
      </edmx:Diagrams>
    </edmx:Designer>
  </edmx:Edmx>

人気のある回答

ああ!

EntityModelCodeGenerator 多くに1作り、2つのテーブル間の多くの関係に0..1を作成します。

このような場合、それは可能な解決策かもしれません。

宜しくお願いします!サラフディン



Related

ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ
ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ