Simple Talk is now part of the Redgate Community hub - find out why

Entity Framework: The Cribsheet

Prasanna Amirthalingam provides an overview of Entity Framework and how it can be used. He shows that it can provide an excellent interface between the Object-oriented model and the relational. The Entity Framework allows developers to work with data in the form objects and properties without having to concern themselves directly with either the stored procedures or functions of the defined interface, or the the underlying database tables and columns where this data is stored.

Entity Framework- The Crib Sheet

For things you need to know rather than the things you want to know



Database developers and application developers need to model business processes and data differently. Database developers use a relational model to represent data and develop stored procedures to represent processes. Application developers use the richness of object oriented models to work with business data and business processes. This mismatch between the relational world and the object oriented world can be addressed by using the ADO.NET Entity Framework. This article is based on the ADO.NET Entity Framework that ships with Visual Studio.NET 2008 Service Pack 1 Beta.

The ADO.NET entity framework is an evolution of ADO.NET to provide object relational mapping between conceptual entities and the data store. Conceptual entities are standard .NET classes. The ADO.NET entity framework provides a mapping technology to map these conceptual entities to the data store schema of your database. This allows an application developer to work with the conceptual entities without having to be concerned  about the underlying data model.

ADO.NET Entity Data Model

The ADO.NET entity framework uses an Entity Data Model. The Entity Data Model consists of the storage schema, the conceptual schema, the mapping schema and the entity classes.

Storage Schema Definition (SSDL)

The storage schema describes, in an XML definition, the schema of the data store. This is the exact schema of your data store. The storage schema definition can be generated directly from the data store. The Storage schema describes the schema of a database table as an entity type. The following XML fragment shows the definition for a Customer table:

The storage schema also describes relationships between tables as an association. The following definition for an association shows that the Customer table has a relationship with an Order table:

The storage schema also refers to any stored procedures in the data store. The following XML definition defines an existing stored procedure named SelectByCountry that accepts a single parameter named ctry:

Finally all the defined entity types and associations are mapped into an entity container. The entity types are grouped into an entity set and the associations are grouped into an association set.

Conceptual Schema Definition (CSDL)

The conceptual schema describes the conceptual entities as entity types and is defined in XML. This is created to describe the schema of the conceptual entities. Conceptual entity types are .NET classes. Though this schema can be auto-generated, most of the times the application developer would alter the conceptual schema to reflect the model used for the conceptual entities. If the conceptual schema is auto-generated it would simply map a conceptual entity type to a table. The following shows the schema for two conceptual entities named Customer and CorporateCustomer. The CorporateCustomer entity type inherits from the Customer entity type.

The conceptual schema also describes relationships between entity types as associations. These relationships are references in the conceptual schema for the entity type when an entity contains instances of another entity. For example in the schema above the navigation property in the Customer entity type references entities by using an association. The following schema shows the association between the Customer entity type and the Order entity type:

Finally all the defined conceptual entity types are grouped into entity sets and associations are grouped into association sets. An entity container is defined to include the entity sets, association sets and any function definitions that can be mapped to stored procedures:

Mapping Schema (MSL)

The mapping schema definition is the glue that binds the conceptual model and the data store model. This XML definition contains information on how the conceptual entities, functions and associations are mapped to the storage schema. The following shows the mapping definition to map the Customer and CorporateCustomer conceptual entities defined in the conceptual schema to the database table named Customer defined in the storage schema:

The associations we define in the conceptual schema is mapped to the associations that we define in the storage schema

The functions we defined in the conceptual schema is mapped to the stored procedures defined in the storage schema

Entity Classes

Entity classes are .NET classes that are generated from the conceptual schema XML definition. The Entity classes are similar to business entities and will be used by the other layers in your application. When working with the Entity Data Model, any changes to the conceptual schema updates the auto generated .NET class definitions. Since these entity classes are partial classes you can extend the functionality by creating your own business logic for the entity classes. The generated entity classes also contain partial methods to plug in your own validation logic.

The following code shows the auto-generated class for the CorporateCustomer entity

C# Code:

You can plug in your own validation logic by providing implementation for the partial methods. For each property in the entity class there will be partial methods that are called before a property changes and after the property is changed. For example on the above code let us assume we want to execute validation logic to make some validation checks if the designation is set to “Manager”. Then we can create a partial class definition for the above class and provide implementation for the OnDesignationChanging partial method as follows:

C# Code:

Working with the designer and tools

Visual Studio.NET 2008

Visual Studio has a project item template for an Entity Data Model. Adding an Entity Data Model to your project allows you to design the conceptual entities and map them to the data store using the Entity Framework designer. You can also create new conceptual entities, create association, and inherit from existing conceptual entities in the designer.


When you work with conceptual entities in the Entity Framework Designer, you can use the Mapping Details window to map the conceptual entity to a data store.


The Mapping Details window also has an alternate view to map your own user defined stored procedures to perform Inserts, Updates and Deletes for your conceptual entity


Visual Studio also has a Model Browser window that shows the conceptual entity types, entity sets and data store. This is similar to the Class Browser window and provides a better view to browse through the Entity Data model.


