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 the 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 I will deal with is the relationship between publishers and titles identified by the presence of the scalar property pub_id being present in both entities.





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

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

3. You will now see the Add Association dialog.

4. For this association, the only thing that needs changing is the name of the Navigation Property. Change it 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).

5. Hit OK. The diagram will be refreshed to include the newly created association.

6. You now need to edit the mappings associated with the newly created association. Highlight the association on the diagram. Then, right click it and select Table Mapping. This will display the Mapping Details pane.

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

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.

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

10. Once the mapping is complete, you can now build the project using Build -> Build Solution.

11. This should result in the following error which is included here, since I (the author) found it misleading.





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.

12. The solution is simple!

Delete the scalar property titles.pub_id. Its only purpose is 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.

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

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

15. The project should now build fine.

You will need to repeat these steps for each association, until you have a completed Entity Data Model like this -





NOTE: It is worthwhile building after each association, since the error messages can be a little confusing.