Explicit connection string for EF

The default constructor for the ObjectContext class in the  Entity Data Model (EDM) retrieves the construction string from the web.config or app.config file. If you have multiple data models or need to pass in the the construction string at run time you must use the ObjectContext constructor that takes a construction string. (Note: Be sure to see the bug/work-around at the end of this article.) The follow snippet shows how to build the string for integrated security.

 //  copy connection string from app.config or web.config  

    // connectionString="metadata=res://*;
    // provider=System.Data.SqlClient;
    // provider connection string="
    // Data Source=ricka0;Initial Catalog=Northwind;Persist Security Info=True;
    // User ID=sa;Password=*(IU89iu;MultipleActiveResultSets=True""
    // providerName="System.Data.EntityClient" 
    

    public static string UglyConStr() {

        return "metadata=res://*;"
          + "provider=System.Data.SqlClient;"
          + "provider connection string=';"  // Replace &quot with ' (single quote)
        + "Data Source=ricka0;"
        + "Initial Catalog=Northwind;"
        + "Persist Security Info=True;"
        + "User ID=sa;Password=*(IU89iu;"
        + "MultipleActiveResultSets=True';"  // Replace &quot with ' (single quote)
            //    + "providerName=\"System.Data.EntityClient\""
        ;
    }

While the construction string above works, it's very ugly. The hair pulling trick to get it working is replacing &quot with a single quote ' as shown in the comments. Using  the raw connection string from the config file (using &quot ) results in a misleading error message Keyword not supported: 'data source' .  

A much more elegant construction string using SQL connection is shown below.

  public static string getConStrSQL() {

        string connectionString = new System.Data.EntityClient.EntityConnectionStringBuilder
        {
            Metadata = "res://*",
            Provider = "System.Data.SqlClient",
            ProviderConnectionString = new System.Data.SqlClient.SqlConnectionStringBuilder
            {
                InitialCatalog = "Northwind",
                DataSource = "ricka0",
                IntegratedSecurity = false,
                UserID = getUID(),                 // User ID such as "sa"
                Password = getPWD(),               // hide the password
            }.ConnectionString
        }.ConnectionString;

        return connectionString;
    }

In VB:

 Public Shared Function getConStrSQL() As String
     Dim connectionString As String = New System.Data.EntityClient.EntityConnectionStringBuilder() _
         With {.Metadata = "res://*", _
               .Provider = "System.Data.SqlClient", _
               .ProviderConnectionString = New System.Data.SqlClient.SqlConnectionStringBuilder() _
                 With {.InitialCatalog = "Northwind", _
                       .DataSource = "ricka0", _
                       .IntegratedSecurity = False, _
                       .UserID = getUID(), _
                       .Password = getPWD()}.ConnectionString}.ConnectionString
     Return connectionString
 End Function

The integrated security approach is slightly different.

 public static string getConStrIntegrated() {

        string conStrIntegratedSecurity = new System.Data.EntityClient.EntityConnectionStringBuilder
           {
               Metadata = "res://*",
               Provider = "System.Data.SqlClient",
               ProviderConnectionString = new System.Data.SqlClient.SqlConnectionStringBuilder
               {
                   InitialCatalog = "NorthwindEF",
                   DataSource = "bing0",
                   IntegratedSecurity = true,
               }.ConnectionString
           }.ConnectionString;

        return conStrIntegratedSecurity;
    }

 For Dynamic Data, simply pass the construction string to the MetaModel RegisterContext as follows. 

  public static void RegisterRoutes(RouteCollection routes) {
        MetaModel model = new MetaModel();


        model.RegisterContext(() => new NorthwindModel.NorthwindEntities(getConStrIntegrated()),
            new ContextConfiguration()
            {
                ScaffoldAllTables = true
            });

        // Routes omitted for clarity
    }

Unfortunately, the current version of Dynamic Data doesn't support this approach with EF (L2S does work). To get the page templates to use the connection string you must add the following line to the Page_Load method in the page templates.

 GridDataSource.ContextCreating += delegate(object ceSender, System.Web.UI.WebControls.EntityDataSourceContextCreatingEventArgs ceArgs) {
            ceArgs.Context = (System.Data.Objects.ObjectContext)table.CreateContext();
        };

The complete Page_Load is below

 protected void Page_Load(object sender, EventArgs e) {
        table = GridDataSource.GetTable();

         GridDataSource.ContextCreating += delegate(object ceSender, System.Web.UI.WebControls.EntityDataSourceContextCreatingEventArgs ceArgs) {
            ceArgs.Context = (System.Data.Objects.ObjectContext)table.CreateContext();
        }; 

        Title = table.DisplayName;
        GridDataSource.Include = table.ForeignKeyColumnsNames;
        InsertHyperLink.NavigateUrl = table.GetActionPath(PageAction.Insert);

        // Disable various options if the table is readonly
        if (table.IsReadOnly) {
            GridView1.Columns[0].Visible = false;
            InsertHyperLink.Visible = false;
        }
    }