For a customer I needed to customize the way an Entity Framework 6 database-first project connects to a SQL Server database. Normally you specify the connectionstring including some metadata about the model in the app.config file. You can choose either to use Windows Authentication or SQL Authentication. If you want to use SQL Authentication you must provide the username and password. These are by default not encrypted and my customer did not like the idea that users who have access to the application can open the configuration file with for example Notepad, and can read and in a worst scenario misuse these credentials. To help secure information in configuration files there are alternatives, but all are very hard to implement and will still ask you to write custom code for Entity Framework. As often, more ways lead to Rome, but I came with the following solution. I build manually the connectionstring in the constructor of the DbContext. First I added these keys in the section of the config file. You can choose your own encryption method to encrypt one or more keys.

<appsettings>
 
  <add key="ServerName" value="MyServerName\MyInstanceName"></add>
  <add key="DatabaseName" value="MyDatabaseName"></add>
  <add key="UserID" value="MyEncryptedUserID"></add>
  <add key="Password" value="MyEncryptedPassword"></add>
 
</appsettings>

In a database-first scenario the file containing the DbContext will be generated each time you change the model. That’s the reason why you have to add a new file with contains the other part of your DbContext class. Otherwise all changes made to the file will be lost every time the model change. Given the fact that my context has the name MyDbContext, I added a file with name MyDbContextPartial.vb and added the following code. Because the first line in the constructor must be the MyBase.New() call, we have to use a shared method to retrieve the details of the connectionstring. The connectionstring is actually not a regular connectionstring as you may expect on first sight. It is an EntityConnectionString, which contains also metadata about the used model. You can find this information in your current app.config file.

Imports System.Configuration
Imports System.Data.Entity.Core.EntityClient
Imports System.Data.SqlClient
 
Partial Public Class MyDbContext
 
  Public Sub New()
    MyBase.New(GetEntityConnectionString())
  End Sub
 
  Private Shared Function GetEntityConnectionString() As String
 
    Dim entityConnectionStringBuilder As New EntityConnectionStringBuilder()
    With entityConnectionStringBuilder
        .Provider = "System.Data.SqlClient"
        .ProviderConnectionString = GetProviderConnectionString()
        .Metadata = "res://*/EntitiesModel.csdl|" &
                            "res://*/EntitiesModel.ssdl|" &
                            "res://*/EntitiesModel.msl"
    End With
 
    Return entityConnectionStringBuilder.ToString()
 
  End Function
 
  Private Shared Function GetProviderConnectionString() As String
 
    Dim userID = DecryptText(ConfigurationManager.AppSettings("UserID"))
    Dim password = DecryptText(ConfigurationManager.AppSettings("Password"))
 
    Dim sqlConnectionStringBuilder As New SqlConnectionStringBuilder()
    With sqlConnectionStringBuilder
      .DataSource = ConfigurationManager.AppSettings("ServerName").ToString()
      .InitialCatalog = ConfigurationManager.AppSettings("DatabaseName").ToString()
      .IntegratedSecurity = False
      .UserID = userID
      .Password = password
    End With
 
    Return sqlConnectionStringBuilder.ToString()
 
  End Function
 
End Class

In this example I’m using for decrypting the method DecryptText() which is a part of a common library I have build. However, you can use your own encryption- and decryption logic for this. On the Internet you can find a couple of libraries which can do these tasks for you. However, building and calling your custom connectionstring is not the only thing you have to do. You must also remove the constructor in the original MyDbContext class. That one is still referring to the name of the connectionstring in the app.config file. So the other part of the Partial Class MyDbContext ends up like this.

Imports System
Imports System.Data.Entity
Imports System.Data.Entity.Infrastructure
 
Partial Public Class MyDbContext
 
  Inherits DbContext  
 
  Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
    Throw New UnintentionalCodeFirstException()
  End Sub
 
  Public Overridable Property Customers() As DbSet(Of Customer)
  Public Overridable Property Orders() As DbSet(Of Order)
     
End Class

Hopes this help you to implement your own custom connectionstring in combination with Entity Framework.