Jet Entity Framework Provider – in Code Setup

You have been tasked to implement Jet database provider in EF6 to support older database types like MS Access and you want to do this in code for VB.NET? This achieves the same effect as configuring your app.config but adds flexibility of choosing provider based other conditions. In my case, I have to choose and setup either MS Access or MSSQL provider on program start.

Note, that configuration settings in app.config take precedence over those in code. Therefore, before you do this remove all references to EF6 from the file.

First, define the configuration:

Public Class ORMConfiguration
    Inherits DbConfiguration
    Public Sub New()
        If isAccessDb() Then
            SetProviderServices(NameOf(JetEntityFrameworkProvider), New JetProviderServices())
        Else
            SetProviderServices(SqlProviderServices.ProviderInvariantName, SqlProviderServices.Instance)
        End If

        SetDefaultConnectionFactory(New MyDbConnectionFactory())

        SetDatabaseInitializer(New NullDatabaseInitializer(Of ORM))
    End Sub
End Class

Public Class MyDbConnectionFactory
    Implements IDbConnectionFactory

    Public Function CreateConnection(nameOrConnectionString As String) As DbConnection Implements IDbConnectionFactory.CreateConnection
        If isAccessDb() Then
            Return New JetConnection(nameOrConnectionString)
        Else
            Return New SqlConnection(nameOrConnectionString)
        End If
    End Function
End Class

Then, initiate the configuration:

' EF6 Setup.
DbConfiguration.SetConfiguration(New ORMConfiguration)

Finally, remove <entityFramework> and <DbProviderFactories> tags from your ‘app.config’:

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  <providers>
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  </providers>
</entityFramework>
<system.data>
  <DbProviderFactories>
  </DbProviderFactories>
</system.data>

Enjoy!