Entity Framework 4 “Code-First”: Custom Database Schema Mapping

Last week I blogged about the new Entity Framework 4 “code first” development option.  The EF “code-first” option enables a pretty sweet code-centric development workflow for working with data.  It enables you to:

  • Develop without ever having to open a designer or define an XML mapping file
  • Define model objects by simply writing “plain old classes” with no base classes required
  • Use a “convention over configuration” approach that enables database persistence without explicitly configuring anything

In last week’s blog post I demonstrated how to use the default EF4 mapping conventions to enable database persistence.  These default conventions work very well for new applications, and enable you to avoid having to explicitly configure anything in order to map classes to/from a database. 

In today’s blog post I’m going to discuss how you can override the default persistence mapping rules, and use whatever custom database schema you want.  This is particularly useful for scenarios involving existing databases (whose schema is already defined and potentially can’t be changed) as well as for scenarios where you want your model shape to be different than how you want to persist it within a relational database.

Quick Recap of our NerdDinner Sample

In my blog post last week I walked through building a simple “NerdDinner” application from scratch, and demonstrated the productivity gains EF “code first” delivers when working with data. 


Below are the two model classes we created to represent data within the application.  They are “plain old CLR objects” (aka “POCO”) that only expose standard .NET data types:


We then created a “NerdDinners” class to help map these classes to/from a database.  “NerdDinners” derives from the DbContext class provided by the EF “code first” library and exposes two public properties:


We used the default EF4 “code first” conventions to enable database persistence.  This means that the “Dinners” and “RSVPs” properties on our “NerdDinners” class map to tables with the same names within our database.  Each property on our “Dinner” and “RSVP” model classes in turn map to columns within the “Dinners” and “RSVPs” tables.

Below is the database schema definition for the “Dinners” table within our database:


Below is the database schema definition for the “RSVPs” table within our database:


We did not have to configure anything in order to get this database persistence mapping with EF4 “code first” – this occurs by default simply by writing the above three classes.  No extra configuration is required.

Enabling Custom Database Persistence Mappings with EF4

EF4 “Code First” enables you to optionally override its default database persistence mapping rules, and configure alternative ways to map your classes to a database.

There are a few ways to enable this.  One of the easiest approaches is to override the “OnModelCreating” method defined on the DbContext base class:


The OnModelCreating method above will be called the first time our NerdDinners class is used within a running application, and it is passed a “ModelBuilder” object as an argument.  The ModelBuilder object can be used to customize the database persistence mapping rules of our model objects.  We’ll look at some examples of how to do this below.

EF only calls the “OnModelCreating” method once within a running application – and then automatically caches the ModelBuilder results.  This avoids the performance hit of model creation each time a NerdDinners class is instantiated, and means that you don’t have to write any custom caching logic to get great performance within your applications.

Scenario 1: Customize a Table Name

Let’s now look at a few ways we can use the OnModelCreating method to customize the database persistence of our models.  We will begin by looking at a pretty common scenario – where we want to map a model class to a database schema whose table names are different than the classes we want to map them to. 

For example, let’s assume our database uses a pattern where a “tbl” prefix is appended to the table names.  And so instead of a “Dinners” table we have a “tblDinners” table in the database: 


We want to still map our clean “Dinners” model class to this “tblDinners” table – and do so without having to decorate it with any data persistence attributes:


We can achieve this custom persistence mapping by overriding the “OnModelCreating” method within our NerdDinners context class, and specify a custom mapping rule within it like so:


The code within our OnModelCreating() method above uses a Fluent API design – which is a style of API design that employs method chaining to create more fluid and readable code.  We are using the ModelBuilder object to indicate that we want to map the “Dinner” class to the “tblDinners” table. 

And that is all the code we need to write.  Now our application will use the “tblDinners” table instead of the “Dinners” table anytime it queries or saves Dinner objects.  We did not have to update our Dinner or RSVP model classes at all to achieve this – they will continue to be pure POCO objects with no persistence knowledge.

Trying out the Above Change

If you downloaded the completed NerdDinner sample from my previous blog post, you can modify it to include the above custom OnModelCreating() method and then re-run it to see the custom database persistence in action.

We enabled the automatic database creation/recreation feature within EF “code-only” with the previous blog post.  This means that when you re-run the downloaded NerdDinner application immediately after making the above OnModelCreating() code change, you’ll notice that the SQL CE database is updated to have a “tblDinners” table instead of a “Dinners” table.  This is because EF detected that our model structure changed, and so re-created the database to match our model structure.  It honored our custom OnModelCreating() mapping rule when it updated it – which is why the table is now “tblDinners” instead of “Dinners”.

