Using Entity Framework Power Tools CTP1 with Oracle, MySQL, PostgreSQL, and SQLite

Released soon after Entity Framework 4.1, EF Power Tools CTP1 is intended to provide design-time support for the development that involves the use of the Entity Framework Code-First approach. EF Power Tools CTP1 can be downloaded from Visual Studio Gallery. Further in the article, you will find an explanation on how to use Entity Framework Power Tools with Devart dotConnect providers for Oracle, MySQL, PostgreSQL and SQLite, as well as information on Entity Developer that can be used as an alternative to Entity Framework Power Tools.

This article deals with the following:

• Use of Existing DbContext
  • Connection String
  • Using DefaultConnectionFactory
  • Instance of Provider-Specific Connection
• Generation of Code-First Model from Existing Database

The functionality of Entity Framework Power Tools is mainly intended for the use of an already existing DbContext class.

Warning! A DbContext derived class must have a public constructor with no parameters – it is this constructor that will be used by EF Power Tools.

The major problem of using CTP1 with Oracle, MySQL, PostgreSQL and SQLite is that the developer cannot employ the commonly accepted technique of storing the connection string, whose name is the same as that of the model class, in the application’s configuration file. EF Power Tools CTP1 simply does not use this string, and that results in errors and non-working functionality.

To resolve this issue, the developer can choose one of the following methods:

• Copy the connection string from the application’s configuration file into machine.config (remember that machine.config must refer to Framework .NET4 rather than .NET2. Normally, this file can be located here: WindowsMicrosoft.NETFrameworkv4.0.30319Configmachine.config ). This technique is rather simple, and, albeit cumbersome, is acceptable for the purpose of development. Use this technique carefully, so that the correctness of the .NET Framework configuration file is preserved.
• Place the connection string into the configuration file of Visual Studio 2010 (Normally located here: Program FilesMicrosoft Visual Studio 10.0Common7IDEdevenv.exe.config). This option is not very convenient and should be used carefully in order to preserve the correctness of VS settings.
• If your project contains Code-First models only for one provider, then you can define DefaultConnectionFactory. This option cannot be used, if your application uses several providers/databases. When used with Oracle, MySQL, PostgreSQL and SQLite, this option involves writing an implementation of IDbConnectionFactory.
• The default model constructor must send an instance of the provider-specific connection to the base DbContext constructor.

The first two techniques are simple enough and involve only copying the connection string. Now, we shall analyze the last two techniques since they require that additional code be written.

If you choose to use this technique, you will need to write an implementation of IDbConnectionFactory. Below is an example of such implementation:

  DevartConnectionFactory class

public class DevartConnectionFactory : IDbConnectionFactory {

    public DevartConnectionFactory(string providerInvariantName, string configFileName) {

      this.ProviderInvariantName = providerInvariantName;
      this.ConfigFileName = configFileName;
    }

    #region IDbConnectionFactory Members

    public DbConnection CreateConnection(string nameOrConnectionString) {

      if (String.IsNullOrWhiteSpace(nameOrConnectionString))
        throw new ArgumentNullException("nameOrConnectionString");

      DbProviderFactory providerFactory = DbProviderFactories.GetFactory(ProviderInvariantName);
      if (providerFactory == null)
        throw new InvalidOperationException(String.Format("The '{0}' provider is not registered on the local machine.", ProviderInvariantName));

      DbConnection connection = providerFactory.CreateConnection();

      if (nameOrConnectionString.Contains("=")) {
        connection.ConnectionString = nameOrConnectionString;
      }
      else {

        string dbContextClassName = nameOrConnectionString;
        if (dbContextClassName.Contains('.')) {
          int classNameFrom = nameOrConnectionString.LastIndexOf('.') + 1;
          int classNameLength = nameOrConnectionString.Length - classNameFrom;
          dbContextClassName = nameOrConnectionString.Substring(classNameFrom, classNameLength);
        }

        ExeConfigurationFileMap map = new ExeConfigurationFileMap();
        map.ExeConfigFilename = this.ConfigFileName;
        Configuration assemblyConfig = ConfigurationManager.OpenMappedExeConfiguration(map, ConfigurationUserLevel.None);

        ConnectionStringSettings connectionSettings = assemblyConfig.ConnectionStrings.ConnectionStrings[dbContextClassName];

        if (connectionSettings == null)
          throw new InvalidOperationException(String.Format("Can't find the '{0}' connection string in the config file.", dbContextClassName));

        if (connectionSettings.ProviderName != ProviderInvariantName)
          throw new InvalidOperationException(String.Format("The '{0}' connection string was expected to be defined for the '{1}' provider, but it is defined for the '{2}' provider.", dbContextClassName, ProviderInvariantName, connectionSettings.ProviderName));

        connection.ConnectionString = connectionSettings.ConnectionString;
      }

      return connection;
    }

