Pass Connection String At Run Time to Entity Framework

A few days ago I was tasked to implement a Web API application to fetch and expose data using the Entity Framework, Cool!! It's the latest technology but there was a little tweak in that. The connection string to fetch the data from the database will come from another service.

Hmm, It was DB first approach application because our database was already built and we just needed to write code to process the data. We know that Entity Framework automatically generates a connection string at the time of model creation , if you closely look at the connection string you will find that there is a little more information within the connection string, the string is not a straight-forward connection string as we see generally for the ADO.NET environment.

Here is one example for your better understanding.

<add name="efDBEntities"
connectionString="metadata=res://*/testModel.csdl|res://*/testModel.ssdl|res://*/testModel.msl;
provider=System.Data.SqlClient;
provider connection string="
data source=SOURAV-PC;initial catalog=efDB;
user id=sourav;password=password;
MultipleActiveResultSets=True;
App=EntityFramework""
providerName="System.Data.EntityClient" />

Just have a look at that metadata property and all the other stuff in there along with username, password, initialCatalog and data source name.

If you want to build your own connection string by setting all those properties at run time then here is the solution. Just open the context file of Entity Framework and modify the code as in the following. In this example we have implemented a Singleton class to supply the connection string. The reason is, when the first request hits a controller to fetch data, the connection string will be formed and it will be used by every subsequent request.

namespace WebAPI
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Core.EntityClient;
    using System.Data.Entity.Infrastructure;
    using System.Data.SqlClient;
    public class SingleConnection
    {
        private SingleConnection(){}
        private static SingleConnection _ConsString = null;
        private String _String = null;
        public static string ConString
        {
            get
            {
                if (_ConsString == null)
                {
                    _ConsString = new SingleConnection { _String = SingleConnection.Connect() };
                    return _ConsString._String;
                }
                else
                  return _ConsString._String;
            }
        }
        public static string Connect()
        {
            //Build an SQL connection string
            SqlConnectionStringBuilder sqlString = new SqlConnectionStringBuilder()
            {
                DataSource = "SOURAV-PC", // Server name
                InitialCatalog = "efDB",  //Database
                UserID = "sourav",         //Username
                Password = "mypassword",  //Password
            };
            //Build an Entity Framework connection string
            EntityConnectionStringBuilder entityString = new EntityConnectionStringBuilder()
            {
                Provider = "System.Data.SqlClient",
                    Metadata =   "res://*/testModel.csdl|res://*/testModel.ssdl|res://*/testModel.msl",
                ProviderConnectionString = sqlString.ToString()
            };
            return entityString.ConnectionString;
        }
    }
    public partial class efDBEntities : DbContext
    {
        public efDBEntities() : base(SingleConnection.ConString)
        {
            //"name=efDBEntities"
        }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
    }
}

In the example we have used the Connect() function to populate all the properties, if needed then the credentials can be pulled from another application.

Thanks, Happy learning.