Dynamic Connection & Schema in EF

Source Code: GitHub

In this article, we will explore how to create a connection that can be modified at runtime with Entity Framework in a desktop application based on .NET Framework 4.8.

Database Structure

The previous image provides an overview of the database structure that we will use as a starting point for our example.

Creating the Context and Entities

Important: CodeFirst was used for creating the context in this example.

Before delving into the implementation of dynamic connections, it is essential to understand how the DbContext works in Entity Framework. This class has multiple constructors that allow us to configure our context, one of which accepts a connection string of type string that the context will use.

Therefore, we will add a constructor that allows us to pass the connection string as a parameter to our context.

public class MinimalDbContext : DbContext
{
    public MinimalDbContext (string connectionString)
        : base(connectionString)
    {
    }

    // More code
}

To set the value of the schema, we can store it in a private variable as follows:

public partial class MinimalDbContext : DbContext
{
    private readonly string _schema;

    public MinimalDbContext(string connectionString, string schema)
        : base(connectionString) => _schema = schema;

    // More code
}

The schema is defined in the OnModelCreating function that overrides our context.

public partial class MinimalDbContext : DbContext
{
    private readonly string _schema;

    public MinimalDbContext(string connectionString, string schema)
        : base(connectionString) => _schema = schema;

    // DbSet definitions

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema(_schema);

        // More code
    }
}

Funcionamiento del método OnModelCreating

How the OnModelCreating Method Works

The OnModelCreating method is used to initialize our context and is where we set our default schema for each of the entities.

Typically, this method (OnModelCreating) is called only once when the first instance of a derived context is created. The model for that context is then cached and is used for all further instances of the context in the app domain. — Microsoft

Since the OnModelCreating method is called only once and is cached, the schema value will not change once it has been initialized the first time (even if another instance of the context is created). Therefore, we need to establish a change in the cache according to our schema name. For this, we will use the interface called IDbModelCacheKeyProvider, which allows us to provide a key to the cache that should be the value of our schema so that the function is called when the schema changes.

public partial class MinimalDbContext
    : DbContext, IDbModelCacheKeyProvider
{
    private readonly string _schema;

    string IDbModelCacheKeyProvider.CacheKey
    {
        get => _schema;
    }

    public MinimalDbContext(string connectionString, string schema)
        : base(connectionString) => _schema = schema;

    // DbSet Definitions

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema(_schema);

        // More code
    }
}

Modifying the Entities

Entities created with CodeFirst usually have a reference to the schema to which the table belongs. These could be:

[Table("Product", Schema = "dbo")]
public partial class Product
{
    // Attributes
}

[Table("dbo.Product")]
public partial class Product
{
    // Attributes
}

It is necessary to remove these references so that they only refer to the table (by default, it will direct to dbo, but when setting the default schema, it will change in the entities that do not have a schema defined). The result should be:

[Table("Product")]
public partial class Product
{
    // Attributes
}

Implementation in Forms

To check the functionality of our code, a form was created with the basic fields of a connection string:

Data Source
Initial Catalog
User Id
Password

In addition, a field to set the schema.

public partial class FrmLogin : Form
{
    private readonly SqlConnectionStringBuilder _sqlConnectionStringBuilder;

    public FrmLogin()
    {
        InitializeComponent();
        _sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
    }

    private void BtnConnect_Click(object sender, EventArgs e)
    {
        _sqlConnectionStringBuilder.DataSource = txtServer.Text;
        _sqlConnectionStringBuilder.InitialCatalog = txtDatabase.Text;
        _sqlConnectionStringBuilder.UserID = txtUserId.Text;
        _sqlConnectionStringBuilder.Password = txtPassword.Text;

        var connectionString = _sqlConnectionStringBuilder.ToString();
        var schema = txtSchema.Text;
        using (var context = new MinimalDbContext(connectionString, schema))
        {
            try
            {
                var count = context.Product.Count();

                MessageBox.Show($"Products: {count}", "Success");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex?.InnerException?.Message ?? ex.Message, "Error");
            }
        }
    }
}

Results

Results of Product Count Requests with Schema_1 in the Database

Results of Product Count Requests with Schema_2 in the Database

Thank you for reading, and I hope this article was helpful. I wouldn’t have taken the time to write it if it weren’t for the lack of documentation out there, lol.

Source Code

Repository: GitHub

Dynamic Schema and Connection String Changes with Entity Framework in NET Framework 4.8