Several people asked me at the end of my first blog post whether there was a way to avoid having EF auto-create the database for you.  I apparently didn’t make it clear enough that the auto-database creation/recreation support is an option you must enable (and doesn’t always happen).  You can always explicitly create your database however you want (using code, .sql deployment script, a SQL admin tool, etc) and just point your connection string at it – in which case EF won’t ever modify or create database schema.

I showed the auto-database creation feature in the first blog post mostly because I find it a useful feature to take advantage of in the early stages of a new project.  It is definitely not required, and many people will choose to never use it.

Importantly we did not have to change any of the code within the Controllers or Views of our ASP.NET MVC application.  Because our “Dinner” class did not change they were completely unaffected by the database persistence change.

Scenario 2: Customize Column/Property Mappings

Let’s now look at another common scenario – one where we want to map a model class to a database schema whose table and column names are different than the classes and properties we want to map them to. 

For example, let’s assume our “tblDinners” database table contains columns that are prefixed with “col” – and whose names are also all different than our Dinner class:


We still want to map our clean “Dinners” model class to this “tblDinners” table – and do so without having to decorate it with any data persistence attributes:


We can achieve this custom persistence by updating our “OnModelCreating” method to have a slightly richer mapping rule like so:


The above code uses the same .MapSingleType() and .ToTable() fluent method calls that we used in the previous scenario.  The difference is that we are also now specifying some additional column mapping rules to the MapSingleType() method.  We are doing this by passing an anonymous object that associates our table column names with the properties on our Dinner class. 

The dinner parameter we are specifying with the lambda expression is strongly-typed – which means you get intellisense and compile-time checking for the “dinner.” properties within the VS code editor.  You also get refactoring support within Visual Studio – which means that anytime you rename one of the properties on the Dinner class - you can use Visual Studio’s refactoring support to automatically update your mapping rules within the above context menu (no manual code steps required). 

Scenario 3: Splitting a Table Across Multiple Types

Relational tables within a database are often structured differently than how you want to design your object-oriented model classes.  What might be persisted as one large table within a database is sometimes best expressed across multiple related classes from a pure object-oriented perspective – and often you want the ability to split or shred tables across multiple objects related to a single entity.

For example, instead of a single “colAddr” column for our address, let’s assume our “tblDinners” database table uses multiple columns to represent the “address” of our event:


Rather than surface these address columns as 4 separate properties on our “Dinner” model class, we might instead want to encapsulate them within an “Address” class and have our “Dinner” class exposes it as a property like so:


Notice above how we’ve simply defined an “Address” class that has 4 public properties, and the “Dinner” class references it simply by exposing a public “Address” property.  Our model classes are pure POCO with no persistence knowledge.

We can update our “OnModelCreating” method to support a mapping of this hierarchical class structure to a single table in the database using a rule like so:


Notice how we are using the same mapping approach we used in the previous example – where we map table column names to strongly-typed properties on our model object.  We are simply extending this approach to support complex sub-properties as well.  The only new concept above is that we are also calling modelBuilder.ComplexType<Address>() to register our Address as a type that we can use within mapping expressions.

And that is all we have to write to enable table shredding across multiple objects.

Download an Updated NerdDinner Sample with Custom Database Persistence Rules

You can download an updated version of the NerdDinner sample here.  It requires VS 2010 (or the free Visual Web Developer 2010 Express).

You must download and install SQL CE 4 on your machine for the above sample to work.  You can download the EF Code-First library here.  Neither of these downloads will impact your machine.


The CTP4 release of the “EF Code-First” functionality provides a pretty nice code-centric way to work with data.  It brings with it a lot of productivity, as well as a lot of power.  Hopefully these two blog posts provides a glimpse of some of the possibilities it provides. 

You can download the CTP4 release of EF Code-First here.  To learn even more about “EF Code-First” check out these blog posts by the ADO.NET team:

Hope this helps,


