none
Multiplicity is not valid because the Dependent Role properties are not the key properties? RRS feed

  • Question

  • Ever since EF 4, there is a nice option in the edmx model designer saying:

    "Include Foreign Key columns in the model".


    When this option is checked, the designer seems to be much more strict about the model;
    When you create a FK, and want the relation mutiplicity to be (1) -- (1) instead of (1) -- (*), the designer says:

    "Multiplicity is not valid in Role X in relationship XY Because the Dependent Role properties are not the key properties."

    The designer actually wants you to change your FK to be the PK;
    One can argue that makes sense, because the edmx designer wants to keep the designer strictness in line with the db strictness.
    (If you make the FK = the PK, the (1) -- (1) multiplicity is enforced at the database level)


    I have 2 questions on this:
    1. Why should it be the PK? It would be sufficient to have a FK with a Unique Constraint on it.
       Many databases are not designed to use the PK as FK.
    2. How do you setup a 0..1 -- 0..1 relationship in this approach?
       It is of course not possible to use the PK in that case.


    Furthermore, it 's really confusing that the edmx designer is more strict when you check the "Include Foreign Key columns in the model".
    It is the same database and the same conceptual model with or without the option. The rules should be the same.


    Thanks for your time,
    Koen

     

     

     

    Friday, November 25, 2011 1:23 PM

