Skip to content

1. Introduction

1.1. Objective

The PDF of the document is available |HERE|.

The examples in the document are available |HERE|.

Entity Framework is an ORM (Object Relational Mapper) originally created by Microsoft and now available as open source [July 2012, http://entityframework.codeplex.com/]. In an ASP.NET course, I use the following architecture for a certain web application:

The NHibernate framework [http://sourceforge.net/projects/nhibernate/] is an ORM that predates Entity Framework. It is a mature product that allows you to connect to various databases. The ORM isolates the [DAO] (Data Access Objects) layer from the ADO.NET connector. It is the ORM that issues SQL commands to the connector. The [DAO] layer, in turn, uses the interface provided by the ORM. This interface depends on the ORM. Therefore, changing the ORM requires changing the [DAO] layer.

This architecture is resilient to changes in the DBMS.

When the [DAO] layer is connected directly to the ADO.NET connector, changing the DBMS affects the [DAO] layer:

  • not all DBMSs have the same data types;
  • DBMSs do not use the same strategies for generating primary keys;
  • DBMSs use proprietary SQL;
  • the [DAO] layer may have used libraries tied to a specific DBMS;
  • ...

When an ORM is connected to the ADO.NET connector, changing the DBMS simply involves configuring the ORM to adapt to the new DBMS. The [DAO] layer remains unchanged.

The Spring.NET framework [http://www.springframework.net/index.html] ensures the integration of an application’s layers. Above:

  • the ASP.NET application requests a reference to the [DAO] layer from Spring;
  • Spring uses a configuration file to create this layer and return the reference.

This architecture handles layer changes well as long as the layers continue to expose the same interface. Changing the [DAO] layer above involves modifying Spring’s configuration file so that the new layer is instantiated in place of the old one. Since both layers implement the same interface and the ASP.NET layer uses this interface, the ASP.NET layer remains unchanged.

We therefore have a flexible and scalable architecture here. To demonstrate this, we will replace the NHibernate ORM with Entity Framework 5:

We will proceed in several steps:

  • we will explore Entity Framework 5 with several DBMS;
  • we will build the [DAO2] layer;
  • we will connect the existing ASP.NET application to this new [DAO] layer.

1.2. Tools Used

The tests were performed on an HP EliteBook laptop running Windows 7 Pro, with an Intel Core i7 processor and 8 GB of RAM. We will use C# as the development language.

This document uses the following tools, all of which are available for free:

Development IDEs:

  • Visual Studio Express for Desktop 2012 [http://www.microsoft.com/visualstudio/fra/downloads];
  • Visual Studio Express for Web 2012 [http://www.microsoft.com/visualstudio/fra/downloads].

The SQL Server Express 2012 DBMS:

  • the DBMS: [http://www.microsoft.com/fr-fr/download/details.aspx?id=29062];
  • an administration tool: EMS SQL Manager for SQL Server Freeware [http://www.sqlmanager.net/fr/products/mssql/manager/download].

The Oracle Database Express Edition 11g Release 2 DBMS:

  • the DBMS: [http://www.oracle.com/technetwork/products/express-edition/downloads/index.html];
  • an administration tool: EMS SQL Manager for Oracle Freeware [http://www.sqlmanager.net/fr/products/oracle/manager/download];
  • an Oracle client for .NET: ODAC 11.2 Release 5 (11.2.0.3.20) with Oracle Developer Tools for Visual Studio: [http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html].

The MySQL 5.5.28 DBMS:

  • the DBMS: [http://dev.mysql.com/downloads/];
  • an administration tool: EMS SQL Manager for MySQL Freeware [http://www.sqlmanager.net/fr/products/mysql/manager/download].

The PostgreSQL 9.2.1 DBMS:

  • the DBMS: [http://www.enterprisedb.com/products-services-training/pgdownload#windows];
  • an administration tool: EMS SQL Manager for PostgreSQL Freeware [http://www.sqlmanager.net/fr/products/postgresql/manager/download].

The Firebird 2.1 DBMS:

  • the DBMS: [http://www.firebirdsql.org/en/firebird-2-1-5/];
  • an administration tool: EMS SQL Manager for InterBase/Firebird Freeware [http://www.sqlmanager.net/fr/products/ibfb/manager/download].

LINQPad 4: a tool for learning LINQ (Language INtegrated Query) [http://www.linqpad.net/, http://www.linqpad.net/GetFile.aspx?LINQPad4.zip].

1.3. Source code

The source code for the examples that follow is available |HERE|.

These are Visual Studio 2012 projects [1], grouped into a solution [2]. In the [databases] folder, there is a folder for each DBMS used. These folders contain the SQL scripts for generating the sample database for those DBMSs.

1.4. The Approach

To learn about Entity Framework 5 Code First, I started with the following book: "Professional ASP.NET MVC 3" by Jon Galloway, Phil Haack, Brad Wilson, and Scott Allen, published by Wrox. In the book’s sample application, the authors use Entity Framework (EF) as the ORM. Since I was unfamiliar with it, I searched the web to learn more. I discovered that the most recent version was EF 5 and that there were incompatibilities with EF 4, as the code from the book, when tested with EF 5, produced compilation errors.

I then discovered that there are several ways to use EF:

  • Model First: There are many articles on this approach to EF, for example [http://msdn.microsoft.com/en-us/data/ff830362.aspx]. This article begins as follows:

Summary: In this paper, we’ll look at the new Entity Framework 4 that ships with .NET Framework 4 and Visual Studio 2010. I’ll discuss how you can approach its usage from a model-first perspective, based on the premise that you can drive database design from a model and build both your database and your data access layer declaratively from this model. The model contains the description of your data represented as entities and relationships, providing a powerful approach to working with ADO.NET and creating a separation of concerns through an abstraction between a model definition and its implementation.

An ORM bridges the gap between database tables and classes.

Above,

  • to the left of the EF5 layer, we have objects, which we call entities;
  • to the right of the EF5 layer, we have database tables.

The [DAO] layer works with objects that represent database tables. These objects are grouped within a persistence context and are called entities (Entity). Changes made to the entities are reflected, via the ORM, in the database tables (insertion, modification, deletion). Additionally, the [DAO] layer uses a query language called LINQ to Entities (Language-Integrated Query) that queries entities rather than tables. The Model-First approach involves building entities using a graphical tool. You define each entity and the relationships connecting it to others. Once this is done, a tool generates:

  • the various classes reflecting the entities built graphically;
  • the DDL (Data Definition Language) used to generate the database.

The examples I found for this method all used Visual Studio 2010 Professional and a model called the ADO.NET Entity Data Model. I was able to test this model with Visual Studio 2010 Professional, but when I switched to Visual Studio Express 2012—which was my target—I found that this model was no longer available. So I abandoned this approach.

  • Database First: The starting point for this method is an existing database. From there, a tool automatically generates the entity models for the database tables. Again, the examples I found, such as [http://msdn.microsoft.com/en-us/data/gg685489.aspx], use Visual Studio 2010 Professional and the ADO.NET Entity Data Model. So I also abandoned this approach, even though it was my favorite. To determine which entities to use as representations of an existing database, it was easy to start with a tool that generates them.
  • Code First: you write the classes that will form the entities yourself. This requires at least a basic understanding of how EF works. This is the approach I took because it was compatible with Visual Studio Express 2012.

With that in mind, I worked as follows:

  • I wrote code for SQL Server Express 2012 because that is the DBMS for which the most examples are available;
  • once this code was debugged, I ported it to the other DBMSs (Firebird, Oracle, MySQL, PostgreSQL).

Here, we will proceed differently. I will first describe all the code for SQL Server, then I will describe how it was ported to the other DBMSs. In this porting process, the following adjustments were made:

  • databases have proprietary features. In particular, I used triggers to automatically generate the content of certain columns. Each DBMS has its own way of handling this;
  • the image entities in the tables may change, but this is intentional. I could have chosen entities suitable for all databases;
  • the ADO.NET driver for the DBMS changes;
  • the connection string to the DBMS changes.

The approach taken is as follows:

  • entity/database mapping. Populating the database;
  • Dumping the database using LINQ queries;
  • LINQPad, a tool for learning LINQ;
  • Adding, deleting, and modifying entities;
  • managing concurrent access;
  • persistence context saved within a transaction;
  • Modifying an entity outside the persistence context;
  • Eager and lazy loading;
  • building the [DAO] layer;
  • Building the ASP.NET web layer.

1.5. Target Audience

This document is intended for beginners.

This document is not a course on Entity Framework 5 Code First. For that, you might want to read, for example, "Programming Entity Framework: Code First" by Julie Lerman and Rowan Miller, published by O'Reilly. This document does not aim to be exhaustive but simply outlines the approach I used to understand this ORM. I believe this approach may be useful to others working with EF5. My objective does not extend beyond this scope.

The book cited above will serve as a reference. There are also articles dedicated to Entity Framework on developpez.com. Here are a few of them:

As noted above, this document is not exhaustive. Readers will benefit from consulting the articles listed above to fill in certain gaps. My research may have been incomplete. I apologize to any authors I may have overlooked.

https://tahe.developpez.com
This tutorial written by Serge Tahé is made available to the public under the terms of the Creative Commons Attribution – Non-Commercial – ShareAlike 3.0 Unported.