Adding a lookup field to an existing table using Entity Framework code first migrations

As you build out a site using the code first approach, at some point you will most likely need to add an additional lookup field to a class. There are a couple of steps required above what’s automatically done for you by EF code first migrations to make things work happily.

If you consider the following simple class

class Client
    {
        public int ID { get; set; }
        public string ClientName { get; set; }
        public string ClientAddress { get; set; }
    }

If you add a migration at this point you’ll end up with something like

public partial class InitialSetup : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Clients",
                c => new
                    {
                        ID = c.Int(nullable: false, identity: true),
                        ClientName = c.String(),
                        ClientAddress = c.String(),
                    })
                .PrimaryKey(t => t.ID);

        }

        public override void Down()
        {
            DropTable("Clients");
        }
    }

Once this class has been added to the database and you have existing data, adding another non nullable lookup field can be tricky. When you add your lookup and it tries to add the lookupID field to the existing Clients table, it’ll fail because the existing data will fail referential integrity due to their being no data in the lookup table.

Once we added a lookup named ClientStatus to the model, it would look like

class Client
    {
        public int ID { get; set; }
        public string ClientName { get; set; }
        public string ClientAddress { get; set; }
        public int ClientStatusID { get; set; }

        public virtual ClientStatus ClientStatus { get; set; }
    }

    class ClientStatus
    {
        public int ClientStatusID { get; set; }
        public int ClientStatusText { get; set; }
    }

and our migration code will look like

public override void Up()
        {
            CreateTable(
                "ClientStatus",
                c => new
                    {
                        ClientStatusID = c.Int(nullable: false, identity: true),
                        ClientStatusText = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.ClientStatusID);

            AddColumn("Clients", "ClientStatusID", c => c.Int(nullable: false));
            AddForeignKey("Clients", "ClientStatusID", "ClientStatus", "ClientStatusID", cascadeDelete: true);
            CreateIndex("Clients", "ClientStatusID");
        }

        public override void Down()
        {
            DropIndex("Clients", new[] { "ClientStatusID" });
            DropForeignKey("Clients", "ClientStatusID", "ClientStatus");
            DropColumn("Clients", "ClientStatusID");
            DropTable("ClientStatus");
        }

The problem lies between the CreateTable and the AddColumn commands. The lookup table will be created, but the ClientStatusID non nullable field can’t be added to the Clients table as there are no records on the ClientStatus table.

To get around this in my code, I am tweaking the Up function to insert a default row into the lookup table and assigning the value of 1 to the default value of the ClientStatusID field in the Clients table.

I’m adding

Sql("INSERT INTO ClientStatus (ClientStatusName) VALUES ('Active')");

and updating

AddColumn("Clients", "ClientStatusID", c => c.Int(nullable: false));

to be

AddColumn("Clients", "ClientStatusID", c => c.Int(nullable: false, defaultValue:1));

As we are just creating the ClientStatus Table, we can reasonably assume that the first record created will have an CLientStatusID of 1.

Telerik ASP.NET MVC controls and jQuery

For those who are using the Telerik controls for MVC you will most likely have the following somewhere on your page or Master page

<%= Html.Telerik().ScriptRegistrar() %>

This will create issues if you then try to add any jQuery to the page including jQuery plugins.

To get around this, you can append a method to the Telerik ScriptRegistrar to disable the jQuery reference. You need to change it to

<%= Html.Telerik().ScriptRegistrar().jQuery(false) %>

And your page should be happy again.

Getting random records from a table using LINQ to SQL

I needed to get a number of random records from a table and a quick google search led me to http://mosesyap.com/BlogWeb/post/2008/07/C-Getting-a-Random-Record-from-a-Table-inside-the-SQL-Server-Database.aspx

This is a very quick and easy mechanism to use and will work on any table.

To load a specific number of random records change the .First() function

DataContext db = new QuoteDataContext();
var quote = db.Quote.OrderBy(q => db.GetNewId()).First();

to be .Take(x)

DataContext db = new QuoteDataContext();
var quote = db.Quote.OrderBy(q => db.GetNewId()).Take(x);

replacing x with however many records you need.