Entity Framework non null foreign key migration

Overview

This post gives a quick overview of how to use Entity Framework migrations and a detailed example of how to handle the addition of a new non null foreign keyed column to an existing table with data in it. Not a trivial process as you will see.
Full source code is provided, there are three versions of the same solution, one for each of the phases described below.

Introduction

Having source control over your SQL database has been a challenge for a while, I've seen companies with the attitude - the database is the source control, and they were not joking! I've seen a few companies use a dedicated SQL directory within their source code for table creation, stored proc and seeding scripts and this worked ok, but it seemed to put up a barrier between development and dbeng.

You can also go down the route of a dedicated piece of software that is supposed to manage it all for you but they are expensive, and in my experience, buggy.

Entity Framework Migrations

Enter Entity Migrations to fill the gap! It allows you to use your source code as the source control for your database! If you haven’t already worked with EF code first, you should probably stop here and review that topic. I've been using EF migrations for a while, there are plenty of tutorials out there but none deal with the scenario I’m handling.

The Problem

I have a Vehicle class, and a TyreType class. This is my starting structure, after my first migration I will have tables like those shown here. Assume that the application has been used for a while and there will be data in both tables.

Initial DB layout

Now I want to add a TyreColor class which will be referenced by the TyreType class.

public class TyreType
    {
        public int TyreTypeID { get; set; }

        //snip

        public int TyreColorId { get; set; }
        public virtual TyreColor TyreColor { get; set; }
    }

    public class TyreColor
    {
        public TyreColor()
        {
            this.TyreTypes = new List();
        }
        public int TyreColorId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }

        public virtual ICollection TyreTypes { get; set; }
    }

There is also some plumbing in the context, you can see that in the source code provided.

This is a pretty standard one to many relationship. In the database it would look like -

DB layout after addition

The tricky part is dealing with the existing data in TyreType. I want to add a non null column, but I can’t because there are already rows of data.

The Solution

The quick answer is - firstly add a nullable TyreColorId column, then set some default value in all existing rows, then set the column to non null for future inserts. If that is enough to help you, good, if not, see the detailed explanation below.

Phase 1 - standard EF Migration creating the first two tables.

Step 1

Create the app, add the models, the context etc.

Go to the package manager console and type -

Enable-Migrations

Enable Migrations

This creates Migrations/Configuration.cs file, I'll come back to this shortly.

Step 2

Add a migration for the models in your project.

Back in the package manager type -

Add-Migration InitialCreate

This adds a file named something like - 201504280200272_InitialCreate.cs to the Migrations directory. Inside this file are Up() and Down() methods. The Up() will create the tables, set the primary key, create the foreign key and create an index.

And Down() drops the foreign key, index and tables.

Step 3

Before I push these changes to the database I will add some seeding code in Configuration.cs

        protected override void Seed(DataAccess.AutomobileContext context)
        {
            context.TyreType.AddOrUpdate(tt => new { tt.Name },
                 new TyreType { Name = "Very Fast Tyre", Material = "Very Fast Rubber" },
                 new TyreType { Name = "Fast Tyre", Material = "Fast Rubber" },
                 new TyreType { Name = "Very Slow Tyre", Material = "Very Slow Rubber" },
                 new TyreType { Name = "Slow Tyre", Material = "Slow Rubber" }
                 );
        }

Back in the package manager call -

Update-Database

The db and tables are created and the seeder run.

Tyre Type Table

Phase 2 – Adding the new table and foreign key fields


After creating this initial schema I added a TyreColor to the TyreType.

I want to make TyreColorId a required filed on TyreType and this is where everything starts to get complicated.
I’ve already seeded the TyreType with four rows and now I want to add a new column that is non null and will act as a foreign key. This is a problem, the existing four rows will have empty TyreColorId columns and SQL server won’t allow this.

To overcome this I first add the new column as a nullable, set a default value in the existing rows, and finally set the column to non null.

Step 1

Add TyreColorId and TyreColor to TyreType

  public class TyreType
    {
	   //snip
        public int TyreColorId { get; set; }

        public virtual TyreColor TyreColor { get; set; }
    }

