This entry explains something that’s confused me for a long time. To prevent myself from forgetting, I’m writing it all down.
I’m working with a legacy database that does not always model everything perfectly. As usual with databases, I have to make the painful decision to either workaround the model, or refactor the database. In particular, there are many foreign key candidates that are not mapped in the database, but would be really nice if I had them available in EF. I “know” that EntityA.Key and EntityB.Key represent the same record and I’ve always been confused about how to let EF know what I know without actually going into the database and creating the FK.
Consider these two entities:
The BatchRecord entity represents a production record, and the AuditRecord entity provides information related to a quality review process. Not all records will be audited, but no record can be audited unless it went through production first. AuditedRecordId is populated with the BatchRecordId for the record selected for audit. So, there will always exist a BatchRecord entity with the same id, but there might not be an AuditRecord entity for each BatchRecord entity. Since AuditedRecordId is a key, each BatchRecord can only be audited one time. So, this relationship has a zero or one cardinality.
I know all this, but there is no foreign key enforcing this on the SQL side. I want Entity Framework to know about this relationship, so that I can use navigation properties. So how to do it?
Add An Association
I know Code First is the new hotness, but I’m not actually in a code first situation. I have all kinds of legacy database problems, like tables that don’t follow conventions that would make it easy for Code First to figure out what’s going on. So I tend to port things into the designer then clean up the mess.
So, poking around the designer we see that we can add things to the model, and one of the items we can add is an “Association”. Since FKs are automatically mapped to associations, this seems promising. Lets try it.
Behold the Add Association dialog:
With a minimal amount of poking around, we can figure this one out. I’ll leave the name alone and use the two dropdowns labeled “Entity” to point at AuditRecord and BatchRecord. Then I’ll use the multiplicity to set BatchRecord’s multiplicity to “One”, and AuditRecord’s multiplicity to “Zero or One”. I’ll leave the rest as is.
At this point, you might be forgiven for thinking, “Hey this isn’t so hard.” Don’t worry, this cruise ship is about to run aground. After clicking Ok, everything seems fine.
Look Ma, I made an association!
Unfortunately, the association is a lie. I could try to build, but we can fail faster by right clicking the model and selecting “Validate.”
(Hint: the steps described in this section don’t work. If you want the solution scroll down the the section that starts Don’t Give Up Yet.)
When trying to validate this model, Entity Framework produces “Error 11008: Association ‘BatchRecordAuditRecord’ is not mapped.” Huh? Lets Google that.
We find this MSDN page:
Error 11008: Association Is Not Mapped
This error occurs when an association in the conceptual model is not mapped to the data source. To resolve this error, map the association to the data source. For more information, see How to: Create and Edit Association Mappings (Entity Data Model Tools).
And clicking the link we find these instructions:
To create an association mapping
- Right-click an association in the design surface and select Table Mapping.
This displays the association mapping in the Mapping Details window.
- Click Add a Table or View.
A drop-down list appears that includes all the tables in the storage model.
- Select the table to which the association will map.
The Mapping Details window displays both ends of the association and the key properties for the entity type at each End.
- For each key property, click the Column field, and select the column to which the property will map.
As of this writing 0 of 2 people found these instructions helpful. I’m one of the two. Feel free to visit MSDN and also find it not helpful.
For the sake of completeness, I’ll go ahead and follow their advice. After right clicking on the Associating and selecting Table Mapping, I added the table backing BatchRecord to the mapping details. Seems strange to map a referential constraint to a single table, but they say EF has a steep learning curve, right? Some strangeness is to be expected.
Now to map each key. I select the BatchRecord table key as the column to map BatchRecordId to. Again, seems strange since Entity Framework already knows that BatchRecordId is mapped to that column. But strangeness aside, it seems to work, so far so good.
Now to map AuditedRecordID to it’s column. Hmm. Seems that I have a problem. The “Column” drop down only lists the columns from BatchRecord’s table. Where are the columns for the table backing AuditRecord?
Maybe I can add another table to this mapping… nope.
Maybe I’m supposed to map it to the same column on BatchRecord’s table? Seems strange but the designer accepts that.
Ok so that was a little rough, but it all worked out, right? Better Validate again. Dang! Now Entity Framework produces “Error 3021: Problem in mapping fragments starting at line 331:Each of the following columns in table BatchRecords is mapped to multiple conceptual side properties…” and goes on to complain about using the key from BatchRecord’s table twice.
Maybe I’m supposed to leave that end unmapped. Double Dang! Now validation produces this message “Error 11010: Association End ‘AuditRecord’ is not mapped.”
Maybe its time to dust off Linq to SQL? This was easier over there.
Don’t Give Up Yet
I wish I could point to some brilliant piece of documentation or blog post that pointed the way to the promised land. But I can’t. I found the answer to my problem through dumb luck While futzing around with the association properties I noticed this:
And said to myself. “I wonder what that is?” Clicking in the property field reveals a button with an ellipsis in it, and clicking that takes you to the land of milk and honey. But before I do that, I delete my half-baked Table Mapping by clicking on “Maps to BatchRecords” and selecting “<Delete>” from the drop down. Now I head back to that property sheet and click the “…” button.
Behold, the Referential Constraint Dialog:
This dialog does exactly what you want it to do. I choose BatchRecord as the principal, and AuditRecord is automatically selected as the dependent. BatchRecordId is automatically chosen as the Principal Key. I use the dropdown to set AuditedRecordId as the Dependent Property.
After I click OK, Visual Studio fills in the property sheet. I also notice that when I right click on the Association, “Table Mapping” is no longer offered. I knew it seemed weird to map an association to a table. More importantly, Validation completes without errors.
That Felt Nice
The Referential Constraint dialog felt nice. In three clicks I setup the association and I never once felt dirty or stupid. The dialog did exactly what I expected it to (I think… I’ll have to test it tomorrow.) The only problem with the dialog is that its buried in the property sheet and Error 11108 leads you down the wrong path. I’m not sure what scenario the MSDN documentation is talking about, but it seems pretty unhelpful.
I could be completely wrong about this, but I’ll know pretty soon if I am.