Entity Framework Code-First support for Oracle, MySQL, PostgreSQL and SQLite

April 2011 saw the release of a new version of Entity Framework 4.1; this blog article and samples of code contained in it have been correspondingly updated to match the new features that are now available.

The latest versions of Devart dotConnect for Oracle, dotConnect for MySQL, dotConnect for PostgreSQL, and dotConnect for SQLite contain the most recent version of Code-First (Code Only) support that was added in Entity Framework 4.1. One of the primary advantages of the Code-First approach is a higher level of abstraction from the database and the capability to develop applications using the domain-driven design (DDD) approach.

This article does not provide detailed examples of different aspects related to the use of the Code-First (Code Only) approach. For detailed information on the Code-First approach, see the series of publications on the ADO.NET team blog or MSDN documentation.

This article deals with the following:

• Peculiarities of Dynamic Database Creation and Dropping Implementation
  • Database Creation
  • Database Existence Check
  • Database Dropping
• Code-First Sample
  • Entity Creation
  • DbContext Creation, Mapping Customization
  • Database Fill
  • Database Call
• DefaultConnectionFactory
• Creating Entity Framework Model with Fluent Mapping in Designer
• Code-First for All Databases

Peculiarities of Dynamic Database Creating and Dropping Implementation

The Code-First approach allows you to describe your model using plain .NET objects and map it to the existing database objects through .NET code without verifications and pre-initializations. You can also choose one of database initialization strategies (DropCreateDatabaseAlways, DropCreateDatabaseIfModelChanges, and CreateDatabaseIfNotExists). These strategies allow you to create or drop database tables dynamically. Let’s consider the main features of dynamic database creation and dropping.

Database Creation

Database creation means creating tables and relationships between them. For Oracle databases, if the primary key is specified as database-generated with the DatabaseGeneratedOption.Identity option, then a sequence and an insert trigger will be generated for this table to make this column autoincrement.

Please note that, by default, the database creation mechanism doesn’t create a storage for tables. For example, if you work with the Oracle or PostgreSQL database, then the corresponding schema must exist in the database before you create database objects. The corresponding database should be created when MySQL DBMS is used. The Oracle user must have appropriate privileges to create/drop/alter tables, sequences, and triggers in corresponding schemas. The connection string for SQLite should contain the “FailIfMissing=false;” parameter to create an empty database when opening connection.

Additionally, EF-providers allow configuring the process of creating and deleting database objects, including the setup of DeleteDatabaseBehaviour that can accept the following three values (four for PostgreSQL): ModelObjectsOnly (by default), AllSchemaObjects, and Schema. In the latter case, an entire schema will be created / deleted. See below for more information on DeleteDatabaseBehaviour. dotConnect for PostgreSQL allows additional value – Database, which enables deleting/creating of entire PostgreSQL database.

Database Existence Check

The implementation of database existence verification includes the check that at least one required object exists in the database. The existence of at least one table in MySQL, PostgreSQL, SQLite is verified as well. Additionally, for Oracle, the existence of sequences is verified.

Database Dropping

Depending on the value of the DeleteDatabaseBehaviour configuration option, the deletion behavior will differ:

• ModelObjectsOnly – only the tables and sequences (in Oracle) that model objects are mapped to are deleted. This is the default behavior.
• AllSchemaObjects – all tables (and corresponding sequences used for auto-increment columns in Oracle) will be deleted from schemas or databases that model objects were mapped to.
• Schema – the entire schema (database) is deleted. If the model contains objects from other schemas, these schemas (databases) will be deleted as well.

Note: There is no possibility to delete the user that is currently connected in Oracle. Thus, to call the DeleteDatabase() method, the user must have grants to delete users and must not have any database objects that model objects are mapped to.

For more information on how to configure the Code-First behavior, see the section Database Script Generation Settings in New Features of Entity Framework Support in dotConnect Providers.

Code-First sample

