다음을 통해 공유

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!

Comments

  • Anonymous
    March 23, 2009
    PingBack from http://blog.a-foton.ru/index.php/2009/03/23/mapping-two-tables-to-one-entity-in-the-entity-framework/

  • Anonymous
    March 31, 2009
    There is some problem with such mapping. I have the same association and have faced the challange. If we have a record in Customer table, that has no related record in CustomerAddress table, then the Customer table record isn't retrieved.

  • Anonymous
    April 02, 2009
    @ Em; That's right, it requires a record in each table to exist as the generated SQL uses an INNER JOIN. It makes sense if you're using a new model, but I guess this is more tricky if you're mapping to an existing schema and data. Simon

  • Anonymous
    August 13, 2009
    I want to create single entity which combines two tables from existing database(not new). But problem is that i am not able to fetch some records if matching record is missing in one of the relationship table. Can i override entity framework default inner join behaviour to left outer join/right outer join to fetch all records or any other workaround is available ? Please sugges us. Thanks

  • Anonymous
    August 14, 2009
    @ Kinnar, As far as I konw there is no way to do this in EF v1. Your best bet might be to; a) Create your own View in the database and surface that as an entity b) Surface both the entity types you're after, and then create your own C# class to hold the data. Select the data using a LINQ expression that causes an outer join to be generated (such as the following); var query = from mytable in myentities.MyTable      select new      {          mytable.MyValue,          mytable.MyOtherTable.OtherValue      }; It might also be worth reading this post; http://odetocode.com/blogs/scott/archive/2008/03/24/11907.aspx HTH Simon

  • Anonymous
    October 07, 2009
    Hi I am trying to do something like this but with a one to many relationship. If I do this... What would it happen if I want to add a new record of the other table. An example of what I am trying to do is this: I have a store entity and a Location Entity they have a many to many relation. I need to show in a grid the name of the store (Store.Name) and the name of the location (Location.Name) but I also need to be able to add new Locations so I do not understand how can I get this working.... Any suggestion

  • Anonymous
    October 07, 2009
    The comment has been removed

  • Anonymous
    March 21, 2010
    That's wonderful, I learned a lot from this article.Thanks!

  • Anonymous
    February 09, 2011
    The comment has been removed

  • Anonymous
    March 18, 2012
    Thanks Simon, this article helped me much...

  • Anonymous
    September 02, 2013
    Hi, Its possible to add values to both tables?

  • Anonymous
    August 21, 2014
    The comment has been removed

  • Anonymous
    September 30, 2014
    The comment has been removed

  • Anonymous
    October 16, 2014
    Hi, I am new to MVC 5/EF 6 so please excuse what might be a naïve question. I'm investigating using EF (database first) for a new MVC application against a legacy DB. It (the DB) has a lot of relationships like ADDRESS 1-----1 PERSON_ADDRESS 0..1------1 PERSON. All 3 tables have their own PK (identity column).  To be clear, while the DB supports a many to many between PERSON & ADDRESS, the UI ensures that a PERSON has just one  ADDRESS.  The idea (I assume) was an attempt by the original DBA to ensure consistent implementation of address across the applications that use the DB (there are also a few entities that have multiple ADDRESSes). Because there are other legacy apps (in addition to the one I am looking to replace) I can't change the schema. How would you suggest I implement a "Create Person with Address" page given this data structure? Thanks :-)