Dynamically Passing Connection String in a Model-First Approach

1. Create a Parameterized Constructor in Your Entity Context

First, you need to create a parameterized constructor in your DbContext (Entity Data Model) class. This allows you to pass the connection string dynamically.

In your generated DbContext class (for example, NorthwindEntities), add a constructor that accepts a connection string.

public class NorthwindEntities : DbContext
{
    // Overloaded constructor to accept a dynamic connection string
    public NorthwindEntities(string sqlServerConnectionString)
        : base(sqlServerConnectionString)
    {
    }

    // Default constructor (if needed)
    public NorthwindEntities()
        : base("name=NorthwindEntities") // Fallback to app.config connection string
    {
    }
}

2. Retrieve the Connection String from Configuration

Next, you need to retrieve the connection string dynamically from an external source, like a configuration file (App.config or Web.config), or it could even be passed as a parameter at runtime.

To retrieve the connection string from App.config:

// Import necessary namespaces
using System.Configuration;
using System.Reflection;
using System.IO;

// Example method to get the connection string dynamically
public string GetConnectionString()
{
    Assembly me = Assembly.GetExecutingAssembly();
    Configuration config = ConfigurationManager.OpenExeConfiguration(
        Path.Combine(
            this.ExecutionContext.DeploymentDirectory,
            "Bin\\Debug\\", // Adjust if needed for your project structure
            me.ManifestModule.Name
        )
    );

    // Get the connection string from the App.config file
    string conn = config.ConnectionStrings.ConnectionStrings["MRM_LatestEntities"].ConnectionString;
    return conn;
}

3. Pass the Connection String to the DbContext

After retrieving the connection string, you can pass it to the DbContext constructor when initializing it.

// Get connection string
string conn = GetConnectionString();

// Instantiate the DbContext with the dynamic connection string
NorthwindEntities context = new NorthwindEntities(conn);

// Now you can perform queries
var items = context.Categories.AsQueryable();
var count = items.Count();

Ensure that your App.config or Web.config contains the appropriate connection string definition:

<configuration>
  <connectionStrings>
    <add name="NorthwindEntities" 
         connectionString="your_connection_string_here" 
         providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

By following the above approach, you can dynamically pass the connection string to your Entity Framework model in a Model-First approach, allowing for more flexibility, especially when the connection string needs to change based on different environments or runtime conditions.