none
Trouble with composite primary keys and foreign keys RRS feed

  • Question

  • I'm having trouble creating associations between entities where there is a foreign key that is part of a composite primary key. Here are my three entities:

    Payee   //PayeeCode and ReportingType are the entity key
    {
    PayeeCode PK and FK to Jurisdiction
    ReportingType PK and FK to Reporting
    }

    Reporting
    {
    ReportingType PK
    }

    Jurisdiction
    {
    PayeeCode PK
    }

    I was able to create the association between the Jurisdiction entity and the Payee entity, and that's without removing the PayeeCode scalar property from the Payee entity. But I'm getting mapping fragment errors if I try to create an association between Payee and Reporting while leaving the ReportingType scalar property on the Payee entity. And if I try to remove the ReportingType scalar property from the Payee entity, it gives me an error about not being able to remove the entity key property.

    This is a very common database scenario and seems like it should work without such headaches. Any suggestions?

    Brad

    Tuesday, September 13, 2011 9:34 PM

Answers

  • Error 1 Error 3021: Problem in Mapping Fragment starting at line 1876: Each of the following columns in table Payee is mapped to multiple conceptual side properties:
    Payee.Reporting is mapped to <ReportingTypePayee.ReportingType.Code, ReportingTypePayee.Payee.Reporting>

     C:\Code\VisualStudio\projects\AccuTax_Project\DataLayer\TAXTEST_Model.edmx 1877 11 DataLayer

    Hello again,

    Thank you for your detailed reply.

    Could you please check what's structure of your edmx file in your side? It looks this issue was duplicated mapping issue. We can add the referential constraint. Please check this blog for more detailed information. http://blogs.msdn.com/b/adonet/archive/2008/12/05/table-splitting-mapping-multiple-entity-types-to-the-same-table.aspx

    Please feel free to let us know if you have any update.

    Thanks,


    Larcolais Gong[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.

    • Marked as answer by BradInDallas Friday, September 16, 2011 1:31 PM
    Friday, September 16, 2011 8:18 AM
  • Bingo! That fixed it!


    Here's the edmx code that worked:

    <Association Name="ReportingTypePayee">
    <End Type="TAXTESTModel.ReportingType" Role="ReportingType" Multiplicity="1" />
    <End Type="TAXTESTModel.Payee" Role="Payee" Multiplicity="*" />
    <ReferentialConstraint>
    <Principal Role="ReportingType">
    <PropertyRef Name="Code"/>
    </Principal>
    <Dependent Role="Payee">
    <PropertyRef Name="Reporting"/>
    </Dependent>
    </ReferentialConstraint>
    </Association>

     

    Thanks for the link, Larcolois!

    Brad

    • Marked as answer by BradInDallas Friday, September 16, 2011 1:31 PM
    Friday, September 16, 2011 1:30 PM

All replies

  • Hello,

    From your description, it'a a little hard for us to provide exact response to you. Could you please provide more error information and code snippets to us?

    Please feel free to let us know if you have any finding.

    Thanks,


    Larcolais Gong[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.

    Thursday, September 15, 2011 3:53 AM
  • Hi Larcolais, thanks for your reply.

    If I try to map a 1:* association from my ReportingType table to my Payee table, I get this error:

    Error 1 Error 3021: Problem in Mapping Fragment starting at line 1876: Each of the following columns in table Payee is mapped to multiple conceptual side properties:
    Payee.Reporting is mapped to <ReportingTypePayee.ReportingType.Code, ReportingTypePayee.Payee.Reporting>

     C:\Code\VisualStudio\projects\AccuTax_Project\DataLayer\TAXTEST_Model.edmx 1877 11 DataLayer

    Here is a screenshot of the association table mapping when I first create it. Notice it doesn't automatically map the Reporting scalar property from my Payee table.

     

    If I map the Reporting property it looks like this, but I get the error I listed above when I build.

    If I try to remove the Reporting scalar property from my Payee entity (since 3.5 doesn't support a property to be used in both an association and a scalar property at the same time as I understand it) I get this error:

    Error 1 Error 113: Multiplicity is not valid in Role 'Payee' in relationship 'FK_Payee_Jurisdiction'. Because the Dependent Role refers to the key properties, the upper bound of the multiplicity of the Dependent Role must be 1. C:\Code\VisualStudio\projects\AccuTax_Project\DataLayer\TAXTEST_Model.edmx 1300 11 DataLayer

     

    This is one example, but I've got several tables that are 'orphaned' because of this same issue. Any ideas?

    Brad

    Thursday, September 15, 2011 1:35 PM
  • Error 1 Error 3021: Problem in Mapping Fragment starting at line 1876: Each of the following columns in table Payee is mapped to multiple conceptual side properties:
    Payee.Reporting is mapped to <ReportingTypePayee.ReportingType.Code, ReportingTypePayee.Payee.Reporting>

     C:\Code\VisualStudio\projects\AccuTax_Project\DataLayer\TAXTEST_Model.edmx 1877 11 DataLayer

    Hello again,

    Thank you for your detailed reply.

    Could you please check what's structure of your edmx file in your side? It looks this issue was duplicated mapping issue. We can add the referential constraint. Please check this blog for more detailed information. http://blogs.msdn.com/b/adonet/archive/2008/12/05/table-splitting-mapping-multiple-entity-types-to-the-same-table.aspx

    Please feel free to let us know if you have any update.

    Thanks,


    Larcolais Gong[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.

    • Marked as answer by BradInDallas Friday, September 16, 2011 1:31 PM
    Friday, September 16, 2011 8:18 AM
  • Bingo! That fixed it!


    Here's the edmx code that worked:

    <Association Name="ReportingTypePayee">
    <End Type="TAXTESTModel.ReportingType" Role="ReportingType" Multiplicity="1" />
    <End Type="TAXTESTModel.Payee" Role="Payee" Multiplicity="*" />
    <ReferentialConstraint>
    <Principal Role="ReportingType">
    <PropertyRef Name="Code"/>
    </Principal>
    <Dependent Role="Payee">
    <PropertyRef Name="Reporting"/>
    </Dependent>
    </ReferentialConstraint>
    </Association>

     

    Thanks for the link, Larcolois!

    Brad

    • Marked as answer by BradInDallas Friday, September 16, 2011 1:31 PM
    Friday, September 16, 2011 1:30 PM
  • Cheers, Brad. Glad to hear it hits your concern. In addition, like it mentioned, I think ReferentialContraints was fixed in the next release. At least, EF4.1 was in order.

    Hope this helps.

    Thanks,


    Larcolais Gong[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, September 16, 2011 1:47 PM