Entity Framework Tutorial

To learn the concepts covered in this book, the reader should have a basic understanding of the following:

Any application has two perspectives. They are the Data Model and the Object Model. While the Data Model defines the way the data is defined and stored, the Object Model defines how the same data will be represented to the user in the presentation layer or, is exposed to the other layers of the application. The Data Model of the application usually deals with the storage and retrieval of the application's data to and from the relational store.

The relational store is used for data persistence, consistency, concurrency, and security. It contains the application's data and typically comprises of a set of tables, views, functions, procedures, and the relationships. You typically use T-SQL to query against the relational store which returns result sets that contains columns and rows of data.

However, the data returned doesn't necessarily match with the application's object oriented programming model. Usually, we don't use the data returned in the same form in which it is returned from the relational store. We write the necessary code to transform the data returned from the relational store to business objects in the data access layer of the application. Similarly, you need into write code to transform your application's business objects into a form that can be persisted into your relational store. But, what if the schema of the underlying relational store changes?

To bridge this apparent mismatch between the data and the object models, ORM tools have evolved. They are used to reduce the code required to transform your application's business objects into a form that can be persisted into the relational store and vice-versa.

The ADO.NET Entity Framework is a type of ORM. It is a development platform that provides a layer of abstraction on top of the relational or logical model. In doing so, it isolates the object model of the application from the way the data is actually stored in the relational store. Developers can use the ADO.NET Entity Framework to program against an object model rather than the logical or relationship model.

This level of abstraction is achieved using the Entity Data Model (EDM)—an extended Entity Relationship Model. The EDM reduces the dependency of your domain object model on the database schema of the data store in use. We will discuss more on this topic later in this chapter.

Developers can use the ADO.NET Entity Framework to work with domain specific properties such as employee name employee address, contact details, etc, without having to be concerned with how the actual data is stored and represented in the underlying data store. The framework can take care of the necessary translations to either retrieve data from your data store, or, perform inserts, updates, and deletes.

The ADO.NET Entity Framework is comprised of the following components:

The following figure illustrates the layers of the ADO.NET Entity Framework and how they are related to each other:

We will now discuss each of the components of the Entity Framework technology stack in the following sections.

The Entity Data Model, an extended entity relationship model, is the core of the ADO.NET Entity Framework.

You can generate an Entity Data Model using the EDMGen.exe command line tool, or, using the ADO.NET Entity Data Model designer—a new Visual Studio template. We will discuss how an Entity Data Model can be generated from a relational schema in the next chapter.

The following figure illustrates where exactly the Entity Data Model fits in:

The Entity Data Model abstracts the logical or the relational schema and exposes the conceptual schema of the data using a three-layered approach. It is comprised of the following layers:

The following figure illustrates the layers of the Entity Data Model:

The Conceptual Layer or the C-Space Layer is responsible for defining the entities and their relationships. It defines your business objects and their relationships in XML files. The C-Space is modeled using CSDL and is comprised of EntityContainer, EntitySets, AssociationSets, AssociationTypes, EntityTypes, and Functions. You can query this layer using Entity SQL or ESQL (EntityConnection, EntityCommand, and EntityDataReader).

The C-S Mapping Layer is responsible for mapping the conceptual and the logical layers. That is, it maps the business objects and the relationships defined in the conceptual layer with the tables and relationships defined in the logical layer. It is a mapping system created in XML, which links or maps the Conceptual and the Logical layers. The C-S Mapping layer is modeled using MSL.

The Logical or the Storage Layer (also called the S-Space) represents the schema of the underlying database. This is comprised of tables, stored procedures, view, and functions. It is modeled using SSDL and queried using ADO.NET Data Providers. Hence, we use SQLConnection, SQLCommand, SQLDataReader, and SQLDataAdapter using T-SQL or PL-SQL if our data store is a SQL database.

Here is how a typical Entity Data Model looks:

<!-- CSDL content -->
<edmx:ConceptualModels>
<EntityContainer Name="PayrollEntities">
<EntitySet Name="Designation" EntityType="PayrollModel.Designation" />
</EntityContainer>
<EntityType Name="Designation">
<Key>
<PropertyRef Name="DesignationID" />
</Key>
<Property Name="DesignationID" Type="Int32" Nullable= "false" />
<Property Name="DesignationName" Type="String" Nullable="false" MaxLength="50" Unicode="false" />
<NavigationProperty Name="Employee" Relationship= "PayrollModel.FK_Employee_Designation" FromRole="Designation" ToRole="Employee" />
</EntityType>
</edmx:ConceptualModels>

