CODEWITHSUNDEEP
c#sql-serverdatabaseentity-framework
Complexity
Complexity

Reputation: 5830

Using Entity Framework Code First with a dynamic connection string

I'm writing an application using Entity Framework (the latest version - 6.0 as we speak) and the latest .NET version (version 4.5.1 as we speak).

The problem that I'm facing is that I want to use a code-first approach because I really like it, but the application consists out of various databases. Now, it's not a problem to add the migrations and updating the database(s). I know the command to do this, even when Entity Framework needs to track multiple context objects.

But, here's the specific situation which I'm dealing with:

That does mean that I have a settings table in which 5 users can be configured, but each user does need another database (but the structure of the database is the same over every database). There are very specific reasons why we're using different databases even if the model is the same but I will not go in detail about that.

Now, the problem is on how I need to define my second context in my Visual Studio Project. I know that on the constructor of my DbContext I can pass a Connection String, so no problem at all during runtime, but when I'm developing, I'm also using the NuGet package manager to manage the migrations of my database.

Is there any way on how I can upgrade all the databases at once (or one at a time), but by taking the connection strings which are stored in the database (because it depends on the user)?

Kind regards

Upvotes: 2

Views: 1124

Answers (1)

Complexity
Complexity

Reputation: 5830

Ok, I've managed to find a solution myself and I'm quite happy with it.

First, I'll lay out the structure of the application.

I do have a DbContext called AppServerSettingsContext which is defined in the source code like this:

/// <summary>
///     Initializes a new instance of the <see cref="AppServerSettingsDataContext"/>.
/// </summary>
public AppServerSettingsDataContext()
    : base("AppServerSettingsDataContext")
{ }

This context has 2 different entities (one for a member and one for all the settings for that specific member).

In order for the migration to be able to execute, I need to have a ConnectionString in the applications configuration file, just like the way we used.

Then, I have another context named AppServerDataContext. This has 2 constructors as showed below:

/// <summary>
///     Initializes a new instance of the <see cref="AppServerDataContext"/>.
/// </summary>
public AppServerDataContext() : 
    this(ConfigurationManager.ConnectionStrings["AppServer"].ConnectionString) 
{ }

/// <summary>
///     Initializes a new instance of the <see cref="AppServerDataContext"/>.
/// </summary>
/// <param name="connectionString">The full connection string which is used to connect to the database.</param>
public AppServerDataContext(string connectionString)
    : base(connectionString) { }

You'll see in the code that I can either specify the connection string or I load the connection string for the application configuration file.

You'll see why this is important later on.

I have the DbContext which points to a connection string inside a configuration file. It's not required to have this, but I'm used to work that way. So that connection string will ONLY be used when calling the add-migration command. This is because that command requires a database to check the current state of the database and add the correct migration.

Now, I'm working inside one single project with 2 context files, so the NuGet Package Manager Console needs a way to identify this.

Therefore, the following commands can be used:

  1. Enable migrations for a specific context:

    • `PM> Enable-migrations -ContextTypeName: -MigrationsDirectory:

This is a command which I need to execute twice, once for every context.

Then, in the Seed method of my AppServerSettings context, I'll write the following code:

/// <summary>
///     Runs after upgrading to the latest migration to allow seed data to be updated.
/// </summary>
/// <param name="context">Context to be used for updating seed data.</param>
protected override void Seed(AppServerSettingsDataContext context)
{
    // Creates the Member and assign all the settings which are required for the application to function.
    context.Members.AddOrUpdate(x => x.Name, new Member("Povlo")
    {
        Settings = new List<MemberSettings>
        {
            new MemberSettings("DatabaseConnectionString", "Removed for Security Reasons"),
        }
    });

    // Make sure that for every member, the database is created by using the "MigrateDatabaseToLatestVersion" migration.
    foreach (var setting in context.Members.Select(member => member.Settings.FirstOrDefault(x => x.Key == "DatabaseConnectionString")))
    {
        using (var appServerContext = new AppServerDataContext(setting.Value))
        {
            var de = new MigrateDatabaseToLatestVersion<AppServerDataContext, AppServer.Configuration>();
            de.InitializeDatabase(appServerContext);

            appServerContext.Database.Initialize(true);
        }
    }
}

What I do here basically is first creating a member with a given connection string (there can be multiple).

Then, in the same method, I do have a foreach loop, which will create a context based on the connection string in the database. Then for this context, the database is being upgraded to the latest version.

The advantage of this is that I'm using a code-first approach, and all the databases are always the latest version.

A disadvantage of this is that all models needs to be exactely the same.

Upvotes: 2

Related Questions