In the package manger run -

Add-Migration AddingTyreColor

This adds a new migration, but I’m going to alter the Up() and Down() methods –

        public override void Up()
        {
            CreateTable(
                "dbo.TyreColor",
                c => new
                    {
                        TyreColorId = c.Int(nullable: false, identity: true),
                        Name = c.String(),
                        Description = c.String(),
                    })
                .PrimaryKey(t => t.TyreColorId);

            AddColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: true));
           
            //Insert a default TyreColor
            Sql("Insert INTO dbo.TyreColor (Name, Description) VALUES ('Black', 'Very black')"); 
            
            //Set all null TyreType.TyreColorId to the default
            Sql("UPDATE dbo.TyreType SET TyreColorId = 1 WHERE TyreColorId IS NULL"); 

            AddForeignKey("dbo.TyreType", "TyreColorId", "dbo.TyreColor", "TyreColorId", cascadeDelete: true);
        }

        public override void Down()
        {
            DropForeignKey("dbo.TyreType", "TyreColorId", "dbo.TyreColor");
            DropColumn("dbo.TyreType", "TyreColorId");
            DropTable("dbo.TyreColor");
        }

See the inline notes for details.

I also updated the seed method

        protected override void Seed(DataAccess.AutomobileContext context)
        {
            context.TyreColor.AddOrUpdate(tc => tc.Name,
                 new TyreColor { Name = "Black", Description = "Black" },
                 new TyreColor { Name = "Gray", Description = "A little black" },
                 new TyreColor { Name = "White", Description = "Very white" },
                 new TyreColor { Name = "Black/White", Description = "A mix of black and white" }
                 );

            context.SaveChanges();
            
            // grab the one that was added
            TyreColor firstTyreColor = context.TyreColor.First();  
            
            context.TyreType.AddOrUpdate(tt => new { tt.Name, tt.TyreColorId },
                 new TyreType { Name = "Very Fast Tyre", Material = "Very Fast Rubber", TyreColorId = firstTyreColor.TyreColorId },
                 new TyreType { Name = "Fast Tyre", Material = "Fast Rubber", TyreColorId = firstTyreColor.TyreColorId },
                 new TyreType { Name = "Very Slow Tyre", Material = "Very Slow Rubber", TyreColorId = firstTyreColor.TyreColorId },
                 new TyreType { Name = "Slow Tyre", Material = "Slow Rubber", TyreColorId = firstTyreColor.TyreColorId }
                 );
        }

In the package manager run,

Update-Database

The changes are applied and the seed method is called (I’ve changed the seed method a little, you can see that in the source code).

I now have very close to the table structure I want, I just need to set the TyreColorId column in TryeType to non nullable and add an index.

Phase 3 – Making the new column non null


In the package manger run -

Add-Migration AlterTyreColorToNonNull

This will create a new migration with empty Up() and Down() methods.
Add the following

        public override void Up()
        {
            AlterColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: false));
            CreateIndex("dbo.TyreType", "TyreColorId"); 
        }
        
        public override void Down()
        {
            DropIndex("dbo.TyreType", new[] { "TyreColorId" });
            AlterColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: true));
        }

Now I have the structure I want

Data In Tables

And the data I want

Final Table Structure

Full source code is provided.

Skipping ActionFilters in ASP.NET MVC

In the previous post I showed how you can use an action filter to execute code both before and after an action method and how to apply the filter globally.

I found myself in a scenario where I wanted to run the action filter almost globally. I needed to exclude all actions methods in one controller and one action method in another controller.

This is easily done with the use of an Attribute, see here for more.

Create a SkipImportantTaskAttribute like so –

  public class SkipImportantTaskAttribute : Attribute {}

I then decorate the relevant action methods or controllers with this attribute.
To skip an entire controller do this –

    [SkipImportantTask]
    public class CustomersController : Controller

To skip just a single action method do this –

	[SkipImportantTask]
	public ActionResult Index()