Let’s consider a simple example of Code-First approach implementation and use with DBMS, different from SQL Server. For the purpose of this example, we shall use dotConnect for Oracle data provider, comments for other providers can be found in the code samples.

Entity Creation

Let’s define the following two related entities: Product and ProductCategory.

Common and additional Data Annotations attributes can be used for mapping customization. We have used them for the autoincrement column definition in the primary key and for the definition of non-nullable columns. You can also use these attributes for the MaxLength limitation of a string field. Similarly, we can do the same using fluent mapping in the OnModelCreating method of DbContext (this example is considered in the next chapter). Besides, we shall not set up the table schema name explicitly using the Table attribute – we shall define it for all tables later using our own convention.

Since entity properties that represent foreign keys are defined with a virtual modifier, we shall use dynamic proxies for POCO classes that allow the use of Lazy Loading. If you are not going to use Lazy Loading or do not want to get dynamic proxies, then do not define properties with a virtual modifier and use explicit Eager Loading (the Include method).

//[Table("Product", Schema = "TEST")]
public class Product
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long ProductID { get; set; }

    [Required]
    [MaxLength(50)]
    public string ProductName { get; set; }

    public string UnitName { get; set; }
    public int UnitScale { get; set; }
    public long InStock { get; set; }
    public double Price { get; set; }
    public double DiscontinuedPrice { get; set; }

    public virtual ProductCategory Category { get; set; }
}

