ADO.NET Entity Framework Support

The latest ADO.NET evolution focuses on raising the level of abstraction from the logical (relational) level to the conceptual (entity) level. For this purpose Microsoft introduces the Entity Framework, designed to simplify data - object conversion and embed data access queries into program code.

The Devart dotConnect product line supports the ADO.NET Entity Framework for Oracle, MySQL, PostgreSQL, and SQLite.

What is ADO.NET Entity Framework?

The Entity Framework is a set of technologies in ADO.NET that support development of data-oriented software applications. The Entity Framework is designed to enable developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications. Entity Framework applications provide the following benefits:

  • Applications can work in terms of a more application-centric conceptual model, including types with inheritance, complex members, and relationships.
  • Applications are freed from hard-coded dependencies on a particular data engine or storage schema.
  • Mappings between the conceptual model and the storage-specific schema can change without changing the application code.
  • Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in different database management systems.
  • Multiple conceptual models can be mapped to a single storage schema.
  • Language-integrated query support provides compile-time syntax validation for queries against a conceptual model.

Entity Framework Support Key Features

  • Conceptual approach to enterprise development
  • High performance
  • Wide support for LINQ to Entities capabilities
  • Wide support for EntitySQL
  • Full CRUD (Create, Retrieve, Update, Delete) support
  • Wide support for server data types
  • Change tracking support
  • Object data caching
  • Inheritance mapping support (TPH, TPT, and TPCT models)
  • Capability to use stored procedures when manipulating data
  • Stored procedure import support
  • Ability to build database-independent applications
  • Visual Studio EDM Wizard support
  • Reverse engineering of database objects to entity model
  • Visual model creation and code generation with Entity Developer

Using the Entity Data Provider

Use of dotConnect for MySQL in the Entity Framework is similar to use of standard SqlClient. Generally it conforms to the following scheme:

  1. Open your project.
  2. Add new item ADO.NET Entity Data Model to your project and specify its name.
  3. Choose Generate from database.
  4. Choose the dotConnect you use as data provider and specify the connection string.
  5. Select tables that are actually used in your project and click Generate to finish the process. If the selected tables contain foreign keys, they will be reflected in the generated models as well.
  6. Specify a name for the model namespace. Note that this name should `1e different from the database (schema) name.

For your convenience there is a brief Entity Framework Tutorial that you can use to get acquainted with the technology. You can find it in the product's documentation or online: Oracle, MySQL, PostgreSQL.

You can also take a look at Entity Framework samples available with the product, or download separate Entity Framework Query Samples package (2MB). The latter is a standard Microsoft demo with added Oracle/MySQL/PostgreSQL/SQLite connectivity.

Also take a look ar Entity Framework Query Samples article that describes this package and contains tutorial on how to build the same model as in the sample with Entity Developer.

ADO.NET Entity Framework vs LINQ to SQL

Nowadays Microsoft provides two rival technologies to make your database development more object-oriented: LINQ to SQL and ADO.NET Entity Framework. At first glance both technologies have similar object-relational mapping capabilities. When deciding which technology to use, consider the following aspects.

Entity Framework advantages

  • Entity Framework is a conceptual approach to database application development. Therefore it requires more efforts to set up and maintain.
  • Entity Framework is more portable across different database servers as it is more abstract than LINQ to SQL.
  • Support for Windows Communication Foundation.
  • Support for ADO.NET Data Services
  • Support for some Microsoft services (for example, reporting and analysis services).

LINQ to SQL advantages

  • LINQ to SQL is better suited for RAD-style development, it is easier to start with.
  • LINQ to SQL has better support for server-oriented features like BLOBs and other server types.
  • LINQ to SQL is less complicated than Entity Framework.
  • LINQ to SQL has somewhat better performance.
  • No restrictions are applied to your objects to be compatible with LINQ to SQL. You only have to implement a couple of interfaces for automatic change tracking.

As you can see, every technology has its strong sides. Generally, ADO.NET Entity Framework is best employed in enterprise development, where you need multiple abstraction levels and greater flexibility instead of low start expenses.

Visual Model Creation

Entity models are created and edited with Entity Developer. The tool includes Database Reverse Engineering wizard, which guides you through the process of creating the model and makes it extremely easy. Entity Developer is integrated with Visual Studio but may be used as a standalone tool.

dotConnects for MySQL, Oracle, and PostgreSQL also support standard Visual Studio Entity Data Model Wizard. However, it is highly recommended to use Entity Developer for its greater feature set and performance.

Entity Developer Screenshot

Compatibility and Requirements

dotConnects for Oracle, MySQL, PostgreSQL, and SQLite are compatible with the latest version of ADO.NET Entity Framework and Visual Studio. The following server versions are required for Entity Framework to function properly:

  • Oracle: 9.2.0.4 or higher
  • MySQL: 5.0 or higher
  • PostgreSQL: 8.0 or higher
  • SQLite: any supported version