Change the OnActionExecuted (or OnActionExecuting) method of the action filter to the following

 
public override void OnActionExecuted(ActionExecutedContext filterContext)
{
	bool skipImportantTaskFilter = filterContext.ActionDescriptor.ControllerDescriptor.IsDefined(typeof(SkipImportantTaskAttribute), true) ||
		filterContext.ActionDescriptor.IsDefined(typeof(SkipImportantTaskAttribute), true);

	if (!skipImportantTaskFilter)
	{
		PerformModelAlteration(filterContext);
	}

	base.OnActionExecuted(filterContext);
}

Now if SkipImportantTaskAttribute is present on the controller or action method the action filter code will not be executed.

Full source code here.

Altering the ASP.NET MVC model with an ActionFilter

Action filters are executed before and/or after the execution of an action method, they can be used to modify how an action executes or to perform a separate task, such as authorization or logging.

Action filters can be applied globally, at the controller level, or on an action method.

See here for more details.

In this post I’ll deal with how action filters allow you intercept an outgoing response to change a value in the returned model.

Action filter

Create a filter that inherits from ActionFilterAttribute and override the OnActionExecuted method.

public override void OnActionExecuted(ActionExecutedContext filterContext)

From the filterContext we can get model.

var model = filterContext.Controller.ViewData.Model as BaseModel;

Any value in the BaseModel can now be changed, in the example provided I change the MeetingDate property.

This action filter to needs to be applied to the appropriate action methods.
I’m going to register the filter globally, in this way it will run on all executed action methods.

Go to the FilterConfig class to register the filter.

public static void RegisterGlobalFilters(GlobalFilterCollection filters)
{
	filters.Add(new HandleErrorAttribute());
	filters.Add(new ModelAlterFilterAttribute());
}

That is all you need to do to create an action filter, register it and to alter the returned model.

As an aside, if you want to alter the value of an incoming parameter you override the OnActionExecuting method of the action filter.
In this example customerID is just an int but this approach will work for complex models too.

public override void OnActionExecuting(ActionExecutingContext filterContext)
{
	var actionParams = filterContext.ActionParameters;

	object customerID;
	actionParams.TryGetValue("customerid", out customerID);
	if (customerID != null)
	{
		customerID = 222;
	}

	base.OnActionExecuting(filterContext);
}

In the next post I show how you to selectively disable a global action filter on specific action methods or controllers.

Full source code here.

Serializing a stack to XML in C#

Serializing a class to XML in C# is straightforward; most collections can be easily serialized also.

Something like the following will work in most scenarios –

public string GetSerializedString<T>(T objectToSerialize)
{
    var serializer = new XmlSerializer(typeof(T));
    TextWriter textWriter = new StringWriter();

    var xmlWriter = XmlWriter.Create(textWriter);
    serializer.Serialize(xmlWriter, objectToSerialize);

    string result = textWriter.ToString();
    return result;
}

