Entity Framework change connection at runtime

Question

I have a web API project which references my model and DAL assemblies. The user is presented with a login screen, where he can select different databases.

I build the connection string as follows:

    public void Connect(Database database)
    {
        //Build an SQL connection string
        SqlConnectionStringBuilder sqlString = new SqlConnectionStringBuilder()
        {
            DataSource = database.Server,
            InitialCatalog = database.Catalog,
            UserID = database.Username,
            Password = database.Password,
        };

        //Build an entity framework connection string
        EntityConnectionStringBuilder entityString = new EntityConnectionStringBuilder()
        {
            Provider = database.Provider,
            Metadata = Settings.Default.Metadata,
            ProviderConnectionString = sqlString.ToString()
        };
    }

First of all, how do I actually change the connection of the data context?

And secondly, as this is a web API project, is the connection string (set at login per above) persistent throughout the user's interaction or should it be passed every time to my data context?

Dorothadorothea

i added a little alternative in case it fitted into your mindset/toolbox requirements. – Julide

@Ivan-Mark How did you solved this part And secondly, as this is a web api project, is the connectionstring (set at login per above) persistent throughout the user's interaction or should it be passed everytime to my datacontext – Crackling

@NarendraSinghRathore The connection strings are stored in a config file with the database name (or something else) being the key. The user selects a database on login and it's stored in a cache where the key might be the username. The user makes a request passing his username as a header and the connectionstring is retrieved and passed to the datacontext. – Dorothadorothea

@Ivan-MarkDebono Can you explain this cache ? Are you using memorycache or session at backend or storing as cookie at frontend. Thanks! – Crackling

@NarendraSinghRathore MemoryCache in a singleton – Dorothadorothea

Answer 1

A bit late on this answer but I think there's a potential way to do this with a neat little extension method. We can take advantage of the EF convention over configuration plus a few little framework calls.

Anyway, the commented code and example usage:

extension method class:

public static class ConnectionTools
{
    // all params are optional
    public static void ChangeDatabase(
        this DbContext source,
        string initialCatalog = "",
        string dataSource = "",
        string userId = "",
        string password = "",
        bool integratedSecuity = true,
        string configConnectionStringName = "") 
        /* this would be used if the
        *  connectionString name varied from 
        *  the base EF class name */
    {
        try
        {
            // use the const name if it's not null, otherwise
            // using the convention of connection string = EF contextname
            // grab the type name and we're done
            var configNameEf = string.IsNullOrEmpty(configConnectionStringName)
                ? source.GetType().Name 
                : configConnectionStringName;

            // add a reference to System.Configuration
            var entityCnxStringBuilder = new EntityConnectionStringBuilder
                (System.Configuration.ConfigurationManager
                    .ConnectionStrings[configNameEf].ConnectionString);

            // init the sqlbuilder with the full EF connectionstring cargo
            var sqlCnxStringBuilder = new SqlConnectionStringBuilder
                (entityCnxStringBuilder.ProviderConnectionString);

            // only populate parameters with values if added
            if (!string.IsNullOrEmpty(initialCatalog))
                sqlCnxStringBuilder.InitialCatalog = initialCatalog;
            if (!string.IsNullOrEmpty(dataSource))
                sqlCnxStringBuilder.DataSource = dataSource;
            if (!string.IsNullOrEmpty(userId))
                sqlCnxStringBuilder.UserID = userId;
            if (!string.IsNullOrEmpty(password))
                sqlCnxStringBuilder.Password = password;

            // set the integrated security status
            sqlCnxStringBuilder.IntegratedSecurity = integratedSecuity;

            // now flip the properties that were changed
            source.Database.Connection.ConnectionString 
                = sqlCnxStringBuilder.ConnectionString;
        }
        catch (Exception ex)
        {
            // set log item if required
        }
    }
}

basic usage:

// assumes a connectionString name in .config of MyDbEntities
var selectedDb = new MyDbEntities();
// so only reference the changed properties
// using the object parameters by name
selectedDb.ChangeDatabase
    (
        initialCatalog: "name-of-another-initialcatalog",
        userId: "jackthelady",
        password: "nomoresecrets",
        dataSource: @".\sqlexpress" // could be ip address 120.273.435.167 etc
    );

