Generating EF Code First model classes from an existing database - Jon Galloway

Generating EF Code First model classes from an existing database

Entity Framework Code First is a lightweight way to "turn on" data access for a simple CLR class. As the name implies, the intended use is that you're writing the code first and thinking about the database later.

However, I really like the Entity Framework Code First works, and I want to use it in existing projects and projects with pre-existing databases. For example, MVC Music Store comes with a SQL Express database that's pre-loaded with a catalog of music (including genres, artists, and songs), and while it may eventually make sense to load that seed data from a different source, for the MVC 3 release we wanted to keep using the existing database. While I'm not getting the full benefit of Code First - writing code which drives the database schema - I can still benefit from the simplicity of the lightweight code approach.

Scott Guthrie blogged about how to use entity framework with an existing database, looking at how you can override the Entity Framework Code First conventions so that it can work with a database which was created following other conventions. That gives you the information you need to create the model classes manually. However, it turns out that with Entity Framework 4 CTP 5, there's a way to generate the model classes from the database schema. Once the grunt work is done, of course, you can go in and modify the model classes as you'd like, but you can save the time and frustration of figuring out things like mapping SQL database types to .NET types.

Note that this template requires Entity Framework 4 CTP 5 or later. You can install EF 4 CTP 5 here.

Step One: Generate an EF Model from your existing database

The code generation system in Entity Framework works from a model. You can add a model to your existing project and delete it when you're done, but I think it's simpler to just spin up a separate project to generate the model classes. When you're done, you can delete the project without affecting your application, or you may choose to keep it around in case you have other database schema updates which require model changes.

I chose to add the Model classes to the Models folder of a new MVC 3 application. Right-click the folder and select "Add / New Item..."

 

EF Code First Models from an existing database

Next, select ADO.NET Entity Data Model from the Data Templates list, and name it whatever you want (the name is unimportant).

EF Code First Models from an existing database

 

Next, select "Generate from database." This is important - it's what kicks off the next few steps, which read your database's schema.

EF Code First Models from an existing database

 