This also works nicely with lists, arrays and the like. But if you want to serialize a stack to XML you get an InvalidOperationException
You must implement a default accessor on System.Collections.Generic.Stack`1[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] because it inherits from ICollection.

There might be some third party libraries out there that will work but I’m going to stick with Microsoft technologies.

So I need to add an accessor to a stack, but a quick look at the System.Collections.Generic.Stack class using dotPeek shows that the underlying collection is a private array.

namespace System.Collections.Generic
{
    public class Stack<T> : IEnumerable<T>, ICollection, IEnumerable
    {
        private T[] _array;
    ...snip...

I could inherit from the Stack class, access the private array through reflection and add the required interface and methods to my class, or I could write my own stack class, but I don’t like these approaches.
An alternative is to start with a collection that can be serialized to XML successfully and add the required stack functionality, peek, pop and push, to it. This a relatively straightforward approach, is easy to understand and provides a familiar interface. I chose to use the List as my base class.

using System.Collections.Generic;
using System.Linq;

namespace SerializingStack
{
    public class StackList<T> : List<T>
    {
        #region Public Methods

        public void Push(T historyItem)
        {
            Add(historyItem);
        }

        public T Pop()
        {
            if (this.Any())
            {
                T historyItem = base[Count - 1];
                Remove(historyItem);
                return historyItem;
            }

            return default(T);
        }

        public T Peek()
        {
            if (this.Any())
            {
                return base[Count - 1];
            }
            return default(T);
        }

        #endregion
    }
}

A problem with this approach is that order that items come out of the enumerator is the opposite from a traditional stack, but as long as the stack is accessed using peek, pop and push it will work the same. Compromises have to be accepted with mixing the features of two different types.

After deserialization

If you have never deserialized a stack then you can just use the StackList with no problems.

If you are used to working with stack, you’ll know that a stack create from another Stack will have its contents reversed. To overcome this you can call the Reverse method on the new stack. This is due to the way the stack constructor is written.

public Stack(IEnumerable<T> collection)
{
    ...snip...
    ICollection<T> collection1 = collection as ICollection<T>;
    if (collection1 != null)
    {
        ...snip...
        collection1.CopyTo(this._array, 0);
        ...snip...
    }
}

If you want to overcome this issue you will need to implement your own enumerator functionality, this is not very difficult, but I didn’t need to do it for my purposes so I’m not including it here.

Conclusions

Be careful as you create your own types that inherit from Microsoft types, especially when you alter how parts of them work!
Every step you take away from the standard approach you are getting in to trouble, it might not be apparent immediately, but it will catch up with you.
This StackList is a cross between and a stack and list that behaves in some particular/peculiar ways that suit me for a for a limited purpose, I don’t plan to use this code widely.

Deserializing to an enum

I have an application where I deserialize an xml stream from a third party. One of the fields is a task code which can be one of only three values, insert, update and delete. This is an obvious candidate for an enum.

public enum TaskCode
{
    Delete,
    Insert,
    Update
}

This is looks good but relies on the xml stream using the same capitalization as shown above, however my incoming xml stream used all caps. I could have changed my enums to all caps, but I’d end up with some ugly lines of code.

Fortunately the XmlEnum attribute solves this problem.

public enum TaskCode
{
    [XmlEnum("DELETE")] Delete,
    [XmlEnum("INSERT")] Insert,
    [XmlEnum("UPDATE")] Update
}

See http://msdn.microsoft.com/en-us/library/system.xml.serialization.xmlenumattribute.aspx for more.

Adaptive/dynamic page numbering in c#

If you need to show many results (tens, hundreds or thousands), in a paged manner, you won’t want to display links to all those pages. I looked online for some c# that would adapt the number of page links shown to with the number of pages returned, but found nothing.

If you get 5 pages of results, show them all. If you get 5,000 pages of results, show some around your current page and less and less the further you get from your current page.

This is something I put together in an hour, I make no claims about its efficiency and there are scenarios that are not catered for (like higher current page than the total number of pages).

Here are some examples of what this code does.

Current Page:1 Total Pages:40 Pages to display around current page:5
1 2 3 4 5 6 7 8 9 10 11 20 30 40

Current Page:90 Total Pages:600 Pages to display around current page:5
1 40 50 60 70 80 85 86 87 88 89 90 91 92 93 94 95 100 110 120 130 140 200 300 400 500 600

Current Page:147 Total Pages:6825 Pages to display around current page:5
1 90 100 110 120 130 140 142 143 144 145 146 147 148 149 150 151 152 160 170 180 190 200 300 400 500 600 700 800 900 1000 1100 2000 3000 4000 5000 6000 6825

You could easily change the code to take a Page type to suit your own needs.

Here’s the code.

namespace NoDogmaBlog
{
    public class PagingHelper
    {
        public IEnumerable<int> GetListOfPages(int currentPage, int pagesAroundCurrent, int totalPages)
        {
            var pages = new Dictionary<int, int>();
            double powerOfTenTotalPages = Math.Floor(Math.Log10(totalPages));
            if ((int)powerOfTenTotalPages == 0)
            {
                powerOfTenTotalPages = 1;
            }
            pages.Add(1, 1);
            if (!pages.ContainsKey(totalPages))
            {
                pages.Add(totalPages, totalPages);
            }

            for (int loop = 1; loop <= powerOfTenTotalPages + 1; loop++)
            {
                GetPages(pages, currentPage, pagesAroundCurrent, totalPages, (int)Math.Pow(10, loop - 1));
            }
            return pages.OrderBy(k=>k.Key).Select(p=>p.Key).AsEnumerable();
        }

        private void GetPages(Dictionary<int, int> pages, int currentPage, int pagesAroundCurrent, int totalPages, int jump)
        {
            int startPage = ((currentPage / jump) * jump) - (pagesAroundCurrent * jump);

            if (startPage < 0)
            {
                startPage = 0;
                pagesAroundCurrent = 10;
            }

            int endPage = currentPage + (pagesAroundCurrent * jump);
            if (endPage > totalPages)
            {
                endPage = totalPages;
            }
            AddPagesToDict(pages, startPage, endPage, jump);
        }

        private void AddPagesToDict(Dictionary<int, int> pages, int start, int end, int jump)
        {
            for (int loop = start; loop <= end; loop += jump)
            {
                if (!pages.ContainsKey(loop))
                {
                    if (loop > 0)
                    {
                        pages.Add(loop, loop);
                    }
                }
            }
        }
    }
}

Drop down lists in ASP.NET MVC

This post shows two methods of implementing drop down lists in ASP.NET MVC 4. The code for data access and the general layout of the application should not be considered suitable for anything other than pedagogical purposes.

The provided source code uses entity framework and requires a local database to be running, see the web.config for naming.

The main components of the application are Vehicle and TyreType classes, and controllers for each.
The controllers have the standard Index/Create/Edit/Details/Delete views and actions.
The VehicleController shows two ways of providing a TyreType drop down list, one using the ViewBag and the other using a VehicleViewModel.

The TyreType is as follows.

using System.ComponentModel.DataAnnotations;

namespace Automobile.Models
{
    public class TyreType
    {
        public int TyreTypeID { get; set; }
        [Required]
        public string Name { get; set; }
        [Required]
        public string Material { get; set; }
    }
}

And the Vehicle looks like this.

using System.ComponentModel.DataAnnotations;

namespace Automobile.Models
{
    public class Vehicle
    {
        public int VehicleID { get; set; }
        [Required]
        public string Name { get; set; }
        public string Description { get; set; }
        public double MSRP { get; set; }
        [Required]
        public int TyreTypeID { get; set; }
        public virtual TyreType TypeType { get; set; }
    }
}

The TyreTypeController is a standard controller with text boxes for entering information on the Edit and Create views.

The VehicleController has a drop down list filled with available tyre types as found in the database.

Using the ViewBag

The first method of filling the drop down list is to use the ViewBag.

In the [HttpPost]Edit method we have –

ViewBag.TyreTypeID = new SelectList(db.TyreType, "TyreTypeID", "Name", vehicle.TyreTypeID);

I’ll explain in detail what this line of code is doing –
ViewBag.TyreTypeID – is referencing the ViewBag and dynamically adding an entry called TyreTypeID.

new SelectList(db.TyreType, "TyreTypeID", "Name", vehicle.TyreTypeID) – is doing five things –

  1. new SelectList – creates a new SelectList(this is what drop down lists use)
  2. db.TyreType – passing in the TypeTypes from the database
  3. "TyreTypeID" – specifying that this public property of TyreType will be used for dataValueField
  4. "Name" – specifying that this public property of TyreType will be used for dataTextField
  5. vehicle.TyreTypeID – specifying the selected value in the drop down list, note that it is the same property as the dataValueField

Inside the TyreType Edit view we have –

@model Automobile.Models.Vehicle
…snip…
   <div class="editor-label">
      @Html.LabelFor(model => model.TyreTypeID, "Type Type")
   </div>
   <div class="editor-field">
      @Html.DropDownList("TyreTypeID","--Select a tyre type--")
      @Html.ValidationMessageFor(model => model.TyreTypeID)
   </div>

In this example the "TyreTypeID" specifies both the source of items for the drop down list and the destination property for the selected value that will be sent back to the controller. In the example, the Vehicle is sent to the Edit action, so the Vehicle. TyreTypeID is set with the selected value.

I found this very confusing at first because I wanted to change the name of the ViewBag property for storing the drop down list items, so my selected value was being lost. MVC is doing a lot by convention over coding, if you fight it you will have to learn many rules.

I’m not a big fan of this approach, too many things are happening without my explicit control and if forces me to use the same name for a list of items (the SelectList in the ViewBag) and the selected item (an int).

Using a ViewModel

One alternative is to create a ViewModel for the Vehicle and the values for the drop down list. Note again, I’m not advocating this methodology for data access for production code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
using Automobile.DataAccess;
using Automobile.Models;

namespace Automobile.ViewModels
{
    public class VehicleViewModel
    {
        private AutomobileContext db = new AutomobileContext();
        public IEnumerable<SelectListItem> TyreTypes { get; set; }
        public Vehicle Vehicle { get; set; }
        public VehicleViewModel(Vehicle vehicle)
        {
            Vehicle = vehicle;
            TyreTypes = PopulateTyreTypes();
        }
        private IEnumerable<SelectListItem> PopulateTyreTypes()
        {
            var tyreTypesQuery = db.TyreType.OrderBy(t => t.Name);
            return new SelectList(tyreTypesQuery,"TyreTypeID","Name");
        }
    }
}

Inside the VehicleController I have

public ActionResult Create()
{
   //we're going to use the view model to send the tyre types to the view here.
   var vehicleViewModel = new VehicleViewModel(new Vehicle());
   return View(vehicleViewModel);
}
   
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create(Vehicle vehicle)
{
   if (ModelState.IsValid)
   {
      db.Vehicle.Add(vehicle);
      db.SaveChanges();
      return RedirectToAction("Index");
   }
   var vehicleViewModel = new VehicleViewModel(vehicle);
   return View(vehicleViewModel);
}

And the view has

@model Automobile.ViewModels.VehicleViewModel
…snip…
<div class="editor-label">
   @Html.LabelFor(model => model.Vehicle.TyreTypeID, "Tyre Type")
</div>
<div class="editor-field">
   @Html.DropDownListFor(model => model.Vehicle.TyreTypeID, Model.TyreTypes, "--Select a tyre type--" )
   @Html.ValidationMessageFor(model => model.Vehicle.TyreTypeID)
</div>

@Html.DropDownListFor(model => model.Vehicle.TyreTypeID, Model.TyreTypes, "--Select a tyre type--" ) is doing three things –

  1. model => model.Vehicle.TyreTypeID – specifies where the selected value from the DDL is stored
  2. Model.TyreTypes – is the source of SelectListItems for the DDL
  3. "--Select a tyre type--" – is a default value

This is the approach I prefer. Full source code is a attached here.

Entity Framework in an Dynamics Nav (Navision) environment (or using EF to access tables with unknown table names)

Summary
In this post I should how to use dynamic compilation to allow Entity Framework to reference tables whose names are not known at compile time.

Details
Entity Framework works great for the majority of scenarios; it handles tables in one database or spread across multiple databases very well.  Let’s say you have a site that sells products online for small companies you would need tables like company, customer, order, product, invoice etc, you would probably put all your data into a single set of tables and EF will work just fine.

However, if you have ever worked Microsoft Dynamics Nav (Navision) you know that it does not follow this standard structure.

Dynamics segregates the data differently. Each company had its own set of customer, order, product, invoice, etc, tables which were prefixed with the company name. For example, if you had two companies called ACME and SuperCorp the table structure would be like the following –

[ACME$Customer]
[ACME$Order]
[ACME$Product]
[ACME$Invoice]
[SuperCorp$Customer]
[SuperCorp$Order]
[SuperCorp$Product]
[SuperCorp$Invoice]

EF does not play nicely with this.

My first attempt was to pass in the table prefix to my DbContext when mapping, this worked for the first company passed in, but when I create a new DbContext for the second company, it would give model errors because somewhere deep in the workings of EF, something was being set statically and the mapping was failing (it was still pointing to the first company). If I created another new DbContext for the first company it worked fine again.

My second attempt was to pass in a DbCompiledModel for the company when constructing  the DbContext

DbContext(string nameOrConnectionString, System.Data.Entity.Infrastructure.DbCompiledModel model)

This worked a bit better than the first attempt, I could create a DbContext for the first and second and first (again) companies. I could retrieve data, but as soon as a there was a join in the query it failed, again it looked like something was being set the first time I created the DbContext and this was not changing when for the second company. I wasn’t happy with this approach anyway as it required a significant step away from well known EF practices of mapping and constructing a context.
My third approach was to create a context file specifically for each company which would inherit from a base context that contained all the DbSets and column mappings, table mapping was done in each individual context. This approached worked for all database queries, but has the huge issue of requiring a recompile and redeployment for each new company; a working, but impractical approach.

Building on this I came up with my solution. I created a CommonDBContext, which has the DbSets and the OnModelCreating where the table prefix is passed to the Mapping classes.
A DynamicContext class that is used to dynamically compile and construct DbContexts for each of the companies as needed. It also caches the compiled DbContext so you only take the compilation hit once for the lifetime of the application.

public abstract class CommonDBContext : DbContext
{

private readonly string tablePrefix;

protected CommonDBContext(string connectionString, string tablePrefix) :base(connectionString)
    {
        this.tablePrefix = tablePrefix;
    }

    public DbSet Customer { get; set; }
    public DbSet Order { get; set; }
    public DbSet Product { get; set; }
    public DbSet Invoice { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new CustomerMap(tablePrefix));
        modelBuilder.Configurations.Add(new OrderMap(tablePrefix));
        modelBuilder.Configurations.Add(new ProductMap(tablePrefix));
        modelBuilder.Configurations.Add(new InvoiceMap(tablePrefix));
    }
}

class DynamicContext
{
    private readonly static Dictionary<string, Type> CompiledContexts = new Dictionary<string, Type>();
    private readonly string _classNamePrefix;
    private static readonly object LockObject = new object();
    private readonly string _sourceCode =
            @"
            using System.Data.Entity;
            namespace Implementation.Context
            {{
                public class {0}_Context : CommonDBContext
                {{
                  public {0}_Context(string connectionString, string tablePrefix)
                        : base(connectionString, tablePrefix)
                    {{
                        Database.SetInitializer<{0}_Context>(null);
                    }}
                }}
            }}";

    #region Constructors

    public DynamicContext(string classNamePrefix)
    {
        _sourceCode = string.Format(_sourceCode, classNamePrefix);
        _classNamePrefix = classNamePrefix;
    }

    #endregion

    #region Public Methods

    public CommonDBContext GetContext(string connectionString, string tablePrefix)
    {
        Type[] constructorTypes = new Type[2] { typeof(string), typeof(string) };

        Type compiledType;
        //Double lock to prevent multiple compilations of same code
        if (!CompiledContexts.TryGetValue(_classNamePrefix, out compiledType))
        {
            lock (LockObject)
            {
                if (!CompiledContexts.TryGetValue(_classNamePrefix, out compiledType))
                {
                    compiledType = CompileContext(_classNamePrefix);
                    CompiledContexts.Add(_classNamePrefix, compiledType);
                }
            }
        }

        CommonDBContext contextObject = GetInstanceOfCompiledContext(connectionString, tablePrefix, compiledType, constructorTypes);
        return contextObject;
    }

    #endregion

    #region Private Methods

    private CommonDBContext GetInstanceOfCompiledContext(string connectionString, string tablePrefix, Type compiledContext, Type[] constructorTypes)
    {
        ConstructorInfo contextConstructorInfo = compiledContext.GetConstructor(constructorTypes);
        object contextObject = contextConstructorInfo.Invoke(new object[] { connectionString, tablePrefix });
        return contextObject as CommonDBContext;
    }

    ///
<summary>
    /// This is a one time hit for each class compiled.
    /// </summary>

    /// <param name="assemblyNames"></param>
    /// <returns></returns>
    private string[] GetAssembly(string[] assemblyNames)
    {
        string [] locations = new string[assemblyNames.Length];

        for (int loop = 0; loop <= assemblyNames.Length - 1; loop++) { locations[loop] = AppDomain.CurrentDomain.GetAssemblies().Where(a => !a.IsDynamic && a.ManifestModule.Name == assemblyNames[loop]).Select(a => a.Location).First();
        }
        return locations;
    }

    private Type CompileContext(string classNamePrefix)
    {
        var compilerParameters = new CompilerParameters { GenerateInMemory = true };

        compilerParameters.ReferencedAssemblies.AddRange(GetAssembly(new string[] { "Implementation.dll", "EntityFramework.dll" }));
        var provider = new CSharpCodeProvider();
        var compiled = provider.CompileAssemblyFromSource(compilerParameters, _sourceCode);

        Type compliedType = compiled.CompiledAssembly.GetType(string.Format("Implementation.Context.{0}_Context", classNamePrefix));
        return compliedType;
    }

    #endregion

}

public class CustomerMap : EntityTypeConfiguration
{
    public CustomerMap(string tablePrefix)
    {
        // Primary Key
        this.HasKey(t => t.CustomerID);
        //snip

        // Map the table name
        string tableName = string.Format("{0}$Customer", tablePrefix);
        this.ToTable(tableName)
    }
}

If you have trouble getting this working, get in touch.

SSN checking and formatting

Here’s something I worked on a few days ago. It shows six ways of verifying that an incoming string has nine numeric characters and then returns a string in the standard social security number format. In this example I perform the SSN checking and formatting using: static and instances of Regex; compiled and non-compiled; string replacements and match evaluators. More details on these can be found here.


using System.Text.RegularExpressions;

namespace NoDogmaBlog
{
  public class SSNFormatter
  {
    private const string IncomingFormat = @"^(d{3})(d{2})(d{4})$";
    private const string OutgoingFormat = "$1-$2-$3";
    readonly Regex regexNotCompiled = new Regex(IncomingFormat);
    readonly Regex regexCompiled = new Regex(IncomingFormat, RegexOptions.Compiled);

    #region Static
    public static string StaticStringRepleacement(string ssnInput)
    {
      var result = Regex.Replace(ssnInput, IncomingFormat, OutgoingFormat);
      return result;
    }

    public static string StaticMatchEvaluatorReplacement(string ssnInput)
    {
      var result = Regex.Replace(ssnInput, IncomingFormat, m => m.Groups[1] +
        "-" + m.Groups[2] + "-" + m.Groups[3]);
      return result;
    }
    #endregion

    #region NotCompiled
    public string InstanceNotCompiledStringReplacement(string ssnInput)
    {
      var result = regexNotCompiled.Replace(ssnInput, OutgoingFormat);
      return result;
    }

    public string InstanceNotCompiledMatchEvaluatorReplaement(string ssnInput)
    {
      var result = regexNotCompiled.Replace(ssnInput, m => m.Groups[1] +
        "-" + m.Groups[2] + "-" + m.Groups[3]);
      return result;
    }
    #endregion

    #region Compiled
    public string InstanceCompiledStringReplacement(string ssnInput)
    {
      var result = regexCompiled.Replace(ssnInput, OutgoingFormat);
      return result;
    }

    public string InstanceCompiledMatchEvaluatorReplaement(string ssnInput)
    {
      var result = regexCompiled.Replace(ssnInput, m => m.Groups[1] + "-"
        + m.Groups[2] + "-" + m.Groups[3]);
      return result;
    }
    #endregion
  }
}

I ran these methods on 10,000,000 randomly generated nine digit strings. I consistently observed results similar to those shown below.

Results

MethodTime
StaticStringRepleacement00:00:16.0028520
StaticMatchEvaluatorReplacement00:00:17.5301894
InstanceNotCompiledStringReplacement00:00:11.6908033
InstanceNotCompiledMatchEvaluatorReplaement00:00:13.8301780
InstanceCompiledStringReplacement00:00:09.1909727
InstanceCompiledMatchEvaluatorReplaement00:00:11.5331829
Be aware that using a compiled regular expression will suffer from a certain amount of overhead when compiling the expression. This overhead should be taken into consideration when writing shortlived applications.