Entity Framework

Introduction to the Entity Framework

Entity Framework is an object-relational mapping technology that enables .NET developers to work with relational data by using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write. Entity Framework is the recommended object-relational mapping (ORM) modeling technology for new .NET applications.

The ADO.NET Entity Framework is an object-relational mapping (ORM) framework that offers an abstraction of ADO.NET to get an object model based on the referential databases. You can use different programming models with the Entity Framework: Model First, Database First, and Code First. Both Model First and Database First provide mapping information with a mapping file. Using Code First, mapping information is all done via C# code. This tutorial provides information about all these programming models.

You will learn about the mappings between the database and the entity classes using the Conceptual Schema Definition Language (CSDL), the Store Schema Definition Language (SSDL), and the Mapping Schema Language (MSL). Different relationships between entities are covered, such as one table per hierarchy of objects, one table per type, and n-to-n relationships.

This tutorial also describes different ways to access the database from the code directly with the EntityClient provider, using Entity SQL or helper methods that create Entity SQL, and using LINQ to Entities. Also described are object tracking and how the data context holds change information for updating data. Finally, you'll learn how POCO (Plain Old CLR Objects) can be used with the Entity Framework, and how to use the Code First programming model.

The ADO.NET Entity Framework provides a mapping from the relational database schema to objects. Relational databases and object-oriented languages define associations differently. For example, the sample database Formula1 contains the Racers and RaceResults tables. To access all the RaceResults rows for a racer, you need to do a SQL join statement. With object-oriented languages, it is more common to define a Racer class and a RaceResult class and access the race results of a racer by using a RaceResults property from the Racer class.

For object-relational mapping before using the Entity Framework, it has been possible to use the DataSet class and typed data sets. Data sets are very similar to the structure of a database containing DataTable, DataRow, DataColumn, and DataRelation classes instead of offering object-support. The ADO.NET Entity Framework supports directly defining entity classes that are completely independent of a database structure and mapping them to tables and associations of the database. Using objects with the application, the application is shielded from changes in the database.

The ADO.NET Entity Framework offers Entity SQL to define entity-based queries to the store (an extension to T-SQL). LINQ to Entities makes it possible to use the LINQ syntax to query data. An object context acts as a bridge regarding entities that are changed, retaining information for when the entities should be written back to the store. Microsoft moves more and more parts of the core framework into NuGet packages, which means that it is not necessary to wait for an update of the complete .NET Framework to deliver new features. With the latest versions of the Entity Framework, more and more parts moved out into a NuGet package. As of Entity Framework 6 (which is discussed in this book), the framework is now completely in a NuGet package. To not get in conflict with previous versions, some parts now have new namespaces, but the classes and members remain the same. The namespaces that contain classes from the ADO.NET Entity Framework are listed in the following table:

Namespaces that include the ADO.NET Entity Framework classes.
Namespace Description
System.Data A main namespace for ADO.NET. With the ADO.NET Entity Framework, this namespace contains exception classes related to entities — for example, MappingException and QueryException.
System.Data.Core.Common Contains classes to build an expression tree.
System.Data.Core.Common .CommandTrees The body of the Web document displayed in the browser window
System.Data.Entity Contains classes for the Code First development model.
System.Data.Entity.Design The list of previously visited Web sites within the browser window
System.Data.Entity.Core. EntityClient Specifies classes for the .NET Framework Data Provider to access the Entity Framework. EntityConnection, EntityCommand, and EntityDataReader can be used to access the Entity Framework.

Entity Framework Mapping

With Model First and Database First, the ADO.NET Entity Framework offers several layers to map database tables to objects. With Database First you can start with a database schema and use a Visual Studio item template (ADO.NET Entity Data Model) to create the complete mapping. You can also start designing entity classes with the designer (Model First) and map it to the database such that the tables and the associations between the tables can have a very different structure. The layers that need to be defined are as follows:

  • Logical: Defines the relational data.
  • Conceptual: Defines the .NET entity classes.
  • Mapping: Defines the mapping from .NET classes to relational tables and associations.

