Entity Framework Code First - Connection with Database

This post of part of a series of posts in which we are discussing various features of entity framework code first. We have been building on an Institute project and adding stuff to it based on the feature discussed. You might have realized that we have never shown you the actual connection string, yet we have been interacting with database for persistence of the institute entities. In this post we will be discussing how we are doing that without even specifying the connection string. We will also be discussing how we can customize this default behavior.

Building Connection String:
As we have discussed, a big part of entity framework code first is learning the various conventions it uses. It also has such conventions while interacting with database. The first convention is about finding connection string from the app.config and building it if one is not found there.

Which Connection String to use from config?
After building the conceptual model, Entity framework Code first API tries to locate the database. In order to connect to it, it needs the connection string. For this purpose, it uses the name of DbContext or a suggestion from DbContext. It can use either of the type name or the fully qualified name of DbContext. Let's use this connection string in the app.config.

<connectionStrings>
  <add name="InstituteEntities"
       providerName="System.Data.SqlClient"
       connectionString="Server=.\SQLExpress;
                          Trusted_Connection=true;
                          Database=EFCodeFirstDatabaseCreation.Entities.InstituteEntities"/>
</connectionStrings>

This would result in connecting to EFCodeFirstDatabaseCreation.Entities.InstituteEntities database on the local system's instance name SQLExpress. Since System.Data.SqlClient provider is used then it must be a SQL Server instance.

Let's see what happens if we use the fully qualified name of DbContext as name of our connection string in the app.config.

<connectionStrings>
  <add name="EFCodeFirstDatabaseCreation.Entities.InstituteEntities"
       providerName="System.Data.SqlClient"
       connectionString="Server=.\SQLExpress;
                          Trusted_Connection=true;
                          Database=EFCodeFirstDatabaseCreation.Entities.InstituteEntities"/>
</connectionStrings>

This would result in the connection as follows:

If you want the cat to really be killed then you must be curious to know what if we use both of them in app.config. Although one would never use this in a real scenario but it really doesn't hurt in finding out. Belive me, "Ignorance is not a bliss". Let's remove the database from the SQL Server instance. Let's first remove the already existing database.

Now we run the application. Since the framework can not decide between which connection string to use, it throws an exception as follows:

Let's see the details of the exception. The framework really got confused to build the connection and resulted in the exception.

As we start debugging we realize the following exception while construction of DbContext instance.

No Connection string found in config?
Now we discuss what if there is no connection string found in the app.config based on the criteria used by EF Code first. The framework still doesn't give up. Now it assumes that there is a SQL Server instance running on the local machine with name SQLExpress. This is the default instance name when SQL Server Express is installed. This is fairly good assumption that the Microsoft platform developers would have SQL Server Express installed on their machines and it would be installed with the default instance name as we (developers) are also fairly lazy to change the default options in installers. Let's comment out the connection strings in app.config and see what happens. When we run the application, it seems that the DbContext is instantiated successfully. Basically, based on the SQL Server Express instance, it has automatically created a database for us using the fully qualified name of DbContext specialization type.

Suggesting Connection String Name:
As we discussed in previous discussion that the framework uses the DbContext type name to look up the connection string in app.config. So we must be keeping the name of the connection string to be the same as the type name of the DbContext. What if we want to keep a specific name. We might be following different naming conventions for type names and configs. They might also be managed by different teams altogether. In this case, how can we help the framework to still be able to use the specific connection string?

Basically DbContext does support that, we just need to use the relevant DbContext constructor when it is being initialized. Let's update the constructor of InstituteEntities to be use the particular base type constructor.

public InstituteEntities() : base("instituteConnectionString")
{   
    this.Configuration.ProxyCreationEnabled = true;
    this.Configuration.AutoDetectChangesEnabled = true;
}

Now the default constructor of InstituteEntities would be using the specific constructor of the base class to specify the connection string name in app.config. Let's update the app.config and add the expected connection string as follows:

<connectionStrings>
  <add name="instituteConnectionString"
       providerName="System.Data.SqlClient"
       connectionString="Server=.\SQLExpress;
                          Trusted_Connection=true;
                          Database=SuggestedInstituteDatabase"/>
</connectionStrings>

If the framework would use the above connection string then it would look for a database named SuggestedInstituteDatabase. If the database is not found then it would create it for us. Let's run the application now. There are no exceptions. Now go to the SQL Server Management Studio and refresh the Databases. That's right, it has created the expected database for us.

Suggesting Database name but still using default configuration:
What if we don't want to specify the app.config but we still want to use the specific database name. The framework also supports that. As a matter of fact, the same DbContext constructor is used for that. The string parameter can be either the connection string name in the app.config. If not found, then it would be creating a database for us in the default SQL Server Express instance on local machine. Let's look at the documentation of the particular constructor of DbContext.

Before going any further, just comment the connection string in app.config. You can also delete the particular database in the SQL Server instance.Let's update the constructor to specify the Database name instead.

