Mapping Associations in the EDM Designer

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!  

4 thoughts on “Mapping Associations in the EDM Designer

  1. On the bug on the last part, I found that if you first delete both ends first, the changes will stick most of the time for some reason.PS I like the picture of your mother’s puppy.

  2. I have read and re-read the pages in chapter 2 of your PEF and have tried to follow the example above, but with my table structures.

    I am using VistaDB4 as the backend, and as yet, VistaDB does not support generating the assocations when an entity model is constructed. So I am in the situation of your example above.

    I have two tables, Persons and ActivityLevels. One person can have multiple activitylevels.

    The primary key unique for the persons table is the field pkuPersonID.

    The primary key for the ActivityLevels table is a composite key:

    fkPersonName

    ActivityLevelDate.

    Steps I used:

    1. Create the association FK_ActivityLevels_Persons with Person’s multiplicity = 1 and ActivityLevel’s multiplicity = many.

    2. Delete the fkPersonName scalar property from the ActivityLevel entity.

    3. Map the Association.

    Select the ActivityLevel (the child table) and the following tree diagram results:

    Activity Level

    ActivityLevelDate : Date time <-> Activity Level Date : datetime

    Person

    pkuPersonName : string <-> [blank]

    from the drop down list, I choose fkPersonName : nvarchar as the obvious choice.

    There is no reference in the ActivityLevel table for the fkPersonName (in the association) to map to the fkPersonName in the datastore.

    And then:

    Click on BUILD and the following error is generated (although the build succeeds).

    Error 1 Error 3025: Problem in Mapping Fragment starting at line 409: Must specify mapping for all key properties (ActivityLevels.ActivityLevelDate, ActivityLevels.fkPersonName) of table ActivityLevels.

    C:\Users\KDJ\Documents\H2T\PersonsModel\PersonsModel.edmx 410 15 PersonsModel

    Sure enough, looking at the mapping for the ActivityLevel entity, the property fkPersonName is listed there (I thought I had deleted it, it doesn’t appear in the AcitvityLevel entity up in the graphic display), with the mapping to the datastore being blank. The drop down list does not give the datastore column fkPersonName as an available choice.

    How do I get rid of that entry in the mappingtable for ActivityLevel, even though I have deleted it from the entity?

    Kenneth James

    P.S. Your book PEF is wonderful. I is the kind of book that I have to read and re-read over and over again. When I go back to the first chapters, I now see how a simple sentence there is very loaded with concepts that will appear much later.

  3. Hi Kenneth

    The "easy" trick is to just go modify the XML of the SSDL and remove the property. But if you "update from database" it will reappear.

    That’s just a band-aid though. Can you look at the note on page 308 about a designer bug to see if it is related to your problem?

    And thanks for the p.s. 🙂

    julie

Leave a Reply to Kenneth James Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.