Note: To view the code samples referenced in the text below, download the file Entity Framework Samples. Unzip the file and open the solution in Visual Studio.

Logical Layer

The logical layer is defined by the Store Schema Definition Language (SSDL) and describes the structure of the database tables and their relationships. The following code uses SSDL to describe the three tables: Books, Authors, and BooksAuthors. The EntityContainer element describes all the tables with EntitySet elements, and associations with AssociationSet elements. The parts of a table are defined with the EntityType element. With EntityType Books you can see the columns Id, Title, Publisher, and ISBN defined by the Property element. The Property element contains XML attributes to define the data type. The Key element defines the primary key of the table. You can find the following code in the code file BooksDemo/BooksModel.edmx.

Conceptual Layer

The conceptual layer defines .NET entity classes. This layer is created with the Conceptual Schema Definition Language (CSDL). Figure 33-2 shows the entities Author and Book defined with the ADO.NET Entity Data Model Designer. The following code (found in code file BooksDemo/BooksModel.edmx) is the CSDL content that defines the entity types Book and Author. Review the code that was created from the Books database in the Book.cs and Auhtor.cs files.

The entity is defined by an EntityType element, which contains Key, Property, and NavigationProperty elements to describe the properties of the created class. The Property element contains attributes to describe the name and type of the .NET properties of the classes generated by the designer. The Association element connects the types Author and Book. Multiplicity="*" means that one Author can write multiple Books, and one Book can be written by multiple Authors.

Mapping Layer

The mapping layer maps the entity type definition from the CSDL to the SSDL using the Mapping Specification Language (MSL). The following specification (code file BooksDemo/BooksModel.edmx) includes a Mapping element that contains the EntityTypeMapping element to reference the Book type of the CSDL and it defines the MappingFragment to reference the Authors table from the SSDL. The ScalarProperty maps the property of the .NET class with the Name attribute to the column of the database table with the ColumnName attribute.

Standard Query Operators Defined By The Enumerable Class

Standard Query Operators Description
Filtering operators define a restriction to the elements returned. With the Where query operator you can use a predicate; for example, a Lambda expression that returns a bool. OfType<TResult> filters the elements based on the type and returns only the elements of the type TResult.
Projection operators are used to transform an object into a new object of a different type. Select and SelectMany define a projection to select values of the result based on a selector function.
Sorting operators change the order of elements returned. OrderBy sorts values in ascending order; OrderByDescending sorts values in descending order. ThenBy and ThenByDescending operators are used for a secondary sort if the first sort gives similar results. Reverse reverses the elements in the collection.
Join operators are used to combine collections that might not be directly related to each other. With the Join operator a join of two collections based on key selector functions can be done. This is similar to the JOIN you know from SQL. The GroupJoin operator joins two collections and groups the results.
Grouping operators put the data into groups. The GroupBy operator groups elements with a common key. ToLookup groups the elements by creating a one-to-many dictionary
Quantifier operators return a Boolean value if elements of the sequence satisfy a specific condition. Any, All, and Contains are quantifier operators. Any determines if any element in the collection satisfies a predicate function; All determines if all elements in the collection satisfy a predicate. Contains checks whether a specific element is in the collection.
Partitioning operators return a subset of the collection. Take, Skip, TakeWhile, and SkipWhile are partitioning operators. With these, you get a partial result. With Take, you have to specify the number of elements to take from the collection; Skip ignores the specified number of elements and takes the rest. TakeWhile takes the elements as long as a condition is true.
Set operators return a collection set. Distinct removes duplicates from a collection. With the exception of Distinct, the other set operators require two collections. Union returns unique elements that appear in either of the two collections. Intersect returns elements that appear in both collections. Except returns elements that appear in just one collection. Zip combines two collections into one.
Element operators return just one element. First returns the first element that satisfies a condition. FirstOrDefault is similar to First, but it returns a default value of the type if the element is not found. Last returns the last element that satisfies a condition. With ElementAt, you specify the position of the element to return. Single returns only the one element that satisfies a condition. If more than one element satisfies the condition, an exception is thrown.
Aggregate operators compute a single value from a collection. With aggregate operators, you can get the sum of all values, the number of all elements, the element with the lowest or highest value, an average number, and so on.
Conversion operators convert the collection to an array: IEnumerable, IList, IDictionary, and so on.
Generation operators return a new sequence. The collection is empty using the Empty operator; Range returns a sequence of numbers, and Repeat returns a collection with one repeated value.

