Entity Framework Code-First in a “semi-production” context

Lately I used Entity Framework Code First in a “semi-production” context, not in an application, but for managing Load Tests. The scenario was I had a Load Test, based on a unit test (not a web test) and I had to manage a pool of users so that a single user wasn’t used at the same time by 2 instances of the unit test. Because the Load Test ran in a multi-agent scenario, this had to be accessible by all the agents running the unit test, thus a Database approach.

Preparing the Database in SQL Server

Entity Framework Code First will create and manage the Database for you. But in this context, I will be accessing the Database through a connection string using a SQL user, because not all the machines running agents are in the same domain, so going through a trusted connection isn’t an option. The SQL user will have access to the Database for the Load Test, but won’t have access to the master Database.

The first thing you need to do is to create the Database, because using the EF connection string would result in an authorization error when creating the Database.

After creating the Database setup the SQL user so that it can connect to it, read, write and manage the schema.

The Entity Framework Model and Context

The very first thing you need to do is add the Entity Framework NuGet package to your solution, this can be done either by going through the NuGet package manager:

Or just by opening the package manager console and typing in Install-Package EntityFramework

After that, create your model and your context. For our scenario we just needed an object User that has a UserName key, a static password that’s not going to the Database, a boolean InUse and a DateTime timestamp ReleasedOn so that we can ask for users that have been released by the unit test for the longest time.

namespace EFCodeFirstStart.Model
{
    using System;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    public class User
    {
        [Key]
        public string UserName { get; set; }

        [NotMapped]
        public string Password { get { return "myStaticPassword"; } }

        public bool InUse { get; set; }

        public DateTime ReleasedOn { get; set; }
    }
}

The context is really simple (we’ll get back to it later on in the post). You need to inherit from DbContext and you are implementing the default constructor to call the DbContext constructor that takes a connection string name so that you can point to the Database previously created.

namespace EFCodeFirstStart.Model
{
    using System.Data.Entity;

    public class LoadTestDataContext : DbContext
    {
        public LoadTestDataContext() : base("name=EFConnectionString") { }

        public DbSet<User> Users { get; set; }
   }
}

Creating the Database from the Model – Code First approach

Make sure that the connection string that you’re using on the DbContext contructor is configured in the app.config or web.config of your application:

<connectionStrings>
  <add name="EFConnectionString" providerName="System.Data.SqlClient" connectionString="Server=localhost; Database=LoadTestData; User=efcodefirst; Password=mypwd1234" />
</connectionStrings>

The first step is enabling Code First migrations for your application, this must be done for every project type with the following command Enable-Migrations:

When this step is executed a new folder Migrations and a Configuration.cs file will be created. The Configuration.cs file is one of the points where control is given to the developer in the Code First approach.

namespace EFCodeFirstStart.Migrations
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;
    using System.Linq;

    internal sealed class Configuration : DbMigrationsConfiguration<EFCodeFirstStart.Model.LoadTestDataContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
        }

        protected override void Seed(EFCodeFirstStart.Model.LoadTestDataContext context)
        {
            //  This method will be called after migrating to the latest version.

            //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
            //  to avoid creating duplicate seed data. E.g.
            //
            //    context.People.AddOrUpdate(
            //      p => p.FullName,
            //      new Person { FullName = "Andrew Peters" },
            //      new Person { FullName = "Brice Lambson" },
            //      new Person { FullName = "Rowan Miller" }
            //    );
            //
        }
    }
}

You then need to add migrations every time we want to snapshot the Database schema, so let’s do one now and call it InitialSetup by running the command Add-Migration InitialSetup:

This will create another file on the Migrations folder with a timestamp followed by _InitialSetup (the name you gave to the Migration):

namespace EFCodeFirstStart.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    
    public partial class InitialSetup : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Users",
                c => new
                    {
                        UserName = c.String(nullable: false, maxLength: 128),
                        InUse = c.Boolean(nullable: false),
                        ReleasedOn = c.DateTime(nullable: false),
                    })
                .PrimaryKey(t => t.UserName);
            
        }
        
        public override void Down()
        {
            DropTable("dbo.Users");
        }
    }
}

In a normal application scenario we would be done, as Entity Framework will handle the Database updates on every run, extra commands are only needed if we need to revert or do other extra work on the migrations. However, because I had to run this from a LoadTest project, the Database update has to be done manually, by calling Update-Database on the package manager console

Where did the EDMX go?

If you’re like me, you do a lot of code generation based on the ADO.NET EDMX model. So far Code First looked really nice and I was liking it a lot, but without the EDMX I don’t have a good source for writing templates against.

The folks at the Entity Framework team created the ability to Save the EDMX file in the framework, so we just need to call that every time we are changing the model (calling Update-Database). This is done by overriding the OnModelCreating method:

namespace EFCodeFirstStart.Model
{
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Text;
    using System.Xml;

    public class LoadTestDataContext : DbContext
    {
        public LoadTestDataContext() : base("name=EFConnectionString") { }

        public DbSet<User> Users { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            var provider = new DbProviderInfo("System.Data.SqlClient", "2008");
            var model = modelBuilder.Build(provider);

            var writer = new XmlTextWriter(@"D:\TmpEdmx\my.edmx", Encoding.UTF8);
            EdmxWriter.WriteEdmx(model, writer);
        }
    }
}

Code First thoughts

So far I liked using Code First, seemed smooth, flexible and lean, making model and schema change iterations a breeze. With the added support to generate the EDMX everything is in place to do code generation like we used to do with Model First approaches.