8.3.5. Manually creating EDM Associations (FKs) for the Progress isports 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.
-
The first Association will deal with is the relationship between Customer and Invoice, identified by the presence of the scalar property Cust_Num in both entities. This is a one-to-many relationship, as a Customer may have any number of Invoices.
Figure 8.95. Association
-
To add the Association, right click on the Customer entity then Add -> Association.
Figure 8.96. add the Association
-
You will now see the Add Association dialog.
Figure 8.97. Add Association dialog
-
For this association the only thing that needs changing from the default, is the name of the Navigation Property from Invoice to invoices on the Customer end of the association.
This better reflects the multiplicity of the association such that a Customer is associated with zero or many Invoices (plural).
Figure 8.98. Navigation Property name
Typically, here is where you will:
-
Select the entity at each end of the association
-
Select the multiplicity of each end of the association, and
-
Provide suitable names for the association and its navigation properties
-
-
Once you then hit OK the diagram is refreshed to include the newly created association.
Figure 8.99. diagram
-
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.
Figure 8.100. Table Mapping
-
Click that line reading <Add a Table or View> to reveal a drop down list of all entities.
Figure 8.101. Add a Table or View
-
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 Invoice entity.
Figure 8.102. Entity
-
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.
Figure 8.103. Mapping Details
Figure 8.104. Mapping Details
Figure 8.105. Mapping Details
-
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.
Figure 8.106. Build the project
-
This should result in the following error:
Figure 8.107. Error
This error indicates that there are two source columns - in this case, the Invoice entity's Scalar Property Cust_Num and Navigation Property Customer, which are both mapped to the same target column - the Progress column Invoice.Cust_Num - and this is not supported.
Figure 8.108. Error
-
The solution is simple! Simply delete the mapping of the Scalar Property Invoice.Cust_Num, since 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 Customer.
Right click on Invoice.Cust_Num then Delete.
Figure 8.109. delete the mapping
-
The model diagram will refresh to reflect this change.
Figure 8.110. Model Diagram
-
Build the project, again, using Build -> Build Solution.
Figure 8.111. Build project
-
The project should now be fine.
Figure 8.112. Build Project
You will need to repeat these steps for each association until you have a completed Entity Data Model.
Figure 8.113. Entity Data Model