EdmGen tool

The EdmGen is a command line tool available with Visual Studio.NET 2008. You can use this to generate ADO.NET entity data model schema files and .NET classes for your application. For example the following command generates the XML files for the conceptual schema, storage schema, mapping schema and entity classes by using the database Contoso:

You can change the mode for EdmGen to specify different starting points. For example if you have already created the storage schema, mapping schema and conceptual schema, and you want to generate the entity classes, you can change the mode to “EntityClassGeneration”. EdmGen is very useful when you want to configure it to be used along with an automated build process.

Working with data

Using Object Services

Object Services allows you to work with data as conceptual entities (.NET objects). This also allows you to perform CRUD operations on objects without writing query statements that are provider dependent or targeting a particular database product. The CRUD operations for entities are executed through the ObjectContext class. The ObjectContext class also manages state for retrieved entities.

Querying data

You have different ways of querying data through Object Services. When data is queried through Object Services, the results are returned as strongly typed objects. This allows you to work with data as working with any strongly typed object or collection in .NET. The simplest way to query data is by using the ObjectQuery type in .NET. The ObjectQuery type represents a query that can be executed against the conceptual model.

Using Query Builder

An ObjectQuery can be constructed by using the available extension methods such as Where, OrderBy, Select etc.  The ObjectQuery always work with an ObjectContext (in this instance ContosoEntities). For example the following code block calls the Where extension method to build the query to return only entities from the Customers entity set where the Country property is Australia.

C# Code

Using Linq to Entities

The entities in ADO.NET are Linq enabled. You can use the language integrated query (Linq) features available in .NET 3.5 to query data. The following code shows the same query we wrote earlier using Linq to entities.

C# Code

We can also retrieve only a few properties of the Customer entity by using anonymous types and type inference feature in C# 3.0. For example the following query retrieves only the Name and the Country of CorporateCustomer instances:

The above code will select Customer entity instances that are of the type CorporateCustomer. The res variable will hold a reference to a list of anonymous type instances that has the Name and Country properties. You can also use the query builder methods to retrieve few properties of entity instances.

Using Entity SQL

Entity SQL is a SQL like query language that can be used to query data. Unlike SQL, Entity SQL uses entities, property names and associations defined in the conceptual schema in retrieving data. The following code block shows the query that we wrote using query builder and Linq to entities written using Entity SQL.

C# Code

You can retrieve few properties of your entity instances similar to how we did using Linq to entities. But when using Entity SQL the results can be returned as instances of the type DbDatRecord.

C# Code:

Using Stored Procedures

If you do not want to rely on the dynamic SQL statements generated by the Entity Framework, you can create your own stored procedures and map them as functions. You can use these functions to query data. For example in the mapping files we had a stored procedure named SelectByCountry mapped to a function with the same name. We can directly call the function from our code to query data:

C# Code

Updating data

Updating entities retrieved happens through the ObjectContext. The ObjectContext manages state changes on the entity set (inserts, updates and deletes). When the entity set needs to be updated with the data store, you can call the SaveChanges method of the ObjectContext class. This method would execute the required insert, update and delete statements specific to the data store based on whether the entity instances were newly created or updated or deleted.

C# Code:

The above code will update the Customer entity instance c1, delete c2 and insert the instance c3 as a new entity to the data store. All the inserts, updates and deletes would occur when the SaveChanges method is invoked.

While updating data, the Entity Framework uses optimistic concurrency. Rows are not locked when entities are retrieved, but at the time of updating it checks whether data was changed. You can control the properties of the entity that will be part of the concurrency check by setting the Concurrency Mode to Fixed. If the Concurrency Mode for a property of an entity is set to fixed then the auto-generated SQL statement would compare the original value that was retrieved with the current value in the data store. If there are any conflicts, an exception would be thrown.

If you do not wish to use the auto-generated SQL statements for persisting changes, you can define your own stored procedures for inserting updating and deleting your entity instances. Once these stored procedures are defined as functions in the storage schema you can map them in the mapping schema to be used by the ObjectContext for performing inserts, updates and deletes:

The above mapping indicates that we want to use the stored procedures defined as functions in the storage schema to be used for inserts, updates and deletes for the Customer entity.

Using the Entity Client Data Provider

The Entity Client Data Provider is an ADO.NET provider for your ADO.NET Entity Framework. The Entity Client Data Provider is built on top of the ADO.NET provider model. This allows you to work with ADO.NET objects that you are familiar with when working with other ADO.NET provides. For example we can run a query and retrieve data through a data reader as follows:

C# Code:

The above code creates an EntityConnection object and sets the connection string. The connection string for the Entity Client provider refers to the schema files, the database provider to use and the connection string to the database. An EntityCommand object uses an Entity SQL statement and the EntityConnection object to execute a query that returns an EntityDataReader instance. The data can be retrieved from the EntityDataReader instance.


The ADO.NET Entity Framework provides a framework and gives life to otherwise static entity data models. Conceptual entities designed in the development life cycle can be mapped to data stores. This allows an application developer to work with an object oriented model, and a database developer to work with a relational model.

Further Reading

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.


Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.