public InstituteEntities()
    : base("SuggestedInstituteDatabase")
{   
    this.Configuration.ProxyCreationEnabled = true;
    this.Configuration.AutoDetectChangesEnabled = true;
}

Now run the application and see the created database in SQL Server Management Studio. [If you are already running SQL Server Management Studio then you would need to refresh the databases.

Using Encrypted Connection String
The world is not so simple. There are extra curious people who want to see how we have done what we have done. They want to do it for fun or monetary or political gains. We want to keep our systems protected from their curious nature. The easiest option is to keep the connection string encrypted so, even if, they are able to access the app.config, the could not find the connection details from the connection string.

Now this requirement would push us how the entity framework resolves the connection string.

Using pre-instantiated DbConnection to construct DbContext
DbContext provides us with various constructors. As we know we can specify what base class constructor to pick when a particular sub-type constructor is used for instantiating the sub-type. We can use the DbContext constructor which allows us to pass the pre-instantiated DbConnection to be used for DbContext initialization. The below code is using the same approach.

public InstituteEntities() : 
    base(new SqlConnection(Constants.ConnectionString), false)
{
    //DbContext sub-type constructor initialization
}

Since we are using SQL Server database to hold the entities, we are using SqlConnection instance to be passed to DbContext constructor. We need to specify what connection string to use for this DbConnection. The above code is getting the connection string from static member ConnectionString from Constants class. Now it is up to us how we want to load / build the value of ConnectionString.

public static class Constants
{
    public static string ConnectionString
    {
        get  { return GetDecryptedConnectionString();  }
    }

    private static string GetDecryptedConnectionString()
    {
        return @"Server=.\SQLExpress;Trusted_Connection=true;Database=SuggestedInstituteDatabase";
    }
}

In the above code, ConnectionString is getting the value from GetDecryptedConnectionString() method. This is here, in this method, that we can load / build the connection string. If this is encrypted then we can decrypt it and return to the calling code. In order to test this code, let's delete the SuggestedInstituteDatabase from the local SQL Server instance. When we run the application, the database should be created automatically. This is created by EF Code First using the connection details passed in the constructor.

EF Code First Provider Model
Like ADO.net, Entity Framework Code First uses provider model for database management systems. It provides a certain framework for third party library developers to develop the providers that it can use to work with a particular database. There is default provider for SQL Server available in the framework. For working with other database, you would need to download the specific provider libraries and use them in your code instead. Let's not waste any time on it and use the default provider as we still want to use SQL Server. We just want to modify this so that it could use the encrypted connection string.

As we discussed above, the providers must be providing the implementation of various abstractions which we can use in our code. One such type is about connection factories. As its name implies, this would be used to create connection for the particular Database Management System [DBMS].

class EncryptedIDbConnectionFactory : IDbConnectionFactory
{
    #region Private Fields

    IDbConnectionFactory _connectionFactory;

    #endregion

    #region Constructors

    public EncryptedIDbConnectionFactory(IDbConnectionFactory dbConnectionFactory)
    {
        if (dbConnectionFactory == null)
        {
            throw new ArgumentNullException("dbConnectionFactory can not be null");
        }

        _connectionFactory = dbConnectionFactory;
    }

    #endregion

    #region IDbConnectionFactory implementation
    public DbConnection CreateConnection(string nameOrConnectionString)        
    {
        //decryption of connection string
        string decryptedConnectionString = 
            GetDecryptedConnectionString(nameOrConnectionString);

        return _connectionFactory.CreateConnection(decryptedConnectionString);
    }
    #endregion

    #region Private Methods
    private string GetDecryptedConnectionString(string nameOrConnectionString)
    {
        //use some encryption library to decrypt
        return nameOrConnectionString;
    }
    #endregion
}

Using connection factory like this would still use the functionality of the existing factory to create database connection. We are just providing a functionality that the connection string passed should be decrypted before it even goes to build the connection. It is the implementation of Decorator design pattern. Here you can use an encryption library to decrypt the connection string in GetDecryptedConnectionString method. For keeping the example simple, we are just returning the same string back to the calling code. Consider this your assignment to be using the appropriate encryption library.

We need to inform the EF Code first framework to use the new database connection factory instead. This is done in application initialization code before DbContext is even initialized.

Database.DefaultConnectionFactory = 
                new EncryptedIDbConnectionFactory(Database.DefaultConnectionFactory);

Now how is this approach different than the previous one? Basically, in this approach the construction of DbConnection is still delegated to the DefaultConnectionFactory. We are just helping it resolve the connection string encryption as the default factory expects it unencrypted. It can also use all the possible conventions based on Entity Framework Code First. This approach is also very extensible to add various features to the connection factory without actually extending it. Now it is up to the requirement which particular feature we want, we can decorate the factory with those additional decorators and Zindabad!!! Like we can add tracing or caching decorators and use it with the default connection factory. Now when we run the application, we should be seeing the exact database created as the previous example.

Download Code