locked
Table per hierarchy and navigation properties RRS feed

  • Question

  • Hi all,

    I have two tables: User and Data. An User is associated to a collection of Data, so Data has a foreign key to User.

    I want to map a table (User) to two entities (User and UserWithData) using a column discriminator.

    The derived type (UserWithData) has no specific properties but should have a collection of Data and the "normal" User should not.
    So what I want to do is to transfer the navigation property from User to CustomUser. How can I achieve that?

    When I try to do so, I have an error saying that the foreign key of Data is mapped to multiple conceptual properties.

    Thanks for your reply.


    Wednesday, April 1, 2009 4:30 PM

Answers



  • The problem appears each time I create such a relation

    Each time, I solve the problem by deleting the changing (or deleting) a mapping on the association between UserWithData and Data and then restoring its correct value.


    • Marked as answer by GaelGF Tuesday, April 7, 2009 1:20 PM
    Tuesday, April 7, 2009 1:20 PM

All replies

  • Hi Gael,

    Could you post the edmx file here?

    thanks


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, April 1, 2009 10:12 PM
  • Assume your tables as simple as:
    Table1 User(Id, Disc) - Id is PK of int type, Disc is discriminitor column.
    Table2 Data(Id, UserId) - Id is PK of int type, UserId is FK references to Id in User table

    For association between derived types using TPH,  we need to define a condition for the AssociationSetMapping to be established. In this case we should set the condition to be whether column UserId is null.

    I have the following set of schemas able to be loaded into Item collections for your scenario, please try it out by replacing the corresponding csdl/ssdl/msl sections in your edmx file.

    -----------CSDL section----------------------------
    <?xml version="1.0" encoding="utf-8"?>
    <Schema Namespace="ABDT_TPH" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
      <EntityContainer Name="ABDT_TPHContext">
        <EntitySet Name="Users" EntityType="ABDT_TPH.User" />
        <EntitySet Name="Data" EntityType="ABDT_TPH.Data" />
        <AssociationSet Name="FK_Users_Data" Association="ABDT_TPH.FK_User_Data">
     <End Role="UserWithData" EntitySet="Users" />
     <End Role="Data" EntitySet="Data" />
        </AssociationSet>
      </EntityContainer>
      <EntityType Name="User">
        <Key>
          <PropertyRef Name="Id" />
        </Key>
        <Property Name="Id" Type="Int32" Nullable="false" />
      </EntityType>
      <EntityType Name="UserWithData" BaseType="ABDT_TPH.User">
        <NavigationProperty Name="FK_UserWithData" Relationship="ABDT_TPH.FK_User_Data" FromRole="UserWithData" ToRole="Data" />
      </EntityType>
      <EntityType Name="Data">
        <Key>
          <PropertyRef Name="Id" />
        </Key>
        <Property Name="Id" Type="Int32" Nullable="false" />
      </EntityType>
      <Association Name="FK_User_Data">
     <End Role="UserWithData" Type="ABDT_TPH.UserWithData" Multiplicity="1" />
     <End Role="Data" Type="ABDT_TPH.Data" Multiplicity="*" />
      </Association>
    </Schema>

    ---------------------------ssdl section-----------------------------------------------------
    <?xml version="1.0" encoding="utf-8"?>
    <Schema Namespace="ABDT_TPH.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="ABDTTPHStoreContainer">
        <EntitySet Name="Users" EntityType="ABDT_TPH.Store.User" store:Type="Tables" Schema="dbo" />
        <EntitySet Name="Data" EntityType="ABDT_TPH.Store.Data" store:Type="Tables" Schema="dbo" />
      </EntityContainer>
      <EntityType Name="User">
        <Key>
          <PropertyRef Name="Id" />
        </Key>
        <Property Name="Id" Type="int" Nullable="false" />
        <Property Name="Disc" Type="nvarchar" MaxLength="1024" />
      </EntityType>
      <EntityType Name="Data">
        <Key>
          <PropertyRef Name="Id" />
        </Key>
        <Property Name="Id" Type="int" Nullable="false" />
        <Property Name="UserId" Type="int" />
      </EntityType>
    </Schema>

    ---------------------- msl section--------------------------------------------------------
    <?xml version="1.0" encoding="utf-8"?>
    <Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
      <EntityContainerMapping StorageEntityContainer="ABDTTPHStoreContainer" CdmEntityContainer="ABDT_TPHContext">
        <EntitySetMapping Name="Users" >
           <EntityTypeMapping TypeName="ABDT_TPH.User" >
      <MappingFragment StoreEntitySet="Users" >
       <ScalarProperty Name="Id" ColumnName="Id" />
             <Condition ColumnName="Disc" Value="A" />
      </MappinFragment>
     </EntityTypeMapping>
     <EntityTypeMapping TypeName="ABDT_TPH.UserWithData" >
      <MappingFragment StoreEntitySet="Users" >
       <ScalarProperty Name="Id" ColumnName="Id" />
             <Condition ColumnName="Disc" Value="B" />
      </MappinFragment>
     </EntityTypeMapping>
        </EntitySetMapping>
        <EntitySetMapping Name="Data" StoreEntitySet="Data" TypeName="ABDT_TPH.Data">
          <ScalarProperty Name="Id" ColumnName="Id" /> 
        </EntitySetMapping>
      </EntityContainerMapping>
      <AssociationSetMapping Name="FK_Users_Data" TypeName="ABDT_TPH.FK_User_Data" StoreEntitySet="Data">
          <EndProperty Name="UserWithData">
            <ScalarProperty Name="Id" ColumnName="UserId" />
          </EndProperty>
          <EndProperty Name="Data">
            <ScalarProperty Name="Id" ColumnName="Id" />
          </EndProperty>
          <Condition ColumnName="UserId" IsNull="false" />
        </AssociationSetMapping>
    </Mapping>



    jichang
    • Proposed as answer by Jing Chang - MSFT Thursday, April 2, 2009 11:23 AM
    • Unproposed as answer by GaelGF Tuesday, April 7, 2009 1:17 PM
    Thursday, April 2, 2009 11:23 AM
  • Thanks for your reply.

    I fact my database Schema is just a litlle bit complicated.

    my tables are:

    Table1 User(Id, Disc) - Id is PK of int type, Disc is discriminitor column.
    Table2 Data(UserId, Date, Value) - UserId is FK references to Id in User table and my PK is composed of UserId and Date

    I get the following error:

    Problem in Mapping Fragmlent starting at line 94: Each of the following columns in table Data is mapped to multiple conceptual side properties:
    Data.UserID is mapped to <UserWithDataData.USerWithData.Id, UserWithData.Data.UserID>

    I've just solved the problem when posting this message. I solved the problem in the designer by deleting the mapping for Date on the association between UserWithData and Data, compiling (with errors), and then create the association again.

    Here is my final working edmx file. The difference between the non working one is in bold. I don't really understand why the designer did not add those lines at first time

    Maybe this problem is similar to the one of the thread http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/28cd9cfb-7cba-49e5-a394-e3e953d95c75/

    ---------EDITED:

    Bold did not show: the difference is those lines in the CSDL content

    <ReferentialConstraint>
                <Principal Role="UserWithData">
                  <PropertyRef Name="Id" /></Principal>
                <Dependent Role="Data">
                  <PropertyRef Name="UserId" /></Dependent>
    </ReferentialConstraint>




    <?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="Test1Model.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
    
            <EntityContainer Name="Test1ModelStoreContainer">
    
              <EntitySet Name="Data" EntityType="Test1Model.Store.Data" store:Type="Tables" Schema="dbo" />
    
              <EntitySet Name="User" EntityType="Test1Model.Store.User" store:Type="Tables" Schema="dbo" />
    
              <AssociationSet Name="FK_TotalReturnHistoricalPrices_Asset" Association="Test1Model.Store.FK_TotalReturnHistoricalPrices_Asset">
    
                <End Role="User" EntitySet="User" />
    
                <End Role="Data" EntitySet="Data" />
    
              </AssociationSet>
    
            </EntityContainer>
    
            <EntityType Name="Data">
    
              <Key>
    
                <PropertyRef Name="UserId" />
    
                <PropertyRef Name="Date" />
    
              </Key>
    
              <Property Name="UserId" Type="uniqueidentifier" Nullable="false" />
    
              <Property Name="Date" Type="datetime" Nullable="false" />
    
              <Property Name="Value" Type="real" Nullable="false" />
    
            </EntityType>
    
            <EntityType Name="User">
    
              <Key>
    
                <PropertyRef Name="Id" />
    
              </Key>
    
              <Property Name="Id" Type="uniqueidentifier" Nullable="false" />
    
              <Property Name="Disc" Type="nchar" MaxLength="20" />
    
            </EntityType>
    
            <Association Name="FK_TotalReturnHistoricalPrices_Asset">
    
              <End Role="User" Type="Test1Model.Store.User" Multiplicity="1" />
    
              <End Role="Data" Type="Test1Model.Store.Data" Multiplicity="*" />
    
              <ReferentialConstraint>
    
                <Principal Role="User">
    
                  <PropertyRef Name="Id" />
    
                </Principal>
    
                <Dependent Role="Data">
    
                  <PropertyRef Name="UserId" />
    
                </Dependent>
    
              </ReferentialConstraint>
    
            </Association>
    
          </Schema>
    
        </edmx:StorageModels>
    
        <!-- CSDL content -->
    
        <edmx:ConceptualModels>
    
          <Schema Namespace="Test1Model" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
    
            <EntityContainer Name="Test1Entities">
    
              <EntitySet Name="Data" EntityType="Test1Model.Data" />
    
              <EntitySet Name="Users" EntityType="Test1Model.User" />
    
              <AssociationSet Name="UserWithDataData" Association="Test1Model.UserWithDataData">
    
                <End Role="UserWithData" EntitySet="Users" />
    
                <End Role="Data" EntitySet="Data" /></AssociationSet></EntityContainer>
    
            <EntityType Name="Data">
    
              <Key>
    
                <PropertyRef Name="UserId" />
    
                <PropertyRef Name="Date" />
    
              </Key>
    
              <Property Name="UserId" Type="Guid" Nullable="false" />
    
              <Property Name="Date" Type="DateTime" Nullable="false" />
    
              <Property Name="Value" Type="Single" Nullable="false" />
    
              <NavigationProperty Name="UserWithData" Relationship="Test1Model.UserWithDataData" FromRole="Data" ToRole="UserWithData" /></EntityType>
    
            <EntityType Name="User">
    
              <Key>
    
                <PropertyRef Name="Id" />
    
              </Key>
    
              <Property Name="Id" Type="Guid" Nullable="false" />
    
            </EntityType>
    
            <EntityType Name="UserWithData" BaseType="Test1Model.User" >
    
              <NavigationProperty Name="Data" Relationship="Test1Model.UserWithDataData" FromRole="UserWithData" ToRole="Data" /></EntityType>
    
            <Association Name="UserWithDataData">
    
              <End Type="Test1Model.UserWithData" Role="UserWithData" Multiplicity="1" />
    
              <End Type="Test1Model.Data" Role="Data" Multiplicity="*" />
    
             <strong> <ReferentialConstraint>
    
                <Principal Role="UserWithData">
    
                  <PropertyRef Name="Id" /></Principal>
    
                <Dependent Role="Data">
    
                  <PropertyRef Name="UserId" /></Dependent></ReferentialConstraint></strong></Association></Schema>
    
        </edmx:ConceptualModels>
    
        <!-- C-S mapping content -->
    
        <edmx:Mappings>
    
          <Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
    
            <EntityContainerMapping StorageEntityContainer="Test1ModelStoreContainer" CdmEntityContainer="Test1Entities">
    
              <EntitySetMapping Name="Users">
    
                <EntityTypeMapping TypeName="IsTypeOf(Test1Model.UserWithData)">
    
                  <MappingFragment StoreEntitySet="User" >
    
                    <ScalarProperty Name="Id" ColumnName="Id" />
    
                    <Condition ColumnName="Disc" Value="B" /></MappingFragment></EntityTypeMapping>
    
                <EntityTypeMapping TypeName="Test1Model.User">
    
                  <MappingFragment StoreEntitySet="User">
    
                    <ScalarProperty Name="Id" ColumnName="Id" />
    
                    <Condition ColumnName="Disc" Value="A" /></MappingFragment></EntityTypeMapping></EntitySetMapping>
    
              <EntitySetMapping Name="Data">
    
                <EntityTypeMapping TypeName="IsTypeOf(Test1Model.Data)">
    
                  <MappingFragment StoreEntitySet="Data">
    
                    <ScalarProperty Name="Value" ColumnName="Value" />
    
                    <ScalarProperty Name="Date" ColumnName="Date" />
    
                    <ScalarProperty Name="UserId" ColumnName="UserId" /></MappingFragment></EntityTypeMapping></EntitySetMapping>
    
              <AssociationSetMapping Name="UserWithDataData" TypeName="Test1Model.UserWithDataData" StoreEntitySet="Data">
    
                <EndProperty Name="UserWithData">
    
                  <ScalarProperty Name="Id" ColumnName="UserId" /></EndProperty>
    
                <EndProperty Name="Data">
    
                  <ScalarProperty Name="Date" ColumnName="Date" />
    
                  <ScalarProperty Name="UserId" ColumnName="UserId" /></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="Model1">
    
            <EntityTypeShape EntityType="Test1Model.Data" Width="1.5" PointX="3.125" PointY="3.75" Height="1.7879850260416665" IsExpanded="true" />
    
            <EntityTypeShape EntityType="Test1Model.User" Width="1.5" PointX="0.75" PointY="1" Height="1.211080729166667" IsExpanded="true" />
    
            <EntityTypeShape EntityType="Test1Model.UserWithData" Width="1.5" PointX="0.75" PointY="3.875" Height="1.018779296875" />
    
            <InheritanceConnector EntityType="Test1Model.UserWithData">
    
              <ConnectorPoint PointX="1.5" PointY="2.211080729166667" />
    
              <ConnectorPoint PointX="1.5" PointY="3.875" /></InheritanceConnector>
    
            <AssociationConnector Association="Test1Model.UserWithDataData">
    
              <ConnectorPoint PointX="2.25" PointY="4.3843896484375" />
    
              <ConnectorPoint PointX="3.125" PointY="4.3843896484375" /></AssociationConnector></Diagram></edmx:Diagrams>
    
      </edmx:Designer>
    
    </edmx:Edmx>
    
    


    Thursday, April 2, 2009 5:22 PM


  • The problem appears each time I create such a relation

    Each time, I solve the problem by deleting the changing (or deleting) a mapping on the association between UserWithData and Data and then restoring its correct value.


    • Marked as answer by GaelGF Tuesday, April 7, 2009 1:20 PM
    Tuesday, April 7, 2009 1:20 PM