Thursday, February 23, 2012

Using Entity Framework Code First with an existing database: 1

For all the talk of Code First in the .Net community, I couldn't find much on the web dealing with how to use it when you have an existing database, or whether it was even meaningful to speak of such a thing. So this is just my account of how I set about the task of switching to Code First with my preexisting, hosted, trusty SQL Server set-up.

See also:

I have an existing SQL Server 2008 database. I've been using Entity Framework Database First at home, but for various reasons I want to switch my workflow (for that is what it is called) to Code First. We use Code First at work, and it's hard to come home and do things differently. Also, Code First just seems to be a much more modern, radical, revolutionary way of doing things - at least to me. It's hot and sexy, like a lady in a music video.

So, in a nutshell, I want to:
  1. Work from an existing database...
  2. Using Code First...
  3. Maintaining the database from then on with EF Migrations

A bookish digression

Entity Framework is a hard beast to cage, from a bibliophile's point of view. In the middle of 2010 I bought Julie Lerman's "Programming Entity Framework", aka "THE book on EF", only to have it superseded by the 2nd edition when EF4 was released with .NET 4. So I upgraded, but was disappointed to find hardly any mention of Code First which by then we had decided to use at my work. I worked through some of the EF articles on Asp.Net, but ... it's nice to have a book. That's just how I was brought up. What can I say?

So, was the 2nd edition useless? Julie Lerman's answer to this hasn't exactly cleared up my confusion:
"I’ve had tweets and emails from readers asking if there is any point to reading the 2nd edition of Programming Entity Framework if they are only planning to use Code First and DbContext... Yes, the 2nd edition is completely relevant although there are bits within there that will be irrelevant."
Anyway, now I'm back on track with la Lerman's latest mini-opus: "Programming Entity Framework Code First". Much as EF has been rapidly iterating through different minor versions, the natural bibliographical response should be to write smaller and smaller books and knock them out quickly. I don't expect that this latest book, at 175 pages, will last too long as the source of truth on EF and Code First, but that's ok: it beats launching into an 800-page bookzilla which is half out-of-date by the time you've read much of it.

Let's go

OK, first step: back up my database. In my case, my site and database are hosted with the excellent DiscountASP, so all I have to do is log in to the control panel and manually create a zipped backup. Just in case.

Next step: upgrade to EF 4.3. Then install Entity Framework Power Tools CTP1 through Visual Studio Extension Manager. With this extension, you can reverse engineer your database using the project context menu. This step is not strictly necessary: reverse engineering gives you a context (with accompanying configuration, if necessary) and a bunch of POCOs, all of which you can do yourself. Of course, your existing database may not be something you want to have to manually recreate on account, say, of its size. But in any case you want to install those Power Tools for another reason: you get a nice model designer for your POCOs, much like the one you get when your workflow is Database First.

Not the world's most interesting model, it has to be said, but hey! designer support for my POCOs!

So, whether you reverse engineer your tables to get your entity POCOs or you hand craft them, you end up with classes corresponding to your existing tables. In my case I went the manual route, mainly because when I tried to use the CTP tool against a hosted database, I got an error that I'm pretty sure is a result of my lack of access to the 'master' database on DiscountASP's SQL Server. No matter: I didn't investigate, I didn't care: I quickly rolled up my own classes.

To test my context and classes, I quickly created a small test (after adding a reference to the context in the connectionString section of the test project's 'app.config').
public void InsertEvent()
    // Arrange
    var testEvent = new Entities.Event
        Content = "New stuff",
        StartDate = DateTime.Now,
        Locations = "Her and there",
        Url = "",
        Name = "My event"

    // Act
    using (var context = new Entities.ConnemaraNetContext())
Bang! It saves to the database. Ok, it's not really a test because it doesn't assert anything, but I can check the results myself in VS2010 Server Explorer. All I need is a quick and easy way of newing up an entity and saving it.

So I'm using Code First with an existing database. In a completely pointless, expensive way. Well, it's a start. But what happens if I change the model? What if I add an extra property/field to my Entities.Event class and try the insert again? Ba-pow :-(
"An error occurred while updating the entries. Invalid column name 'TestProperty'."

Where's the initializer?

I don't have an initializer in this set-up. Nor do I want one. Let me explain. In its default state, which is to create the database if none exists, initialization would be useless because in this case...a database exists. I could always use the DropCreateDatabaseIfModelChanges strategy, which is perhaps the most instantly identifiable artefact of Entity Framework Code First, to just nuke my database every time I change the smallest thing in the model, but when I talked about working with an existing database, I meant one with data in it. I want to leave it alone in that respect.

So how do you do that? I'll have a look in Part 2.

Flickr photo
FlickrAtomic Explosion, by Jacob01123. Entity Framework goes to work on a database


  1. This was good article. I have written similar type of article explaining in step by step manner about Code First for Existing Database with POCO class auto generated If possible please review it and pass your comment. Thank you.

  2. You probably know this already, but the Entity Framework power tools will reverse engineer an existing database into Code-First POCO's. You also have control over the T4 templates it uses too.

    1. Yep, I mention it slap-bang in the middle of this article!