I know you already have the basic functionality in place, but thought this would add a little diversity.

Julide

This is great, thank you! I am able to use this on a multi-tenant project along with an extended Controller that will always set the 'db' of the controller to their customer specific db. This also frees me (or any future admins/developers) from having to create a new connection string for every client that gets added. – Shortwinded

yeah, i struggled for literally days trying to come up with a viable robust solution to this issue and this simple extension method answered my problems. since creating it back in nov last year, i've not had to make any changes to it, so i think it's well road tested as is :). anyway, glad it ticks a few boxes... good to talk. – Julide

I am getting this error System.ArgumentException: Keyword not supported: 'data source' in EF 4 – Calices

hi there, I'm afraid I haven't encountered this error. are you certain that you're creating the static class with the ChangeDatabase method?? it sounds like the extension method isn't being referenced. keep me posted if you've since resolved your issue of course – Julide

Does this work for migrations on the change database? – Cimino

Byron, I have to be perfectly honest, I've never tried. I guess setting up a test case and running it would answer your question. report back on your findings as it would b interesting to know. – Julide

@user1234 I also got the Error: Keyword not supported 'data source'. To solve this problem I had to change this part of his code: // add a reference to System.Configuration var entityCnxStringBuilder = new EntityConnectionStringBuilder { ProviderConnectionString = new SqlConnectionStringBuilder(System.Configuration.ConfigurationManager .ConnectionStrings[configNameEf].ConnectionString).ConnectionString }; – Fruge

@jbueno You're welcome. I couldn't format the comment very well. See this for better format: Link to Answer – Fruge

does this work for any type of database: oracle/mongo/mysql/sqlserver ? – Humanism

@jimtollan Every time that I create new instance it created from the old connection string that saved in app.config !! – Geibel

I am getting an error on that line as well. as @Fruge but the below solution to change it is erroring as well. Anyone else fixed this? – Ventris