P.S. In addition to blogging, I am also now using Twitter for quick updates and to share links. Follow me at: twitter.com/scottgu


  • Due to a RSS caching problem (that was preventing the post from showing up) - I reposted this blog post this morning. I'll try and migrate comments over so that they aren't lost.



  • @Luiz Adilson,

    >>>>>>>>>>>>> It is possible to have this mappings in other assembly?

    Yes - you can definitely separate your model classes (which can be persistent ignorant) from your mappings. That is a good best practice to do for any large application.

    Hope this helps,


  • @loman,

    >>>>>>>> It's nice to be able to define structure and relations using these Fluent APIs or using Data Annotations. But what about indexes? This is the thing that always forces me to create new databases using t-sql. And once you do that, you can just as well create all of the database using scripts. For a web application this is not a big deal. For standalone applications that use multiple databases created by code on user request, I would prefer to be able to create not only tables and relations, but also indexes. Maybe a new model.(Database/Store.)CreateIndex(dinner => new Expression[] { dinner.EventDate.Desc(), dinner.Title.Asc() }, clustered = true, unique = true)?

    I'll double check on this - just copying this comment over from the origional post now so that it isn't lost.



  • @koistya,

    >>>>>>>> Is there a way to define certain order for columns in a created by EF4 table? For example I want my primary/foreign keys columns be on top of the table and helper columns like ModiifedDate, RowGuid etc. - on bottom

    Yes - the column order will be mapped in the order you specify them - so you have complete control over this.

    Hope this helps,


  • @Phil,

    >>>>>>>>> Thanks for the follow up post the stuff you're doing with POCO support in EF4 is great.

    >>>>>>>> You've described how to map child relations in the same database table (the Address example above), but I'm having problems mapping to a separate table. I've got a 'Person' entity with two child properties of type 'Address' (Person.HomeAddress and Person.WorkAddress). I'm able to retrieve data from the Person table, but it doesn't query the Address table. Do you know of any documentation/blog posts which might be able to shed more light onto the Fluent API?

    Can you send me email (scottgu@microsoft.com) with more details on this scenario? I can then follow-up.



  • @Bart,

    >>>>>>>> Nice work, when can we expect the SQL CE 4 tooling for VS2010?

    We'll be updating the VS 2010 tools later this year to have full server explorer and designer support for SQL CE.

    Hope this helps,


  • @Colin,

    >>>>>>>> How about mapping simple collection types?

    >>>>>>>> Such as adding tags to your dinner model

    >>>>>>>> public virtual ICollection Tags {get;set;}

    >>>>>>>> Is this supported?

    Good question - I will follow-up to see.

    Hope this helps,


  • @Tiklu,

    >>>>>>>> In case I don't want to recreate the table. I only want the tables to update. Say for example I added one property in my model class and when I run it next time. Will it be possible for EF Code-First to alter the existing table and add the new column in it instead of completely deleting the table and recreating it?

    That isn't supported right now - but is something we are looking to enable in the future.

    Hope this helps,


  • @Bikal,

    >>>>>>> You might also want to visit this code here where I discuss mapping to custom conventions. It also contains a complete code sample.

    >>>>>>> theminimalistdeveloper.com/23478019

  • @Andries,

    >>>>>>>>> Scott, I really like the direction this is going, and are sure other control developers would agree, instead of the Model First Approach. Question though, are there any plans to make this "work" via RIA Services? We've invested a lot in Silverlight, and need to decide if we should stick to WCF Data Services for communication (which is possible adopting Code First), or plan migration to RIA Services?

    Yes - we are looking to enable this with RIA services in the future as well.

    Hope this helps,


  • @Andrew,

    >>>>>>> I should have picked this up on the last article, but isn't it "odd" that I'm having to include both ID fields AND the virtual relationships in my POCO's ? Surely I should define one or the other, not both ? ID's, and their identity behaviour are specific to the persistence mechanism. Putting a DinnerID in the RSVC implies the relationship that we also describe with Public Virtual Dinner Dinner (or perhaps the Virtual implies the DinnerID).

    >>>>>>> I've been trying to solve some issues doing edits with automatic binding where my view contains a graph:

    >>>>>>> DinnerID

    >>>>>>> Title

    >>>>>>> RSVPs[0].RsvpID

    >>>>>>> RSVPs[0].AttendeeEmail

    >>>>>>> RSVPs[1].RsvpID

    >>>>>>> RSVPs[1].AttendeeEmail

    >>>>>>> I think this will suffer the same fate "The EntityCollection has already been initialized. The InitializeRelatedCollection method should only be called to initialize a new EntityCollection during deserialization of an object graph"

    >>>>>> if you use

    >>>>>> [HttpPost]

    >>>>>> Edit(MyViewModel vm) {
    >>>>>> ...
    >>>>>> }

    EF does require a primary key to be exposed on the model - although you can hide the foreign keys (they do not need to be exposed on the model class).

    To avoid the issue you are having, delete the DinnerID/RSVPId textboxes from your view - that will avoid it binding those when you post. Alternatively you can explictly disable binding of those properties using ASP.NET MVC.

    Hope this helps,


  • If all my model classes inherit from a base model class, will the tables created from the derived classes include the properties in the base class? Thanks.

  • Ohhh... I thought what Scott was doing commenting alone :) -
    Anyway, to the post: Soctt, you are always sharing priceless articles and blogs that definitely helped me for fast-startup with the newest technologies that you guys announce and release! Thank you for being such a great man dedicated to his work!

  • Scott, can you please start writing samples in IronPython or IronRuby, I think your examples will really help people get started with these new languages in .NET 4!

    Thanks for considering...

  • Scott, is there any way of including value converters in the custom schema mapping layer?

    For example, my model class has a property:

    Public System.Windows.Point Position { get; set; }

    I have to store the X and Y values as separate database properties of type Double. I'd like to be able to provide a conversion in the schema mapping layer and avoid polluting my model classes.

  • What if I want to save an object in 2 or more tables? Is that possible?

  • >>>>>>>> In case I don't want to recreate the table. I only want the tables to update. Say for example I added one property in my model class and when I run it next time. Will it be possible for EF Code-First to alter the existing table and add the new column in it instead of completely deleting the table and recreating it?

    >>> That isn't supported right now - but is something we are looking to enable in the future.

    For this omission I wouldn't recommend this for real world use. If I have to write SQL code to update the database after the first deployment I might as well create the database from scratch with my own code. Seems more like a toy to me.

  • Scott - Are there any plans for SQL2005/2008 XML column support through Entity Framework? (or Linq to SQL).

    I already have a solution for this (based on a few UDFs) but it's pretty elaborate (effectively a mini-EF on top of L2S). It'd be great if XML mapping could be baked into EF instead...

  • @MikeT

    Code First and EF support some “value types” via mapping them to a complex type. These value-types are required to:

    • Be a class (and not a struct)
    • Have a default ctor
    • Have read/write properties for all mapped properties

    Unfortunately the “Point” class doesn’t meet these because it is a struct and doesn’t have a default ctor. There are several things we are looking at the enabling in the next major release of the Entity Framework that will broaden the kinds of value types you can use:

    1. Support for Complex Types as structs
    2. Allow you to use factory methods and non-default constructors

    Entity Framework/Code First Team

  • Scott

    Looks good, does this also support many->many via a join table eg. (Posts(id,name) , PostTags(postId, tagId), Tags(id,name))


  • @Sam

    Many-to-many through a join table is fully supported. You can do a basic mapping with a call like this:

    .HasMany(p => p.Tags)
    .WithMany(t => t.Posts);

    If you want control over the join-table's name and its column names, you can do this:

    .HasMany(p => p.Tags)
    .WithMany(t => t.Posts)
    (p, t) => new
    PostId = p.Id,
    TagId = t.Id


  • Scott,

    I work for a company with a mature enterprise system that has a lot of outdated code and practices (VB6). It is a windows form application heavily operational and transactional based with supporting SSIS and SSRS systems. We are looking at a base rewrite and considering framework options. Do you think that the Entity Framework is a viable option for something like this, or should we look at some more matured frameworks like CSLA? I realize it's a fairly broad question, but I'm just looking for an extremely high level answer at this point.

  • A late response, but to follow up on Scott's reply above:

    >> Yes - the column order will be mapped in the order you specify them - so you have complete control over this.

    This does not work for me in the current CTP. A minimal example is:

    class App
    static void Main() {
    Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");
    Database.SetInitializer(new AlwaysRecreateDatabase());
    using (var ctx = new TestData()) ctx.DumpScript();
    class TestData: DbContext {
    public TestData() {}
    public void DumpScript() { Console.WriteLine(ObjectContext.CreateDatabaseScript()); }
    protected override void OnModelCreating(ModelBuilder mb) {
    mb.Entity().MapSingleType(f => new { key = f.ID, data = f.Blob });
    public DbSet Foo {get;set;}
    class Foo {
    public int ID {get;set;}
    public byte[] Blob {get;set;}

    which displays the following output:

    CREATE TABLE "EdmMetadata" (
    "Id" int not null identity,
    "ModelHash" nvarchar(4000) null,
    PRIMARY KEY ("Id")
    CREATE TABLE "Foos" (
    "data" image null,
    "key" int not null identity,
    PRIMARY KEY ("key")

    oddly enough, if you change the Blob to be a string, the column order is corrected.

  • Thanks Scott, Great post, very informative. Question: If I have a predefined SQL db and if I would like to use EF4, can I have all my POCO models generated from db schema and then only tweak what I need to create my complex models? If that is possible it could save a lot of typing for existing dbs.

  • Comparing to NHiberante how will you rate the Entity Framework ?
    Here we wont be using a lot of config settings right ?


  • This is of course, totally awesome!

    In scenario 3, you map multiple classes to a single table (TPH inheritance mapping). Is it possible to do the inverse? i.e. map a single class to multiple tables?

    Here's a contrived example to illustrate my question:

    class Customer {
    public int Id { get; set; }
    public string Forename { get; set; }
    public string Surname { get; set; }
    public string Street { get; set; }
    public stirng City { get; set; }

    create table Customer (
    CustomerId int not null, -- PK, identity
    Forename varchar(50) not null,
    Surname varcher(50) not null

    create table CustomerAddress (
    CustomerId int not null, -- FK to Customer.CustomerId, PK but NOT identity
    Street varchar(50) not null,
    City varchar(50) not null

  • Dying to use this on a project I'm starting. But how do I combine this with using the membership services and tables that are supplied as part of an MVC2 project template?

  • +1 for how to achieve one class to many tables (@dlpowell, @ colin), definitely a scenario we would need
    Also, are there any other limitations to be aware with in terms of Linq operations or is anything supported in EF4 supported in code first. Specifically thinking of .Contains()

  • the same issue i have, when I use the sql profiler, i found the sql built by LINQ is using the default schema "dbo". Is there a way to map sql server schema?
    And when mapping table columns, what if i only have one column which has different name with the property, but I have to map all the columns? Can you do a smart way just to map the different columns/properties?


  • There seems to be an issue using ToTable when you inherit off a base class.

    public abstract class Entity
    public virtual int Id { get; set; }

    public class AnEntity : Entity
    public virtual string SomeProperty { get; set; }

    In order to get this scenario to map correctly I had to do the following in OnModelCreating.

    Where you have a DbSet property defined:
    .HasKey(a => a.Id)

    And where you do not have a a DbSet property defined:
    .HasKey(a => a.Id)
    .MapSingleType(a => new { a.SomeProperty, a.Id })

    There definately appears to be some bugs around MapSingleType() and ToTable().

  • Will this work with just SQL Server or any RDBMS?

  • @Bhavesh,

    Yes - this will work with any database that has an EF providers (including Oracle, MySQL, etc)

    Hope this helps,


  • Hi Scott,

    I noticed that you've called ToTable() on the entityMap object after specifying column mappings. Is there any way under the new CTP to map the entity to the output of a stored procedure?


  • @Dav,

    >>>>>>>>> I noticed that you've called ToTable() on the entityMap object after specifying column mappings. Is there any way under the new CTP to map the entity to the output of a stored procedure?

    Unfortunately I don't think there is any automatic way to enable this today with code-first. Instead you'd need to write code to enable this.

    You can, though, I believe enable this using EF with the designer and a .EDMX file.

    Hope this helps,


  • Hi,
    Though not a very right place to bring this item, but nowhere it could be well noticed!!!

    Since we moved into VS2010 from VS2008 we see ONLY MS SQL related Data Source/Providers in the "New Connection" of EDMX wizard. We do have installed DB2 9.7 client and ODAC.

    Microsoft.Connection.UI shows all available providers in all other scenarios but when in ADO.NET entity data model - wizard shows only MS SQL providers. If we use edmgen2.exe and generate edmx files sucessfully, our program does not utilise the edmx as it says that the connection string is not found or is not an entity based connection. THIS CONDITION IS A SHOW STOPPER IN VS2010!!!. We are going no where!!!

    Need to also tell our PCs provided by our company have "Documents and Settings" folder only having "Read, Write, Execute" permissions. Rest all folders have "Read and Execute" permissions only in our User IDs. Please note this cannot be changed as our users count in company nearly 100,000. We use WinXP-SP3, DB2 9.5 server with DB29.7 clients and Oracle 10G with ODAC Entity providers.

    I've posted this issue already in connect.microsoft.com but until now in vain. If we do not get any solution before Sept 30, 2010 we will be forced to use VS2008 as we have projects to be delivered.


Comments have been disabled for this content.