Dynamically changing the connection string for WCF RIA Services Linq2Entities

One of the business requirements I get a lot is the ability to switch which database they use when they authenticate. I have just recently ported my applications over from Linq2Sql to Linq2Entties and I have noticed just enough difference that I figured I would blog about how to do this. I will also publish a subsequent post that shows how this is accomplished in Linq2Sql.

Okay, so you want to provide the ability to change the underlying connection string for a given WCF RIA Services request? The following is an example of creating a WCF RIA Services Class Library. I have also added an ADO.NET Entity Data Model as well a DomainService Class.

Data Access Project

One thing that you will notice, is that I also manually created the DataAccessDomainService.partial.cs. It is in this class that I implement the ability to switch connection strings. I have found that since the other files are code generated, that by having my logic in a partial class, I can bypass the headache when I need to update or change my model and domain service.

Note that I had to add the partial keyword to the main class in order to get my partial class to work. Also the main class derives from LinqToEntitiesDomainService where T is the model we are building our DomainService. In this case it would be DataAccessContext.

Let’s take a look at partial class now:

public partial class DataAccessDomainService
{
    /// <summary>
    /// We need override this function since we are dynamically changing the
    /// connection string based on the user's session information.
    /// </summary>
    /// <returns></returns>
    protected override DataAccessContext CreateObjectContext()
    {
        DataAccessContext ctx = null;
        if (!string.IsNullOrEmpty(ServiceContext.User.Identity.Name))
        {
            SecureLoginEntities sctx = new SecureLoginEntities();
            var userSession = (from c in sctx.sl_us_UserSession where
                c.sl_us_Token.HasValue.Equals(true) &&
                c.sl_us_Token.Value.Equals(new G
                    uid(ServiceContext.User.Identity.Name)) select
                c).FirstOrDefault();
            if (userSession != null)
            {
                sl_d_Database db = (from c in sctx.sl_d_Database where
                    c.sl_d_DatabaseIdent == userSession.sl_us_DatabaseId
                    select c).FirstOrDefault();
                if (db != null)
                {
                    string connString = EntityConnectionStringHelper.Build(
                        db.sl_d_ServerName,
                        db.sl_d_DatabaseName,
                        db.sl_d_UserName,
                        db.sl_d_Password,
                        "DataAccess");
                    ctx = new DataAccessContext(connString);
                }
            }
        }

        return ctx;
    }

};

Basically what I want to happen is that whenever there is a request to this service, I want to override the CreateContext method and change the connection string accordingly. I have some authentication logic that I am using that helps me determine what database the user wants to use but once I get past that I basically create a new instance of my context passing in the newly created connection string.

Note: The string “DataAccess” that I am passing into the EntityConnectionStringHelper.Build method above is actually from the Web.Config connection string. It represents the *.csdl, *.ssdl, *.msl names that are represented in the style that the Entity Framework requires.

In order to help me and get some code reuse, I created a helper class that helps me with buildng my connection string. This is one of the main differences between Linq2Sql and Linq2Entities.

Let’s look at this code.

public class EntityConnectionStringHelper
{
    public static string Build(
        string serverName,
        string databaseName,
        string userName,
        string password,
        string metaData)
    {
        // Specify the provider name, server and database.
        string providerName = "System.Data.SqlClient";
        return EntityConnectionStringHelper.Build(
            providerName,
            serverName,
            databaseName,
            userName,
            password,
            metaData);
    }
    public static string Build(
        string providerName,
        string serverName,
        string databaseName,
        string userName,
        string password,
        string metaData)
    {
        // Initialize the connection string builder for the
        // underlying provider.
        SqlConnectionStringBuilder sqlBuilder =
            new SqlConnectionStringBuilder();

        // Set the properties for the data source.
        sqlBuilder.DataSource = serverName;
        sqlBuilder.InitialCatalog = databaseName;
        sqlBuilder.IntegratedSecurity = false;
        sqlBuilder.UserID = userName;
        sqlBuilder.Password = password;

        // Build the SqlConnection connection string.
        string providerString = sqlBuilder.ToString();

        // Initialize the EntityConnectionStringBuilder.
        EntityConnectionStringBuilder entityBuilder =
            new EntityConnectionStringBuilder();

        //Set the provider name.
        entityBuilder.Provider = providerName;

        // Set the provider-specific connection string.
        entityBuilder.ProviderConnectionString = providerString;

        // Set the Metadata location.
        entityBuilder.Metadata = string.Format(@"res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl",
            metaData);

        return entityBuilder.ToString();
    }
};

Basically this class has two static methods called Build. One calls into the other passing in the providername. In order to correctly build a Linq2Entities connection string you have to also include the Metadata. This is the main difference between Linq2Sql and Linq2Entities. For this example, I am building connection strings for SQL Server. I am using the SqlConnectionStringBuilder class in conjunction with the EntityConnectionStringBuilder class.

When all is said and done, you will get a connection string that looks like the following example taken from my Web.config:

<configuration>
  <connectionStrings>

    <add name="DataAccessContext"
      connectionString="metadata=res://*/DataAccess.csdl|res://*/DataAccess.ssdl|
        res://*/DataAccess.msl;provider=System.Data.SqlClient;
        provider connection string=&quot;Data Source=bunkerhill;
        Initial Catalog=emgov_data;Integrated Security=False;
        User Id=emgovuser;Password=xxxx;
        MultipleActiveResultSets=True&quot;"
        providerName="System.Data.EntityClient" />

  </connectionStrings>
  ...
<configuration>

(NOTE: I put break carriage return in this so that you could read it better but it would not be like this in your Web.config file.)

By following this example, you can now provide the ability to switch between connection string dynamically. You can even go as far as storing the connection information in a database and build the connection string on the fly as well.

Hope this helps…