Sunday, February 26, 2012

Using Entity Framework Code First with an existing database: 2

To recap: I recreated my database as a Code First POCO layer (by hand!) in Part 1, and now I need to migrate changes to the entity model to my database.

See also:

Switching on EF Migrations

Ok - this is the big one: this is where I enrol the Migrations aspect of Code First to manage changes to the model/database. I'm pretty sure that prior to EF 4.3 this whole next step would have been impossible with an existing database. To be able to use Migrations we have to simulate the initialization of the database, even though this database was 'initialized' ages ago, when I first created it.

One thing to pay attention to before you start: despite the fact that your app may already have a Web.config with your newly-created context as a named connectionString, it's the App.config that matters when you're working with the the console. This lost me a bit of time, to my chagrin, until I understood what was going on. Despite the fact that the Default Project was set to ConnemaraNet (my MVC website project) in the console, any commands ('Add-Migration' or 'Update-Database') would only execute against a SQLEXPRESS instance, which was no use to me. I could see this by using the -Verbose flag when running those commands. Once I added the connectionString to the App.config, all was gravy.

Executing the 'Enable-Migrations -EnableAutomaticMigrations' command in Package Manager Console ('the console') adds a Migrations folder to the project, with the following Configuration class:
namespace ConnemaraNet.Migrations
    internal sealed class Configuration : DbMigrationsConfiguration
        public Configuration()
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
        protected override void Seed(ConnemaraNetContext context)

Setting the baseline

In the console the command 'Add-Migration Initial [-StartUpProjectName 'Your.FullyQualified.ProjectName']' creates a migration class (inheriting from DbMigration) based on your model in a Migrations folder in your app (if your solution has more than one startup project you'll have to qualify every command with the -StartUpProjectName as I only found out when I tried all this at work; at home my project was rather simple so it didn't need it). But, as Julie Lerman points out, you want to remove all the code business from this class, leaving only two empty stubs.
namespace ConnemaraNet.Migrations
    using System.Data.Entity.Migrations;
    public partial class Initial : DbMigration
        public override void Up()
        public override void Down()
Running 'Update-Database' in the console will now create the '__MigrationHistory' table, which gives me a snapshot, a starting point, a point from which Code First can manage my database while I tend lovingly to my entity model as if it was a zen garden, using minor code changes as a tiny rake.

This time the baby stays

So what exactly happens when you change the model from now on? What else do I have to do? I added an extra field, 'MiddleName', to one of my entity classes, the Author class. Now if I run a test that selects all authors, for example, from this table, I'll get an error because my model is out of whack with my database. Ok, that makes sense. Running 'Update-Database' rectifies this by adding the offending field to the 'Author' table. But the magic part is that it left the data alone. This is the new part to me: I've only ever seen Code First handle model/table synchronisation by dropping and recreating the database, what I call the 'ThrowingTheBabyOutWithTheBathwater' strategy. This time the baby stays! This is what just happened:
PM> Update-Database -Verbose
No pending explicit migrations.
Applying automatic migration: 201202260331399_AutomaticMigration.
ALTER TABLE [Author] ADD [MiddleName] [nvarchar](100)
[Inserting migration history record]
PM >
This is so exciting! It's worth repeating using bad language: the frickin' database got updated using f*&#in' Code First, and it left the effin' data alone! Back of the net!

Added 'MiddleName' column AND left the existing data there. Niiiice

If you're using Visual Studio Server Explorer to do most of your database inspection and query execution etc., you won't actually see a System Tables folder, which is where Code First creates '__MigrationHistory', but no matter: this table, which in EF 4.3 replaces the antediluvian 'EdmMetadata' table will just show alongside your regular tables when it gets created.

All in all, not a bad day's work. EF 4.3 Migrations allows me to ride the Code First bullet train without forfeiting my existing database, and as a bonus I got away without having to install SQL Server 2008 Management Studio - I seem to be able to do it all with Server Explorer and Package Manager Console. There's a fair bit more to Migrations that you can read about on the ADO.Net team blog, specifically around the differences between Automatic and Code-based migrations. In this post I just wanted to explain how I got Entity Framework Code First to play nice with my trusty old SQL Server database.

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

