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

March 1st, 2011

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 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).

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.

Database Filling

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

Code Sample

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.


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

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:

Then, for each context created in the application,

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.

20 Responses to “Entity Framework Code-First support for Oracle, MySQL, PostgreSQL and SQLite”

  1. Challenger Says:

    Hey dude,

    i’m recently working on the so called “code first” stuff in EF4.
    it’s really made confused a lot, until i see your article here.
    it’s cool! THANKS A LOT!

  2. Microsoft’s code-first Entity Framework 4.1 nearly done « Tim Anderson’s ITWriting Says:

    […] It looks fantastic; though there are a few caveats. One is that Microsoft tends to assume use of its own database managers, SQL Server or for simple cases, SQL Server CE. That said, there are drivers for other databases; for example devart has code-first drivers for Oracle, MySQL, PostgreSQL and SQLite. […]

  3. Ilya Says:

    Can you publish an example of the same code working with the released version of Code First (EF 4.1). The code you have does not work with the release.

    Thank you!

  4. Shalex Says:

    Dear users,

    We have updated this article (and samples) to reflect changes between EF CTP5 and EF 4.1 RC. This article includes now usage of the IgnoreSchemaName workaround, the DefaultConnectionFactory and Creating Entity Framework Model with Fluent Mapping in Designer sections.

    Devart Team

  5. dotConnect Team’s Blog » Blog Archive » Entity Developer – EF Code First DbContext Template Says:

    […] In the latter case, the EF-provider is responsible for the selection of a particular database-specific data type. The provider can be defined in several ways. For more information on how to define a provider-specific connection, see Entity Framework Code-First support for Oracle, MySQL, PostgreSQL and SQLite. […]

  6. Fred Says:


    great stuff. But how does EF Code Only know which Entity Framework Provider to use when you only define the underlying ADO.NET Provider ?


  7. Devart Says:

    Fred, each Entity Framework provider has one and only one underlying ADO.NET provider.
    Thus, the Code Only provider gets corresponding ProviderFactory from the provider-specific connection, and identifies the ADO.NET provider. Entity Framework Provider is then determined with the help of the ADO.NET Provider.

  8. Andrew Says:

    When attempting to follow your example, I am getting an “ORA-01005: null password given; logon denied” error. This only occurs when the project tries to create the seed data. If I comment out the line

    System.Data.Entity.Database.SetInitializer(new MyDbContextDropCreateDatabaseAlways());

    I can perform regular CRUD actions against the database. When I look at the context passed to the Seed method, it has the password in the DbConnectionBase class.

  9. Shalex Says:

    Andrew, please try setting the “Persist Security Info=true;” connection string parameter.

  10. mike Says:

    When I try the SQLite DB first code I get errors due to no ProductId.
    This is due, i believe, to no identity option set on ProductId

  11. mike Says:

    I see this but it isn’t creating the identity in SQLite
    public long CompanyID { get; set; }

  12. mike Says:

    Does your connection string support BinaryGUID=False??

  13. mike Says:

    Hey, I put “BinaryGUID=False” in my connection string but guids still are not non-binary
    I defined my key as
    public Guid StuffId {get; set;}

    context.Stuff.Add(new Stuff(),{ StuffId = Guid.NewGuid()};

    the row is inserted into the table but StuffId is binary gibberish

  14. Shalex Says:

    1. > I see this but it isn’t creating the identity in SQLite
    > [Key]
    > [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    > public long CompanyID { get; set; }

    Please refer to http://www.devart.com/dotconnect/sqlite/revision_history.html
    3.60.258 08-Dec-11
    The bug with generating AUTOINCREMENT in DDL for key columns when using Identity in Code First is fixed

    We recommend you upgrading to the latest (3.60.283) version of dotConnect for SQLite.

    2. > Does your connection string support BinaryGUID=False??

    Yes, connection string of dotConnect for SQLite supports BinaryGUID.

    3. > I put “BinaryGUID=False” in my connection string but guids still are not non-binary

    We will investigate the issue and post here about the results.

  15. mike Says:

    1. > I see this but it isn’t creating the identity in SQLite
    > [Key]
    > [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    > public long CompanyID { get; set; }

    Please refer to http://www.devart.com/dotconnect/sqlite/revision_history.html
    3.60.258 08-Dec-11
    The bug with generating AUTOINCREMENT in DDL for key columns when using Identity in Code First is fixed

    We recommend you upgrading to the latest (3.60.283) version of dotConnect for SQLite.

    Ummm, I downloaded the Professional trial version a couple days ago which gave me 3.60.268
    How about the trail version also be the current version?

  16. mike Says:

    ok, i see 3.60.283 was put on on 12 Jan 2012, yesterday
    what’s the best way to updated my trail download to the new version?

  17. mike Says:

    for the BinaryGUID isue, I tried this.
    In SQLiteConnectionFactory.cs
    public DbConnection CreateConnection(…)


    connection.ConnectionString = builder.ToString();

    along with
    public Guid StuffId {get; set;}

    context.Stuff.Add(new Stuff(),{ StuffId = Guid.NewGuid()};

    the row is inserted into the table but StuffId is binary gibberish

  18. Shalex Says:

    > what’s the best way to updated my trail download to the new version?
    Please download the latest (3.60.283) trial version and run it (the previous version will be uninstalled automatically). After this, clear the bin and obj folders of your project and rebuild the project.

  19. Shalex Says:

    The bug with Guid parameters representation depending on the BinaryGUID connection string parameter’s value is fixed. We will post here when the corresponding build of dotConnect for SQLite is available for download.

  20. Shalex Says:

    New version of dotConnect for SQLite 3.70 is released!
    It can be downloaded from http://www.devart.com/dotconnect/sqlite/download.html (trial version) or from Registered Users’ Area (for users with active subscription only).
    For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=23257 .