Mapping two Tables to one Entity in the Entity Framework
Whilst at Q-Con a few weeks ago someone asked me about how to map two database tables to one entity in the Entity Framework... something a lot of people want to do at some point or another. At this point I must thank Chris Barker for pinging me some links to get this working. It seems a common thing to need to do so I thought I’d post briefly here.
Options
There are actually two main options;
1. Do the mapping in the Entity Framework, which requires that both tables must share the same primary key. Therefore if you have Customer and CustomerAddress tables (with a 1:1 relationship) the CustomerId must be the primary key on the CustomerAddress table.
2. Do the mapping in the database, using an updatable View that you manually map into the Entity Framework’s model. Rick has a post about how you might do this here.
If you want to use Option 1, which I personally prefer (assuming you have control of the database schema at this point), there is a walkthrough in the documentation here. The walkthrough creates a new entity type – I actually cheated and made it even simpler.
Walkthrough
I have two simple tables – Customer and CustomerAddress. I want them to be encapsulated in a single Customer entity within my Entity Framework model.
When I generate my entity model this gives me two entities in both the Conceptual and Storage schemas, appropriately mapped. It looks a bit like this;
The problem is, I want my address fields on the Customer entity. There are two ways to achieve this... the “designer” way is to right click “Line1” and choose Cut, then Paste it into the Scalar Properties in Customer, then repeat for all the remaining properties (except of course CustomerId – that’s already there).
The XML fan way is to dive into the Conceptual Schema in the dbml file and add these properties manually. I find this quicker as I can copy/paste and tweak the names very easily and all in one operation (for example, if I want to call a field AddressLine1 instead). I end up with the following XML;
This gives us a slightly different looking interim model, as CustomerAddress starts to become a bit empty;
So now we have a nice complete Entity in our Conceptual space; next we need to map these new properties to the table in the Store. I could do this in the mapping section of the XML, or we can use the designer. The tool makes some assumptions here that actually automate mapping all the properties for you if you haven’t changed their names, so I’ll use that for simplicity.
Right-click on the Customer entity and choose “Table Mapping”. You’ll see something like the following. We can now choose to add a Table to the mapping;
As soon as I pick CustomerAddress, it pre-populates all the fields for me with a guess at their mapping. If you’ve renamed any properties in your entity you need to fix them up manually. Also, map the CustomerId field in the database to the Id property on the entity. The result is a mapping that looks like this;
Finally, delete the CustomerAddress entity from the designer (which just deletes it from the Conceptual schema, not the Storage schema – which is good because we need to table still!). In deleting this entity, the navigation property named CustomerAddress that was on Customer disappears, completing the tidy-up of our model.
... and we’re finished!
A Quick Query
Just to prove it, a nice simple query like this now works;
var customersAndAddresses = from c in db.Customer
where c.Name.StartsWith("Simon")
select c;
var result = customersAndAddresses.First();
string address = String.Format("{0}\n{1}\n{2} {3}",
result.Name,
result.Line1,
result.Line2,
result.PostCode);
Using SQL Profiler to see what’s going on shows that the Entity Framework is doing the join for us, just as we would expect;
Good huh?
Hope that helps!