<!-- SSDL content -->
<edmx:StorageModels>
<EntityContainer Name="dbo">
<EntitySet Name="Designation" EntityType= "PayrollModel.Store.Designation" />
</EntityContainer>
<EntityType Name="Designation">
<Key>
<PropertyRef Name="DesignationID" />
</Key>
<Property Name="DesignationID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
<Property Name="DesignationName" Type="varchar" Nullable="false" MaxLength="50" />
</EntityType>
</Schema>
</edmx:StorageModels>

<!-- C-S mapping content -->
<edmx:Mappings>
<Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
<EntityContainerMapping StorageEntityContainer="dbo" CdmEntityContainer="PayrollEntities">
<EntitySetMapping Name="Designation">
<EntityTypeMapping TypeName="IsTypeOf(PayrollModel.Designation)">
<MappingFragment StoreEntitySet="Designation">
<ScalarProperty Name="DesignationID" ColumnName= "DesignationID" />
<ScalarProperty Name="DesignationName" ColumnName= "DesignationName" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
</EntityContainerMapping>
</Mapping>
</edmx:Mappings>
</edmx:Runtime>
</edmx:Edmx>

The Object Model (O-Space)

When working with the ADO.NET Entity Framework, you will have an Object Model on top of all the Entity Data Model layers. You need to model the Object Model using .NET objects. The following figure illustrates how the Object Model fits in with the EDM layers:

The Object Model Layer contains, .NET objects, collection of .NET objects, types, properties and methods. You can use the Object Model or the O-Space Model to query your business objects, or the collections of your business objects, using LINQ to Entities or Entity SQL. The C-Space and O-Space models are actually mapped by the O-C Mapping Layer using code attributes applied to the O-space Model.

PayrollModel.PayrollEntities ctx = new PayrollModel.PayrollEntities();
var query = from emp in ctx.Employee
select emp;
foreach (var employee in query)
Response.Write("<BR>"+employee.FirstName);