Now it's time to point the Entity Data Model Wizard at your existing database. I'll assume you know how to find your database - if not, I covered that a bit in the MVC Music Store tutorial section on Models and Data. Select your database, uncheck the "Save entity connection settings in Web.config" (since we won't be using them within the application), and click Next.

EF Code First Models from an existing database

 

Now you can select the database objects you'd like modeled. I just selected all tables and clicked Finish.

EF Code First Models from an existing database

 

And there's your model. If you want, you can make additional changes here before going on to generate the code.

EF Code First Models from an existing database

 

Step Two: Add the DbContext Generator

Like most code generation systems in Visual Studio lately, Entity Framework uses T4 templates which allow for some control over how the code is generated. K Scott Allen wrote a detailed article on T4 Templates and the Entity Framework on MSDN recently, if you'd like to know more. Fortunately for us, there's already a template that does just what we need without any customization.

Right-click a blank space in the Entity Framework model surface and select "Add Code Generation Item..."

EF Code First Models from an existing database

Select the Code groupt in the Installed Templates section and pick the ADO.NET DbContext Generator. If you don't see this listed, make sure you've got EF 4 CTP 5 installed and that you're looking at the Code templates group. Note that the DbContext Generator template is similar to the EF POCO template which came out last year, but with "fix up" code (unnecessary in EF Code First) removed.

EF Code First Models from an existing database

 

As soon as you do this, you'll two terrifying Security Warnings - unless you click the "Do not show this message again" checkbox the first time. It will also be displayed (twice) every time you rebuild the project, so I checked the box and no immediate harm befell my computer (fingers crossed!).

EF Code First Models from an existing database

 

Here's the payoff: two templates (filenames ending with .tt) have been added to the project, and they've generated the code I needed.

 EF Code First Models from an existing database

The "MusicStoreEntities.Context.tt" template built a DbContext class which holds the entity collections, and the "MusicStoreEntities.tt" template build a separate class for each table I selected earlier. We'll customize them in the next step.

I recommend copying all the generated .cs files into your application at this point, since accidentally rebuilding the generation project will overwrite your changes if you leave them there.

Step Three: Modify and use your POCO entity classes

Note: I made a bunch of tweaks to my POCO classes after they were generated. You don't have to do any of this, but I think it's important that you can - they're your classes, and EF Code First respects that. Modify them as you need for your application, or don't.

The Context class derives from DbContext, which is what turns on the EF Code First features. It holds a DbSet for each entity. Think of DbSet as a simple List, but with Entity Framework features turned on.

 

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace EF_CodeFirst_From_Existing_Database.Models
{
    using System;
    using System.Data.Entity;
    
    public partial class Entities : DbContext
    {
        public Entities()
            : base("name=Entities")
        {
        }
    
        public DbSet<Album> Albums { get; set; }
        public DbSet<Artist> Artists { get; set; }
        public DbSet<Cart> Carts { get; set; }
        public DbSet<Genre> Genres { get; set; }
        public DbSet<OrderDetail> OrderDetails { get; set; }
        public DbSet<Order> Orders { get; set; }
    }
}

It's a pretty lightweight class as generated, so I just took out the comments, set the namespace, removed the constructor, and formatted it a bit. Done.

If I wanted, though, I could have added or removed DbSets, overridden conventions, etc.

using System.Data.Entity;

namespace MvcMusicStore.Models
{
    public class MusicStoreEntities : DbContext
    {
        public DbSet     Albums  { get; set; }
        public DbSet     Genres  { get; set; }
        public DbSet    Artists { get; set; }
        public DbSet      Carts { get; set; }
        public DbSet     Orders { get; set; }
        public DbSet OrderDetails { get; set; }
    }
}

Next, it's time to look at the individual classes. Some of mine were pretty simple - for the Cart class, I just need to remove the header and clean up the namespace.

//------------------------------------------------------------------------------
// 
//     This code was generated from a template.
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// 
//------------------------------------------------------------------------------

namespace EF_CodeFirst_From_Existing_Database.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class Cart
    {
        // Primitive properties
    
        public int RecordId { get; set; }
        public string CartId { get; set; }
        public int AlbumId { get; set; }
        public int Count { get; set; }
        public System.DateTime DateCreated { get; set; }
    
        // Navigation properties
    
        public virtual Album Album { get; set; }
    
    }
}

I did a bit more customization on the Album class. Here's what was generated:

//------------------------------------------------------------------------------
// 
//     This code was generated from a template.
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// 
//------------------------------------------------------------------------------

namespace EF_CodeFirst_From_Existing_Database.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class Album
    {
        public Album()
        {
            this.Carts = new HashSet();
            this.OrderDetails = new HashSet();
        }
    
        // Primitive properties
    
        public int AlbumId { get; set; }
        public int GenreId { get; set; }
        public int ArtistId { get; set; }
        public string Title { get; set; }
        public decimal Price { get; set; }
        public string AlbumArtUrl { get; set; }
    
        // Navigation properties
    
        public virtual Artist Artist { get; set; }
        public virtual Genre Genre { get; set; }
        public virtual ICollection Carts { get; set; }
        public virtual ICollection OrderDetails { get; set; }
    
    }
}

I removed the header, changed the namespace, and removed some of the navigation properties. One nice thing about EF Code First is that you don't have to have a property for each database column or foreign key. In the Music Store sample, for instance, we build the app up using code first and start with just a few columns, adding in fields and navigation properties as the application needs them. EF Code First handles the columsn we've told it about and doesn't complain about the others. Here's the basic class:

using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Web.Mvc;
using System.Collections.Generic;
namespace MvcMusicStore.Models
{
    public class Album
    {
        public int      AlbumId    { get; set; }
        public int      GenreId    { get; set; }
        public int      ArtistId   { get; set; }
        public string   Title      { get; set; }
        public decimal Price       { get; set; }
        public string AlbumArtUrl  { get; set; }
        public virtual Genre  Genre                     { get; set; }
        public virtual Artist Artist                    { get; set; }
        public virtual List OrderDetails   { get; set; }
    }
}

