ADO EF - 錯誤映射TPH中派生類型之間的關聯

c# entity-framework orm

背景

我正在使用.NET Framework 3.5 SP1在Visual Studio 2008 SP1中使用ADO實體框架編寫數據訪問庫。我試圖在兩個實體之間創建關聯,這兩個實體都是從抽像類型派生的。我使用Table Per Hierarchy (TPH)表示兩個實體繼承層次結構 ,這意味著只有兩個表 - 每個實體繼承層次結構一個。

注意您可以使用每種類型的表(TPT)來避免此問題,但它帶來了它自身的缺點。在繼承持久性模型之間進行選擇時,請參閱此處此處了解更多詳細信息

以下是實體模型的Designer視圖的屏幕截圖:

Visual Studio 2008 SP1中實體模型的設計視圖

這是數據庫模式的屏幕截圖:

數據庫架構

假設

當您使用Visual Studio 2008 SP1和.NET Framework 3.5 SP1在使用TPH建模的派生類型之間在ADO實體框架設計器中創建關聯時,您可能會收到以下“錯誤3034:具有不同鍵的兩個實體映射到相同的確保這兩個映射片段不會將具有重疊鍵的兩組實體映射到同一組行。“

基於我在線閱讀的內容 ,為了解決此問題,您必須在外鍵上為關聯添加條件,如下所示:

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

以下是PersonPersonToPerson1關聯的此編輯的屏幕截圖:

Visual Studio 2008 SP1編輯GraphModel.edmx

約束

  • 每個層次結構的基類(即Node和Link) 必須是抽象的。
  • 兩個派生類型之間的關聯的導航屬性必須可以通過鏈接類型區分(例如PersonToPerson和PersonToLocation)。這意味著您無法在鏈接和節點抽象基類之間創建關聯。

問題:

當我如上所述創建實體模型並將條件添加到AssociationMappings時,如上面的假設中所述,當我構建/驗證模型時,我收到“錯誤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

在上面的場景中,實體框架正在掛起的東西是有兩個屬性被映射到相同的外鍵。例如,Endpoint1的列和外鍵映射到PersonToLocation派生類型中的Person屬性,並且它映射到PersonToPerson派生類型中的Leader屬性。

我不明白為什麼這是一個問題。由於Leader / Follower屬性僅在PersonToPerson派生類型中 - 不是任何其他派生類型或基本類型 - 並且Person / Location屬性也是如此,為什麼TypeDiscriminator字段不足以讓EF找出哪個設置一個給定的行屬於?

對我來說,似乎如果你正在處理TypeDiscriminator = 1的對象,你將Endpoint1放在Leader中,將Endpoint2放在Follower中。同樣,如果您正在處理TypeDiscriminator = 2的對象,則將Endpoint1放在Person中,將Endpoint2放在Location中。

題:

如何解決錯誤3023以允許這些關聯發生?

要么

您如何在我上面描述的ADO實體框架中創建關聯類型?

參考文獻:

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. 為派生類型之間的每個關聯創建一個單獨的列,並使每個列都可以為
  2. 在每個新列和主鍵表之間創建一個外鍵。
  3. 將實體模型中的每個關聯映射到特定的唯一列和外鍵,以便每個列和外鍵僅使用一次。

問題

這是一個非常不合適的解決方案,因為它會爆炸出您需要的列數。

  • 更多列 - 為派生類型之間的每個關聯添加列會導致列數爆炸。
  • 空列在TPH的情況下,這意味著您的表中會有很多列。
  • SQL JOIN - 從TPH切換到TPT以避免空列的數量導致EF必須使用必須極其頻繁發生的JOIN(幾乎每次處理任何派生類型時)。
  • 重構如果將來添加派生類型,則不僅需要更新實體模型(* .edmx)及其映射,還必須通過添加其他列來更改數據庫模式!

對於上面的鏈接/節點示例,生成的數據庫模式如下所示:

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在兩個表之間創建0..1到多個關係,使其為1到多個

在這種情況下,它可能是一種可能的解決方案。

最好的祝福!薩拉赫丁



許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因