| content
| - --+++Manually creating EDM Associations (FKs) for the Microsoft Access Northwind 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. <i><b>NOTE:</b> These steps will need to be repeated for each association.</i>
1 The first Association I will deal with is the relationship between <b><code>Customer</code></b> and <b><code>Invoice</code></b>, identified by the presence of the scalar property <b><code>Cust_Num</code></b> in both entities. This is a one-to-many relationship, as a Customer may have any number of Invoices.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs01.png" style="wikiautogen"/>%BR%%BR%
1 To add the Association, right click on the <b><code>Customer</code></b> entity, then *Add* -> *Association*.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs02.png" style="wikiautogen"/>%BR%%BR%
1 You will now see the *Add Association* dialog.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs04.png" style="wikiautogen"/>%BR%%BR%
1 For this association, the only thing that needs changing is the default name assigned to the *Navigation Property*. Change the default from <b><code>Invoice</code></b> to <b><code>invoices</code></b> on the <b><code>Customer</code></b> end of the association.%BR%%BR%This better reflects the multiplicity of the association such that a Customer is associated with zero or many Invoices (plural).
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs05.png" style="wikiautogen"/>%BR%%BR%Typically, here is where you will - 1) Select the entity at each end of the association, 2) Select the multiplicity of each end of the association, and 3) Provide suitable names for the association and its navigation properties. %BR%%BR%
1 Hit *OK*. The diagram will be refreshed to include the newly created association.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs06.png" style="wikiautogen"/>%BR%%BR%
1 You now need to edit the mappings associated with the newly created association, so right-click the association on the diagram. Then, select *Table Mapping* to display the *Mapping Details* pane.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs07.png" style="wikiautogen"/>%BR%%BR%
1 Click the line which reads <b><Add a Table or View></b> to reveal a drop down list of all entities.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs08.png" style="wikiautogen"/>%BR%%BR%
1 Here you need to select the entity on the right/far side of the association (the entity where the foreign key exists).%BR%%BR%In this example, it is the <b><code>Invoice</code></b> entity.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs09.png" style="wikiautogen"/>%BR%%BR%
1 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, as depicted here.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs10.png" style="wikiautogen"/>
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs11.png" style="wikiautogen"/>
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs12.png" style="wikiautogen"/>%BR%%BR%
1 Once the mapping is complete, you can build the project using *Build* -> *Build Solution*. <i><b>NOTE:</b> It is worthwhile building as each association is made, since the error messages can be a little confusing.</i>
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs13.png" style="wikiautogen"/>%BR%%BR%
1 This should result in the following error, which is included here since I (the author) found it misleading.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs14.png" style="wikiautogen"/>%BR%%BR%
This error indicates that there are two source columns — in this case, the Invoice entity's Scalar Property <b><code>Cust_Num</code></b> and Navigation Property <b><code>Customer</code></b> — which are both mapped to the same target column — the Progress column <b><code>Invoice.Cust_Num</code></b> — and this is not supported.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs15.png" style="wikiautogen"/>%BR%%BR%
1 The solution is simple! Simply delete the mapping of the Scalar Property <b><code>Invoice.Cust_Num</code></b>. 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 <b><code>Customer</code></b>.%BR%%BR%
Right click on <b><code>Invoice.Cust_Num</code></b> then *Delete*.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs16.png" style="wikiautogen"/>%BR%%BR%
1 The model diagram will refresh to reflect this change.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs17.png" style="wikiautogen"/>%BR%%BR%
1 Build the project, again, using *Build* -> *Build Solution*.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs13.png" style="wikiautogen"/>%BR%%BR%
1 The project should now build fine.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs18.png" style="wikiautogen"/>%BR%%BR%
You will need to repeat these steps for each association, until you have a completed Entity Data Model.
%BR%%BR%<img src="%ATTACHURLPATH%/AccessEDMFKs19.png" style="wikiautogen"/>%BR%%BR%
|
![[RDF Data]](/fct/images/sw-rdf-blue.png)
![[cxml]](/fct/images/cxml_doc.png)
![[csv]](/fct/images/csv_doc.png)
![[text]](/fct/images/ntriples_doc.png)
![[turtle]](/fct/images/n3turtle_doc.png)
![[ld+json]](/fct/images/jsonld_doc.png)
![[rdf+json]](/fct/images/json_doc.png)
![[rdf+xml]](/fct/images/xml_doc.png)
![[atom+xml]](/fct/images/atom_doc.png)
![[html]](/fct/images/html_doc.png)