Wednesday, February 1, 2012

Subscribe to a custom YouTube feed in Google Reader

Organise arbitrary YouTube movies in Google Reader

Hunting around on YouTube recently, I came across a video that I was interested in - "Hubble Space Telescope - Chapter 1", uploaded by Cronoslogic. What's even better, it was part of a series. Even better again, the titles of the videos in the series were consistent, differing only by index numbers. The next one is "Hubble Space Telescope - Chapter 2": you get the picture.

It's nice when you get that, but for all of YouTube's mind-boggling riches, I just find it overwhelming. Too much stuff. Sometimes I'm happy to click from video to video on a drunken walk further and further from my starting point, but I usually feel a sense of frustration that the organisation of all that gold, all that stuff that growing up I would have killed for, isn't easier to grasp. Every click on a related video reveals another branch of this bewilderingly rich and complex tree of content that I don't have time to climb. I feel like a chimp who has started to climb a tree only to realise it's a city and I'm a homeless urchin travelling backwards in time on a faraway planet.

Maybe it's because I'm a programmer that I began to wonder how I could tame that "Hubble" list. How could I access those videos without having to click around the related videos list on the right-hand side of the page? Unless Cronoslogic has made a playlist consisting of only those videos, I'd have to make one manually, I think. I mean, I'm not an authority on navigating YouTube, but that's what it seemed like I'd have to do.

As it happened, in this case he had made a playlist. This guy is a seriously organised power-YouTube user. Now you can subscribe pretty easily, in an RSS reader, or app like Google Reader, to a YouTube playlist, as this WebApps StackExchange question shows. And as I point out in my answer to that question, you want to use the "base" version of the feed rather than the "api" version:
You can find this by playing around with the YouTube API demo page, but as I say, make sure to select the ATOM/RSS "projection", which gives you the base in the URL. This makes a big difference in Reader, as you get a nice formatted preview:

But of course, the title of this post refers to an "arbitrary" feed, and there's nothing arbitrary about a list of videos already organised into a playlist. Let's get arbitrary. Which means a search. Say I only want to see videos that are about that other famous eye in the sky, the Spitzer space telescope, of a reasonable length (duration=medium: longer than 4 minutes but not longer than 20). HD would be nice too: no iPhone video thanks (hd=true). Newest videos should show first (orderby=published).

Using the YouTube API I can manufacture a feed URL like this:"spitzer+space+telescope"&duration=medium&hd=true&restriction=AU&orderby=published&v=2
The ordering is necessary because the default is to order videos by 'relevance'. I just want the newest at the top. That's how feed readers usually operate, and since I'm customising this API call to act like an RSS feed, then reverse chronological order is the go. If you don't specify otherwise the number of videos returned will be 25, which is fine in my case. If it was 100, then I could add "&max-results=25" to minimise unread (in this case unwatched) items and thereby avoid the dreaded nagging bold number of infamy that feed readers love to inflict. And the main search phrase ("spitzer space telescope") must be in quotes, as is usual for a Google search, to be preserved in its integrity for the search.

You have to specify the version ("v=2") because "If your request does not specify an API version, YouTube will handle your request using API version 1." I find that weird: you'd think they would default to the latest version for a version-agnostic request. The "restriction=AU" part means that you can be sure it won't include videos that are blocked to Australian viewers.

Here are some other example YouTube feeds I subscribe to.
Any videos to do with ASP.Net MVC3. I put the Asp in there too because MVC3 in YouTubeland has more to do with the testosterone-addled shenanigans of (yes, you have to shout it) MARVEL VS. CAPCOM 3 than the refined, chappish pursuit of programming in the ASP.NET MVC framework."official+video"&author=subpoprecords&restriction=AU&v=2
Official videos from Sub Pop Records.
Episodes of "The What If Machine" by gamespot.
One of the main purposes of doing all this, for me at least, is so I can watch YouTube videos via a video podcast - a "vodcast" if you will - catcher - a "vodcatcher", again if you will - such as Doggcatcher, which you can run on an Android tablet for instance. This is très convenient. You get home from work, flop down on the couch, swat away irritating questions from your wife and children, and consume art and popular science directly with your eyes!