Jon Galloway : Entity Framework

Generating EF Code First model classes from an existing database

Jon Galloway — Thu, 24 Feb 2011 21:25:11 GMT

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..."

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

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

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.

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

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

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..."

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.

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!).

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

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.

FIX: WCF Data Service with Entity Framework Code-First DbContext doesn’t accept updates

Jon Galloway — Fri, 21 Jan 2011 18:40:03 GMT

Summary

The Entity Framework Code First DbContext doesn’t expose the interfaces to support updates when exposed via WCF Data Services. Attempting to save changes results in a fault with the message "The data source must implement IUpdatable or IDataServiceUpdateProvider to support updates." The fix is to alter your WCF Data Service to expose the DbContext's underlying ObjectContext and to disable proxy generation.

Background

Jesse Liberty and I have stumbled into some frontier country. Our work on a Windows Phone + WCF Data Services + EF Code First + MVC 3 solution for the The Full Stack has put us in that delightful developer place where the combination of two or more pre-release or newly-released bits can feel like you're in primordial-ooze-technical-preview stage. Honestly, it's our job to get there before you do, and we love it.

I've been pushing Entity Framework Code-First to anyone who will listen. My kids are sick of hearing about it... "yes, dad, POCO object, no config, we GET it!"

We'd figured out how to connect all the pieces together so that data in an ASP.NET MVC 3 using SQL CE and EF Code First could expose data to a Windows Phone client via a WCF Data Service. That sounds like a lot of moving parts, but it actually went pretty smoothly once we figured out the steps, as documented here.

The problem - the service was read-only

Our goal is to build out a contact manager application that allows you to quickly store and look up contacts by description, e.g. short, long hair, works for Microsoft, met at MIX 2010.

Our phone client did a fine job of reading the data from the service, but our attempts to save changes back to the service gave some errors that were hard to troubleshoot. Based on some help from Chris "Woody" Woodruff, we changed our update method to stop using batching, and we started seeing the specific error message: "The data source must implement IUpdatable or IDataServiceUpdateProvider to support updates."

That error message led me to a comment on a post by Rowan Miller on using WCF Data Services against a DbContext, which is exactly what we were doing. Rowan points out that if you're going by experience or documentation on using Entity Framework (before Code First), you'd create a simple WCF Data Service and point it at your Context class, and everything would work. However:

Now what if your BlogContext derives from DbContext instead of ObjectContext? In the current CTP4 you can’t just create a DataService of a derived DbContext, although you can expect this to work by the time there is an RTM release.

But there is some good news, DbContext uses ObjectContext under the covers and you can get to the underlying context via a protected member.

Aha, you say - that was for CTP4, and there's a newer release. Read on...

DbContext as a lightweight, convention-based wrapper over ObjectContext

DbContext is really easy to work with. It does the basic things you'd expect from ObjectContext, like tracking changes to your entities, batching changes, etc. The DbContext also adds some other convention-based goodness on top, though, which does things like infer what the database connection should be based on the the entity name, creating the database if it doesn't exist, etc.

However, the DbContext abstracts away / hides some capabilities in the ObjectContext. Usually, those capabilities aren't things you'll miss, but occasionally you will. In this case, we need IUpdatable support, as the MSDN documentation for DataService<T> explains:

The type of the DataService<T> must expose at least one property that returns an entity set that is an IQueryable<T> collection of entity types. This class must also implement the IUpdatable interface to enable updates to be made to entity resources.

The workaround - Expose the DbContext's base ObjectContext

Rowan Miller explains the workaround, which involves two short steps. Fortunately, since he posted that info (valid for CTP4), it's become even easier, since DbContext directly exposes the ObjectContext with your having to write a property to expose it. Prior to EF Code First CTP 5, if you wanted to call into the base ObjectContext, you had to expose the underlying context via a property, like this:

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    public ObjectContext UnderlyingContext
    {
        get { return this.ObjectContext; }
    }
}

In CTP5, the DbContext implements IObjectContextAdapter, which exposes the ObjectContext. So, putting that together, if you want to set up a WCF Data Service that exposes a DbContext named PersonContext, you’ll need to change from this:

public class PersonTestDataService : DataService<PersonContext>
{
    public static void InitializeService(DataServiceConfiguration config)
    {
        config.SetEntitySetAccessRule("*", EntitySetRights.All);
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
    }
}

to this:

public class PersonTestDataService : DataService<ObjectContext>
{
    public static void InitializeService(DataServiceConfiguration config)
    {
        config.SetEntitySetAccessRule("*", EntitySetRights.All);
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
    }

    protected override ObjectContext CreateDataSource()
    {
        var ctx = new PersonContext();
        var objectContext = ((IObjectContextAdapter)ctx).ObjectContext;
        objectContext.ContextOptions.ProxyCreationEnabled = false;
        return objectContext;
    }
}

Here’s the summary of what we did:

The DataService is typed as ObjectContext rather than the class which implemented DbContext (PersonContext).
We override CreateDataSource to get at the PersonContxt’s underlying ObjectContext, returning the ObjectContext as the result.
We disable Proxy Creation, which apparently does something magical. I just got it from Rowan Miller’s post and it works, so I’m not going to complain.

Updating the client proxy

If you’d previously created a client service proxy (via DataSvcUtil) for the service, you’ll need to regenerate it because the ObjectContext based WCF Service now implements IUpdatable and thus exposes some new additional methods. In our test case - a flat object model with one class holding simple strings - the changes to the client proxy class were to set up property change notifications:

public event global::System.ComponentModel.PropertyChangedEventHandler PropertyChanged;
protected virtual void OnPropertyChanged(string property)

Fun with IObjectContextAdapter

I’ve run into other circumstances where I needed access to the DbContext’s ObjectContext – for instance working with the ObjectStateManager. It’s nice to be able to cast a DbContext to an IObjectContextAdapter to get at the ObjectContext, then go to town with the base ObjectContext.