It's my class, not Entity Framework's, so I'm free to do what I want with it. I added a bunch of MVC 3 annotations for scaffolding and validation support, as shown below:

using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Web.Mvc;
using System.Collections.Generic;

namespace MvcMusicStore.Models
{
    [Bind(Exclude = "AlbumId")]
    public class Album
    {
        [ScaffoldColumn(false)]
        public int      AlbumId    { get; set; }

        [DisplayName("Genre")]
        public int      GenreId    { get; set; }

        [DisplayName("Artist")]
        public int      ArtistId   { get; set; }

        [Required(ErrorMessage = "An Album Title is required")]
        [StringLength(160)]
        public string   Title      { get; set; }

        [Required(ErrorMessage = "Price is required")]
        [Range(0.01, 100.00,
            ErrorMessage = "Price must be between 0.01 and 100.00")]
        public decimal Price       { get; set; }

        [DisplayName("Album Art URL")]
        [StringLength(1024)]
        public string AlbumArtUrl { get; set; }

        public virtual Genre  Genre                     { get; set; }
        public virtual Artist Artist                    { get; set; }
        public virtual List<OrderDetail> OrderDetails   { get; set; }
    }
}

The end result was that I had working EF Code First model code for the finished application. You can follow along through the tutorial to see how I built up to the finished model classes, starting with simple 2-3 property classes and building up to the full working schema.

Thanks to Diego Vega (on the Entity Framework team) for pointing me to the DbContext template.

Published Thursday, February 24, 2011 1:25 PM by Jon Galloway

Comments

# re: Generating EF Code First model classes from an existing database

Jon,

Great article.

Is there a way to select the database objects and generate the classes from a command line approach rather than a drag and drop wizard?

Thanks!

Thursday, February 24, 2011 5:13 PM by Rafael

# re: Generating EF Code First model classes from an existing database

Does this work for vb.net as well?

Friday, February 25, 2011 12:43 AM by Solmead

# re: Generating EF Code First model classes from an existing database

@Solmead - Yes, I just checked, there's a VB.NET version of the DbContext Generator as well.

Friday, February 25, 2011 2:20 AM by Jon Galloway

# re: Generating EF Code First model classes from an existing database

@Rafael - Maybe - I've read that it's possible to get EdmGen2 working against EF 4, and then you could execute the T5 template with TextTransform.

Keep in mind that this is a one-time import, though, so I think it's probably faster and simpler to go through Visual Studio than to try to set up a repeatable process for a one-off job.

Friday, February 25, 2011 2:26 AM by Jon Galloway

# re: Generating EF Code First model classes from an existing database

Great tip. Exactly what I needed. Saved me tons of work. Thanks a lot. :)

Friday, February 25, 2011 6:59 AM by velio

# re: Generating EF Code First model classes from an existing database

What happens when your underlying models change (new tables/new columns or updated columns)

Friday, February 25, 2011 11:54 AM by vs

# re: Generating EF Code First model classes from an existing database

@vs - You have three options after creating the model classes as shown above:

1) Switch to code-first development, in which you no longer directly modify your tables or colums directly and make all model changes to your model classes. EF Code First can then modify your database to match your model schema.

2) If you want to make changes to your underlying table schema, you can regenerate the classes above if you've kept the generation project around (as mentioned at the top of step one), then merge or copy the new or changed model code into your existing models.

3) If you're making small changes, you might just want to modify the database and classes separately.

Saturday, February 26, 2011 2:33 AM by Jon Galloway

# re: Generating EF Code First model classes from an existing database

Thanks for the great article Jon. I am starting to work with MVC3 and Code First EF on a proof of concept project.  The entire Music Store sample application and Tutorial have been great for getting things up and running very quickly. Thanks for creating this great reference.

Tuesday, March 1, 2011 7:43 AM by Paige Cook

# re: Generating EF Code First model classes from an existing database

Thanks great Tutorial.