//[Table("ProductCategory", Schema = "TEST")]
public class ProductCategory
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long CategoryID { get; set; }

    [Required]
    [MaxLength(20)]
    public string CategoryName { get; set; }

    public virtual ProductCategory ParentCategory { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

DbContext Creating, Mapping Customization

Let us define a DbContext descendant that is an ObjectContext wrapper. We can turn on and off conventions and set class mapping to the database objects using fluent mapping in the OnModelCreating method of DbContext.

public class MyDbContext : DbContext
{

    public MyDbContext()
      : base() {
    }

    public MyDbContext(DbConnection connection)
      : base(connection, true) {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

      /*-------------------------------------------------------------
      ColumnTypeCasingConvention should be removed for dotConnect for Oracle.
      This option is obligatory only for SqlClient.
      Turning off ColumnTypeCasingConvention isn't necessary
      for  dotConnect for MySQL, PostgreSQL, and SQLite.
      -------------------------------------------------------------*/

      modelBuilder.Conventions
        .Remove<System.Data.Entity.ModelConfiguration.Conventions
          .ColumnTypeCasingConvention>();

      /*-------------------------------------------------------------
      If you don't want to create and use EdmMetadata table
      for monitoring the correspondence
      between the current model and table structure
      created in a database, then turn off IncludeMetadataConvention:
      -------------------------------------------------------------*/

      modelBuilder.Conventions
        .Remove<System.Data.Entity.Infrastructure.IncludeMetadataConvention>();

      /*-------------------------------------------------------------
      In the sample above we have defined autoincrement columns in the primary key
      and non-nullable columns using DataAnnotation attributes.
      Similarly, the same can be done with Fluent mapping
      -------------------------------------------------------------*/

      //modelBuilder.Entity<Product>().HasKey(p => p.ProductID);
      //modelBuilder.Entity<Product>().Property(p => p.ProductID)
      //    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
      //modelBuilder.Entity<Product>().Property(p => p.ProductName)
      //    .IsRequired()
      //    .HasMaxLength(50);
      //modelBuilder.Entity<ProductCategory>().HasKey(p => p.CategoryID);
      //modelBuilder.Entity<ProductCategory>().Property(p => p.CategoryID)
      //    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
      //modelBuilder.Entity<ProductCategory>().Property(p => p.CategoryName)
      //    .IsRequired()
      //    .HasMaxLength(20);
      //modelBuilder.Entity<Product>().ToTable("Product", "TEST");
      //modelBuilder.Entity<ProductCategory>().ToTable("ProductCategory", "TEST");

      //-------------------------------------------------------------//
    }

    public DbSet<Product> Products { get; set; }
    public DbSet<ProductCategory> ProductCategories { get; set; }
}

Database Filling

Let us consider descendants for all available strategies – DropCreateDatabaseAlways, DropCreateDatabaseIfModelChanges, and CreateDatabaseIfNotExists with additional data filling functionality after database creation:

Code Sample

public class MyDbContextDropCreateDatabaseAlways 
  : DropCreateDatabaseAlways<MyDbContext> 
{ 
    protected override void Seed(MyDbContext context) 
    {
      MyDbContextSeeder.Seed(context);
    }
}

public class MyDbContextDropCreateDatabaseIfModelChanges 
  : DropCreateDatabaseIfModelChanges<MyDbContext> 
{ 
    protected override void Seed(MyDbContext context) 
    {
      MyDbContextSeeder.Seed(context);
    }
}

public class MyDbContextCreateDatabaseIfNotExists 
  : CreateDatabaseIfNotExists<MyDbContext> 
{ 
    protected override void Seed(MyDbContext context) 
    {
      MyDbContextSeeder.Seed(context);
    }
}

public static class MyDbContextSeeder 
{ 
    public static void Seed(MyDbContext context) 
    { 
      context.ProductCategories.Add(new ProductCategory() 
      {
        CategoryName = "prose"
      });
      context.ProductCategories.Add(new ProductCategory() 
      {
        CategoryName = "novel"
      });
      context.ProductCategories.Add(new ProductCategory() 
      { 
        CategoryName = "poem",
        ParentCategory = 
         context.ProductCategories.Local.Single(p => p.CategoryName == "novel")
      });
      context.ProductCategories.Add(new ProductCategory() 
      { 
        CategoryName = "fantasy",
        ParentCategory = 
          context.ProductCategories.Local.Single(p => p.CategoryName == "novel")
      });

      context.Products.Add(new Product() 
      { 
        ProductName = "Shakespeare W. Shakespeare's dramatische Werke",
        Price = 78,
        Category = 
          context.ProductCategories.Local.Single(p => p.CategoryName == "prose")
      });      
      context.Products.Add(new Product() 
      { 
        ProductName = "Plutarchus. Plutarch's moralia", 
        Price = 89, 
        Category = 
          context.ProductCategories.Local.Single(p => p.CategoryName == "prose")
      });
      context.Products.Add(new Product() 
      { 
        ProductName = "Harrison G. B. England in Shakespeare's day", 
        Price = 540, 
        Category = 
          context.ProductCategories.Local.Single(p => p.CategoryName == "novel")
      });
      context.Products.Add(new Product() 
      { 
        ProductName = "Corkett Anne. The salamander's laughter", 
        Price = 5,
        Category = 
          context.ProductCategories.Local.Single(p => p.CategoryName == "poem")
      });
    }
  }

Database Call

In this example, we have turned on OracleMonitor to view executed DDL и DML statements in the
Devart dbMonitor application. However, it should be remembered that the use of OracleMonitor is feasible for the purpose of testing and debugging but should be limited in production environments, since monitoring can decrease the performance of your application.

By default, when a class wasn’t explicitly mapped using the Table attribute (see the Product and ProductCategory class definition above) or fluent mapping (the ToTable method), then Model-First uses default mapping, suitable for SQL Server, but ineligible for other databases: the table will have the “dbo” prefix, so table names will be as follows: “dbo.TableName”.

If IncludeMetadataConvention was turned off, then it will be enough to define Table attributes for all entities or use .ToTable() in fluent mapping. If IncludeMetadataConvention was turned on, then the “dbo”.”EdmMetadata” table is used and it is impossible to easily change the schema name.
It is also necessary to explicitly set the schema name for the table that represents the many-to-many association.

To avoid the issue with the schema name, we use the capability for configuring the behavior of the Entity Framework provider to switch off schema name generation.

class Program
{
    static void Main(string[] args)
    {
      Devart.Data.Oracle.OracleMonitor monitor
          = new Devart.Data.Oracle.OracleMonitor() { IsActive = true };

      //--------------------------------------------------------------
      // You use the capability for configuring the behavior of the EF-provider:
      Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig config =
          Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
      // Now, you switch off schema name generation while generating 
      // DDL scripts and DML:
      config.Workarounds.IgnoreSchemaName = true;
      //--------------------------------------------------------------

      /*--------------------------------------------------------------
      You can set up a connection string for DbContext in different ways.
      It can be placed into the app.config (web.config) file.
      The connection string name must be identical to the DbContext descendant name.

      <add name="MyDbContext" connectionString="Data Source=ora1020;
      User Id=test;Password=test;" providerName="Devart.Data.Oracle" />

      After that, you create a context instance, while a connection string is 
      enabled automatically:
      MyDbContext context = new MyDbContext();
      ---------------------------------------------------------------*/

      /*--------------------------------------------------------------
      And now it is possible to create an instance of the provider-specific connection 
      and send it to the context constructor, like we did in this application. 
      That allows us to use the StateChange connection event to change the Oracle 
      current schema on its occurrence. Thus, we can connect as one user and 
      work on a schema owned by another user.
      ---------------------------------------------------------------*/
      DbConnection con = new Devart.Data.Oracle.OracleConnection(
          "Data Source=ora1020;User Id=scott;Password=tiger;");
      con.StateChange += new StateChangeEventHandler(Connection_StateChange);
      ---------------------------------------------------------------*/

      /*--------------------------------------------------------------
      You can choose one of database initialization
      strategies or turn off initialization:
      --------------------------------------------------------------*/
      System.Data.Entity.Database.SetInitializer
        <MyOracleContext>(new MyDbContextDropCreateDatabaseAlways());
      /*System.Data.Entity.Database.SetInitializer
        <MyOracleContext>(new MyDbContextCreateDatabaseIfNotExists());
      System.Data.Entity.Database.SetInitializer
        <MyOracleContext>(new MyDbContextDropCreateDatabaseIfModelChanges());
      System.Data.Entity.Database.SetInitializer<MyOracleContext>(null);*/
      //--------------------------------------------------------------

      /*--------------------------------------------------------------
      Let's create MyDbContext and execute a database query.
      Depending on selected database initialization strategy,
      database tables can be deleted/added, and filled with source data.
      ---------------------------------------------------------------*/

      using (MyDbContext context = new MyDbContext(con))
      {
        var query = context.Products.Include("Category")
                       .Where(p => p.Price > 20.0)
                       .ToList();

        foreach (var product in query)
          Console.WriteLine("{0,-10} | {1,-50} | {2}",
            product.ProductID, product.ProductName, product.Category.CategoryName);

        Console.ReadKey();
      }
    }

    // On connection opening, we change the current schema to "TEST":
    static void Connection_StateChange(object sender, StateChangeEventArgs e) {

      if (e.CurrentState == ConnectionState.Open) {
        DbConnection connection = (DbConnection)sender;
        connection.ChangeDatabase("TEST");
      }
    }
}

DefaultConnectionFactory

In addition to the two ways of specifying the connection string that are described in the previous section, here we shall review yet another one. This way involves non-explicit specification of the connection string through Database.DefaultConnectionFactory. For that purpose, you need to have a provider-specific class that implements the IDbConnectionFactory interface.

Admittedly, this approach does not provide sufficient flexibility and should rather be used with embedded databases, for instance, SQLite. Below is the example of the SQLiteConnectionFactory class that can be used to obviate the need to specify a connection string per each context.

Class SQLiteConnectionFactory

  public class SQLiteConnectionFactory : IDbConnectionFactory {

    private const string invariantName = "Devart.Data.SQLite";

    #region Constructors

    public SQLiteConnectionFactory()
      : this(String.Empty, String.Empty) {
    }

    public SQLiteConnectionFactory(string basePath, string defaultConnectionString) {

      this.BasePath = basePath;
      this.DefaultConnectionString = defaultConnectionString;
    }

    #endregion

    #region IDbConnectionFactory Members

    public DbConnection CreateConnection(string nameOrConnectionString) {

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

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

      DbConnection connection = sqliteProviderFactory.CreateConnection();

      if (nameOrConnectionString.Contains('='))
        connection.ConnectionString = nameOrConnectionString;
      else {
        StringBuilder builder = new StringBuilder(128);
        builder.Append(DefaultConnectionString);

        if (builder.Length > 0 && builder[builder.Length - 1] != ';')
          builder.Append(';');

        builder.Append("Data Source=");
        builder.Append(BasePath);
        string dbFileName = nameOrConnectionString;
        if (dbFileName.Contains('.')) {
          int classNameFrom = nameOrConnectionString.LastIndexOf('.') + 1;
          int classNameLength = nameOrConnectionString.Length - classNameFrom;
          dbFileName = nameOrConnectionString.Substring(classNameFrom, classNameLength);
        }
        builder.Append(dbFileName);
        builder.Append(".db");

        connection.ConnectionString = builder.ToString();
      }

      return connection;
    }

    #endregion

    #region Properties

    public string BasePath { get; private set; }

    public string DefaultConnectionString { get; private set; }

    #endregion

  }

DefaultConnectionFactory is specified once at the application startup. In the example, we shall also specify the relative path to be used when searching for/creating the SQLite database file and define an additional connection string option so that the database file is created if it is non-existent:

 Database.DefaultConnectionFactory = 
   new SQLiteConnectionFactory(".\..\..\", "FailIfMissing=False");

Then, for each context created in the application,

 MySQLiteContext context = new MySQLiteContext();

a provider-specific connection will be created through the use of previously defined SQLiteConnectionFactory. At first access to the database, the connection is created and, on its opening, provided that the database file does not exist, the “…..MySQLiteContext.db” file will be created for the MySQLiteContext class.

Creating Entity Framework Model with Fluent Mapping in Designer

Initially, fluent mapping was intended for the Code-First development, when the DbContext class, all entity classes and complex types were written manually, mapping was defined through the use of Data Annotation attributes and/or fluent mapping (it being impossible to define all possible mapping using only attributes). In case of large models, all this required a significant amount of manual operations; besides, it was impossible to use the Database-First and Model-First approaches to development. So the users requested that a capability for at least partial development of the Code-First EF-model in the designer be implemented.

For standard EDM Designer in Microsoft Visual Studio 2010 there is the ADO.NET DbContext Generator template that is included in the full installation of Entity Framework 4.1. It generates DbContext and POCO entity classes, but does not allow generating fluent mapping.

The latest versions of Devart Entity Developer include the DbContext template that allows generating DbContext both with and without fluent mapping. Admittedly, the use of the designer results in lower flexibility and does not account for all cases of mapping; on the upside, the process of development has grown more comfortable and faster. The users can enhance the initial DbContext template or can combine different techniques, for example, they can create and map some entities using the designer and write other entities manually.

For more information on functional capabilities of DbContext template, see Entity Developer – EF Code First DbContext Template.

Code-First for All Databases

In the attached archive, you can find the full version of the above examples that use EF Code-First for each ADO.NET provider:

• Devart dotConnect for Oracle
• Devart dotConnect for MySQL
• Devart dotConnect for PostgreSQL
• Devart dotConnect for SQLite
• as well as for standard Microsoft .NET Framework Data Provider for SQL Server (SqlClient)

These C# samples were created for Visual Studio 2010, and Microsoft ADO.NET Entity Framework 4.1 installation is required. Devart dbMonitor, which is a free tool, is used for the monitoring of DML and DDL statements of Devart ADO.NET providers.

You can find the updated version of the samples for Entity Framework 6 in this blog article: Entity Framework 6 Support for Oracle, MySQL, PostgreSQL, SQLite and Salesforce.