using (EntityConnection conn = new EntityConnection("Name=PayrollEntities"))
{
try
{
conn.Open();
EntityCommand cmd = conn.CreateCommand();
cmd.CommandText = "PayrollEntities.AddNewEmployee";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("FirstName", "Joydip");
cmd.Parameters.AddWithValue("LastName", "Kanjilal");
cmd.Parameters.AddWithValue("Address", "Hyderabad");
cmd.Parameters.AddWithValue("DepartmentID", 4);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}

Select Employee.FirstName, Employee.LastName, Department.DepartmentName, Salary.Basic
from Employee
INNER JOIN
Department on Department.DepartmentID = Employee.DepartmentID
INNER JOIN
Salary on Salary.EmployeeID = Employee.EmployeeID

Select FirstName, LastName, DepartmentName, Basic from EmployeeData

using (ObjectContext ctx = new ObjectContext("Name=PayrollEntities"))
{
var query = from employee in ctx.CreateQuery<PayrollModel.Employee>("PayrollEntities.
Employee") select employee;
foreach (PayrollModel.Employee emp in query)
{
Response.Write("<BR>" + emp.FirstName);
}
}

To run the programs given in this book, you should have the following elements installed on your system:

Installing the ADO.NET Entity Framework and Its Tools

Before you proceed with installing EF Beta 3 and its tools, make sure that you have installed Visual Studio.NET 2008 Professional and SQL Server 2005 on your system. I will skip the discussion on installing Visual Studio 2008 Professional and SQL Server 2005 at this time.

Downloading the Software

Let us take a look at the necessary software that you need to download in order to work with the ADO.NET Entity Framework.

• Download ADO.NET Entity Framework Beta 3 from the following link:

  http://www.microsoft.com/downloads/details.aspx?FamilyId=15DB9989-1621-444D-9B18-D1A04A21B519&displaylang=en

• Download ADO.NET Entity Framework Tools December 2007 CTP from the link give below:

  http://www.microsoft.com/downloads/details.aspx?FamilyId=D8AE4404-8E05-41FC-94C8-C73D9E238F82&displaylang=en

• Download the patch update to Visual Studio.NET 2008 from the link given below:

  http://go.microsoft.com/fwlink/?LinkID=104985

• Download Microsoft .NET Framework 3.5 SP1 from the following link:

  http://www.microsoft.com/downloads/details.aspx?FamilyId=8C36ACA4-E947-4760-9B05-93CAC04C6F87&displaylang=en

Installing the Software

Now that the required software has been downloaded, we will now install each one individually.

Double-click on the ADO.NET Entity Framework Setup file. The following installation wizard window will appear:

Now, click on Next.

Here is how the next screen looks like:

In the above window that appears, check the checkbox and click on Install.

The installation procedure starts and when done, the following screen appears:

Click on Finish to complete the installation process.

Now that you have installed the ADO.NET Entity Framework successfully on your system, the next step is to install the ADO.NET Entity Framework Tools CTP. But before you do that, you need to install the Visual Studio.NET 2008 patch update.

Double-click on the Visual Studio.NET patch update setup file that you downloaded earlier. The following window appears:

Now, click on Next.

The following window appears:

Check the check box to agree and accept the license terms and click on Next to start the installation process.

Once the installation is complete, the following window appears:

Click on Finish to complete the installation process.

Now, we need to install the ADO.NET Entity Framework Tools CTP. To do this, double-click on the ADO.NET Entity Framework December 2007 Tools CTP you downloaded earlier. The following screen appears:

Click on Next. The following window appears:

Now, check the check box to accept the license terms and click on Next. The following window will appear:

Click on Install to start the installation process. Once the installation is complete, the following window is displayed:

Clink on Finish to complete the installation process.

We will now install Microsoft .NET Framework 3.5 SP1. It contains many additional features and most importantly, we need it to work with the Entity Data Source control. We will discuss this control in the next chapter.

To install Microsoft .NET Framework 3.5 SP1 on your system, double-click on the setup file you downloaded earlier. The following screenshot shows the window that will appear:

Select the radio button to accept the terms of the license agreement and click on Install to start the installation process.

Once the installation process is complete, the following window is displayed:

Click on Exit to complete the installation process. You are done!

Having installed these prerequisites, we will now design our sample database. We will use this database throughout this book. We will assign the name Payroll to our design database. The next section discusses the design of this sample database.

In this section, we will design our Payroll database, which we will use throughout this book. This database will comprised of the following five tables:

Here is the script for creating these tables.

CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Phone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DepartmentID] [int] NOT NULL,
[JoiningDate] [datetime] NOT NULL,
[LeavingDate] [datetime] NULL,
[DesignationID] [int] NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Department](
[DepartmentID] [bigint] NOT NULL,
[DepartmentName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DepartmentHead] [bigint] NOT NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Designation](
[DesignationID] [bigint] NOT NULL,
[DesignationName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Designation] PRIMARY KEY CLUSTERED
(
[DesignationID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Salary](
[SalaryID] [bigint] NOT NULL,
[EmployeeID] [bigint] NOT NULL,
[Basic] [money] NOT NULL,
[Allowance] [money] NOT NULL,
[PFID] [bigint] NULL,
[Tax] [money] NOT NULL,
[GrossSalary] [money] NOT NULL,
[NetSalary] [money] NOT NULL,
CONSTRAINT [PK_Salary] PRIMARY KEY CLUSTERED
(
[SalaryID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[ProvidentFund](
[PFID] [bigint] NOT NULL,
[PFAmount] [money] NOT NULL,
CONSTRAINT [PK_ProvidentFund] PRIMARY KEY CLUSTERED
(
[PFID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Here is how the database diagram for our Payroll database looks:

We will create some stored procedures now that we will use to insert, update, and delete data from the tables we just created. Here is a list of the stored procedures that we will create for our Payroll database:

Here is the script for these procedures.

Create Procedure Employee_Insert
As
@FirstName varchar(50), @LastName varchar(50), @Address varchar(50), @Phone varchar(50), @DepartmentID int, @DesignationID int,
@JoiningDate datetime, @LeavingDate datetime
as
Insert into Employee(DepartmentID, DesignationID, JoiningDate, LeavingDate) values (@DepartmentID, @DesignationID, @JoiningDate, @LeavingDate)
Go
Create Procedure Employee_Update
@EmployeeID int, @FirstName varchar(50), @LastName varchar(50), @Address varchar(50), @Phone varchar(50), @DepartmentID int, @DesignationID int
as
Update Employee Set DepartmentID = @DepartmentID, DesignationID = @DesignationID Where Employee.EmployeeID = @EmployeeID
Go
Create Procedure Employee_Delete
@EmployeeID int
as
Delete from Employee where Employee.EmployeeID = @EmployeeID
Go
Create Procedure Department_Insert
@DepartmentName varchar(50)
as
Insert into Department (DepartmentName) values (@DepartmentName)
Go
Create Procedure Department_Update
@DepartmentID int,@DepartmentName varchar(50)
as
Update Department Set DepartmentName = @DepartmentName where DepartmentID = @DepartmentID
Go
Create Procedure Department_Delete
@DepartmentID int
as
Delete from Department where DepartmentID = @DepartmentID
Go
Create Procedure Designation_Insert
@DesignationName varchar(50)
as
Insert into Designation (DesignationName) values (@DesignationName)
Go
Create Procedure Designation_Update
@DesignationID int, @DesignationName varchar(50)
as
Update Designation Set DesignationName = @DesignationName where DesignationID=@DesignationID
Go
Create Procedure Designation_Delete
@DesignationID int
as
Delete from Designation where DesignationID=@DesignationID
Go
Create Procedure ProvidentFund_Insert
@EmployeeID int, @PFAmount money
as
Insert into ProvidentFund (EmployeeID, PFAmount) values (@EmployeeID, @PFAmount)
Go
Create Procedure ProvidentFund_Update
@PFID int, @EmployeeID int, @PFAmount money
as
Update ProvidentFund set EmployeeID = @EmployeeID, PFAmount = @PFAmount where PFID = @PFID
Go
Create Procedure ProvidentFund_Delete
@PFID int
as
Delete from ProvidentFund where PFID = @PFID
Go
Create Procedure Salary_Insert
@EmployeeID int, @PFID int, @Basic money, @Allowance money, @Tax money, @GrossSalary money, @NetSalary money
as
Insert into Salary(EmployeeID, PFID, Basic, Allowance, Tax, GrossSalary, NetSalary) values (@EmployeeID, @PFID, @Basic, @Allowance, @Tax, @GrossSalary, @NetSalary)
Go
Create Procedure Salary_Update
@SalaryID int, @EmployeeID int, @PFID int, @Basic money, @Allowance money, @Tax money, @GrossSalary money, @NetSalary money
as
Update Salary set EmployeeID = @EmployeeID, PFID = @PFID, Basic = @Basic, Allowance = @Allowance, Tax = @Tax, GrossSalary = @GrossSalary, NetSalary = @NetSalary where SalaryID = @SalaryID
Go
Create Procedure Salary_Delete
@SalaryID int
as
Delete from Salary where SalaryID = @SalaryID

We will add more tables and stored procedures to our Payroll database as necessary in the chapters to follow.

The ADO.NET Entity Framework mainly addressees how easily you can persist and query your data with many of added services. You can use the ADO.NET Entity Framework to focus on the object model rather than the logical model. In other words, you can add a level of abstraction on top of your relational store.

In this chapter, we have had a look at what this framework is, its architecture, advantages, and how it differs from LINQ to SQL (formerly called DLINQ). In the next chapter, we will learn how to get started with the ADO.NET Entity Framework.

Entity: This is the core concept in the Entity Framework. An Entity essentially models individual objects, like, employees, customers, etc. It is something that is uniquely definable, distinctly identifiable, and contains information pertaining to the entity.

Relational or Logical Model: A relational or logical model depicts the logical view of data that comprises of the normalized entities and their inter-relationships in a database.

Object Model: An Object Model may be defined as a collection of the objects, members, and properties of a class and the relationships between them that illustrates the structure of a system. An Object Model of a system is centered on the three basic properties of Object Oriented Programming—encapsulation, abstraction, and inheritance.

The Entity Data Model (EDM) is an implementation for the Entity Relationship model (commonly called the E-R model). It depicts the entities and their relationships. The EDM is a view of the data store that your application will use.