LINQ to Entities Query Syntax

LINQ to Entities queries can be composed in two different syntaxes: query expression syntax and method-based query syntax. Query expression syntax is new in C# 3.0 and Visual Basic 9.0, and it consists of a set of clauses written in a declarative syntax similar to Transact-SQL or XQuery. However, the .NET Framework common language runtime (CLR) cannot read the query expression syntax itself. Therefore, at compile time, query expressions are translated to something that the CLR does understand: method calls. These methods are known as the standard query operators. As a developer, you have the option of calling them directly by using method syntax, instead of using query syntax. For more information, see Query Syntax and Method Syntax in LINQ.

Note: In order to use LINQ queries in your code you will need to include a using directive for the System.Linq namespace.

Query Expression Syntax

Query expressions are a declarative query syntax. This syntax enables a developer to write queries in a high-level language that is formatted similar to Transact-SQL. By using query expression syntax, you can perform even complex filtering, ordering, and grouping operations on data sources with minimal code. This code sample shows an example of query expression syntax:

IQueryable<Customer> customers =
    from c in context.Customers
    where c.ContactName.Length > 5
    orderby c.ContactName
    select c;

foreach (Customer c in customers)
    Label1.Text += c.ContactName + ": " + c.ContactTitle + "<br />";

Method-based Query Syntax

Another way to compose LINQ to Entities queries is by using method-based queries. The method-based query syntax is a sequence of direct method calls to LINQ operator methods, passing lambda expressions as the parameters. For more information, see Lambda Expressions. This code sample shows an example of method-based query syntax.

IQueryable<Customer> customers =
    context.Customers.Where(c => c.ContactName.Length > 5).OrderBy(c => c.ContactName);

foreach (Customer c in customers)
    Label1.Text += c.ContactName + ": " + c.ContactTitle + "<br />";

Entity Framework Demo

  1. Create a New Empty ASP.NET Web Application (.NET Framework) Project
  2. Add a new folder to the project named Models
  3. Right-Click on the Models folder and add an ADO.NET Entity Data Model
  4. Add a new Web form to the project and add a Label control to it.
  5. Press F7 to open the code-behind file for the Web form and replace all the template code with the following:

Note: In the examples below, replace <namespace> with the actual namespace being used in your project.

using System;
using <namespace>.Models;

namespace <namespace>
    public partial class WebForm1 : System.Web.UI.Page
        protected void Page_Load(object sender, EventArgs e)
            Label1.Text = "";
            using (var context = new Northwind())
                foreach (var customer in context.Customers)
                    Label1.Text += customer.ContactName + "<br />";

This example uses model-based query syntax to order customers by the ContactName field:

using System;
using <namespace>.Models;

namespace <namespace>
    public partial class WebForm1 : System.Web.UI.Page
        protected void Page_Load(object sender, EventArgs e)
            Label1.Text = "";
            using (var context = new Northwind())
                var customers = context.Customers.OrderBy(c => c.ContactName);

                foreach (var c in customers)
                    Label1.Text += c.ContactName + ": " + c.ContactTitle + "<br />";

Note: the OrderBy method sorts in ascending order, to sort in descending order use the OrderByDescending method.

Video: Using Visual Studio to Perform a Database First Migration

Entity Framework Links of Interest

Table of Contents