Entity Framework MigrateDatabaseToLatestVersion giving error

Question

I am attempting to use Entity Framework code based migrations with my web site. I currently have a solution with multiple projects in it. There is a Web API project which I want to initialize the database and another project called the DataLayer project. I have enabled migrations in the DataLayer project and created an initial migration that I am hoping will be used to create the database if it does not exist.

Here is the configuration I got when I enabled migrations

public sealed class Configuration : DbMigrationsConfiguration<Harris.ResidentPortal.DataLayer.ResidentPortalContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
    }

    protected override void Seed(Harris.ResidentPortal.DataLayer.ResidentPortalContext 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" }
        //    );
        //
    }
}

The only change I made to this after it was created was to change it from internal to public so the WebAPI could see it and use it in it's databaseinitializer. Below is the code in the code in the Application_Start that I am using to try to initialize the database

Database.SetInitializer(new MigrateDatabaseToLatestVersion<ResidentPortalContext, Configuration>());
new ResidentPortalUnitOfWork().Context.Users.ToList();

If I run this whether or not a database exists I get the following error

Directory lookup for the file "C:\Users\Dave\Documents\Visual Studio 2012\Projects\ResidentPortal\Harris.ResidentPortal.WebApi\App_Data\Harris.ResidentPortal.DataLayer.ResidentPortalContext.mdf" failed with the operating system error 2(The system cannot find the file specified.).

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

It seems like it is looking in the totally wrong place for the database. It seems to have something to do with this particular way I am initializing the database because if I change the code to the following.

Database.SetInitializer(new DropCreateDatabaseAlways<ResidentPortalContext>());
new ResidentPortalUnitOfWork().Context.Users.ToList();

The database will get correctly created where it needs to go.

I am at a loss for what is causing it. Could it be that I need to add something else to the configuration class or does it have to do with the fact that all my migration information is in the DataLayer project but I am calling this from the WebAPI project?

Answer 1

I have figured out how to create a dynamic connection string for this process. You need to first add this line into your EntityFramework entry on Web or App.Config instead of the line that gets put there by default.

<defaultConnectionFactory type="<Namespace>.<ConnectionStringFacotry>, <Assembly>"/>  

This tells the program you have your own factory that will return a DbConnection. Below is the code I used to make my own factory. Part of this is a hack to get by the fact that a bunch of programmers work on the same set of code but some of us use SQL Express while others use full blown SQL Server. But this will give you an example to go by for what you need.

public sealed class ResidentPortalConnectionStringFactory: IDbConnectionFactory 
{
    public DbConnection CreateConnection(string nameOrConnectionString)
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["PortalDatabase"].ConnectionString);

        //save off the original catalog
        string originalCatalog = builder.InitialCatalog;

        //we're going to connect to the master db in case the database doesn't exist yet
        builder.InitialCatalog = "master";
        string masterConnectionString = builder.ToString();

        //attempt to connect to the master db on the source specified in the config file
        using (SqlConnection conn = new SqlConnection(masterConnectionString))
        {
            try
            {
                conn.Open();
            }
            catch
            {
                //if we can't connect, then append on \SQLEXPRESS to the data source
                builder.DataSource = builder.DataSource + "\\SQLEXPRESS";
            }
            finally
            {
                conn.Close();
            }
        }

        //set the connection string back to the original database instead of the master db
        builder.InitialCatalog = originalCatalog;

        DbConnection temp = SqlClientFactory.Instance.CreateConnection();
        temp.ConnectionString = builder.ToString();

        return temp;
    }
}

Once I did that I coudl run this code in my Global.asax with no issues

Database.SetInitializer(new MigrateDatabaseToLatestVersion<ResidentPortalContext, Configuration>());
using (ResidentPortalUnitOfWork temp = new ResidentPortalUnitOfWork())
{
    temp.Context.Database.Initialize(true);
}