Also did this work for anyone trying to read the tables? var a = selectedDb.Table1(// some where clause).FirstorDefault(); ? At this point it reverts back to whats in the webconfig and does not honor the ChageDatabase. – Ventris

lol - was looking for this in old code and did a google search and found my own answer -loverly!! :D – Julide

What about SQLite? – Agonized

Great answer! Worked like a charm for me. I am using "EntityFramework" version="6.4.4" – Ceolaceorl

This worked for me! I just simplified the ext. class like this and it works like a charm. Since the DBContext lifetime is scoped, this change only affects the current request pipeline only and the subsequent requests retain the original dbcontext. public static class DBContextDBChanger { public static void ChangeDatabaseConnection(this DbContext source, string configConnectionStringName) { source.Database.GetDbConnection().ConnectionString = configConnectionStringName; } } – Dux

Answer 2

DbContext has a constructor overload that accepts the name of a connection string or a connection string itself. Implement your own version and pass it to the base constructor:

public class MyDbContext : DbContext
{
    public MyDbContext( string nameOrConnectionString ) 
        : base( nameOrConnectionString )
    {
    }
}

Then simply pass the name of a configured connection string or a connection string itself when you instantiate your DbContext

var context = new MyDbContext( "..." );

Campinas

I did not realise that function already existed in my DbContext derived class so I just used that. – Methylene

I think this answer should mark as approved answer. – Bismuthous

This answer is great, but as @Hunnicutt explain. This class is autogenerated, so instead you should create another class based on this one so doesnt get overwrite if you update the model. – Zeidman

@JuanCarlosOropeza: EF cleverly marks generated classes (bot hcontext and entities) as partial, so you can create your own file, re-declare your DbContext therein (as partial) and add your custom functions in there. – Sortilege

Answer 3

Jim Tollan's answer works great, but I got the Error: Keyword not supported 'data source'. To solve this problem I had to change this part of his code:

// add a reference to System.Configuration
var entityCnxStringBuilder = new EntityConnectionStringBuilder
    (System.Configuration.ConfigurationManager
            .ConnectionStrings[configNameEf].ConnectionString);

to this:

// add a reference to System.Configuration
var entityCnxStringBuilder = new EntityConnectionStringBuilder
{
    ProviderConnectionString = new  SqlConnectionStringBuilder(System.Configuration.ConfigurationManager
               .ConnectionStrings[configNameEf].ConnectionString).ConnectionString
};

I'm really sorry. I know that I should't use answers to respond to other answers, but my answer is too long for a comment :(

Fruge

Answer 4

The created class is 'partial'!

public partial class Database1Entities1 : DbContext
{
    public Database1Entities1()
        : base("name=Database1Entities1")
    {
    }

... and you call it like this:

using (var ctx = new Database1Entities1())
      {
        #if DEBUG
        ctx.Database.Log = Console.Write;
        #endif

so, you need only create a partial own class file for original auto-generated class (with same class name!) and add a new constructor with connection string parameter, like Moho's answer before.

After it you able to use parametrized constructor against original. :-)

example:

using (var ctx = new Database1Entities1(myOwnConnectionString))
      {
        #if DEBUG
        ctx.Database.Log = Console.Write;
        #endif

Hunnicutt

Above solution is working for me. You can get more details from link – Akkad

Answer 5

You can do this on-the-fly with an IDbConnectionInterceptor. This has the advantage of allowing you to work with a standard connection string and not the Entity Client version, and also not having to modify the auto-generated context classes in an EDMX model, or using overloaded constructors. It just works!

We use this, for instance, to replace a tokenized connection string with a password from a secrets vault.

First, implement the interface. I'm only showing one of the many interface methods that will need to be implemented. In this case, I'm implementing ConnectionStringGetting, and leaving all other method bodies empty:

public class SecretsDbConnectionInterceptor : IDbConnectionInterceptor
{
    public void ConnectionStringGetting(DbConnection connection, DbConnectionInterceptionContext<string> interceptionContext)
    {
        var originalConnectionString = connection.ConnectionString;
        try
        {
            connection.ConnectionString = /* Build your new connection string */;
        }
        catch (Exception e)
        {
            connection.ConnectionString = originalConnectionString;
            Trace.WriteLine(e.Message);
        }
    }
    
    // ... Many other methods here; leave them empty
}

You can wire this up via your .config file; just add an <interceptor /> to the existing <entityFramework /> node with your new inteceptor's fully qualified type name:

  <entityFramework>
    <interceptors>
      <interceptor type="Foo.Bar.SecretsDbConnectionInterceptor, Foo.Bar" />
    </interceptors>
    ...
  </entityFramework>

Or, my personal preference, you can wire it up via code. It is equivalent to the config version. Ideally this would go in an Application_Startup in a service/UI project, or towards the top of Main in a console app, because it must run before you attempt to establish any new DbContexts:

DbInterception.Add(new Foo.Bar.SecretsDbConnectionInterceptor());

When you configure via code, you could pass parameters to your interceptor constructor, or use DI.

Note: the interceptor code runs every time you create a new instance of any DbContext in your application, so beware of performance impacts. You could implement some caching strategy within your interceptor, or make it a singleton instance with a context name/connection string mapping, or something smart like that.

Jacal

Answer 6

Add multiple connection strings in your web.config or app.config.

Then you can get them as a string like :

System.Configuration.ConfigurationManager.
    ConnectionStrings["entityFrameworkConnection"].ConnectionString;

Then use the string to set :

Provider
Metadata
ProviderConnectionString

It is better explained here :

Read connection string from web.config

Effulgence

the connection strings are stored in a separate sql server database and a list is presented to the user. – Dorothadorothea

Answer 7

string _connString = "metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string="data source=localhost;initial catalog=DATABASE;persist security info=True;user id=sa;password=YourPassword;multipleactiveresultsets=True;App=EntityFramework"";

EntityConnectionStringBuilder ecsb = new EntityConnectionStringBuilder(_connString);
ctx = new Entities(_connString);

You can get the connection string from the web.config, and just set that in the EntityConnectionStringBuilder constructor, and use the EntityConnectionStringBuilder as an argument in the constructor for the context.

Cache the connection string by username. Simple example using a couple of generic methods to handle adding/retrieving from cache.

private static readonly ObjectCache cache = MemoryCache.Default;

// add to cache
AddToCache<string>(username, value);

// get from cache

 string value = GetFromCache<string>(username);
 if (value != null)
 {
     // got item, do something with it.
 }
 else
 {
    // item does not exist in cache.
 }


public void AddToCache<T>(string token, T item)
    {
        cache.Add(token, item, DateTime.Now.AddMinutes(1));
    }

public T GetFromCache<T>(string cacheKey) where T : class
    {
        try
        {
            return (T)cache[cacheKey];
        }
        catch
        {
            return null;
        }
    }

Adrienneadrift

Yes but does the new connectionstring need to be passed to the dbcontext everytime the user calls a controller's action? – Dorothadorothea

You would probably dispose the context after each call, so yes. The context should only live for one request (Unit of Work). Explanation – Adrienneadrift

So how and where would I store the user's connectionstring for the duration of his session? (Many users can connect to the web api project and can have different connectionstrings) – Dorothadorothea

How about caching it, and retrieve it by username or some other key. – Adrienneadrift

Answer 8

In my case I'm using the ObjectContext as opposed to the DbContext so I tweaked the code in the accepted answer for that purpose.

public static class ConnectionTools
{
    public static void ChangeDatabase(
        this ObjectContext source,
        string initialCatalog = "",
        string dataSource = "",
        string userId = "",
        string password = "",
        bool integratedSecuity = true,
        string configConnectionStringName = "")
    {
        try
        {
            // use the const name if it's not null, otherwise
            // using the convention of connection string = EF contextname
            // grab the type name and we're done
            var configNameEf = string.IsNullOrEmpty(configConnectionStringName)
                ? Source.GetType().Name
                : configConnectionStringName;

            // add a reference to System.Configuration
            var entityCnxStringBuilder = new EntityConnectionStringBuilder
                (System.Configuration.ConfigurationManager
                    .ConnectionStrings[configNameEf].ConnectionString);

            // init the sqlbuilder with the full EF connectionstring cargo
            var sqlCnxStringBuilder = new SqlConnectionStringBuilder
                (entityCnxStringBuilder.ProviderConnectionString);

            // only populate parameters with values if added
            if (!string.IsNullOrEmpty(initialCatalog))
                sqlCnxStringBuilder.InitialCatalog = initialCatalog;
            if (!string.IsNullOrEmpty(dataSource))
                sqlCnxStringBuilder.DataSource = dataSource;
            if (!string.IsNullOrEmpty(userId))
                sqlCnxStringBuilder.UserID = userId;
            if (!string.IsNullOrEmpty(password))
                sqlCnxStringBuilder.Password = password;

            // set the integrated security status
            sqlCnxStringBuilder.IntegratedSecurity = integratedSecuity;

            // now flip the properties that were changed
            source.Connection.ConnectionString
                = sqlCnxStringBuilder.ConnectionString;
        }
        catch (Exception ex)
        {
            // set log item if required
        }
    }
}

Chaetognath

I got this error Keyword not supported: 'data source'. I am using EF 4 – Calices

Answer 9

I wanted to have multiple datasources in the app config. So after setting up a section in the app.config i swaped out the datasource and then pass it into the dbcontext as the connection string.

//Get the key/value connection string from app config  
var sect = (NameValueCollection)ConfigurationManager.GetSection("section");  
var val = sect["New DataSource"].ToString();

//Get the original connection string with the full payload  
var entityCnxStringBuilder = new EntityConnectionStringBuilder(ConfigurationManager.ConnectionStrings["OriginalStringBuiltByADO.Net"].ConnectionString);     

//Swap out the provider specific connection string  
entityCnxStringBuilder.ProviderConnectionString = val;

//Return the payload with the change in connection string.   
return entityCnxStringBuilder.ConnectionString;

This took me a bit to figure out. I hope it helps someone out. I was making it way too complicated. before this.

Transliterate

Answer 10

I have two extension methods to convert the normal connection string to the Entity Framework format. This version working well with class library projects without copying the connection strings from app.config file to the primary project. This is VB.Net but easy to convert to C#.

Public Module Extensions

    <Extension>
    Public Function ToEntityConnectionString(ByRef sqlClientConnStr As String, ByVal modelFileName As String, Optional ByVal multipleActiceResultSet As Boolean = True)
        Dim sqlb As New SqlConnectionStringBuilder(sqlClientConnStr)
        Return ToEntityConnectionString(sqlb, modelFileName, multipleActiceResultSet)
    End Function

    <Extension>
    Public Function ToEntityConnectionString(ByRef sqlClientConnStrBldr As SqlConnectionStringBuilder, ByVal modelFileName As String, Optional ByVal multipleActiceResultSet As Boolean = True)
        sqlClientConnStrBldr.MultipleActiveResultSets = multipleActiceResultSet
        sqlClientConnStrBldr.ApplicationName = "EntityFramework"

        Dim metaData As String = "metadata=res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl;provider=System.Data.SqlClient;provider connection string='{1}'"
        Return String.Format(metaData, modelFileName, sqlClientConnStrBldr.ConnectionString)
    End Function

End Module

After that I create a partial class for DbContext:

Partial Public Class DlmsDataContext

    Public Shared Property ModelFileName As String = "AvrEntities" ' (AvrEntities.edmx)

    Public Sub New(ByVal avrConnectionString As String)
        MyBase.New(CStr(avrConnectionString.ToEntityConnectionString(ModelFileName, True)))
    End Sub

End Class

Creating a query:

Dim newConnectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=DB;Persist Security Info=True;User ID=sa;Password=pass"

Using ctx As New DlmsDataContext(newConnectionString)
    ' ...
    ctx.SaveChanges()
End Using

Ureide

Answer 11

For both SQL Server and SQLite Databases, use:

_sqlServerDBsContext = new SqlServerDBsContext(new DbContextOptionsBuilder<SqlServerDBsContext>().UseSqlServer("Connection String to SQL DB").Options);

For SQLite, make sure Microsoft.EntityFrameworkCore.Sqlite is installed, then the connection string is simply "'DataSource='+ the file name".

_sqliteDBsContext = new SqliteDBsContext(new DbContextOptionsBuilder<SqliteDBsContext>().UseSqlite("Connection String to SQLite DB").Options);

Agonized

Answer 12

well if you are working with EFCore, Then You can do something like to create a new connection string: In Your Context File (For Sqlite)

public biorevContext(string connectionString) : base(GetOptions(connectionString))
    {
       this.Database.EnsureCreated();
    }
    private static DbContextOptions GetOptions(string connectionString)
    {
        return SqliteDbContextOptionsBuilderExtensions.UseSqlite(new DbContextOptionsBuilder(), connectionString).Options;
    }

For MySql:

 public biorevContext(string connectionString) : base(GetOptions(connectionString))
    {
       this.Database.EnsureCreated();
    }
    private static DbContextOptions GetOptions(string connectionString)
    {
        return MySQLDbContextOptionsExtensions.UseMySQL(new DbContextOptionsBuilder(), connectionString).Options;
    }

For Sql:

    public biorevContext(string connectionString) : base(GetOptions(connectionString))
    {
       this.Database.EnsureCreated();
    } 
    private static DbContextOptions GetOptions(string connectionString)
    {
        return SqlServerDbContextOptionsExtensions.UseSqlServer(new DbContextOptionsBuilder(), connectionString).Options;
    }

and Then You can use it like this:

        var context = new biorevContext("connectionString");

Chittagong

Answer 13

Linq2SQLDataClassesDataContext db = new Linq2SQLDataClassesDataContext();

var query = from p in db.SyncAudits orderby p.SyncTime descending select p;
Console.WriteLine(query.ToString());

try this code...

Dendrochronology