Dynamically Changing Entity Framework Connection String

Posted on 2012-08-27
Last Modified: 2013-01-28
I've been searching for a solution for a few hours now to what seems to be a very simple problem with no luck.

I'm using Visual Studio 2012,, .Net 4.5, Entity Framework 5, dbContext (generated from the designer) and SQL Server Express to create a web app.

I have 1 "MasterDb" that stores registration information for each client who signs up with my service and allows them to administer their account.  It also stores their "ClientId" which is a unique number assigned to each client.

Each client will have their own separate but identical database which will be dynamically created when they sign up.  Each database will have a name something like "ClientDb_0001", "ClientDb_0002", etc.  The trailing number will be the "ClientId" I mentioned.  What I need to do is override the connection string entity framework uses once the user logs in with my dynamically created string so that they can use their own database.  I've read some posts talking about passing a connection string to dbContext but I don't understand how this is possible.

The way I currently use ef is as follows:

Using dbContext As New myEntities
    Dim res = (from myVals In dbContext.myTable
                     where = someid
                     select myVal).FirstOrDefault
End Using

Open in new window

How in the world can I pass a connection string to this?

Thanks for any help anyone can provide,
Question by:thesnoman
    LVL 69

    Expert Comment

    by:Éric Moreau
    You need to override the CreateObjectContext as shown in

    Author Comment

    Thanks for the reply however this method doesn't seem to work for me.  When I attempt to override CreateObjectContext() of my entities I get the following error:

    function 'CreateObjectContext' cannot be declared 'Overrides' because it does not override a function in a base class

    I don't know if this is because I'm using EF 5 or if I'm just doing something wrong or what.
    LVL 83

    Expert Comment

    What class is your myEntities deriving from? I think there is an overloaded constructor on the base class which takes in the connection string. So you can add a constructor to your myEntities class which takes a connection string and passes it to the base class.

    Accepted Solution

    I've found what seems to be a solution. I build a connection string such as:

    Dim sqlBuilder As New SqlConnectionStringBuilder
        sqlBuilder.DataSource = clientDbDataSource
        sqlBuilder.InitialCatalog = "ClientDb_" & companyId
        sqlBuilder.IntegratedSecurity = True
        sqlBuilder.MultipleActiveResultSets = True
        sqlBuilder.ApplicationName = "EntityFramework"
    Dim ecs As New EntityConnectionStringBuilder
        ecs.Provider = "System.Data.SqlClient"
        ecs.ProviderConnectionString = sqlBuilder.ToString
        ecs.Metadata = "res://*/Model.ClientDb.csdl|res://*/Model.ClientDb.ssdl|res://*/Model.ClientDb.msl"

    Open in new window

    I then created the following class based off of some examples I found on the web:

    Public Class ClientDbEntities
        Inherits DbContext
        Public Sub New(ByVal connString As String)
        End Sub
    End Class

    Open in new window

    And now I can pass a connection string as follows:

    Using dbContext As New ClientDbEntities(ecs.ToString)
       Dim ui = (From cui In dbContext.client_custom_UserInfo
          Where cui.UserId = user.ProviderUserKey
          Select cui).FirstOrDefault
    End Using

    Open in new window

    I hope this helps someone else!  Is it ok to take the dbContext I'm creating and save it as a module scoped variable or would this cause the connection to the database to stay open?  I would continue to use "using" when I need it, I would just like to avoid recreating it each time it's used if possible.

    One thing I've found that I've not yet figured out is how to avoid issues if 2 databases have tables identically name and their structure is different.  My MasterDb database and ClientDb database both have membership tables in them and I added an additional table to each I called "custom_UserInfo".  The structure of each of these tables is different and it caused errors about that table not belonging to the current context.  Renaming one of them fixed the problem.  It appears to be a problem with Type creation as these tables show up as MyApplication.Tablename, there for I think it was basically a type mismatch if you will.
    LVL 19

    Expert Comment

    by:Amandeep Singh Bhullar
    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
    User art_snob ( encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now