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 (14)

  1. Em says:

    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.

  2. Simon J Ince says:

    @ 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

  3. Kinnar Shah says:

    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

  4. Simon J Ince says:

    @ 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

  5. Daniel says:

    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

  6. Simon J Ince says:

    @ Daniel,

    what you describe sounds like you don’t need to follow this post – it is more focused on having a different conceptual model to the physical database representation.

    All I think you need is to execute a query against your entities. You could either do something like this;

    var result = from st in db.Store

                select new { st, st.Location.Name };

    … or you could use the "join" keyword syntax.

    Hope that helps!

    Simon

  7. Nhu Keu says:

    That’s wonderful, I learned a lot from this article.Thanks!

  8. Renato Golia says:

    Following your example, what if a Customer has no related CustomerAddress?

    Is it possible to explicit that we want a LEFT JOIN instead of an INNER JOIN?

    thanks

  9. Gyanendrasingh says:

    Thanks Simon, this article helped me much…

  10. @Jayabalan says:

    Hi,

    Its possible to add values to both tables?

  11. Json says:

    I would murder you if you were on my team and made the Primary Key of one table be the Primary key of a different table…just say'n

  12. Simon J Ince says:

    @ Json – murder away – in most circumstances I'd rather use something that genuinely uniquely identifies the data (a natural key) and makes it easy to join two tables than create a surrogate key for zero value add… 🙂

  13. LiamD1 says:

    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 🙂

Skip to main content