Question: what do you mean by:

" recommend copying all the generated .cs files into your application at this point"

I generated the edmx file into the model folder and the .tt files are also in the model folder. So how can I move .cs file generated if they are already in the model folder.

Do you mean copy/paste the generated code to a new file?

Thank you

Thursday, March 3, 2011 10:20 AM by regis

# re: Generating EF Code First model classes from an existing database

Is this available in new EF4.1 RC?

Thanks

Rad

Monday, March 21, 2011 1:59 AM by Rad

# re: Generating EF Code First model classes from an existing database

Probably a stupid question:

Why does  EF Code First not need any "fix up" code?

Friday, June 10, 2011 3:27 AM by Walter Oesch

# re: Generating EF Code First model classes from an existing database

This is excellent, thanks heaps, but your recommendation to generate the model classes in another project and copy the generated classes across to my main project has me stumped.  Admittedly, I'm a noob!

How do I take the generated metadata across as well, which is within the .tt files?  Without it, obviously, these classes (which contain foreign keys etc) don't work.  And what should be the connection string in the new project, as the generated on contains links to the metadata in the .tt files?

Thanks again.

Thursday, September 1, 2011 9:57 PM by Daryl

# re: Generating EF Code First model classes from an existing database

I don't see any foreign keys or other attributes generated.  Am I missing something?

Tuesday, November 1, 2011 12:11 PM by Peter Kellner

# re: Generating EF Code First model classes from an existing database

I love the idea of being able to start with an existing database schema and add EF magic that uses POCOs instead of all the EDMX stuff.

Like commenter Daryl, I too was wondering how I needed to set up my connection string.  I then found that it simply follows the normal convention:  stackoverflow.com/.../entity-framework-code-first-and-connection-string-issue.

Then I ran into this error: "Code generated using the T4 templates for Database First and Model First development may not work correctly if used in Code First mode...", which led me to this:  stackoverflow.com/.../model-first-with-dbcontext-fails-to-initialize-new-database.  

So do I need to keep the EDMX around or not?  This is all becoming too confusing and having to jump through so many hoops makes me think this approach is not quite ready for prime time.  If anyone can simplify things for me, I'd be most appreciative.

Thursday, November 17, 2011 1:50 PM by rhmayer

# re: Generating EF Code First model classes from an existing database

Greate article, Thanks a lot,

You mean with in this way we dont need to write POCO codes, we just create the data base and tables and views and ... then we have POCO classes ?

Sunday, March 18, 2012 5:32 AM by Kamran

# re: Generating EF Code First model classes from an existing database

Bind Pair,care week attack west extra transfer culture increase easily hot author bird help rule her improve enable outcome group ever with approve market existence vehicle unemployment each investment beside yard little absolutely soldier centre corporate tomorrow point general institute weak hill himself troop feeling attach manner route force certainly search unit major parliament room new issue only plan release merely existence fruit normal path current appeal as reject internal run consequence writer author evidence over offer through work product construction actual agree since school dead easy reply construction tiny opportunity fashion

Wednesday, March 28, 2012 1:30 AM by Guenstige Fluege Nach Bangkok Maerz 2010

# re: Generating EF Code First model classes from an existing database

Greate article. Thanks a lot.

Wednesday, April 11, 2012 1:18 AM by bpatel

# re: Generating EF Code First model classes from an existing database

This is an interesting blog but it is unfortunately incomplete. Controllers cannot be created based upon the model classes and the dbcontext because it cannot find the metadata. As can be seen from the other comments, people have really hard time with this. I wish there was a follow-up to complete the application all the way. The link to the "starting with simple 2-3 property classes" tutorial is really un-related to this blog because it is not a continuation of the application used in this blog. This kind of incomplete blog really discourages people from using the technology.

Wednesday, July 18, 2012 2:04 PM by Surya

# re: Generating EF Code First model classes from an existing database

I find it useless. I prefer making changes to Database itself using scripts and then updating my model from database.

Wednesday, September 12, 2012 11:28 AM by lradunovic