    #endregion

    #region Properties

    public string ProviderInvariantName {
      get;
      set;
    }

    public string ConfigFileName {
      get;
      set;
    }

    #endregion

  }

To use it, place the definition of DefaultConnectionFactory, for example, into the static constructor of the DbContext class. For dotConnect for Oracle, this may look like:

  public class MyOracleContext : DbContext {

    public MyOracleContext() {
    }

    static MyOracleContext() {

      Database.DefaultConnectionFactory = new DevartConnectionFactory(
        providerInvariantName: "Devart.Data.Oracle",
        configFileName: @"D:ProjectsSamplesCrmDemo.EFCodeFirstapp.config"
      );
    }

    //...

  }

DevartConnectionFactory can be used with dotConnect for Oracle, MySQL, PostgreSQL and SQLite; however, make sure that the provider invariant name is correctly defined. Additionally check that the path to the configuration file of your application is set correctly.
For SQLite, acquaint yourself with the SQLite-specific example of IDbConnectionFactory implementation, extended with additional functionality, – see the example class SQLiteConnectionFactory in the article “Entity Framework Code-First support for Oracle, MySQL, PostgreSQL and SQLite”.

In this case, the default provider must create a provider-specific connection. We shall demonstrate that using an example for dotConnect for Oracle; the idea is similar for other providers.
This can be achieved by creating a connection of the required type. We also need to explicitly add references to provider-specific assemblies to the project.

  public class MyOracleContext : DbContext {
     public MyOracleContext()
      : base(new Devart.Data.Oracle.OracleConnection("Data Source=ora1020;User Id=scott;Password=tiger;"), true) {
    }
     //...
   }

We can also create a connection using DbProviderFactory.

  
  public class MyOracleContext : DbContext {

    public MyOracleContext()
      : base(CreateConnection(), true) {
    }

    private const string providerInvariantName = "Devart.Data.Oracle";

    private static DbConnection CreateConnection() {

      DbProviderFactory providerFactory = DbProviderFactories.GetFactory(providerInvariantName);
      if (providerFactory == null)
        throw new InvalidOperationException(String.Format("The '{0}' provider is not registered on the local machine.", providerInvariantName));

      DbConnection connection = providerFactory.CreateConnection();
      connection.ConnectionString = "Data Source=ora1020;User Id=scott;Password=tiger;";
      return connection;
    }

    //...

  }

The first version of Entity Framework Power Tools also contained the capability of generating a Code-First model with fluent mapping from an existing database. Although useful, this functionality is limited as regards its flexibility: the developer can only set the connection string; following that, classes are generated from all database objects available to the user. That is not extremely convenient, since in Oracle, for example, numerous schemas containing hundreds and sometimes thousands of tables are available to the user.

Rather than resort to this limited functionality, the users of our ADO.NET providers can avail themselves of impressively robust design-time development capabilities of Entity Developer, an EF-designer delivered with our providers. Also possible is the choice between the Database-First approach, as provided in EF Power Tools, and the Model-First approach, within which Code-First classes are created in the EDM-designer.

In contrast to EF Power Tools, the Database-First approach to the development of EF Code-First models also allows selecting objects that must be available in the model, setting naming rules for the generation of class names and properties and so on. Besides, the resulting model can be modified and improved in the designer.

To better meet developers’ needs, Code-First code generation in Entity Developer both for C# and VB is based on the T4-template that is easily accessible and can be modified in feature-rich T4 Editor contained in Entity Developer.

For more information on Code-First development in Entity Developer, see Entity Developer – EF Code First DbContext Template.

See also

• Entity Framework Code-First support for Oracle, MySQL, PostgreSQL and SQLite — an introductory article on how to use the Code-First approach with different providers. The article also contains a downloadable example of an application that uses several databases.
• Entity Developer – EF Code First DbContext Template — an article describing design-time development capabilities of Code-First models in Entity Developer.
• New Features of Entity Framework Support in dotConnect Providers — an article that describes new capabilities for configuring EF-providers, including several workarounds for Code-First and some customization for creating and deleting database objects.