All replies

  • Hi Koen,

    I think you can try Code First, here is the link that you can refer: http://weblogs.asp.net/manavi/archive/2011/04/14/associations-in-ef-4-1-code-first-part-3-shared-primary-key-associations.aspx

    http://weblogs.asp.net/manavi/archive/2011/05/01/associations-in-ef-4-1-code-first-part-5-one-to-one-foreign-key-associations.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, November 29, 2011 9:00 AM
    Moderator
  • Hi,

    Thanks for your response,
    But I'm not looking to use code-first.

    I want to create my model, starting from the database.
    But in the edmx designer, I want to check "Include Foreign Key columns in the model".


    Then I want to model my relation as (0..1) -- (0..1).
    Or is that just not possible with Entity Framework? 

     

    Regards,
    Koen 

    Tuesday, November 29, 2011 9:03 AM
  • Hi Koen,

    Thanks for your feedback.

    Would you please share your DataTable Schema(T-SQL) with us.

    --------------

    One-to-One Primary Key Associations, means two related tables share the same primary key values. The primary key of one table is also a foreign key of the other. 

    --------------

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, November 29, 2011 9:10 AM
    Moderator
  • Hi,

    Sure, this is an example schema, which creates a Person and a Car table.
    There is a FK on Car, PersonId. 


    Remember I'm looking to create a Person (0..1) -- (0..1) Car assocation here.

    Steps to reproduce:
    1. Create db
    2. Visual Studio, Add new Item -> ADO.Net Entity Data Model
    3. Generate from database
    4. Leave the "Include Foreign Keys in the model" option checked
    5. Finish

    EF creates the relations ship as a (0..1) -- (*) relation.
    When you try to change this relation's multiplicity to (0..1) -- (0..1), you get the error;

    Error	1	Error 113: Multiplicity is not valid in Role 'Car' in relationship 'FK_Car_Person'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be *.	
    

    Of course, it makes no sense to make "PersonId" the Primary Key on Car, since I want to be able to save Cars without a Person.


    Note that everything works just fine, when you uncheck the option "Include Foreign Keys in the model".
    That makes no sense to me; It is the same database and the same conceptual model with or without the option. 

     

    Thanks for your time,
    Koen 

     

    CREATE TABLE [dbo].[Person](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [nvarchar](50) NOT NULL,
     CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
    (
    	[Id] 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
    
    
    
    CREATE TABLE [dbo].[Car](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Brand] [nvarchar](50) NOT NULL,
    	[PersonId] [int] NULL,
     CONSTRAINT [PK_Car] PRIMARY KEY CLUSTERED 
    (
    	[Id] 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
    
    ALTER TABLE [dbo].[Car]  WITH CHECK ADD  CONSTRAINT [FK_Car_Person] FOREIGN KEY([PersonId])
    REFERENCES [dbo].[Person] ([Id])
    GO
    
    ALTER TABLE [dbo].[Car] CHECK CONSTRAINT [FK_Car_Person]
    GO
    


    Tuesday, November 29, 2011 10:34 AM
  • Hi,

    I am writing to check the status of the issue on your side.
    Were you able to reproduce the problem?


    Thanks for your time,
    Koen 

    Monday, December 5, 2011 9:34 AM
  • Hi Koen,

    Yes, I can reproduce your problem, you should modify the .edmx to fix your problem, here is my steps:

    1.Click the Association-->Property, change End2 Multiplicity to 0..1

    2.Delete the PersonId property from Car entity on the edmx designer.

    3.Right Click the edmx file --->open with XML Editor

    4.Changes in CSDL:

    <Association Name="FK_Car_Person">
              <End Role="Person" Type="ConsoleApplication58.TestContextModel.Person" Multiplicity="0..1" />
              <End Role="Car" Type="ConsoleApplication58.TestContextModel.Car" Multiplicity="0..1" />
            </Association>
    

    Delete the Constraint.

    5.Changes in C-S:

    Append AssociatinSetMaping after "EntitySetMapping":

     <AssociationSetMapping Name="FK_Car_Person" TypeName="ConsoleApplication58.TestContextModel.FK_Car_Person" StoreEntitySet="Car">
                <EndProperty Name="Person">
                  <ScalarProperty Name="Id" ColumnName="PersonId" />
                </EndProperty>
                <EndProperty Name="Car">
                  <ScalarProperty Name="Id" ColumnName="Id" />
                </EndProperty>
                <Condition ColumnName="PersonId" IsNull="false" />
              </AssociationSetMapping>
    

    6.Save.

    Here is the whole edmx:

    <?xml version="1.0" encoding="utf-8"?>
    <edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
      <!-- EF Runtime content -->
      <edmx:Runtime>
        <!-- SSDL content -->
        <edmx:StorageModels>
          <Schema Namespace="ConsoleApplication58.TestContextModel.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/2009/02/edm/ssdl">
            <EntityContainer Name="ConsoleApplication58TestContextModelStoreContainer">
              <EntitySet Name="Car" EntityType="ConsoleApplication58.TestContextModel.Store.Car" store:Type="Tables" Schema="dbo" />
              <EntitySet Name="Person" EntityType="ConsoleApplication58.TestContextModel.Store.Person" store:Type="Tables" Schema="dbo" />
              <AssociationSet Name="FK_Car_Person" Association="ConsoleApplication58.TestContextModel.Store.FK_Car_Person">
                <End Role="Person" EntitySet="Person" />
                <End Role="Car" EntitySet="Car" />
              </AssociationSet>
            </EntityContainer>
            <EntityType Name="Car">
              <Key>
                <PropertyRef Name="Id" />
              </Key>
              <Property Name="Id" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
              <Property Name="Brand" Type="nvarchar" Nullable="false" MaxLength="50" />
              <Property Name="PersonId" Type="int" />
            </EntityType>
            <EntityType Name="Person">
              <Key>
                <PropertyRef Name="Id" />
              </Key>
              <Property Name="Id" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
              <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50" />
            </EntityType>
            <Association Name="FK_Car_Person">
              <End Role="Person" Type="ConsoleApplication58.TestContextModel.Store.Person" Multiplicity="0..1" />
              <End Role="Car" Type="ConsoleApplication58.TestContextModel.Store.Car" Multiplicity="*" />
              <ReferentialConstraint>
                <Principal Role="Person">
                  <PropertyRef Name="Id" />
                </Principal>
                <Dependent Role="Car">
                  <PropertyRef Name="PersonId" />
                </Dependent>
              </ReferentialConstraint>
            </Association>
          </Schema>
        </edmx:StorageModels>
        <!-- CSDL content -->
        <edmx:ConceptualModels>
          <Schema Namespace="ConsoleApplication58.TestContextModel" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
            <EntityContainer Name="Entities3" annotation:LazyLoadingEnabled="true">
              <EntitySet Name="Car" EntityType="ConsoleApplication58.TestContextModel.Car" />
              <EntitySet Name="Person" EntityType="ConsoleApplication58.TestContextModel.Person" />
              <AssociationSet Name="FK_Car_Person" Association="ConsoleApplication58.TestContextModel.FK_Car_Person">
                <End Role="Person" EntitySet="Person" />
                <End Role="Car" EntitySet="Car" />
              </AssociationSet>
            </EntityContainer>
            <EntityType Name="Car">
              <Key>
                <PropertyRef Name="Id" />
              </Key>
              <Property Name="Id" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
              <Property Name="Brand" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" />
              <NavigationProperty Name="Person" Relationship="ConsoleApplication58.TestContextModel.FK_Car_Person" FromRole="Car" ToRole="Person" />
            </EntityType>
            <EntityType Name="Person">
              <Key>
                <PropertyRef Name="Id" />
              </Key>
              <Property Name="Id" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
              <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" />
              <NavigationProperty Name="Car" Relationship="ConsoleApplication58.TestContextModel.FK_Car_Person" FromRole="Person" ToRole="Car" />
            </EntityType>
            <Association Name="FK_Car_Person">
              <End Role="Person" Type="ConsoleApplication58.TestContextModel.Person" Multiplicity="0..1" />
              <End Role="Car" Type="ConsoleApplication58.TestContextModel.Car" Multiplicity="0..1" />
            </Association>
          </Schema>
        </edmx:ConceptualModels>
        <!-- C-S mapping content -->
        <edmx:Mappings>
          <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
            <EntityContainerMapping StorageEntityContainer="ConsoleApplication58TestContextModelStoreContainer" CdmEntityContainer="Entities3">
              <EntitySetMapping Name="Car"><EntityTypeMapping TypeName="ConsoleApplication58.TestContextModel.Car"><MappingFragment StoreEntitySet="Car">
                <ScalarProperty Name="Id" ColumnName="Id" />
                <ScalarProperty Name="Brand" ColumnName="Brand" />
              </MappingFragment></EntityTypeMapping></EntitySetMapping>
              <EntitySetMapping Name="Person">
                <EntityTypeMapping TypeName="ConsoleApplication58.TestContextModel.Person"><MappingFragment StoreEntitySet="Person">
                <ScalarProperty Name="Id" ColumnName="Id" />
                <ScalarProperty Name="Name" ColumnName="Name" />
              </MappingFragment>
              </EntityTypeMapping>
              </EntitySetMapping>
              <AssociationSetMapping Name="FK_Car_Person" TypeName="ConsoleApplication58.TestContextModel.FK_Car_Person" StoreEntitySet="Car">
                <EndProperty Name="Person">
                  <ScalarProperty Name="Id" ColumnName="PersonId" />
                </EndProperty>
                <EndProperty Name="Car">
                  <ScalarProperty Name="Id" ColumnName="Id" />
                </EndProperty>
                <Condition ColumnName="PersonId" IsNull="false" />
              </AssociationSetMapping>
            </EntityContainerMapping>
          </Mapping>
        </edmx:Mappings>
      </edmx:Runtime>
      <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
      <Designer xmlns="http://schemas.microsoft.com/ado/2008/10/edmx">
        <Connection>
          <DesignerInfoPropertySet>
            <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
          </DesignerInfoPropertySet>
        </Connection>
        <Options>
          <DesignerInfoPropertySet>
            <DesignerProperty Name="ValidateOnBuild" Value="true" />
            <DesignerProperty Name="EnablePluralization" Value="False" />
            <DesignerProperty Name="IncludeForeignKeysInModel" Value="True" />
          </DesignerInfoPropertySet>
        </Options>
        <!-- Diagram content (shape and connector positions) -->
        <Diagrams>
          <Diagram Name="Model1">
            <EntityTypeShape EntityType="ConsoleApplication58.TestContextModel.Car" Width="1.5" PointX="3.125" PointY="0.875" Height="1.4430468571186066" IsExpanded="true" />
            <EntityTypeShape EntityType="ConsoleApplication58.TestContextModel.Person" Width="1.5" PointX="0.75" PointY="0.875" Height="1.4430468571186061" IsExpanded="true" />
            <AssociationConnector Association="ConsoleApplication58.TestContextModel.FK_Car_Person" ManuallyRouted="false">
              <ConnectorPoint PointX="2.25" PointY="1.5965234285593031" />
              <ConnectorPoint PointX="3.125" PointY="1.5965234285593031" />
            </AssociationConnector>
          </Diagram>
        </Diagrams>
      </Designer>
    </edmx:Edmx>
    

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, December 6, 2011 2:19 AM
    Moderator
  • Hi,

    Thanks for your answer;

    However
    - I think it's pretty poor that i would have to edit my edmx manually for every (0..1) -- (0..1) relationship i create, is this the only way?
    - Would i have to manually edit the edmx after every "update model from database" that I do afterwards?


    Thanks for your time,
    Koen 
    Tuesday, December 6, 2011 1:01 PM
  • Hi Koen,

    I think this is the only way.  Based on my testing, we don't need wo mannually edit the edmx after "update model from database".

    BTW, you can use Model-First to create two entities(0..1 to 0..1)-->generate the "T-sql", you will find the trick for index.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, December 9, 2011 8:03 AM
    Moderator