8.7.5.Manually creating EDM Associations (FKs) for the Sybase pubs2 database

The following steps will detail what is required to manually create "Associations" in your Entity Data Model.

You will need to determine where these associations exist and their multiplicity (one-to-one, one-to-many, etc.) before commencing with the following steps.

Note: These steps will need to be repeated for each association.

  1. The first Association will deal with is the relationship between publishers and titles, identified by the presence of the scalar property pub_id in both entities. This is a one-to-many relationship, as a Customer may have any number of Invoices.

    Figure8.351.Association

    Association

    This is a one to many relationship insomuch that publishers may have zero or many titles.

  2. To add the Association, right click on the publishers entity then Add -> Association.

    Figure8.352.add the Association

    add the Association

  3. You will now see the Add Association dialog.

    Figure8.353.Add Association dialog

    Add Association dialog

  4. For this association the only thing that needs changing is the name of the Navigation Property from publishers to publisher on the publishers end of the association.

    This more correctly reflects the multiplicity of the association such that a publisher is associated with zero or many titles (plural).

    Figure8.354.Navigation Property name

    Navigation Property name

  5. Once you then hit OK the diagram is refreshed to include the newly created association.

    Figure8.355.diagram

    diagram

  6. You now need to edit the mappings associated with the newly created association, so right-click the association on the diagram, and select Table Mapping to display the Mapping Details pane.

    Figure8.356.Table Mapping

    Table Mapping

  7. Click that line reading <Add a Table or View> to reveal a drop down list of all entities.

    Figure8.357.Add a Table or View

    Add a Table or View

  8. Here you need to select the entity on the right/far side of the association (the entity where the foreign key exists). In this example, it is the titles entity.

    Figure8.358.Entity

    Entity

  9. The Mapping Details pane now refreshes to display both ends of the association, requiring that you provide relevant target store data types in the Column column for the key fields, as depicted here.

    Figure8.359.Mapping Details

    Mapping Details

  10. Once the mapping is complete, you can build the project using Build -> Build Solution. NOTE: It is worthwhile building as each association is made, since the error messages can be a little confusing.

    Figure8.360.Build the project

    Build the project

  11. This should result in the following error:

    Figure8.361.Error

    Error

    It seems that this error is attempting to say that there are two mappings which map to the same target source column.

    In this case it is the target column titles.pub_id.

    That is, the scalar property pub_id and the Navigation Property publishers in the Invoice entity, both map to the Sybase table column titles.pub_id - which is not supported

    Figure8.362.Error

    Error

  12. The solution is simple! Simply delete the scalar property titles.pub_id since its purpose is only to hold data representing a relationship/association (it is a Foreign Key) which has already been represented by the newly created association and resulting Navigation Property publishers.

    Right click on titles.pub_id then Delete.

    Figure8.363.delete the mapping

    delete the mapping

  13. The model diagram will refresh to reflect this change.

    Figure8.364.Model Diagram

    Model Diagram

  14. Build the project, again, using Build -> Build Solution.

    Figure8.365.Build project

    Build project

  15. The project should now be fine.

    Figure8.366.Build Project

    Build Project

You will need to repeat these steps for each association until you have a completed Entity Data Model.

Figure8.367.Entity Data Model

Entity Data Model