I had to figure this out, so here are a billion screenshots so anyone who wants to do this will have an easier time of it!

One of the niceties about being able to customize your conceptual models is not having to bother the dba while they are doing important things like indexing tables and optimizing the database. If you find you have some tables that came into your entity data model that have primary/foreign key relationships that were never defined in the database, then the wizard won’t have built associations for you.

Imagine I have two entities, client and title. A client has many titles (kinda like a publisher but I used a different db for my example, so just live with “client”.) The title entity has a property called clientid. I want to build an association between the two

Right click in the model (white area, not on an entity) and you will get a context menu. Select Add, then Association.

This little window will pop up. It will default the two ends populated with the first two alphabetical entities.

Use the dropdowns in End Entity to change the two end points. I made mine CLient and Title.

The multiplicity options are 1, Zero or One and Many. My relationship is that is one to many, but I can have titles that don’t have a client, so I’m choosing 0..1 for the client and * for the title.

I changed the names of the navigation properties. They started out as Title and Client1. When I’m working with a client, I want to see a property called Titles. (Client.TItles) and title only has one client, so these are the names I want, Titles and Client.

Next is a step that may not make sense but you need to delete the “clientid” property in the title entity. Because of this association, the clientid has a job now and doesn’t belong as a property. (This is debatable and there have been requests to modify this rule for EDMs, but right now, that’s the way it works. You can read more about why in this blog post.)

Now to the mappings. We have told the entities that they are related but they don’t know the basis for their relationship. This happens in the mappings where we wire things up to the representation of the database that is part of the EDMX file created by the wizard.

Right click in the model again and choose Show Entity Mapping Details.

You’ll see an almost empty view, that has only Association and under it <Add a Table or View>. Click on Add a Table or View. Choose one of the entities. This is a little confusing here and I have actually made a suggestion in the forums to simplify it, but just go with the flow.

You’ll get a default that shows the end points you identified in the association and most likely you’ll see id’s on the left and id’s on the right. The “id” on the left refers to the property of the end point. The one on the right refers to the column in the database table (literally the store entity, but db table is easier to grok in this case) that you are mapping to. SO that’s why you see that I have changed my mapping so that client’s id property will map to the client id field in “the other end of the association”, e.g. the Title. Then the title end point (End2) will map over to the id of the other endpoint (client).

Now here’s a small bug with the designer, even if I choose clientid from the drop down list under column, it doesn’t stick (remember, the tool is just a preview right now). I originally had gone into the raw xml to fix this, but thanks to a pointer by Philip in the comments, I see how to get around it more easily.

In the drop down, first choose <Delete>. Then there will be no column mapping for the client end point. Now drop down the list again and choosed clientid. This way it stays.

—————————————
I’m going to leave the by-hand notes in here just for educational purposes.

So I had to doctor this up in the raw xml.

Luckily this was at the bottom of the whole xml file.

<AssociationSetMapping Name=“ClientTitle“ TypeName=“EFSampleDatabaseModel.ClientTitle“ StoreEntitySet=“Titles“ >
  <EndProperty Name=“Title“ >
    <ScalarProperty Name=“id“ ColumnName=“id“ />
  </EndProperty>
  <EndProperty Name=“Client“ >
    <ScalarProperty Name=“id“ ColumnName=“clientid“ />
  </EndProperty>
  <Condition ColumnName=“clientid“ IsNull=“false“ />
</AssociationSetMapping>

I’ve bolded the two changes I made. These were originally “id” because of the mapper problem.
—————————————

The condition exists because it makes no sense to map titles that have null clientids.

Now I can write code like this and see the associations working

Dim oc As New EFSampleDatabaseModel.Entities
Dim q = From c In oc.Clients Select c, c.Titles

For Each c In q
  Console.WriteLine(c.Titles.Count)
Next

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!