3. Case Study with SQL Server Express 2012
3.1. Introduction
Most of the examples found online for Entity Framework are examples using SQL Server. This is quite normal. It is likely the most widely used DBMS in the enterprise .NET world. We will follow this trend. The examples will then be extended to all the databases mentioned in section 1.2.
3.2. Installing the Tools
We will not describe the installation of the tools. Doing so would require a large number of screenshots that quickly become obsolete. This is a task (admittedly not always easy) that we leave to the reader.
We need to install the following tools:
- the SQL Server Express 2012 DBMS: [http://www.microsoft.com/fr-fr/download/details.aspx?id=29062]. Download the "With Tools" version, which includes an administration tool with the DBMS:
 |
Once the DBMS is installed, we launch it:
 |
 |
- [1]: From the Start Menu, launch the "SQL Server Configuration Manager";
- [2]: In this manager, start the server;
- [3]: It is now running.
Now launch the SQL Server administration tool:
 |
- [1]: From the Start menu, launch "SQL Server Management Studio";
- [2]: the administration tool.
We will connect to the server:
 |
- In [1], open Object Explorer;
- in [2], enter the connection parameters:
- [3]: the (local) server (note the required parentheses) refers to the server installed on the machine,
- [4]: Select Windows authentication. You must be an administrator on your computer for this connection to succeed,
 |
- [6]: You are connected;
- [7]: You want to modify certain server properties;
 |
- [8]: We request that there be two authentication modes:
- Windows authentication, as just used. A Windows user with the appropriate permissions can then log in,
- SQL Server authentication. The user must be one of the users registered in the database management system;
Once this is done, we can validate the server properties;
- [9]: Edit the properties of the user "sa" (system administrator);
 |
- in [10], set a password for the user. In the rest of this document, the password is sqlserver2012;
 |
- In [10], grant them permission to connect;
- in [11], the connection is enabled. The wizard can now be confirmed;
- In [12], log out of the server.
Now, we reconnect using the sa/sqlserver2012 login:
 |
- In [1], we reconnect;
- in [2], during SQL Server authentication;
- In [3], the user is sa;
- in [4], their password is sqlserver2012;
- in 5, we log in;
 |
- in [6], we are logged in.
We will now create a demo database:
 |
- in [1], create a new database;
- in [2], name it demo;
- in [3], click "Validate";
 |
- in [4], the database is created;
- in 5, create a new table in the "demo" database;
 |
 |
 |
 |
- in [6], we define a table with two columns, ID and NAME;
- in [7], we make the [ID] column the primary key;
- in [8], the primary key is represented by a key;
- in [9], the table is saved;
- In [10], we give it a name;
- In [11], to make the table appear in the [demo] database, you must refresh the database;
- in [12], the [PERSONNES] table has been successfully created.
We now know enough about using the SQL Server Management Studio.
3.3. The embedded server (localdb)\v11.0
VS Express 2012 comes with an embedded SQL Server. We assume here that VS Express 2012 has been installed [http://www.microsoft.com/visualstudio/fra/downloads]. Launch VS 2012 [1]:
 |
Launch the SQL Server 2012 Management Studio [2] and log in [3].
 |
- In [4], connect to the (localdb)\v11.0 server;
- In 5, use Windows authentication;
- In [6], the successful connection displays the server’s databases. As before, you could create a new database.
We will not use this embedded server in VS 2012.
3.4. Creating the database from entities
Entity Framework 5 Code First allows you to create a database from entities. That is what we will now explore. Using VS Express 2012, we create an initial console project in C#:
 |
 |
- in [1], the project definition;
- in [2], the created project.
All our projects will need to , the Entity Framework 5 DLL. We add it:
 |
- in [1], the NuGet tool allows you to download dependencies;
 |
- in [2], we download the Entity Framework dependency;
- in [3], the reference has been added to the project.
You can learn more by viewing the properties of the added reference:
 |
- in [1], the DLL version. You need version 5;
- in [2], its location in the file system: <solution>\packages\EntityFramework.5.0.0\lib\net45\EntityFramework.dll where <solution> is the VS solution folder. All packages added by NuGet will go into the <solution>/packages folder;
- in [3], a [packages.config] file has been created. Its contents are as follows:
<?xml version="1.0" encoding="utf-8"?>
<packages>
<package id="EntityFramework" version="5.0.0" targetFramework="net45" />
</packages>
It lists the packages imported by NuGet.
Let's go back to the VS project and create a [Models] folder in the project:
 |
- in [1], adding a folder to the project;
- in [2], it will be named [Models].
We will continue this practice of placing our entity definitions in the [Models] folder.
To build our entities, we’ll use the MySQL 5 database definition used in the NHibernate project. Let’s review the role of EF entities:
 |
Entities must reflect the database tables. The data access layer uses these entities instead of working directly with the tables. Let’s start with the [DOCTORS] table:
3.4.1. The [Medecin] entity
It contains information about the doctors managed by the [RdvMedecins] application.
 |  |
- ID: the doctor’s ID number—the table’s primary key
- VERSION: number identifying the version of the row in the table. This number is incremented by 1 each time a change is made to the row.
- LAST_NAME: the doctor’s last name
- FIRST NAME: the doctor's first name
- TITLE: their title (Ms., Mrs., Mr.)
We could start with the following [Doctor] class:
using System;
[Table("DOCTORS", Schema = "dbo")]
namespace RdvMedecins.Entities
{
public class Doctor
{
// data
public int Id { get; set; }
public string Title { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
}
- Line 3: The [Medecin] class is associated with the [MEDECINS] table in the database. This table will be located in a schema named "dbo".
We place this class in a file named [Entities.cs] [1]. This is where we will place all our entities.
 |
Still in the [Models] folder, we create the following [Context.cs] file:
using System.Data.Entity;
using RdvMedecins.Entities;
namespace RdvMedecins.Models
{
// the context
public class RdvMedecinsContext : DbContext
{
// doctors
public DbSet<Doctor> Doctors { get; set; }
}
// database initialization
public class DoctorAppointmentsInitializer : DropCreateDatabaseAlways<DoctorAppointmentsContext>
{
}
}
- line 8: the [RdvMedecinsContext] class will represent the persistence context, i.e., the set of entities managed by the ORM. It must derive from the [System.Data.Entity.DbContext] class;
- line 11: the [Medecins] field represents the [Medecin] entities in the persistence context. It is of type DbSet<Medecin>. There are generally as many [DbSet]s as there are tables in the database, one per table;
- line 15: we define a [RdvMedecinsInitializer] class to initialize the created database. Here, it derives from the [DropCreateDataBaseAlways] class, which, as its name suggests, deletes the database if it already exists and then recreates it. This is useful during the database development phase. The parameter of the [DropCreateDataBaseAlways] class is the type of persistence context associated with the database. Other parent classes besides [DropCreateDataBaseAlways] can be used for the initialization class:
- [DropCreateDatabaseIfModelChanges]: recreates the database if the entities have changed,
- [CreateDatabaseIfNotExists]: creates the database if it does not exist;
We still need to create a main program. It will be the following [CreateDB_01.cs]:
using System;
using System.Data.Entity;
using RdvMedecins.Models;
namespace RdvMedecins_01
{
class CreateDB_01
{
static void Main(string[] args)
{
// Create the database
Database.SetInitializer(new RdvMedecinsInitializer());
using (var context = new RdvMedecinsContext())
{
context.Database.Initialize(false);
}
}
}
}
- line 12: [System.Data.Entity.DataBase] is a class that provides static methods for managing the database associated with a persistence context. The static method [SetInitializer] allows you to specify the database initialization class. This does not trigger initialization;
- line 13: to work with a persistence context, you must instantiate it. This is what is done here. A using statement is used so that the context is automatically closed when the statement ends. Therefore, on line 17, the context is closed;
- Line 15: We explicitly trigger the generation of the database associated with the persistence context [RdvMedecinsContext]. The false parameter indicates that this operation should not be performed if it has already been done for this context. Here, we could just as easily have set it to true.
When working with a database, connection parameters are generally stored in the [App.config] file. Note that for now, they are not there:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
</entityFramework>
</configuration>
The elements above were added to [App.config] when the Entity Framework dependency was added to the project references.
Let’s run the project (Ctrl-F5) after starting SQL Server Express (this is important):
 |  |
The execution should complete without errors. Now let’s open the SQL Server Management Studio and refresh the view:
 |
We can see that a database with the full name of the [RdvMedecinsContext] class has been created and that it contains a table named [dbo.MEDECINS] (the name we gave it) with columns that match the field names of the [Medecin] entity. If the code executed successfully but the database above does not appear, check the embedded server (localdb)\v11.0 (see page 19). With VS 2012 Pro, this server is used if SQL Server is not active when the code is executed. With VS 2012 Express, it is not.
Let’s examine the structure of the [MEDECINS] table:
- it uses the field names from the [Medecin] entity;
- the [Id] column is the primary key. This is an EF convention: if entity E has an Id or Eid field (MedecinId), then this column is the primary key in the associated table;
- the column types in the table are those of the entity fields;
- for the Title, Last Name, and First Name columns, a [nvarchar(max)] type was used. We could be more specific: 5 characters for the title, 30 for the last name and first name;
- The Title, Last Name, and First Name columns can have a NULL value. We are going to change that.
Let’s look at the properties of the primary key [Id]:
 |
In [1], we see that the primary key is of type [Identity], which means its value is automatically generated by SQL Server. We will adopt this strategy for all DBMSs.
We’ll rely less on EF conventions by using annotations. The entity code in [Entities.cs] becomes the following:
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace RdvMedecins.Entities
{
[Table("DOCTORS", Schema = "dbo")]
public class Doctor
{
// data
[Key]
[Column("ID")]
public int Id { get; set; }
[Required]
[MaxLength(5)]
[Column("TITLE")]
public string Title { get; set; }
[Required]
[MaxLength(30)]
[Column("NAME")]
public string Name { get; set; }
[Required]
[MaxLength(30)]
[Column("LAST_NAME")]
public string FirstName { get; set; }
[Required]
[Column("VERSION")]
public int Version { get; set; }
}
}
- Lines 2 and 3: The annotations are found in the [System.ComponentModel.DataAnnotations] namespace (Key, Required, MaxLength) and the [System.ComponentModel.DataAnnotations.Schema] namespace (Column). Additional annotations can be found at the URL [http://msdn.microsoft.com/en-us/data/gg193958.aspx];
- line 11: [Key] designates the primary key;
- line 12: [Column] sets the column name corresponding to the field;
- line 14: [Required] indicates that the field is required (SQL NOT NULL);
- line 15: [MaxLength] sets the maximum length of the string, [MinLength] its minimum length;
Let’s run the project with this new definition of the [Medecin] entity. The resulting database is as follows:
 |
- the columns have the names we assigned to them;
- the [Required] annotation has been translated to SQL NOT NULL;
- The [MaxLength(N)] annotation has been mapped to an SQL nvarchar(N) type.
In the NHibernate application, the [VERSION] column was there to prevent concurrent access to the same row in a table. The principle is as follows:
- A process P1 reads a row L from the [DOCTORS] table at time T1. The row has version V1;
- A process P2 reads the same row L from the [DOCTORS] table at time T2. The row has version V1 because process P1 has not yet committed its modification;
- process P1 commits its modification to row L. The version of row L then changes to V2 = V1 + 1;
- Process P2 commits its modification to row L. The ORM then throws an exception because process P2 has a version V1 of row L that differs from the version V2 found in the database.
This is called optimistic concurrency management. With EF 5, a field serving this role must have one of two attributes: [Timestamp] or [ConcurrencyCheck]. SQL Server has a [timestamp] type. A column of this type has its value automatically generated by SQL Server whenever a row is inserted or modified. Such a column can then be used to manage concurrent access. To revisit the previous example, process P2 will find a timestamp different from the one it read, because in the meantime, the modification made by process P1 will have changed it.
Our [Doctor] entity evolves as follows:
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace RdvMedecins.Entities
{
[Table("DOCTORS", Schema = "dbo")]
public class Doctor
{
// data
[Key]
[Column("ID")]
public int Id { get; set; }
[Required]
[MaxLength(5)]
[Column("TITLE")]
public string Title { get; set; }
[Required]
[MaxLength(30)]
[Column("NAME")]
public string Name { get; set; }
[Required]
[MaxLength(30)]
[Column("LAST_NAME")]
public string FirstName { get; set; }
[Column("TIMESTAMP")]
[Timestamp]
public byte[] Timestamp { get; set; }
}
}
- Lines 26–28: the new column with the [Timestamp] attribute from line 27. The field type must be byte[] (line 28). The field name can be anything. We do not set the [Required] attribute because the application will not provide this value; rather, the DBMS itself will.
If we run the project with this new entity, the database evolves as follows:
 |
We have one last point to address. The persistence context "knows" that an entity must be inserted into the database because its primary key is null at that point. It is the database insertion that will assign a value to the primary key. Here, the int type assigned to the primary key [Id] is not suitable because this type does not accept the null value. We therefore assign it the int? type, which accepts int values plus the null pointer. The [Medecin] entity used will therefore be as follows:
public class Doctor
{
// data
[Key]
[Column("ID")]
public int? Id { get; set; }
...
We still need to see how to represent the concept of a foreign key between tables in an entity.
3.4.2. The [Creneau] entity
The [CRENEAUX] table lists the time slots where appointments are possible:
 |
 |
- ID: the ID number of the time slot—the table's primary key
- VERSION: number identifying the version of the row in the table. This number is incremented by 1 each time a change is made to the row.
- ID_MEDECIN: ID number identifying the doctor to whom this time slot belongs – foreign key on the MEDECINS(ID) column.
- START_TIME: start time of the time slot
- MSTART: Start minute of the time slot
- HFIN: slot end time
- MFIN: End minutes of the slot
The second row of the [SLOTS] table (see [1] above) indicates, for example, that slot #2 begins at 8:20 AM and ends at 8:40 AM and belongs to doctor #1 (Ms. Marie PELISSIER).
With what we know, we can define the [Creneau] entity as follows in [Entites.cs]:
[Table("SLOTS", Schema = "dbo")]
public class Slot
{
// data
[Key]
[Column("ID")]
public int? Id { get; set; }
[Required]
[Column("HDEBUT")]
public int startTime { get; set; }
[Required]
[Column("MDEBUT")]
public int Mstart { get; set; }
[Required]
[Column("END")]
public int EndDate { get; set; }
[Required]
[Column("MFIN")]
public int Mfin { get; set; }
[Required]
public virtual Doctor Doctor { get; set; }
[Column("TIMESTAMP")]
[Timestamp]
public byte[] Timestamp { get; set; }
}
The only change is in lines 20–21. The fact that the [CRENEAUX] table has a foreign key on the [MEDECINS] table is reflected in the [Creneau] entity by the presence of a reference to the [Medecin] entity on line 21. The field name is irrelevant; only the type matters. The property must be declared virtual using the virtual keyword. This is because EF needs to redefine all so-called navigational properties—that is, those corresponding to a foreign key and allowing navigation between tables.
To test the new entity, we need to make a few changes in [Context.cs]:
using System.Data.Entity;
using RdvMedecins.Entities;
namespace RdvMedecins.Models
{
// the context
public class RdvMedecinsContext : DbContext
{
// the entities
public DbSet<Doctor> Doctors { get; set; }
public DbSet<Creneau> Creneaux { get; set; }
}
// Initialize the database
public class RdvMedecinsInitializer : DropCreateDatabaseIfModelChanges<RdvMedecinsContext>
{
}
}
Line 12 reflects the fact that the context has one more entity to manage. When we run the project, we get the following new database:
 |
The [CRENEAUX] table has indeed been created, and the new feature is the presence of foreign keys [1] and [2]. Their names were generated from the names of the corresponding fields in the entity (Medecin) suffixed with "_Id". To view the properties of this foreign key, we try to modify it [3].
 |
The screenshot above shows that [Medecin_Id] is a foreign key in the [CRENEAUX] table and that it references the primary key [ID] in the [MEDECINS] table.
If we create the entities for an existing database, the foreign key column will not necessarily be named [Medecin_Id]. For other columns, we saw that the [Column] annotation solved this problem. Strangely, it’s more complicated for a foreign key. We must proceed as follows:
public class Creneau
{
// data
...
[Required]
[Column("DOCTOR_ID")]
public int DoctorId { get; set; }
[Required]
[ForeignKey("MedecinId")]
public virtual Doctor Doctor { get; set; }
...
}
- lines 5-7: we create a field of the foreign key type (int). Using the [Column] attribute, we specify the name of the column that will be the foreign key in the table associated with the entity;
- line 9: we add the [ForeignKey] annotation to the field of type [Medecin]. The argument of this annotation is the name of the field (not the column) that is associated with the foreign key column in the table.
Running the project this time creates the following table:
 |
Above, the foreign key column does indeed bear the name we gave it. Note that the fields:
[Required]
[Column("MEDECIN_ID")]
public int MedecinId { get; set; }
[Required]
[ForeignKey("MedecinId")]
public virtual class Doctor { get; set; }
have resulted in only a single column, the [MEDECIN_ID] column. Nevertheless, the presence of the [MedecinId] field is important. When reading a row from the [SLOTS] table, it will receive the value of the [DOCTOR_ID] column, i.e., the value of the foreign key in the [DOCTORS] table. This is often useful.
The [Medecin] field above reflects the many-to-one relationship linking the [Creneau] entity to the [Medecin] entity. Multiple [Slot] objects are linked to the same [Doctor]. The inverse relationship—where a single [Doctor] object is associated with multiple [Slot] objects—can be modeled using an additional field in the [Doctor] entity:
public class Doctor
{
// data
[Key]
[Column("ID")]
public int? Id { get; set; }
...
public ICollection<TimeSlot> TimeSlots { get; set; }
[Column("TIMESTAMP")]
[Timestamp]
public byte[] Timestamp { get; set; }
On line 8, we added the [Slots] field, which is a collection of [Slot] objects. This field will give us access to all of the doctor's available time slots.
When we run the project again, we see that the [DOCTORS] table has not changed:
 |
No columns have been added. The foreign key relationship between the [CRENEAUX] table and the [MEDECINS] table is sufficient for EF to generate the related fields:
public class Doctor
{
...
public ICollection<Creneau> Creneaux { get; set; }
...
}
public class Slot
{
...
[Required]
[Column("DOCTOR_ID")]
public int DoctorId { get; set; }
[Required]
[ForeignKey("MedecinId")]
public virtual Doctor Doctor { get; set; }
...
}
We know the basics. We can finish by creating the other two entities.
3.4.3. The [Client] and [Appointment] entities
With what we’ve learned, we can write the [Client] and [Appointment] entities. The [Client] entity contains information about the clients managed by the [DoctorAppointments] application.
 |  |
- ID: the customer’s ID number—the table’s primary key
- VERSION: number identifying the version of the row in the table. This number is incremented by 1 each time a change is made to the row.
- LAST_NAME: the client’s last name
- FIRST NAME: the client’s first name
- TITLE: their title (Ms., Mrs., Mr.)
The [Client] entity could be as follows:
[Table("CLIENTS", Schema = "dbo")]
public class Client
{
// data
[Key]
[Column("ID")]
public int? Id { get; set; }
[Required]
[MaxLength(5)]
[Column("TITLE")]
public string Title { get; set; }
[Required]
[MaxLength(30)]
[Column("NAME")]
public string Name { get; set; }
[Required]
[MaxLength(30)]
[Column("LAST_NAME")]
public string FirstName { get; set; }
// the client's Rvs
public ICollection<Rv> Rvs { get; set; }
[Column("TIMESTAMP")]
[Timestamp]
public byte[] Timestamp { get; set; }
}
The [Client] class is almost identical to the [Doctor] class. They could be derived from the same parent class. The new element is on line 21. It reflects the fact that a client can have multiple appointments and is derived from the presence of a foreign key from the [RVS] table to the [CLIENTS] table.
The [Rv] entity represents an appointment:
 |
- ID: number uniquely identifying the appointment – primary key
- DAY: day of the appointment
- SLOT_ID: appointment time slot – foreign key on the [ID] column of the [SLOTS] table – determines both the time slot and the doctor involved.
- CLIENT_ID: ID of the client for whom the appointment is made – foreign key on the [ID] column of the [CLIENTS] table
The [Rv] entity could be as follows:
[Table("DOCTORS", Schema = "dbo")]
public class Appointment
{
// data
[Key]
[Column("ID")]
public int? Id { get; set; }
[Required]
[Column("DAY")]
public DateTime Day { get; set; }
[Column("CLIENT_ID")]
public int ClientId { get; set; }
[ForeignKey("ClientId")]
[Required]
public virtual Client Client { get; set; }
[Column("SLOT_ID")]
public int SlotId { get; set; }
[ForeignKey("CreneauId")]
[Required]
public virtual Slot Slot { get; set; }
[Column("TIMESTAMP")]
[Timestamp]
public byte[] Timestamp { get; set; }
}
- lines 5-7: primary key;
- lines 8-10: appointment date;
- lines 11-12: the foreign key from the [RVS] table to the [CLIENTS] table;
- lines 13–15: the customer with the appointment;
- lines 16–17: the foreign key from the [RVS] table to the [CRENEAUX] table;
- lines 18–20: the appointment time slot;
- Lines 21-23: the concurrent access control field.
In line 17, we see a many-to-one relationship: a single time slot can correspond to multiple appointments (not on the same day). The inverse relationship can be reflected in the [Creneau] entity:
public class Slot
{
// the appointments in the time slot
public ICollection<Rv> Rvs { get; set; }
...
}
Line 4: the collection of appointments scheduled for this time slot.
When the project is run, the generated database is as follows:
 |
The [DOCTORS] and [SLOTS] tables have not changed. The [CLIENTS] and [APPs] tables are as follows:
 |  |
This is what was expected. We still have a few details to sort out:
3.4.4. Setting the database name
To set the name of the database generated by EF, we will use a connection string defined in [App.config]. This configuration file changes as follows:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
</entityFramework>
<!-- database connection string -->
<connectionStrings>
<add name="RdvMedecinsContext"
connectionString="Data Source=localhost;Initial Catalog=rdvmedecins-ef;User Id=sa;Password=sqlserver2012;"
providerName="System.Data.SqlClient" />
</connectionStrings>
<!-- the factory provider -->
<system.data>
<DbProviderFactories>
<add name="SqlClient Data Provider"
invariant="System.Data.SqlClient"
description=".NET Framework Data Provider for SQL Server"
type="System.Data.SqlClient.SqlClientFactory, System.Data,
Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
/>
</DbProviderFactories>
</system.data>
</configuration>
- lines 15–19: the database connection string;
- line 16: the [name] attribute uses the name of the [RdvMedecinsContext] class used for the persistence context. It is important to remember this. This constraint can be bypassed in the context constructor:
// constructor
public RdvMedecinsContext()
: database("myContext")
{
}
In this case, we can have name= "myContext". This is what we will have in the rest of the document.
- Line 17: the connection string. [Data Source]: the name of the server hosting the DBMS; [Initial Catalog]: the name of the database, in this case [rdvmedecins-ef]; [User Id]: the connection owner; [Password]: the owner’s password. The reader should adapt this string to their environment;
- Lines 21–29: define a [DbProviderFactory]. I don’t know what this is. Judging by the name, it could be a class used to generate the [ADO.NET] layer that separates EF from the DBMS:
 |
Actually, these lines are unnecessary for SQL Server, but I had to add them for other DBMSs. So I’m including them here for reference. They don’t cause any issues. The only important point is the version in line 27. It’s the version of the [System.Data] DLL listed in the project references:
 |
There we go. We’re ready. We run the project and get the following database [rdvmedecins-ef]:
 |
This will be our final database. All that’s left is to populate it with data.
3.4.5. Filling the database
The database initialization class can be used to insert data into it:
public class RdvMedecinsInitializer : DropCreateDatabaseIfModelChanges<RdvMedecinsContext>
{
// database initialization
public class RdvMedecinsInitializer : DropCreateDatabaseAlways<RdvMedecinsContext>
{
protected override void Seed(RdvMedecinsContext context)
{
database.Seed(context);
// initialize the database
// clients
Client[] clients = {
new Client { Title = "Mr", LastName = "Martin", FirstName = "Jules" },
new Client { Title = "Ms.", LastName = "German", FirstName = "Christine" },
new Customer { Title = "Mr.", LastName = "Jacquard", FirstName = "Jules" },
new Customer { Title = "Ms.", LastName = "Bistrou", FirstName = "Brigitte" }
};
foreach (Client client in clients)
{
context.Clients.Add(client);
}
// doctors
Doctor[] doctors = {
new Doctor { Title = "Ms.", LastName = "Pelissier", FirstName = "Marie" },
new Doctor { Title = "Mr.", LastName = "Bromard", FirstName = "Jacques" },
new Doctor { Title = "Mr.", LastName = "Jandot", FirstName = "Philippe" },
new Doctor { Title = "Ms", LastName = "Jacquemot", FirstName = "Justine" }
};
foreach (Doctor doctor in doctors)
{
context.Doctors.Add(doctor);
}
// time slots
Slots[] slots = {
new Slot{ StartHour=8, StartMinute=0, EndHour=8, EndMinute=20, Doctor=doctors[0]},
new Slot{ StartHour=8, StartMinute=20, EndHour=8, EndMinute=40, Doctor=doctors[0]},
new Slot{ StartHour=8, StartMinute=40, EndHour=9, EndMinute=0, Doctor=doctors[0]},
new Slot{ StartTime=9, StartMinutes=0, EndTime=9, EndMinutes=20, Doctor=doctors[0]},
new Slot{ StartTime=9, StartMinutes=20, EndTime=9, EndMinutes=40, Doctor=doctors[0]},
new Slot{ StartTime=9, StartMinute=40, EndTime=10, EndMinute=0, Doctor=doctors[0]},
new Slot{ StartTime=10, StartMinute=0, EndTime=10, EndMinute=20, Doctor=doctors[0]},
new Slot{ StartTime=10, StartMin=20, EndTime=10, EndMin=40, Doctor=doctors[0]},
new Slot{ StartTime=10, StartMin=40, EndTime=11, EndMin=0, Doctor=doctors[0]},
new Slot{ StartTime=11, StartMinute=0, EndTime=11, EndMinute=20, Doctor=doctors[0]},
new Slot{ StartTime=11, StartMin=20, EndTime=11, EndMin=40, Doctor=doctors[0]},
new Slot{ StartTime=11, StartMin=40, EndTime=12, EndMin=0, Doctor=doctors[0]},
new Slot{ StartTime=14, StartMin=0, EndTime=14, EndMin=20, Doctor=doctors[0]},
new Slot{ StartTime=14, StartMin=20, EndTime=14, EndMin=40, Doctor=doctors[0]},
new Slot{ StartTime=14, StartMin=40, EndTime=15, EndMin=0, Doctor=doctors[0]},
new Slot{ StartTime=15, StartMinute=0, EndTime=15, EndMinute=20, Doctor=doctors[0]},
new Slot{ StartTime=15, StartMinute=20, EndTime=15, EndMinute=40, Doctor=doctors[0]},
new Slot{ StartTime=15, StartMinute=40, EndTime=16, EndMinute=0, Doctor=doctors[0]},
new Slot{ StartTime=16, StartMinute=0, EndTime=16, EndMinute=20, Doctor=doctors[0]},
new Slot{ StartTime=16, StartMin=20, EndTime=16, EndMin=40, Doctor=doctors[0]},
new Slot{ StartTime=16, StartMinute=40, EndTime=17, EndMinute=0, Doctor=doctors[0]},
new Slot{ StartTime=17, StartDate=0, EndTime=17, EndDate=20, Doctor=doctors[0]},
new Slot{ StartTime=17, StartMin=20, EndTime=17, EndMin=40, Doctor=doctors[0]},
new Slot{ StartTime=17, StartMin=40, EndTime=18, EndMin=0, Doctor=doctors[0]},
new Slot{ StartTime=8, StartPatient=0, EndTime=8, EndPatient=20, Doctor=doctors[1]},
new Slot{ StartTime=8, StartMinute=20, EndTime=8, EndMinute=40, Doctor=doctors[1]},
new Slot{ StartTime=8, StartMinute=40, EndTime=9, EndMinute=0, Doctor=doctors[1]},
new Slot{ StartTime=9, StartMinutes=0, EndTime=9, EndMinutes=20, Doctor=doctors[1]},
new Slot{ StartTime=9, StartMinutes=20, EndTime=9, EndMinutes=40, Doctor=doctors[1]},
new Slot{ StartTime=9, StartMinute=40, EndTime=10, EndMinute=0, Doctor=doctors[1]},
new Slot{ StartTime=10, StartMinute=0, EndTime=10, EndMinute=20, Doctor=doctors[1]},
new Slot{ StartTime=10, StartMin=20, EndTime=10, EndMin=40, Doctor=doctors[1]},
new Slot{ StartTime=10, StartMin=40, EndTime=11, EndMin=0, Doctor=doctors[1]},
new Slot{ StartTime=11, StartMin=0, EndTime=11, EndMin=20, Doctor=doctors[1]},
new Slot{ StartTime=11, StartMin=20, EndTime=11, EndMin=40, Doctor=doctors[1]},
new Slot{ StartTime=11, StartMin=40, EndTime=12, EndMin=0, Doctor=doctors[1]},
};
foreach (Slot slot in slots)
{
context.Slots.Add(slot);
}
// Appointments
context.Appointments.Add(new Appointment { Date = new System.DateTime(2012, 10, 8), Client = clients[0], Slot = slots[0] });
}
}
}
- line 6: initialization occurs in the [Seed] method. This method exists in the parent class. It is redefined here. The argument is the application’s persistence context [RdvMedecinsContext];
- line 8: the argument is passed to the parent class; it is likely that the parent class opens the persistence context passed to it, as this context no longer needs to be opened later;
- lines 11–16: creation of 4 clients;
- lines 17–20: these are added to the persistence context, more specifically to its doctors. Note the [Add] method that enables this. Recall the definition of the context:
public class RdvMedecinsContext : DbContext
{
// the entities
public DbSet<Doctor> Doctors { get; set; }
public DbSet<TimeSlot> TimeSlots { get; set; }
public DbSet<Client> Clients { get; set; }
public DbSet<Appointment> Appointments { get; set; }
...
It is also said that the Clients have been attached to the context, i.e., they are now managed by EF. Previously, they were detached. They existed as objects but were not managed by EF;
- lines 21–27: creation of 4 doctors;
- lines 28–31: adding them to the persistence context;
- lines 33–70: creation of time slots. Lines 34–57 for doctor medecins[0], lines 58–69 for doctor medecins[1]. The other doctors have no time slots;
- lines 71–74: these time slots are placed in the persistence context;
- line 76: creation of an appointment for the first client with the first time slot and placing it in the persistence context.
When the project is run, the following database is obtained:
 |  |
Above, we see the [CLIENTS] table populated.
3.4.6. Modifying entities
Currently, the [Doctor] and [Client] classes are nearly identical. In fact, if we remove the fields added for persistence management with EF 5, they are identical. We will have them derive from a [Person] class. These two entities then become the following:
// a person
public abstract class Person
{
// data
[Key]
[Column("ID")]
public int? Id { get; set; }
[Required]
[MaxLength(5)]
[Column("TITLE")]
public string Title { get; set; }
[Required]
[MaxLength(30)]
[Column("NAME")]
public string Name { get; set; }
[Required]
[MaxLength(30)]
[Column("LAST_NAME")]
public string FirstName { get; set; }
[Column("TIMESTAMP")]
[Timestamp]
public byte[] Timestamp { get; set; }
// signature
public override string ToString()
{
return String.Format("[{0},{1},{2},{3},{4}]", Id, Title, FirstName, LastName, dump(Timestamp));
}
// short signature
public string ShortIdentity()
{
...
}
// utility
private string dump(byte[] timestamp)
{
...
}
}
[Table("DOCTORS", Schema = "dbo")]
public class Doctor : Person
{
// the doctor's time slots
public ICollection<TimeSlot> TimeSlots { get; set; }
// signature
public override string ToString()
{
return String.Format("Doctor {0}", base.ToString());
}
}
[Table("CLIENTS", Schema = "dbo")]
public class Client : Person
{
// the client's Rvs
public ICollection<Appointment> Appointments { get; set; }
// signature
public override string ToString()
{
return String.Format("Client {0}", base.ToString());
}
}
When the project is run, the same database is generated. EF 5 has mapped each of the lowest classes in the inheritance hierarchy to a separate table. In fact, EF 5 has different table generation strategies for representing entity inheritance. We will not cover them here. For example, you can read " Entity Framework Code First Inheritance: Table Per Hierarchy and Table Per Type" at the URL [http://www.codeproject.com/Articles/393228/Entity-Framework-Code-First-Inheritance-Table-Per].
We will now use this version of the entities.
3.4.7. Adding Constraints to the Database
There is one more detail to address. The [RVS] table for appointments is as follows:
 |
This table must have a uniqueness constraint: for a given day, a doctor’s time slot can only be booked once for an appointment. In terms of the table, this means that the pair (DAY, SLOT_ID) must be unique. I don’t know if this constraint can be expressed directly in the code, either on the entities or in the context. It’s likely, but I haven’t checked. We’ll take a different approach. We’ll use a SQL Server management client to add this constraint.
Using "SQL Server Management Studio," I haven't found a simple way to add this constraint other than executing the SQL statement that creates it:
 |
- in [1] we create an SQL query for the [rdvmedecins-ef] database;
- in [2], the SQL query that creates the uniqueness constraint;
- in [3], executing this query created a new index in the [RVS] table.
There are other SQL Server administration tools. Here, we will use the EMS SQL Manager for SQL Server Freeware tool [http://www.sqlmanager.net/fr/products/mssql/manager/download]. Once installed, we launch it:
 |
- in [1], we register a database;
- in [2], connect to the (local) server;
- in [3], using SQL Server authentication;
- in [4], under the username sa;
- in 5, and the password sqlserver2012;
- in [6], proceed to the next step;
 |
- in [7], select the database [rdvmedecins-ef];
- in [8], finish the wizard;
- in [9], the database appears in the database tree. Connect to it [10];
- in [11], you are connected.
"SQL Manager Lite for SQL Server" allows you to create the unique constraint on the [RVS] table.
 |
- In [1], you can see the unique constraint we created earlier;
- In [2], delete it;
- In [3], the index corresponding to this uniqueness constraint has disappeared.
We recreate the deleted constraint:
 |
- In [1], we create a new index for the [RVS] table;
- in [2], we give it a name;
- in [3], it is a uniqueness constraint;
- in [4], on the DAY and SLOT_ID columns;
The DDL tab provides the SQL code to be executed:
 |
- in [6], we compile the SQL statement;
 |
- in [7], we confirm;
- in [8], the new index has appeared.
The interface provided by "SQL Manager Lite for SQL Server" is similar to that of "SQL Server Management Studio." Similar interfaces are available for Oracle, PostgreSQL, Firebird, and MySQL databases. We will therefore continue with this family of database administration tools.
To access information about a table, simply double-click on it:
 |
Information about the selected table is available in tabs. Above, we see the [Fields] tab for the [CLIENTS] table. The [Data] tab displays the table’s contents:
3.4.8. The Final Database
We now have our final database. We export its SQL script so we can regenerate it if necessary.
 |
- in [1], start of the wizard;
- in [2], the server;
- in [3], the database to be exported;
 |
- in [4], specify the name of the file where the SQL script will be saved;
- in 5, specify its encoding;
- in [6], specify what you want to extract (tables, constraints, data);
 |
- in [7], you can refine the script that will be generated;
- in [8], finish the wizard.
The script has been generated and loaded into the script editor. You can view the generated SQL code. We will rebuild the database using this script.
 |
- In [1], delete the database;
- In [2] and [3], we recreate it;
 |
- In [4], log in;
- in 5, run the SQL script to create the database;
 |
- in [6], we save it in "SQL Manager";
- In [7], we connect to the database that has just been created;
 |
- in [8], the database currently has no tables;
- In [9a], open an SQL script editor;
 |
- In [9b], open the SQL script created earlier;
- In [10], execute it;
 |
- in [11], the tables have been created;
- in [12], they are populated;
 |
- in [14], we see the unique constraint we created for the [RVS] table.
We will now work with this existing database. If it is destroyed or corrupted, we know how to regenerate it.
3.5. Working with the database using Entity Framework
We will:
- add, delete, and modify database elements;
- query the database using LINQ to Entities;
- manage concurrent access to the same database element;
- understand the concepts of Lazy Loading and Eager Loading;
- discover that database updates via the persistence context occur within a transaction.
3.5.1. Deleting items from the persistence context
We have a populated database. We are going to empty it. We create a new class [Erase.cs] in the current project [1]:
 |
The [Erase] class is as follows:
using RdvMedecins.Models;
namespace RdvMedecins_01
{
class Erase
{
static void Main(string[] args)
{
using (var context = new RdvMedecinsContext())
{
// clear the current database
// customers
foreach (var customer in context.Customers)
{
context.Clients.Remove(client);
}
// doctors
foreach (var doctor in context.Doctors)
{
context.Doctors.Remove(doctor);
}
// save the persistence context
context.SaveChanges();
}
}
}
}
- line 9: operations on a persistence context are always performed within a [using] block. This ensures that when the [using] block ends, the context has been closed;
- line 13: we iterate through the clients context [context.Clients]. All clients in the database will be placed in the persistence context;
- line 15: For each of them, we perform the [Remove] operation, which removes them from the context. In fact, they are still in the context but in a "removed" state;
- lines 18–21: we do the same for the doctors;
- line 23: we save the persistence context to the database.
When saving the context to the database, entities in the context that:
- have a null primary key are subject to an SQL INSERT operation;
- are in a "deleted" state are subject to an SQL DELETE operation;
- are in a "modified" state are subject to an SQL UPDATE operation;
As we will see later, these SQL operations are performed within a transaction. If any of them fails, everything that was done previously is rolled back.
Let’s make the [Erase] program the new starting point for project [1] and then run the project.
 |
Let’s check the database. We’ll see that all the tables are empty [2]. This is surprising, since we simply asked to delete the doctors and clients. It is through the mechanism of foreign keys that the other tables were emptied in a cascade.
The foreign key definition from the [CRENEAUX] table to the [MEDECINS] table was defined as follows by the EF 5 provider:
 |
- In [1], select the [CRENEAUX] table;
- in [2], select the foreign keys tab;
- in [3], edit the single foreign key;
 |
- in [4], in the DDL tab, the SQL definition of the foreign key constraint;
- in 5, the ON DELETE CASCADE clause ensures that deleting a doctor results in the deletion of the time slots associated with them.
The foreign key constraints for the [RVS] table are defined in a similar manner:
- Lines 1-6: Deleting a client will also delete the appointments associated with it;
3.5.2. Adding items to the persistence context
Now that we have emptied the database, we will fill it again. We add the program [Fill.cs] [1] to the project.
 |
The [Fill.cs] program is as follows:
using RdvMedecins.Entities;
using RdvMedecins.Models;
namespace RdvMedecins_01
{
class Fill
{
static void Main(string[] args)
{
using (var context = new RdvMedecinsContext())
{
// clear the current database
foreach (var client in context.Clients)
{
context.Clients.Remove(client);
}
foreach (var doctor in context.Doctors)
{
context.Doctors.Remove(doctor);
}
// we reset it
// the clients
Client[] clients = {
new Client { Title = "Mr", LastName = "Martin", FirstName = "Jules" },
new Customer { Title = "Ms.", LastName = "German", FirstName = "Christine" },
new Customer { Title = "Mr.", LastName = "Jacquard", FirstName = "Jules" },
new Customer { Title = "Ms.", LastName = "Bistrou", FirstName = "Brigitte" }
};
foreach (Client client in clients)
{
context.Clients.Add(client);
}
// doctors
Doctor[] doctors = {
new Doctor { Title = "Ms.", LastName = "Pelissier", FirstName = "Marie" },
new Doctor { Title = "Mr.", LastName = "Bromard", FirstName = "Jacques" },
new Doctor { Title = "Mr.", LastName = "Jandot", FirstName = "Philippe" },
new Doctor { Title = "Miss", LastName = "Jacquemot", FirstName = "Justine" }
};
foreach (Doctor doctor in doctors)
{
context.Doctors.Add(doctor);
}
// time slots
Slots[] slots = {
new Slot{ StartHour=8, StartMinute=0, EndHour=8, EndMinute=20, Doctor=doctors[0]},
new Slot{ StartHour=8, StartMinute=20, EndHour=8, EndMinute=40, Doctor=doctors[0]},
new Slot{ StartHour=8, StartMinute=40, EndHour=9, EndMinute=0, Doctor=doctors[0]},
new Slot{ StartTime=9, StartMinutes=0, EndTime=9, EndMinutes=20, Doctor=doctors[0]},
new Slot{ StartTime=9, StartMinutes=20, EndTime=9, EndMinutes=40, Doctor=doctors[0]},
new Slot{ StartTime=9, StartMinute=40, EndTime=10, EndMinute=0, Doctor=doctors[0]},
new Slot{ StartTime=10, StartMinute=0, EndTime=10, EndMinute=20, Doctor=doctors[0]},
new Slot{ StartTime=10, StartMin=20, EndTime=10, EndMin=40, Doctor=doctors[0]},
new Slot{ StartTime=10, StartMin=40, EndTime=11, EndMin=0, Doctor=doctors[0]},
new Slot{ StartTime=11, StartMinute=0, EndTime=11, EndMinute=20, Doctor=doctors[0]},
new Slot{ StartTime=11, StartMin=20, EndTime=11, EndMin=40, Doctor=doctors[0]},
new Slot{ StartTime=11, StartMin=40, EndTime=12, EndMin=0, Doctor=doctors[0]},
new Slot{ StartTime=14, StartMin=0, EndTime=14, EndMin=20, Doctor=doctors[0]},
new Slot{ StartTime=14, StartMin=20, EndTime=14, EndMin=40, Doctor=doctors[0]},
new Slot{ StartTime=14, StartMin=40, EndTime=15, EndMin=0, Doctor=doctors[0]},
new Slot{ StartTime=15, StartMinute=0, EndTime=15, EndMinute=20, Doctor=doctors[0]},
new Slot{ StartTime=15, StartMin=20, EndTime=15, EndMin=40, Doctor=doctors[0]},
new Slot{ StartTime=15, StartMin=40, EndTime=16, EndMin=0, Doctor=doctors[0]},
new Slot{ StartTime=16, StartMin=0, EndTime=16, EndMin=20, Doctor=doctors[0]},
new Slot{ StartTime=16, StartMin=20, EndTime=16, EndMin=40, Doctor=doctors[0]},
new Slot{ StartTime=16, StartMinute=40, EndTime=17, EndMinute=0, Doctor=doctors[0]},
new Slot{ StartTime=17, StartMinute=0, EndTime=17, EndMinute=20, Doctor=doctors[0]},
new Slot{ StartTime=17, StartMinute=20, EndTime=17, EndMinute=40, Doctor=doctors[0]},
new Slot{ StartTime=17, StartMin=40, EndTime=18, EndMin=0, Doctor=doctors[0]},
new Slot{ StartTime=8, StartDate=0, EndTime=8, EndDate=20, Doctor=doctors[1]},
new Slot{ StartTime=8, StartMin=20, EndTime=8, EndMin=40, Doctor=doctors[1]},
new Slot{ StartTime=8, StartMinute=40, EndTime=9, EndMinute=0, Doctor=doctors[1]},
new Slot{ StartTime=9, StartMinutes=0, EndTime=9, EndMinutes=20, Doctor=doctors[1]},
new Slot{ StartTime=9, StartMinutes=20, EndTime=9, EndMinutes=40, Doctor=doctors[1]},
new Slot{ StartTime=9, StartMinute=40, EndTime=10, EndMinute=0, Doctor=doctors[1]},
new Slot{ StartTime=10, StartMinute=0, EndTime=10, EndMinute=20, Doctor=doctors[1]},
new Slot{ StartTime=10, StartMin=20, EndTime=10, EndMin=40, Doctor=doctors[1]},
new Slot{ StartTime=10, StartMin=40, EndTime=11, EndMin=0, Doctor=doctors[1]},
new Slot{ StartTime=11, StartMin=0, EndTime=11, EndMin=20, Doctor=doctors[1]},
new Slot{ StartTime=11, StartMin=20, EndTime=11, EndMin=40, Doctor=doctors[1]},
new Slot{ StartTime=11, StartMin=40, EndTime=12, EndMin=0, Doctor=doctors[1]},
};
foreach (Slot slot in slots)
{
context.Slots.Add(slot);
}
// Appointments
context.Appointments.Add(new Appointment { Date = new System.DateTime(2012, 10, 8), Client = clients[0], Slot = slots[0] });
// Save the persistence context
context.SaveChanges();
}
}
}
}
- line 10: we open the persistence context;
- lines 13–20: rows from the [CLIENTS] and [DOCTORS] tables are added to the context and then removed from it. We just saw that this completely emptied the database;
- lines 22–88: elements are added to the persistence context. All of them have a null primary key. They will therefore be inserted into the database;
- line 90: the changes made to the context are synchronized with the database. The database will be subject to a series of SQL DELETE operations followed by a series of SQL INSERT operations;
We make the [Fill] program the new starting object for project [1] and then execute it.
 |
We can see in [2] that the tables have been populated.
3.5.3. Displaying the database contents
We will now display the contents of the database using LINQ to Entities queries. LINQ (Language-Integrated Query) was introduced with the .NET Framework 3.5 in 2007. It functions as an extension of the .NET languages, meaning it is integrated into the language and its syntax is validated by the compiler. It allows you to query various collections using a syntax similar to SQL (Structured Query Language) for database querying. There are different versions of LINQ:
- LINQ to Objects, for querying in-memory collections;
- LINQ to XML, for querying XML;
- LINQ to Entity, for querying databases;
LINQ relies on numerous extensions to the .NET languages. These can be used outside of LINQ. We will not present them here but simply provide two references where the reader can find an in-depth description of LINQ:
- LINQ in Action, by Fabrice Marguerie, Steve Eichert, and Jim Wooley, published by Manning;
- LINQ Pocket Reference, by Joseph and Ben Albahari, published by O’Reilly.
I’ve read the first one and found it excellent. I haven’t read the second one, but I did read “C# 3.0 in a Nutshell” by the same authors when LINQ was released. I found that book to be far above the average of the books I usually read. It seems that the other books by these two authors are of the same caliber. We will also be using LINQPad, a LINQ learning tool written by Joseph Albahari.
We’re going to display the entities in the database. To do this, we’ll add two display methods to their classes. Let’s start with the [Doctor] entity:
// a doctor
public class Doctor
{
// data
[Key]
[Column("ID")]
public int? Id { get; set; }
[Required]
[MaxLength(5)]
[Column("TITLE")]
public string Title { get; set; }
[Required]
[MaxLength(30)]
[Column("NAME")]
public string Name { get; set; }
[Required]
[MaxLength(30)]
[Column("LAST_NAME")]
public string FirstName { get; set; }
// the doctor's appointment slots
public ICollection<TimeSlot> TimeSlots { get; set; }
[Column("TIMESTAMP")]
[Timestamp]
public byte[] Timestamp { get; set; }
// signature
public override string ToString()
{
return String.Format("Doctor[{0},{1},{2},{3},{4}]", Id, Title, FirstName, LastName, dump(Timestamp));
}
// short signature
public string ShortIdentity()
{
return ToString();
}
// utility
private string dump(byte[] timestamp){
string str = "";
foreach (byte b in timestamp)
{
str += b;
}
return str;
}
}
- lines 27–30: the class’s ToString method. Note that it does not display the collection from line 21;
- lines 32–37: the ShortIdentity method, which does the same thing.
Here, we need to explain the concepts of Lazy and Eager Loading to assess the impact of the two previous methods. We have seen that an entity can have dependencies on another entity. These dependencies are of two types:
- one-to-many, as above, where a doctor is linked to multiple time slots;
- many-to-one, as in the [Slot] entity below, where one or more slots are linked to the same doctor;
public class Slot
{
// data
...
[Required]
[Column("DOCTOR_ID")]
public int DoctorId { get; set; }
[Required]
[ForeignKey("MedecinId")]
public virtual Doctor Doctor { get; set; }
...
}
When dependencies are loaded at the same time as the entities to which they are attached, this is called Eager Loading. Otherwise, it is called Lazy Loading: dependencies are loaded only when they are referenced for the first time. By default, EF 5 uses Lazy Loading: dependencies are not loaded at the same time as the entity.
Let’s look at our [ToString] method above:
// the doctor's time slots
public ICollection<TimeSlot> TimeSlots { get; set; }
// signature
public override string ToString()
{
return String.Format("Doctor[{0},{1},{2},{3},{4}]", Id, Title, FirstName, LastName, dump(Timestamp));
}
// short signature
public string ShortIdentity()
{
return ToString();
}
The [ToString] method does not display the [Slots] dependency on line 2. If it had, it would have forced the loading of all the doctor’s slots before execution. It is to avoid this costly loading that the dependency was not included in the entity’s signature. Generally speaking, we will include two signatures in each entity:
- a ToString method that will display the entity and any one-to-many dependencies. As just explained, this will trigger the loading of the dependency;
- a ShortIdentity method that will not reference any dependencies. Therefore, no dependencies will be loaded;
The display methods for the other entities will be as follows:
The [Client] entity:
public class Client
{
// data
...
// the client's Rvs
public ICollection<Rv> Rvs { get; set; }
// signature
public override string ToString()
{
return String.Format("Client[{0},{1},{2},{3},{4}]", Id, Title, FirstName, LastName, dump(Timestamp));
}
// short signature
public string ShortIdentity()
{
return ToString();
}
}
- lines 9–12: the [ToString] method does not display the dependency on line 6;
The [Creneau] entity:
public class Slot
{
...
[Required]
[Column("DOCTOR_ID")]
public int DoctorId { get; set; }
[Required]
[ForeignKey("MedecinId")]
public virtual Doctor Doctor { get; set; }
// the Rvs for the slot
public ICollection<Rv> Appointments { get; set; }
// signature
public override string ToString()
{
return String.Format("Slot[{0},{1},{2},{3},{4}, {5}]", Id, StartTime, StartMinute, EndTime, EndMinute, Doctor, dump(Timestamp));
}
// short signature
public string ShortIdentity()
{
return String.Format("Slot[{0},{1},{2},{3},{4}, {5}, {6}]", Id, StartTime, StartMinute, EndTime, EndMinute, Timestamp, DoctorId, dump(Timestamp));
}
}
- line 16: the [ToString] method references the dependency on line 9. This will force it to be loaded;
- line 11: the [Rvs] dependency is not referenced. It will not be loaded;
- lines 21-22: the [ShortIdentity] method no longer references the [Medecin] reference from line 9. Therefore, it will not be loaded.
The [Rv] entity:
public class Rv
{
// data
...
[Column("CLIENT_ID")]
public int ClientId { get; set; }
[ForeignKey("ClientId")]
[Required]
public virtual Client Client { get; set; }
[Column("SLOT_ID")]
public int SlotId { get; set; }
[ForeignKey("CreneauId")]
[Required]
public virtual Slot Slot { get; set; }
// signature
public override string ToString()
{
return String.Format("Rv[{0},{1},{2},{3},{4}]", Id, Day, Client, Slot, dump(Timestamp));
}
// short signature
public string ShortIdentity()
{
return String.Format("Rv[{0},{1},{2},{3},{4}]", Id, Day, ClientId, SlotId, dump(Timestamp));
}
}
- lines 17–20: the [ToString] method references the dependencies on lines 9 and 14. This will force them to be loaded;
- lines 17–20: the [ShortIdentity] method prevents this, so the dependencies will not be loaded.
In conclusion, we must pay attention to the [ToString] methods of entities. If we do not pay attention to this, displaying a table can load half the database if the table has many dependencies.
With that explained, we write the following new code [Dump.cs]:
using RdvMedecins.Entities;
using RdvMedecins.Models;
using System;
using System.Linq;
namespace RdvMedecins_01
{
class Dump
{
static void Main(string[] args)
{
// dump the database
using (var context = new RdvMedecinsContext())
{
// clients
Console.WriteLine("Clients--------------------------------------");
var clients = from client in context.Clients select client;
foreach (Client client in clients)
{
Console.WriteLine(client);
}
// Doctors
Console.WriteLine("Doctors--------------------------------------");
var doctors = from doctor in context.Doctors select doctor;
foreach (Doctor doctor in doctors)
{
Console.WriteLine(doctor);
}
// time slots
Console.WriteLine("Time slots--------------------------------------");
var timeSlots = from timeSlot in context.TimeSlots select timeSlot;
foreach (TimeSlot timeSlot in timeSlots)
{
Console.WriteLine(timeSlot);
}
// Appointments
Console.WriteLine("Appointments--------------------------------------");
var appointments = from appointment in context.Appointments select appointment;
foreach (Rv rv in rvs)
{
Console.WriteLine(rv);
}
}
}
}
}
We will explain lines 17–21, which display the [Client] entities. The explanation provided will apply to the other entities as well.
// customers
Console.WriteLine("Clients--------------------------------------");
var clients = from client in context.Clients select client;
foreach (Client client in clients)
{
Console.WriteLine(client);
}
- line 3: the var keyword was introduced with C# 3.0. It allows you to avoid specifying the exact type of a variable. The compiler then infers the type from the type of the expression assigned to the variable;
- line 3: the expression assigned to the clients variable is a LINQ to Entities query. It includes SQL keywords ported to LINQ. The syntax used here is as follows:
from variable in DbSet select variable
A more general LINQ syntax is
from variable in collection select variable
The collection will be traversed, and for each element in it, the variable will be evaluated. This is only done when the [clients] variable from line 3 is iterated over by the for/each loop in lines 4–7. Until this happens, the [clients] variable is merely an unevaluated query;
- line 4: the [clients] query is iterated over. This will force the evaluation of the query. The rows of the [CLIENTS] table will be brought into the persistence context one by one;
- line 6: the [ToString] method of the [Client] entity is used for display. No dependencies are loaded;
Let’s move on to the following lines of code:
- Lines 24–28: The rows of the [DOCTORS] table are brought into the persistence context and displayed. No dependencies are loaded;
- lines 31–35: the rows of the [SLOTS] table are brought into the persistence context and displayed. We saw that the [ToString] method of this entity displays the [Doctor] dependency. However, this is already loaded. Therefore, there will be no reloading;
- lines 38–42: the rows of the [RVS] table are brought into the persistence context and displayed. We saw that the [ToString] method of this entity displays the [Client] and [Slot] dependencies. However, these are already loaded. Therefore, there will be no new loading.
Note that the display order is not neutral. If we had wanted to display the [Rv] entities first, their [ToString] method would have triggered the loading of the [Client] and [Creneau] entities linked to these appointments. The others would not have been loaded. They would have been loaded later in another view. This has an impact on performance. The previous code requires four SQL statements to display all entities. Now suppose we first query the [RVS] table of appointments. A first SQL query is required for the [RVS] table. Next, the [ToString] method of the [Rv] entity will trigger the potential loading of the associated [Client] and [Slot] entities. One SQL query is required for each. Assuming there are N2 clients and N3 time slots and that all these entities are referenced in the [RVS] table, displaying it will require 1+N2+N3 SQL queries. Therefore, performance is lower than in the version we examined. To display the [RVS] table with its dependencies, a table join would be necessary. This can be achieved using LINQ. We’ll revisit this with an example. For now, let’s remember that we must pay attention to the SQL queries underlying our LINQ code.
We configure the project to run this new code [1] and [2], then execute it:
 |
The console output is as follows:
3.5.4. Learning LINQ with LINQPad
Above, we used LINQ to Entity queries to display the contents of the database tables. Joseph Albahari has written a program to help you learn the different forms of LINQ. We’ll introduce it now.
LINQPad is available at the following URL [http://www.linqpad.net/]. Once installed, we launch it [1]:
 |
LINQ beginners can get started with the examples in the [Samples] tab [2], which provides a wide variety of examples. Let’s select example [3], which then opens in a new window [4]. The complete code for the example is as follows:
// Now for a simple LINQ-to-Objects query expression (note the lack of a semicolon):
from word in "The quick brown fox jumps over the lazy dog".Split()
orderby word.Length
select word
// Feel free to edit this... (no one's watching!) You'll be prompted to save any
// changes to a separate file.
//
// Tip: You can execute part of a query by highlighting it, and then pressing F5.
Lines 3–5 are an example of a LINQ to Objects query. The LINQ query follows the syntax:
from variable in collection orderby element1 select element2
- variable refers to the current element in the collection. In our example, this collection is the list of words resulting from the split string;
- the collection is sorted according to the element1 parameter of orderby. In our example, the collection of words will be sorted by length;
- the select keyword specifies what we want to extract from the current element variable in the collection. In our example, this will be the word.
Let’s run this LINQ query:
 |
- in [1]: a LINQ expression is executed by pressing [F5] or using the Run button;
- in [2]: the display. The words are displayed in order of their length. This simple example demonstrates the power of LINQ;
- in [3], you can download other examples, including those from the book "LINQ in Action" [4];
 |
- in 5, we choose an example from the book;
string[] words = { "hello", "wonderful", "linq", "beautiful", "world" };
// Group words by length
var groups =
from word in words
sort by word in ascending order
Group words by word.Length into lengthGroups
order by lengthGroups.Key in descending order
select new { Length = lengthGroups.Key, Words = lengthGroups };
// Print each group
foreach (var group in groups)
{
Console.WriteLine("Words of length " + group.Length);
foreach (string word in group.Words)
Console.WriteLine(" " + word);
}
- line 4: a new LINQ query with new keywords;
- line 5: the queried collection is the array of words from line 1;
- line 6: the collection is sorted in alphabetical order by word;
- line 7: the collection is grouped into (keyword into) a new collection called lengthGroups. lengthGroups.Key represents the grouping factor (keyword by), here the length of the words. lengthGroups groups together words with the same grouping factor, i.e., the same length;
- line 8: the lengthGroups collection is sorted by grouping key in descending order, so here by decreasing word size;
- line 9: from this collection, new objects (anonymous classes) are created with two fields:
- Length: the length of the words,
- Words: the words of that length;
Here, we can particularly see the benefit of the var keyword in line 4. Because we used an anonymous class in line 9, we cannot specify the type of the groups variable. The compiler, however, will assign an internal name to the anonymous class and use it to type the groups variable. It will then be able to determine whether the groups variable is used correctly
- Line 12: Iteration over the query from line 4. It is only at this point that it is evaluated. Recall that its execution will produce a collection of objects, specified on line 9;
- Line 14: We display the Length property of the current element, i.e., the length of the words;
- Lines 15–17: We display each element of the Words collection, i.e., the set of words with the length displayed previously.
When we execute this query, we get the following result in LINQPad:
 |
Now that we’ve seen a few examples of [LINQ to Object] queries, let’s look at [LINQ to Entity] queries that will allow us to query databases. First, we’ll connect to the SQL Server database we created and populated:
 |
- in [1], we add a connection to a database;
- in [2], the means of accessing the data source. To access the SQL Server database, we will use the [LINQPad Driver];
- in [3], it is also possible to retrieve a persistence context [DbContext] defined in an .exe or .dll assembly (option 3). Unfortunately, as of today (October 8, 2012), Entity Framework 5 is not supported;
- in [4], it is possible to download drivers for DBMSs other than SQL Server;
- In 5, we will download the driver for the MySQL and Oracle DBMS;
 |
- in [6], the downloaded driver;
- in [7], we connect to a SQL Server database;
 |
- in [8], the database is on the local server;
- in [9], we connect using the sa / sqlserver2012 credentials;
- in [10], to the [rdvmedecins-ef] database that we created;
- In [11], you can test the connection;
- In [12], finish the wizard;
- In [13], the connection appears in LINQPad.
The entities were created from the [rdvmedecins-ef] table. They are as follows:
 |
- In [1], [CLIENTS] represents the set of [Client] entities. Each entity has:
- the properties (ID, TITLE, LAST_NAME, FIRST_NAME, TIMESTAMP),
- a one-to-many relationship [CLIENTRVS];
- In [2], [CRENEAUXes] represents the set of [Creneau] entities. Each entity has:
- the properties (ID, START_TIME, MIN_TIME, END_TIME, MAX_TIME, DOCTOR_ID, TIMESTAMP),
- a one-to-many relationship [CRENEAURVS],
- a many-to-one relationship [DOCTOR];
- in [3], the entity [MEDECINS] represents the set of [Medecin] entities. Each entity has:
- the properties (ID, TITLE, LAST_NAME, FIRST_NAME, TIMESTAMP),
- a one-to-many relationship [DOCTOR-SLOTS];
- in [4], the entity [RVS] represents the set of [Rv] entities. Each entity has:
- the properties (ID, DAY, CLIENT_ID, SLOT_ID, TIMESTAMP),
- a many-to-one relationship [CLIENT],
- a many-to-one relationship [SLOT].
Note that the names of the properties above are different from the names we have used so far. This doesn’t matter. We just want to learn the basic principles of database querying.
Let’s see how we can query this entity database. For example, we want a list of doctors sorted by their TITLE and LAST_NAME:
 |
- in [1], we create a new query;
- in [2], the query text;
 |
- in [3], the result of the query;
- in [4], the same query using lambda expressions. A query with lambda expressions is less readable than a text query, and you might prefer to avoid them. However, they are sometimes indispensable because they allow certain things that text queries do not. A lambda expression denotes a function with an input parameter a and an output parameter b, in the form a=>b. The OrderBy method above accepts a lambda function as its sole parameter. This provides the parameter by which a collection should be sorted. Thus, MEDECINS.OrderBy(m=>m.TITRE) is the list of doctors sorted by their titles. The statement should be read as a pipeline on a collection. The collection of doctors is provided as input to the OrderBy method. This method will process the [Doctor] entities one by one. In the lambda expression m=>m.TITLE, m represents the input to the lambda function. It can be named as desired. Here, the input to the lambda function will be a [Doctor] entity. The function m=>m.TITLE reads as follows: if I call m my input (a [Doctor] entity), then my output is m.TITLE, i.e., the doctor’s title. MEDECINS.OrderBy(m=>m.TITRE) is in turn a collection, the collection of doctors sorted by their titles. This new collection can feed into another method, in this example the ThenBy method. This method works on the same principle. It is used to specify additional parameters for sorting the collection.
Reading the lambda code equivalent to the text code we usually type is a good way to learn it;
 |
- in 5, the SQL query sent to the database. Here again, we will read this code carefully. It allows us to evaluate the actual cost of a LINQ query.
Below, we present a few examples of LINQ queries. For each one, we show the displayed results and the equivalent lambda and SQL code. To understand these queries, we must recall the many-to-one relationships that connect entities to one another. It is through these that we navigate from one entity to another. They are called navigational properties.
 |
// Customers whose title is "Mr" sorted in descending order by name
Results:
 |
LINQ | |
Lambda | |
SQL | |
// all time slots with the associated doctor
Results (partial):
 |
LINQ | |
Lambda |  |
SQL | |
// all appointments with the associated client and doctor
Results:
 |
LINQ | |
Lambda |  |
SQL | |
// doctors with no appointments
Results:
 |
LINQ | |
Lambda |  |
SQL | |
There is no LINQ query for this request. You must use lambda expressions. This one reads as follows: I take the collection of doctors (DOCTORS) and I keep (Where) only those doctors (m) for whom I cannot find an appointment (rv) with that doctor (m) in the appointment collection (APPOINTMENTS).
// Ms. Pélissier's time slots
(Partial) results:
 |
LINQ | |
Lambda |  |
SQL | |
// Number of appointments for Ms. Pélissier on October 8, 2012
Results:
 |
LINQ | |
Lambda | |
SQL | |
// List of clients who made an appointment with Ms. Pélissier on 10/08/2012
Results:
 |
LINQ | |
Lambda |  |
SQL | |
// number of time slots per doctor
Results:
 |
LINQ | |
Lambda |  |
SQL | |
3.5.5. Modifying an entity attached to the persistence context
We have covered the following operations on the persistence context:
- adding an element to the context ([dbContext].[DbSet].Add);
- Remove an item from the context ([dbContext].[DbSet].Remove);
- Query a context using LINQ queries.
To synchronize the context with the database, write [dbContext].SaveChanges().
 |  |
The [ModifyAttachedEntity] code demonstrates how to modify an entity attached to the context:
using System;
using System.Data;
using System.Linq;
using RdvMedecins.Entities;
using RdvMedecins.Models;
namespace RdvMedecins_01
{
class ModifyAttachedEntity
{
static void Main(string[] args)
{
Client client1, client2, client3;
// 1st context
using (var context = new RdvMedecinsContext())
{
// clear the current database
foreach (var client in context.Clients)
{
context.Clients.Remove(client);
}
foreach (var doctor in context.Doctors)
{
context.Doctors.Remove(doctor);
}
// add a client
client1 = new Client { LastName = "xx", FirstName = "xx", Title = "xx" };
context.Clients.Add(client1);
// tracking
Console.WriteLine("client1--before");
Console.WriteLine(client1);
// save context
context.SaveChanges();
// tracking
Console.WriteLine("client1--after");
Console.WriteLine(client1);
}
// 2nd context
using (var context = new RdvMedecinsContext())
{
// retrieve client1 from client2
client2 = context.Clients.Find(client1.Id);
// tracking
Console.WriteLine("client2");
Console.WriteLine(client2);
// we modify client2
client2.Name = "yy";
// save context
context.SaveChanges();
}
// 3rd context
using (var context = new RdvMedecinsContext())
{
// retrieve client2 from client3
client3 = context.Clients.Find(client2.Id);
// tracking
Console.WriteLine("client3");
Console.WriteLine(client3);
}
}
}
}
- line 15: application context is opened;
- lines 18–25: the context is cleared. More precisely, all entities are loaded into the context from the database and then set to a "deleted" state. Note that at this stage, the database has not changed. As long as the context is not synchronized with the database, the database remains unchanged. Recall that deleting the [Doctor] and [Client] entities is sufficient to empty the database through cascading deletes;
- lines 27–28: a new client is added to the database;
- lines 30-31: the client is displayed before being saved to the database;
- line 33: the context is synchronized with the database. Entities marked as "deleted" will be subject to an SQL DELETE operation, and the added entity to an SQL INSERT operation;
- lines 35-36: the customer is displayed after synchronization with the database;
The result displayed in the console is as follows:
Note the following points:
- Before synchronization with the database, the client has neither a primary key nor a timestamp;
- after synchronization, it has them. Recall here that the primary key was configured to be generated by SQL Server. Similarly, this DBMS automatically generates the timestamp;
- line 37: the persistence context is closed. The entities it contained become "detached." They exist as objects but not as entities attached to a persistence context;
- line 39: a new empty context is started;
- line 42: the client is retrieved directly from the database via its primary key. It is then brought into the context. If it is not found, the Find method returns a null pointer;
- lines 48–49: we display it;
This produces the following result:
- Line 47: We modify it;
- line 49: we synchronize the context with the database. EF will detect that certain elements of the context have been modified since they were added to it. For these elements, it will generate SQL UPDATE statements for the database. So here, the synchronization will consist of a single UPDATE statement;
- line 50: the second context is closed. The client2 entity that was attached to the context is now detached from it;
- line 52: a third empty context is opened;
- line 55: the single client from the database is brought into it again. We want to see if the modification made to it in the previous context has been reflected in the database;
- lines 57–58: the client is displayed. This yields the following result:
The customer’s name has indeed been updated in the database. Note that its timestamp has also been updated.
- line 59: we close the context. Incidentally, note that unlike the previous two instances, we did not need to synchronize the context with the database (SaveChanges) beforehand because the context had not been modified.
3.5.6. Management of detached entities
Let’s return to the layered architecture of an application such as the one in the case study:
 |
The [DAO] layer uses the EF5 ORM to access data. We have the basic building blocks of this layer. Each method will open a persistence context, perform the necessary operations (insert, update, delete, query), and then close it. The entities managed by the [DAO] layer will be passed up to the ASP.NET web layer. In this layer, they are outside the persistence context and therefore detached. In the web layer, a user can modify these entities (add, update, delete). When they return to the [DAO] layer, they are still detached. However, the [DAO] layer will need to reflect the changes made by the user in the database. It will therefore have to work with detached entities. Let’s look at the three possible cases:
Adding a detached entity
This is the standard procedure for an addition. Simply add (Add) the detached entity to the context, ensuring that its primary key is null.
Modifying a detached entity
You can use the following code:
- The [DbContext].Entry(detached-entity) method will add the entity to the context;
- the state of this entity is set to "modified" so that it is subject to an SQL UPDATE statement.
Deleting a detached entity
You can use the following code:
- Line 1: Add the entity with the same primary key as the detached entity to the context;
- Line 2: We delete it:
Note that this requires a SELECT followed by a DELETE in the database, whereas normally a DELETE alone is sufficient. You can also follow the example of modifying a detached entity and write:
Since I was unable to implement logging for SQL operations performed on the database, I do not know if one method is preferable to the other.
Here is an example:
 |  |
The code for the [ModifyDetachedEntities] program is as follows:
using System;
using System.Data;
using RdvMedecins.Entities;
using RdvMedecins.Models;
namespace RdvMedecins_01
{
class ModifyDetachedEntities
{
static void Main(string[] args)
{
Client client1;
// clear the current database
Erase();
// add a client
using (var context = new RdvMedecinsContext())
{
// create client
client1 = new Client { Title = "x", LastName = "x", FirstName = "x" };
// Add the client to the context
context.Clients.Add(client1);
// save the context
context.SaveChanges();
}
// display database
Dump("1-----------------------------");
// client1 is not in the context - modify it
client1.Name = "y";
// new context
using (var context = new RdvMedecinsContext())
{
// here, we have an empty context
// Set client1 to the modified state in the context
context.Entry(client1).State = EntityState.Modified;
// Save the context
context.SaveChanges();
}
// basic display
Dump("2-----------------------------");
// delete entity outside of context
using (var context = new RdvMedecinsContext())
{
// Here, we have a new empty context
// we add client1 to the context in a deleted state
context.Entry(client1).State = EntityState.Deleted;
// we save the context
context.SaveChanges();
}
// basic display
Dump("3-----------------------------");
}
static void Erase()
{
// clears the database
using (var context = new RdvMedecinsContext())
{
foreach (var client in context.Clients)
{
context.Clients.Remove(client);
}
foreach (var doctor in context.Doctors)
{
context.Doctors.Remove(doctor);
}
// save the context
context.SaveChanges();
}
}
static void Dump(string str)
{
Console.WriteLine(str);
// displays the database
using (var context = new RdvMedecinsContext())
{
foreach (var rv in context.Rvs)
{
Console.WriteLine(rv);
}
foreach (var slot in context.Slots)
{
Console.WriteLine(slot);
}
foreach (var client in context.Clients)
{
Console.WriteLine(client);
}
foreach (var doctor in context.Doctors)
{
Console.WriteLine(doctor);
}
}
}
}
}
- line 15: the database is cleared;
- lines 17–25: a customer is added to the database;
- line 27: displays the contents of the database;
- After line 25, the persistence context no longer exists. Therefore, there are no longer any attached entities. The client1 entity has transitioned to the "detached" state;
- line 29: the name of the detached entity is modified;
- line 31: a new empty context is opened;
- line 35: the detached entity client1 is placed in the context in a "modified" state;
- line 37: the context is synchronized with the database;
- line 38: it is closed;
- line 40: the database is displayed;
The client's name has been successfully updated in the database. Note that the timestamp has been updated;
- line 42: opening a new empty context;
- line 46: the detached entity client1 is placed in the context in a "deleted" state;
- line 48: the context is synchronized with the database;
- line 49: it is closed;
- line 51: the database is displayed;
The entity has indeed been deleted from the database.
Now, we will look at the two modes for loading an entity's dependencies: Lazy and Eager Loading.
3.5.7. Lazy and Eager Loading
Let’s revisit the many-to-one dependency schema of our four entities:
 |
Above, the [Creneau] entity has a navigational property [Creneau.Medecin] pointing to the [Medecin] entity. This is called a dependency. We’ve seen that there are also one-to-many dependencies. The principle explained here applies to them as well.
By default, EF 5 is in Lazy Loading mode: when it brings an entity into the persistence context from the database, it does not bring its dependencies. These will be loaded when they are first used. This is a common-sense measure. If this were not the case, bringing the appointments into the context would, based on the dependencies above, result in:
- the [Time Slot] entities linked to the appointments;
- the [Doctor] entities linked to those slots;
- the [Clients] entities linked to the appointments.
Sometimes, however, we need an entity and its dependencies. We will illustrate both loading modes.
 |  |
The code for [LazyEagerLoading] is as follows:
using RdvMedecins.Entities;
using RdvMedecins.Models;
using System;
using System.Linq;
namespace RdvMedecins_01
{
class LazyEagerLoading
{
// entities
static Doctor[] doctors;
static Client[] clients;
static Slot[] slots;
static void Main(string[] args)
{
// initialize the database
InitBase();
Console.WriteLine("Initialization complete");
// eager loading
Slot slot;
int slotId = (int)slots[0].Id;
using (var context = new RdvMedecinsContext())
{
// slot #0
slot = context.Slots.Include("Doctor").Single<Slot>(c => c.Id == slotId);
Console.WriteLine(slot.ShortIdentity());
}
// display dependency
try
{
Console.WriteLine("Doctor={0}", creneau.Doctor);
}
catch (Exception e)
{
Console.WriteLine("The following error occurred: {0}", e);
}
// lazy loading - default mode
using (var context = new RdvMedecinsContext())
{
// slot #0
slot = context.Slots.Single<Slot>(c => c.Id == slotId);
Console.WriteLine(slot.ShortIdentity());
}
// display dependency
try
{
Console.WriteLine("Doctor={0}", creneau.Doctor);
}
catch (Exception e)
{
Console.WriteLine("The following error occurred: {0}", e);
}
}
static void InitBase()
{
// Initialize the database
using (var context = new RdvMedecinsContext())
{
// clear the current database
...
// Initialize the database
// clients
clients = new Client[] {
new Customer { Title = "Mr.", LastName = "Martin", FirstName = "Jules" },
new Customer { Title = "Ms.", LastName = "German", FirstName = "Christine" },
new Customer { Title = "Mr.", LastName = "Jacquard", FirstName = "Jules" },
new Customer { Title = "Ms.", LastName = "Bistrou", FirstName = "Brigitte" }
};
...
// Appointments
context.Appointments.Add(new Appointment { Date = new System.DateTime(2012, 10, 8), Client = clients[0], Slot = slots[0] });
// save the persistence context
context.SaveChanges();
}
}
}
}
- line 18: we start from a known base, the one used so far. After this operation, the arrays in lines 11–13 are filled with detached entities;
- lines 21–22: we focus on the first time slot and the associated doctor;
- line 23: new context;
- line 26: we place the time slot in the context along with its dependency (eager loading). Because this is not the default mode, we must explicitly request this dependency. The Include method allows us to do this. Its parameter is the name of the dependency within the entity brought into the context. The query that brings the entity into the context uses lambda expressions. The Single method allows you to specify a condition to retrieve a single entity. Here, we search the database for the [Creneau] entity that has the primary key of slot #0;
- line 27: we display the retrieved entity. Let’s review the two write methods used in entities:
// signature
public override string ToString()
{
return String.Format("Creneau[{0},{1},{2},{3},{4}, {5},{6}]", Id, StartTime, StartMinute, EndTime, EndMinute, Doctor, dump(Timestamp));
}
// short signature
public string ShortIdentity()
{
return String.Format("Slot[{0},{1},{2},{3},{4}, {5}, {6}]", Id, StartTime, StartMinute, EndTime, EndMinute, DoctorId, dump(Timestamp));
}
- lines 2-5: the [ToString] method displays the [Doctor] dependency. If this is not already in the context, it will be looked up in the database to add it;
- lines 8-11: the [ShortIdentity] method does not display the [Doctor] dependency. It will therefore not be looked up in the database if it is not in the context;
At this point, the console output is as follows:
- Line 28: the context is closed;
- Lines 30–37: We attempt to write the entity’s [Doctor] dependency. Recall how Lazy Loading works: a dependency is loaded upon its first use if it is not present. Here, it is normally present. The output is as follows:
- lines 39–44: in a new context, slot #0 is searched for again in the database and brought into the context. Here, the [Doctor] dependency is not explicitly requested. It will therefore not be brought in (Lazy Loading);
- line 43: the short identity of the slot is displayed as follows:
Here, it is important to use ShortIdentity instead of ToString to display the entity. If ToString is used, the [Doctor] dependency will be displayed, and to do so, it will be looked up in the database. However, we do not want that.
- Line 44: the context is closed;
- lines 46–53: we attempt to display the entity’s dependency. It is important to do this out of context; otherwise, it will be looked up in the database and found. Here, we are out of context. The [Creneau] entity is detached and its [Medecin] dependency is missing (Lazy Loading). What will happen? The screen display is as follows:
EF found that the [Medecin] dependency was missing. It attempted to load it, but since the context was closed, this operation was no longer possible. We will note this exception [System.ObjectDisposedException] as it is characteristic of loading a dependency outside of an open context.
Now let’s examine concurrent access to entities.
3.5.8. Concurrency in Entity Access
Let’s revisit the definition of the [Client] entity:
public class Client
{
// data
[Key]
[Column("ID")]
public int? Id { get; set; }
[Required]
[MaxLength(5)]
[Column("TITLE")]
public string Title { get; set; }
[Required]
[MaxLength(30)]
[Column("NAME")]
public string Name { get; set; }
[Required]
[MaxLength(30)]
[Column("LAST_NAME")]
public string FirstName { get; set; }
// the client's Rvs
public ICollection<Rv> Rvs { get; set; }
[Column("TIMESTAMP")]
[Timestamp]
public byte[] Timestamp { get; set; }
// signature
...
}
We will focus on the [Timestamp] field in row 23. We know that its value is generated by the DBMS. We also noted that the [Timestamp] annotation in row 22 causes EF 5 to use the annotated field to manage concurrency in access to entities. Let’s recall what concurrency management is:
- a process P1 reads a row L from the [DOCTORS] table at time T1. The row has timestamp TS1;
- a process P2 reads the same row L from the [DOCTORS] table at time T2. The row has timestamp TS1 because process P1 has not yet committed its modification;
- process P1 commits its modification to row L. The timestamp of row L then changes to TS2;
- Process P2 commits its modification to row L. The ORM then throws an exception because process P2 has a timestamp TS1 for row L that differs from the timestamp TS2 found in the database.
This is called optimistic concurrency management. With EF 5, a field serving this role must have one of two attributes: [Timestamp] or [ConcurrencyCheck]. SQL Server has a [timestamp] type. A column of this type has its value automatically generated by SQL Server upon any insertion or modification of a row. Such a column can then be used to manage concurrency.
We will illustrate this concurrent access with two threads that will simultaneously modify the same [Client] entity in the database. The project evolves as follows:
 |  |
The code for the [ConcurrentAccess] program is as follows:
using System;
using System.Data;
using System.Linq;
using System.Threading;
using RdvMedecins.Entities;
using RdvMedecins.Models;
namespace RdvMedecins_01
{
// object shared with threads
class Data
{
public int Duration { get; set; }
public string Name { get; set; }
public Client Client { get; set; }
}
// test program
class ConcurrentAccess
{
static void Main(string[] args)
{
Client client1;
using (var context = new RdvMedecinsContext())
{
// main thread
Thread.CurrentThread.Name = "main";
// clear the current database
foreach (var client in context.Clients)
{
context.Clients.Remove(client);
}
foreach (var doctor in context.Doctors)
{
context.Doctors.Remove(doctor);
}
// add a client
client1 = new Client { LastName = "xx", FirstName = "xx", Title = "xx" };
context.Clients.Add(client1);
// tracking
Console.WriteLine("{0} client1--before saving the context", Thread.CurrentThread.Name);
Console.WriteLine(client1.ShortIdentity());
// save
context.SaveChanges();
// follow-up
Console.WriteLine("{0} client1--after saving the context", Thread.CurrentThread.Name);
Console.WriteLine(client1.ShortIdentity());
}
// We will modify client1 using two threads
// thread t1
Thread t1 = new Thread(Modify);
t1.Name = "t1";
t1.Start(new Data { Duration = 5000, Name = "yy", Client = client1 });
// thread t2
Thread t2 = new Thread(Modifie);
t2.Name = "t2";
t2.Start(new Data { Duration = 5000, Name = "zz", Client = client1 });
// waiting for both threads to finish
Console.WriteLine("Thread {0} -- waiting for both threads to finish", Thread.CurrentThread.Name);
t1.Join();
t2.Join();
Console.WriteLine("Thread {0} -- end of wait for both threads", Thread.CurrentThread.Name);
// display the change - only one should have succeeded
using (var context = new RdvMedecinsContext())
{
// retrieve client1 from client2
Client client2 = context.Clients.Find(client1.Id);
Console.WriteLine("Thread {0} client2", Thread.CurrentThread.Name);
Console.WriteLine("Thread {0} {1}", Thread.CurrentThread.Name, client2.ShortIdentity());
}
}
// thread
static void Modify(object info)
{
...
}
- line 26: we start an empty context;
- line 29: we name the current thread to distinguish it from the two threads that will be created later;
- lines 31–38: the [Doctor] and [Client] entities are set to the "deleted" state;
- lines 40–41: a client is added to the context;
- lines 43–44: display it before context synchronization;
- line 46: context synchronization with the database: entities in the "deleted" state will be removed from the database. The [Client] entity placed in the context will be inserted into the database. It will be the only element in the database;
- lines 47-49: the customer is displayed after context synchronization. At this point, the screen displays are as follows:
Note that after context synchronization, the client has a primary key and a timestamp;
- line 50: the context is closed;
- line 53: a thread t1 is associated with the [Modify] method on line 84. This means that when it is launched, it will execute the [Modify] method;
- line 54: the thread t1 is given a name;
- line 55: thread t1 is launched. Parameters are passed to it in the form of a [Data] structure defined on lines 12–17:
- Duration: The thread will stop X seconds before completing its execution,
- Client: a reference to the client to be updated in the database,
- Name: the name to give this client;
- lines 57–59: same procedure with a second thread. Ultimately, two threads will attempt to change the name of the same client in the database;
- lines 60–63: after launching the two threads, the main thread waits for them to finish executing;
- line 62: waiting for thread t1 to finish;
- line 63: waiting for thread t2 to finish;
- line 64: we don’t know in which order the two threads will finish. What is certain is that by line 64, they have finished;
- lines 66-72: in a new context, we look up the client in the database to see its status.
Now let’s see what the two threads t1 and t2 do. They execute the following [Modify] method:
static void Modify(object info)
{
// retrieve the parameter
Data data = (Data)infos;
try
{
using (var context = new RdvMedecinsContext())
{
Console.WriteLine("Start of Thread {0}", Thread.CurrentThread.Name);
// retrieve client1 from client2
Client client2 = context.Clients.Find(data.Client.Id);
Console.WriteLine("Thread {0} client2", Thread.CurrentThread.Name);
Console.WriteLine("Thread {0} {1}", Thread.CurrentThread.Name, client2.ShortIdentity());
// Modify client2
client2.Name = data.Name;
// wait a moment
Thread.Sleep(data.Duration);
// save the changes
context.SaveChanges();
}
}
catch (Exception e)
{
// exception
Console.WriteLine("Thread {0} {1}", Thread.CurrentThread.Name, e);
}
// end of thread
Console.WriteLine("End of Thread {0}", Thread.CurrentThread.Name);
}
- line 4: retrieve the thread parameters (Duration, Name, Client);
- line 7: new context;
- line 11: the client is brought into the context;
- lines 12–13: monitoring to check the client’s status;
- line 15: change its name;
- Line 17: The thread pauses for a duration of milliseconds. This has an interesting effect. The thread releases the processor that was executing it, making way for another thread. In our example, we have three threads: main, t1, and t2. The main thread is paused, waiting for threads t1 and t2 to finish. Assuming that thread t1 has the processor first, it now yields it to thread t2. This will result in thread t2 reading exactly the same data as thread t1—the same client with the same timestamp;
- Line 19: the context is synchronized with the database. Let’s assume again that thread t1 wakes up first. It will save the client with the name "yy". It will be able to do so because it has the same timestamp as in the database. Because of this update, the DBMS will modify the timestamp. When thread t2 wakes up in turn, it will have a client with a timestamp different from the one now in the database. Its update will be rejected.
The screen displays are as follows:
- line 4: the client in the database;
- line 9: the client as read by thread t2;
- line 11: the client as read by thread t1. Both threads have therefore read the same thing;
- line 12: thread t2 finishes first. It was therefore able to perform its update. The name must have changed to "zz";
- line 13: thread t1 throws a [System.Data.OptimisticConcurrencyException]. EF detected that it did not have the correct timestamp;
- line 21: thread t1 finishes in turn;
- line 22: the main thread has finished waiting;
- line 24: the main thread displays the client in the database. It is indeed thread t2 that won. The name is "zz". Note that the timestamp has changed.
Now, let’s examine another aspect: the transaction that governs the synchronization of the persistence context with the database.
3.5.9. Synchronization within a transaction
The [CRENEAUX] table has a uniqueness constraint that we added manually (see section 2.2.4, page 12):
We will proceed as follows: we will add two appointments at the same time for the same doctor, on the same day, and in the same time slot. Let’s see what happens.
The project evolves as follows:
 |  |
The code for the [SynchronisationTransaction] program is as follows:
using System;
using System.Linq;
using RdvMedecins.Entities;
using RdvMedecins.Models;
namespace RdvMedecins_01
{
// test program
class TransactionSynchronization
{
static void Main(string[] args)
{
using (var context = new RdvMedecinsContext())
{
// clear the current database
foreach (var client in context.Clients)
{
context.Clients.Remove(client);
}
foreach (var doctor in context.Doctors)
{
context.Doctors.Remove(doctor);
}
context.SaveChanges();
}
// Create a client
Client client1 = new Client { LastName = "xx", FirstName = "xx", Title = "xx" };
// create a doctor
Doctor doctor1 = new Doctor { LastName = "xx", FirstName = "xx", Title = "xx" };
// create a slot for this doctor
TimeSlot timeSlot1 = new TimeSlot { StartHour = 8, StartMinute = 20, EndHour = 8, EndMinute = 40, Doctor = doctor1 };
// create two Appointments for this doctor and this patient, same day, same slot
Appointment appointment1 = new Appointment { Client = client1, Slot = slot1, Day = new DateTime(2012, 10, 18) };
Appointment appointment2 = new Appointment { Client = client1, Slot = slot1, Day = new DateTime(2012, 10, 18) };
try
{
// we put all of this into the persistence context
using (var context = new RdvMedecinsContext())
{
context.Clients.Add(client1);
context.Slots.Add(slot1);
context.Doctors.Add(doctor1);
context.Appointments.Add(appointment1);
context.Appointments.Add(appointment2);
// we save the context - we should get an exception
// because the underlying database has a uniqueness constraint preventing
// two appointments on the same day and in the same time slot
context.SaveChanges();
}
}
catch (Exception e)
{
Console.WriteLine("Error: {0}", e);
}
// if the save occurs within a transaction, then nothing should have been inserted into the database
// due to the previous exception—we check
using (var context = new RdvMedecinsContext())
{
// clients
Console.WriteLine("Clients--------------------------------------");
var clients = from client in context.Clients select client;
foreach (Client client in clients)
{
Console.WriteLine(client);
}
// Doctors
Console.WriteLine("Doctors--------------------------------------");
var doctors = from doctor in context.Doctors select doctor;
foreach (Doctor doctor in doctors)
{
Console.WriteLine(doctor);
}
// Time slots
Console.WriteLine("Time slots--------------------------------------");
var timeSlots = from timeSlot in context.TimeSlots select timeSlot;
foreach (TimeSlot timeSlot in timeSlots)
{
Console.WriteLine(timeSlot);
}
// Appointments
Console.WriteLine("Appointments--------------------------------------");
var appointments = from appointment in context.Appointments select appointment;
foreach (Rv rv in rvs)
{
Console.WriteLine(rv);
}
}
}
}
}
- lines 15–27: a persistence context is used to empty the database;
- line 30: creation of a [Client] object;
- line 32: creation of a [Doctor] object;
- line 34: creation of a [Slot] object;
- line 36: creation of an [Appointment] object;
- line 37: creation of a second [Appointment] object identical to the previous one;
- line 41: opening a new context;
- lines 43–47: the previously created objects are attached to the new context. Note here that, by taking dependencies into account, we could have minimized the number of Add operations. However, EF will optimize the SQL INSERT statements to be sent to the database;
- line 51: the context is synchronized with the database. As the comment indicates, the insertion of one of the two appointments must fail due to the uniqueness constraint on the [RVS] table. But more than that, if the synchronization occurs within a transaction, everything must be rolled back. Therefore, no insertion should take place. The database must remain empty;
- line 53: the context is closed;
- lines 61–90: display of the database contents. It must be empty.
The screen display is as follows:
- line 1: exception due to a violation of the unique constraint on the [RVS] table;
- lines 9–12: the database is indeed empty. The synchronization of the context with the database therefore took place within a transaction.
There are undoubtedly other aspects to explore in EF 5. But we know enough to return to our study of a multi-layer architecture. At the beginning of this document, the reader will find references to articles and books that will allow them to deepen their knowledge of EF 5.
3.6. Study of a Multi-Layer Architecture Based on EF 5
We return to our case study described in Section 2. This is an ASP.NET web application structured as follows:
 |
We will begin by building the [DAO] data access layer. This layer will be based on EF5.
3.6.1. The new project
We create a new VS 2012 console project [RdvMedecins-SqlServer-02] in the current solution [1]:
 |
We add four folders [2] to it, into which we will organize our code. The [Entities] folder is a copy of the [Entities] folder from the previous project. After copying, errors appear because we do not have the correct references. We need to add a reference to Entity Framework 5. To do this, we will follow the method explained in section 3.4, page 21. The list of references becomes as follows [3]:
 |
At this point, the project should no longer have any compilation errors. From the previous project, we also copy the [App.config] file, which configures the database connection:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
</entityFramework>
<!-- database connection string -->
<connectionStrings>
<add name="myContext"
connectionString="Data Source=localhost;Initial Catalog=rdvmedecins-ef;User Id=sa;Password=sqlserver2012;"
providerName="System.Data.SqlClient" />
</connectionStrings>
<!-- the factory provider -->
<system.data>
<DbProviderFactories>
<add name="SqlClient Data Provider"
invariant="System.Data.SqlClient"
description=".NET Framework Data Provider for SQL Server"
type="System.Data.SqlClient.SqlClientFactory, System.Data,
Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
/>
</DbProviderFactories>
</system.data>
</configuration>
3.6.2. The Exception Class
We will use a project-specific exception class. This is the one that will be thrown by the [DAO] layer:
 |
The [DAO] layer will catch all exceptions that are propagated up to it and encapsulate them in an exception of type [RdvMedecinsException]. This exception will be as follows:
using System;
namespace RdvMedecins.Exceptions
{
public class RdvMedecinsException : Exception
{
// properties
public int Code { get; set; }
// constructors
public RdvMedecinsException()
: base()
{
}
public RdvMedecinsException(string message)
: base(message)
{
}
public RdvMedecinsException(int code, string message)
: base(message)
{
Code = code;
}
public RdvMedecinsException(int code, string message, Exception ex)
: base(message, ex)
{
Code = code;
}
// identity
public override string ToString()
{
if (InnerException == null)
{
return string.Format("RdvMedecinsException[{0},{1}]", Code, base.Message);
}
else
{
return string.Format("RdvMedecinsException[{0},{1},{2}]", Code, base.Message, base.InnerException.Message);
}
}
}
}
- Line 5: The class extends the [Exception] class;
- line 9: it adds an error code to its base class;
- lines 12–32: the various constructors incorporate the [Code] field.
The project evolves as follows:
 |
3.6.3. The [DAO] layer
 |
The [DAO] layer provides an interface to the [ASP.NET] layer. To identify this, look at the application’s web pages:
 |
- in [1] above, the drop-down list has been populated with the list of doctors. The [DAO] layer will provide this list;
- in [2], the [DAO] layer will provide;
- the list of a doctor’s appointments for a given day,
- a list of a doctor’s available time slots,
- additional information about the selected doctor;
 |
- in [3], the drop-down list of clients will be provided by the [DAO] layer;
 |
- in [4], the user confirms an appointment. The [DAO] layer must be able to add it to the database. It must also be able to provide additional information about the selected client;
 |
- in 5, the user deletes an appointment. The [DAO] layer must allow this.
With this information, the [IDao] interface of the [DAO] layer could be as follows:
using System;
using System.Collections.Generic;
using RdvMedecins.Entities;
namespace RdvMedecins.Dao
{
public interface IDao
{
// list of clients
List<Client> GetAllClients();
// list of doctors
List<Doctor> GetAllDoctors();
// list of a doctor's available time slots
List<TimeSlot> GetDoctorTimeSlots(int doctorId);
// list of appointments for a given doctor on a given day
List<Appointment> GetDoctorAppointmentsByDay(int doctorId, DateTime day);
// add an appointment
int AddAppointment(DateTime day, int slotId, int clientId);
// delete an appointment
void DeleteAppointment(int appointmentId);
// find an entity T via its primary key
T Find<T>(int id) where T : class;
}
}
The methods in lines 10–20 are derived from the analysis just performed. The method in line 22 is there to address the fact that we are working with lazy loading. If, in the [ASP.NET] layer, we need a dependency on an entity, we will retrieve it from the database using this method.
The [Dao] implementation of this interface will be as follows:
using System;
using System.Collections.Generic;
using System.Linq;
using RdvMedecins.Entities;
using RdvMedecins.Exceptions;
using RdvMedecins.Models;
namespace RdvMedecins.Dao
{
public class Dao : IDao
{
//list of clients
public List<Client> GetAllClients()
{
// list of clients
List<Client> clients = null;
try
{
// open persistence context
using (var context = new RdvMedecinsContext())
{
// list of clients
clients = context.Clients.ToList();
}
}
catch (Exception ex)
{
throw new RdvMedecinsException(1, "GetAllClients", ex);
}
// return the result
return clients;
}
// list of doctors
public List<Doctor> GetAllDoctors()
{
// list of doctors
List<Doctor> doctors = null;
try
{
// open persistence context
using (var context = new RdvMedecinsContext())
{
// list of doctors
doctors = context.Doctors.ToList();
}
}
catch (Exception ex)
{
throw new RdvMedecinsException(2, "GetAllMedecins", ex);
}
// return the result
return doctors;
}
// list of time slots for a given doctor
public List<TimeSlot> GetDoctorSlot(int doctorId)
{
...
}
// List of a doctor's appointments for a given day
public List<Appointment> GetDoctorAppointmentsForDay(int doctorId, DateTime day)
{
...
}
// Add an appointment
public int AddAppointment(DateTime day, int slotId, int clientId)
{
...
}
// delete an appointment
public void DeleteAppointment(int appointmentId)
{
...
}
// find a client
public Client FindClient(int id)
{
...
}
// find a time slot
public Slot FindSlot(int id)
{
...
}
// find a doctor
public Doctor FindDoctor(int id)
{
....
}
// find an appointment
public Appointment FindAppointment(int id){
...
}
}
}
Let's explain the [GetAllClients] method, which should return a list of all clients:
- lines 18–31: the client search is performed within a try/catch block. The same will apply to all subsequent methods;
- line 21: opening a new context;
- line 24: [Client] entities are loaded into the context and placed in a list.
The [GetAllMedecins] method, which returns a list of all doctors, is similar (lines 37–57).
The [GetCreneauxMedecin] method is as follows:
// list of time slots for a given doctor
public List<AppointmentSlot> GetDoctorAppointmentSlots(int doctorId)
{
// list of time slots
try
{
// open persistence context
using (var context = new RdvMedecinsContext())
{
// retrieve the doctor and their available slots
Doctor doctor = context.Doctors.Include("Slots").Single(m => m.Id == doctorId);
// list of the doctor's time slots
return doctor.Slots.ToList<Slot>();
}
}
catch (Exception ex)
{
throw new RdvMedecinsException(3, "GetCreneauxMedecin", ex);
}
}
- line 9: opening a new persistence context;
- line 11: search for the doctor whose primary key is known. Request that the [Creneaux] dependency—a collection of the doctor’s time slots—be included. If the doctor does not exist, the Single method throws an exception;
- line 13: returns the list of time slots.
The [GetRvMedecinJour] method must return the list of a doctor’s appointments for a given day. Its code could be as follows:
// list of a doctor's appointments for a given day
public List<Rv> GetRvMedecinJour(int idMedecin, DateTime jour)
{
// list of appointments
List<Rv> rvs = null;
try
{
// open persistence context
using (var context = new RdvMedecinsContext())
{
// retrieve the doctor
Doctor doctor = context.Doctors.Find(doctorId);
if (doctor == null)
{
throw new RdvMedecinsException(10, string.Format("Doctor [{0}] does not exist", doctorId));
}
// list of appointments
rvs = context.Rvs.Where(r => r.Appointment.Doctor.Id == doctorId && r.Date == date).ToList();
}
}
catch (Exception ex)
{
throw new RdvMedecinsException(4, "GetRvMedecinJour", ex);
}
// return the result
return rvs;
}
- line 13: retrieve the doctor with the given primary key;
- lines 14–17: if it doesn’t exist, throw an exception;
- line 19: the LINQ query to retrieve the appointments for this doctor;
The [AddAppointment] method must add an appointment to the database and return the primary key of the inserted item. Its code could be as follows:
// add an appointment
public int AddAppt(DateTime day, int slotId, int clientId)
{
// ID of the added appointment
int idApp;
try
{
// open persistence context
using (var context = new RdvMedecinsContext())
{
// retrieve the appointment slot
Appointment slot slot = context.AppointmentSlots.Find(slotId);
if (slot == null)
{
throw new RdvMedecinsException(5, string.Format("Slot [{0}] does not exist", slotId));
}
// retrieve the client
Client client = context.Clients.Find(clientId);
if (client == null)
{
throw new RdvMedecinsException(6, string.Format("Client [{0}] does not exist", idCreneau));
}
// create appointment slot
Appointment appointment = new Appointment { Day = day, Client = client, Slot = slot };
// add to context
context.Rvs.Add(rv);
// save context
context.SaveChanges();
// retrieve the primary key of the added Rv
idRv = (int)rv.Id;
}
}
catch (Exception ex)
{
throw new RdvMedecinsException(7, "AddAppointment", ex);
}
// result
return idApp;
}
- line 12: search for the appointment slot in the database;
- lines 13–16: if it is not found, an exception is thrown;
- line 18: search for the appointment's client in the database;
- lines 19–22: if not found, throw an exception;
- line 24: create an [Rv] object with the necessary information;
- line 26: add it to the persistence context;
- line 28: we synchronize the persistence context with the database. The appointment will then be saved to the database;
- line 30: we know that after database synchronization, the primary keys of the inserted items are available. We retrieve the one for the added appointment;
- line 31: we close the persistence context.
The [DeleteAppointment] method must delete an appointment for which the primary key is passed to it.
// delete an appointment
public void DeleteAppt(int apptId)
{
try
{
// open persistence context
using (var context = new RdvMedecinsContext())
{
// retrieve the appointment
Appointment appointment = context.Appointments.Find(appointmentId);
if (rv == null)
{
throw new RdvMedecinsException(5, string.Format("Appointment [{0}] does not exist", idRv));
}
// Delete appointment
context.Rvs.Remove(rv);
// Save context
context.SaveChanges();
}
}
catch (Exception ex)
{
throw new RdvMedecinsException(8, "DeleteAppointment", ex);
}
}
- line 7: new persistence context;
- line 10: the appointment to be deleted is passed to the context;
- lines 11–15: if it does not exist, an exception is thrown;
- line 16: remove it from the context;
- line 18: synchronize the context with the database;
- line 19: close the context.
The [Find<T>] method allows you to search the database for an entity of type T using its primary key. Its code could be as follows:
public T Find<T>(int id) where T : class
{
try
{
// Open persistence context
using (var context = new RdvMedecinsContext())
{
return context.Set<T>().Find(id);
}
}
catch (Exception ex)
{
throw new RdvMedecinsException(20, "Find<T>", ex);
}
}
- Line 8: The Set<T> method allows you to retrieve a DbSet<T> to which you can apply the usual methods.
The project evolves as follows:
 |
3.6.4. Testing the [DAO] layer
We will create a test program for the [DAO] layer. The test architecture will be as follows:
 |
A console program asks [Spring.net] to instantiate the [DAO] layer. Once this is done, it tests the various features of the [DAO] layer interface. Rather than a console program, it would have been preferable to write a NUnit-style test program. A test program for the [DAO] layer could look like this:
using System;
using System.Collections.Generic;
using RdvMedecins.Dao;
using RdvMedecins.Entities;
using RdvMedecins.Exceptions;
using Spring.Context.Support;
namespace RdvMedecins.Tests
{
class Program
{
public static void Main()
{
IDao dao = null;
try
{
// Instantiate the [DAO] layer via Spring
dao = ContextRegistry.GetContext().GetObject("rdvmedecinsDao") as IDao;
// display clients
List<Client> clients = dao.GetAllClients();
DisplayClients("List of clients:", clients);
// Display doctors
List<Doctor> doctors = dao.GetAllDoctors();
DisplayDoctors("List of doctors:", doctors);
// list of time slots for doctor #0
List<AppointmentSlot> appointmentSlots = dao.GetDoctorAppointmentSlots((int)doctors[0].Id);
DisplaySlots(string.Format("List of slots for doctor {0}", doctors[0]), slots);
// list of a doctor's appointments for a given day
DisplayAppointments(string.Format("List of doctor {0}'s appointments on 11/23/2013:", doctors[0]), dao.GetDoctorAppointmentsByDate((int)doctors[0].Id, new DateTime(2013, 11, 23)));
// Add an appointment for doctor #1 in slot #0
Console.WriteLine(string.Format("Adding an appointment for doctor {0} with patient {1} on 11/23/2013", doctors[0], patients[0]));
int idApp1 = dao.AddApp(new DateTime(2013, 11, 23), (int)slots[0].Id, (int)clients[0].Id);
Console.WriteLine("Appointment added");
DisplayRvs(string.Format("List of appointments for doctor {0} on 11/23/2013:", doctors[0]), dao.GetDoctorAppointments(int doctors[0].Id, new DateTime(2013, 11, 23)));
// Add an appointment to an already occupied slot - should throw an exception
int idAppt2;
Console.WriteLine("Adding an appointment to an already occupied slot");
try
{
idRv2 = dao.AddAppointment(new DateTime(2013, 11, 23), (int)slots[0].Id, (int)clients[0].Id);
Console.WriteLine("Appointment added");
DisplayRvs(string.Format("List of appointments for doctor {0} on 11/23/2013:", doctors[0]), dao.GetRvMedecinJour((int)doctors[0].Id, new DateTime(2013, 11, 23)));
}
catch (RdvMedecinsException ex)
{
Console.WriteLine(string.Format("The following error occurred: {0}", ex));
}
// delete an appointment
Console.WriteLine(string.Format("Deleting appointment #{0}", idRv1));
dao.DeleteRv(idRv1);
DisplayRvs(string.Format("List of appointments for doctor {0} on 11/23/2013:", medecins[0]), dao.GetRvMedecinJour((int)medecins[0].Id, new DateTime(2013, 11, 23)));
}
catch (Exception ex)
{
Console.WriteLine(string.Format("The following error occurred: {0}", ex));
}
//pause
Console.ReadLine();
}
// utility methods - displays lists
public static void DisplayClients(string Message, List<Client> clients)
{
Console.WriteLine(Message);
foreach (Client c in clients)
{
Console.WriteLine(c.ShortIdentity());
}
}
public static void DisplayDoctors(string Message, List<Doctor> doctors)
{
...
}
public static void DisplaySlots(string Message, List<Slot> slots)
{
...
}
public static void DisplayRvs(string Message, List<Rv> rvs)
{
...
}
}
}
- line 14: the reference to the [DAO] layer. To make the test independent of the actual implementation of the [DAO] layer, this reference is of type [IDao] (the interface) rather than of type [Dao] (the class);
- line 18: the [DAO] layer is instantiated by Spring. We will return to the configuration required to make this possible. We cast the object reference returned by Spring to a reference of the [IDao] interface type;
- Lines 21–22: display the clients;
- lines 25–26: display the doctors;
- lines 29-30: display the list of time slots for doctor #0;
- line 33: displays doctor #0’s appointments for November 23, 2013. There should be none;
- line 37: adds an appointment for doctor #0 on 11/23/2013;
- line 39: displays the appointments for doctor #0 on 11/23/2013. There should be one;
- line 46: the same appointment is added a second time. An exception should occur;
- Line 57: Delete the single appointment that was added;
- Line 58: Displays appointments for doctor #0 on 11/23/2013. There should be none.
3.6.5. Spring.net Configuration
In the test program above, we briefly touched on the statement that instantiates the [DAO] layer:
dao = ContextRegistry.GetContext().GetObject("rdvmedecinsDao") as IDao;
The [ContextRegistry] class is a Spring class in the [Spring.Context.Support] namespace. To use Spring, we need to add its DLL to the project references. We proceed as follows:
 |
- in [1], search for packages using the [NuGet] tool;
 |
- in [2], search for packages online;
- in [3], enter the keyword "spring" in the search box;
- in [4], packages whose description contains this keyword are displayed. Here, [Spring.Core] is what we need. We install it.
The project references change as follows:
 |
The [Spring.Core] package had a dependency on the [Common.Logging] package. This was also loaded. At this point, the project should no longer have any errors.
That doesn’t mean it will work, though. We first need to configure Spring in the [App.config] file. This is the trickiest part of the project. The new [App.config] file is as follows:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
<!-- spring -->
<sectionGroup name="spring">
<section name="context" type="Spring.Context.Support.ContextHandler, Spring.Core" />
<section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core" />
</sectionGroup>
<!-- common logging-->
<section name="logging" type="Common.Logging.ConfigurationSectionHandler, Common.Logging" />
</configSections>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<!-- Entity Framework -->
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
<parameters>
<parameter value="v11.0" />
</parameters>
</defaultConnectionFactory>
</entityFramework>
<!-- Connection strings -->
<connectionStrings>
<add name="myContext" connectionString="Data Source=localhost;Initial Catalog=rdvmedecins-ef;User Id=sa;Password=sqlserver2012;" providerName="System.Data.SqlClient" />
</connectionStrings>
<system.data>
<DbProviderFactories>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".NET Framework Data Provider for SQL Server" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</DbProviderFactories>
</system.data>
<!-- Spring configuration -->
<spring>
<context>
<resource uri="config://spring/objects" />
</context>
<objects xmlns="http://www.springframework.net">
<object id="rdvmedecinsDao" type="RdvMedecins.Dao.Dao,RdvMedecins-SqlServer-02" />
</objects>
</spring>
<!-- common.logging configuration -->
<logging>
<factoryAdapter type="Common.Logging.Simple.ConsoleOutLoggerFactoryAdapter, Common.Logging">
<arg key="showLogName" value="true" />
<arg key="showDataTime" value="true" />
<arg key="level" value="DEBUG" />
<arg key="dateTimeFormat" value="yyyy/MM/dd HH:mm:ss:fff" />
</factoryAdapter>
</logging>
</configuration>
Let's start by removing everything that is already known: Entity Framework, connection strings, ProviderFactory. The file evolves as follows:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" ... />
<!-- spring -->
<sectionGroup name="spring">
<section name="context" type="Spring.Context.Support.ContextHandler, Spring.Core" />
<section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core" />
</sectionGroup>
<!-- common logging-->
<sectionGroup name="common">
<section name="logging" type="Common.Logging.ConfigurationSectionHandler, Common.Logging" />
</sectionGroup>
</configSections>
...
<!-- Spring configuration -->
<spring>
<context>
<resource uri="config://spring/objects" />
</context>
<objects xmlns="http://www.springframework.net">
<object id="rdvmedecinsDao" type="RdvMedecins.Dao.Dao,RdvMedecins-SqlServer-02" />
</objects>
</spring>
<!-- common.logging configuration -->
<common>
<logging>
<factoryAdapter type="Common.Logging.Simple.ConsoleOutLoggerFactoryAdapter, Common.Logging">
<arg key="showLogName" value="true" />
<arg key="showDataTime" value="true" />
<arg key="level" value="DEBUG" />
<arg key="dateTimeFormat" value="yyyy/MM/dd HH:mm:ss:fff" />
</factoryAdapter>
</logging>
</common>
</configuration>
- lines 3–15: define configuration sections;
- line 8: defines the class that will manage the <spring><context> section of the XML file (lines 19–21);
- line 9: defines the class that will manage the <spring><objects> section of the XML file (lines 22–24);
- line 13: defines the class that will manage the <common><logging> section of the XML file (lines 27–36);
- Lines 7–14: are stable. Do not need to be changed in another project;
- lines 18–25: Spring configuration. Is stable except for lines 22–24, which define the objects that Spring will instantiate;
- line 23: definition of an object. The id attribute is arbitrary. It is the object’s identifier. The type attribute specifies the class to be instantiated in the form “full class name, Assembly containing the class.” The class here is the one that implements the [DAO] layer: [RdvMedecins.Dao.Dao]. To find its assembly, check the project properties:
 |
In [1], the name of the assembly to be provided;
- lines 27–36: the "Common Logging" configuration is stable. You may need to modify the logging level on line 32. After the debugging phase, you can set the level to INFO.
Ultimately, although complex at first glance, the Spring configuration file turns out to be simple. The only changes needed are:
- lines 22–24, which define the objects to instantiate;
- line 32: the logging level.
In the test program, the statement that instantiates the [DAO] layer is as follows:
dao = ContextRegistry.GetContext().GetObject("rdvmedecinsDao") as IDao;
[ContextRegistry] is a Spring class that uses the Spring configuration specified in a [Web.config] or [App.config] file. Here, it will use the following section of the [App.config] file:
<spring>
<context>
<resource uri="config://spring/objects" />
</context>
<objects xmlns="http://www.springframework.net">
<object id="rdvmedecinsDao" type="RdvMedecins.Dao.Dao,RdvMedecins-SqlServer-02" />
</objects>
</spring>
- ContextRegistry.GetContext() uses the context defined in lines 2–4. Line 3 means that the Spring objects are defined in the [spring/objects] section of the configuration file. This section is lines 5–7;
- ContextRegistry.GetContext().GetObject("rdvmedecinsDao") uses the section in lines 5–7. It returns a reference to the object with the attribute id="rdvmedecinsDao". This is the object defined in line 6. Spring will then instantiate the class defined by the type attribute using its parameterless constructor. This constructor must therefore exist. Once this is done, the reference to the created object is returned to the calling code. If the object is requested a second time in the code, Spring simply returns a reference to the first object created. This is the design pattern known as the singleton.
Object construction can be more complex. You can use a constructor with parameters or specify the initialization of certain object fields once the object has been created. For more information on this topic, see the article "Spring IOC Tutorial for .NET" at [http://tahe.developpez.com/dotnet/springioc/].
Once this is done, we can run the application. The screen results are as follows:
The results are as expected. We will now consider our [DAO] layer to be valid. The tutorial could end here. So far, we have covered:
- the basics of the Entity Framework 5 ORM;
- a [DAO] layer using this ORM.
Let’s recall our case study described at the beginning of this document. We start with an existing application with the following architecture:
 |
which we want to transform into this:
 |
where EF5 has replaced NHibernate. We have just built the [DAO2] layer. In fact, it does not have the same interface as the [DAO1] layer, whose interface was more limited:
public interface IDao
{
// list of clients
List<Client> GetAllClients();
// list of doctors
List<Doctor> GetAllDoctors();
// list of a doctor's available time slots
List<AppointmentSlot> GetDoctorAppointments(int doctorId);
// list of appointments for a given doctor on a given day
List<Appointment> GetDoctorAppointmentsByDate(int doctorId, DateTime date);
// add an appointment
int AddAppointment(DateTime day, int slotId, int clientId);
// delete an appointment
void DeleteAppointment(int appointmentId);
}
The [DAO2] layer has added the following method to this interface:
// find an entity T via its primary key
T Find<T>(int id) where T : class;
This method was added because the EF 5 ORM operates in Lazy Loading mode by default. Entities arrive in the [ASP.NET] layer without their dependencies. The method above allows us to retrieve them if needed, and in some cases, we do need them. NHibernate also operates in Lazy Loading mode by default, but I had used it in Eager Loading mode. Entities arrived in the [ASP.NET] layer with their dependencies.
We will complete the porting of the ASP.NET/NHibernate application to the ASP.NET/EF 5 application. However, since this no longer concerns EF5, we will not comment on the web code. We will simply explain how to set up the web application and test it. It is available on this tutorial’s website.
3.6.6. Generating the [DAO] Layer DLL
In the following architecture:
 |
the [ASP.NET] layer will have the layers to its right available to it in the form of DLLs. We will therefore build the [DAO] layer DLL.
 |
- In [1], select the test program, and in [2], do not include it in the DLL that will be generated;
- In [3], in the project properties, specify that the assembly to be created is a DLL;
- In [4], in the VS menu, we specify that we will generate a [Release] assembly, which contains less information than a [Debug] assembly;
 |
- In 5, regenerate the project assembly. The DLL will be generated;
- In [6], display all the project files;
 |
- In [7], the DLL for the [DAO] layer project. This is the one the ASP.NET web project will use;
- In [8], we refresh the project view;
 |
- in [9], the DLLs from the [Release] folder are gathered into an external [lib] folder [10]. This is where the web project will retrieve its references.
3.6.7. The [ASP.NET] layer
Here we will explain how to port the [ASP.NET / NHibernate] application to the [ASP.NET / EF 5] application. We will be working with Visual Studio Express 2012 for Web, available for free at [http://www.microsoft.com/visualstudio/fra/downloads].
We will start with the existing web project created with VS 2010.
 |
- In [1], we open the existing project:
- In [2], the loaded project has the following references [3]:
- [NHibernate] is the NHibernate framework DLL,
- [Spring.Core] is the DLL for the Spring.net framework,
- [log4net] is the DLL for the log4net logging framework. This framework is used by Spring.net,
- [MySql.Data] is the ADO.NET driver for the MySQL DBMS,
- [rdvmedecins] is the DLL for the [DAO] layer built with NHibernate;
- in [4], we change the project name, and in 5, we remove the previous references;
 |
- in [6], we add references to the project;
- in [7], in the wizard, we use the [Browse] option;
 |
- in [8], we select all the DLLs from project #2 previously placed in the [lib] folder;
- in [9], a summary that we confirm;
- in [10], the web project with its new references.
Once this is done, the project looks as follows:
 |
- In [1], the code for managing the web pages is split between the two files [Global.asax] and [Default.aspx]. Utility code has been placed in the [Entities] folder. Finally, the application is configured by the [Web.config] file;
- in [2], we generate the project assembly;
- in [3], errors appear.
Let’s examine the errors, for example the following one:
and its explanation:
The type of [medecin.Id] is int?, whereas the [GetCreneauxMedecin] method is of type int. Therefore, a cast is required. This error occurs repeatedly throughout the code because the entities in the ASP.NET/NHibernate project had primary keys of type int, whereas those in the ASP.NET/EF 5 project are of type int?. We correct all errors of this type and regenerate the project. There are then no more errors.
There is one more detail to address before running the project: the instantiation of the [DAO] layer by the Spring framework. This is done in [Global.asax]:
protected void Application_Start(object sender, EventArgs e)
{
// cache certain data from the database
try
{
// instantiate the [DAO] layer
Dao = ContextRegistry.GetContext().GetObject("rdvmedecinsDao") as IDao;
...
}
catch (Exception ex)
{...
}
}
In the [DAO] layer test program, it instantiated the [DAO] layer as follows:
dao = ContextRegistry.GetContext().GetObject("rdvmedecinsDao") as IDao;
The two methods are identical. Recall that this instantiation of the [DAO] layer relied on a configuration specified in [App.config]. We then replace the current [Web.config] content of the web project with that of [App.config] from the [DAO] layer project to ensure the same configuration.
We are ready for the first run. The home page is displayed [1]:
 |
- in [2], we enter an appointment date and submit;
 |
- in [3], an error occurs.
When examining the error message displayed by the page, we see that the exception reported is related to Lazy Loading: we attempted to load a dependency of an object while the persistence context managing it had been closed. The object is now in a "detached" state. This error is due to the fact that NHibernate was used in Eager Loading mode, whereas EF 5 works in Lazy Loading mode by default. In the line highlighted in red above:
- rdv represents an [Rv] object that was loaded without its dependencies;
- to evaluate rdv.Creneau.Id, the application attempts to load the dependency rdv.Creneau. But since we are no longer in the context, this is not possible, hence the exception.
Here, the solution is simple. Line 108: We create an entry in a dictionary with the primary key of the appointment slot as the key. However, it turns out that the [Rv] entity encapsulates the primary key of the associated slot. So we write:
dicoRvPris[(int)rdv.CreneauId] = rdv;
We try running the code again. This time, the error is as follows:
 |
The error is similar. Line 132: we attempt to load the [Client] dependency of an [Rv] object in the ASP.NET layer, which is out of context. We must retrieve the [Client] object from the database. To resolve this issue, the [IDao] interface has been enhanced with the following method:
// find an entity T via its primary key
T Find<T>(int id) where T : class;
This will allow us to retrieve dependencies. Thus, the erroneous line above will be rewritten as follows:
Client client = Global.Dao.Find<Client>(agenda.Slots[i].Appointment.ClientId);
Once again, we note the benefit of entities embedding their foreign keys. Here, the [Rv] entity gives us access to the foreign key of the associated [Creneau] dependency. With these two corrections made, the application works. The reader is invited to test the [RdvMedecins-SqlServer-03] application available in the example downloads on this article’s website.
3.7. Conclusion
We have successfully ported an ASP.NET / NHibernate application:
 |
to an ASP.NET / EF 5 application:
 |
Although this architecture should have allowed us to keep the [ASP.NET] layer intact, we had to modify it for two reasons:
- the entities were not exactly the same. The primary key type for NHibernate entities was
int, whereas for EF 5 it wasint?</span>**<span style="color: #000000">. This led us to introduce casts in the web code; - the entity loading mode was not the same for the two ORMs: Eager Loading for NHibernate, Lazy Loading for EF 5. This led us to enhance the [DAO] layer interface with a generic method allowing us to retrieve an entity via its primary key.
Nevertheless, the port proved to be fairly straightforward, once again justifying—if proof were needed—the layered architecture and dependency injection with Spring or another dependency injection framework.
We will now assess the impact of a DBMS change on the previous architecture. We will port all previous projects to four other DBMSs:
- Oracle Database Express Edition 11g Release 2;
- MySQL 5.5.28;
- PostgreSQL 9.2.1;
- Firebird 2.1.
The code will remain unchanged. Only the following elements will change:
- the definition in the entities of the field used to control concurrent access to an entity;
- the configuration files [App.config] or [Web.config];
We will only comment on the elements that are changing.