Chapter 33

ADO.NET Entity Framework

WHAT’S IN THIS CHAPTER?

• Programming Models
• Mapping
• Entity classes
• Object contexts
• Relationships
• Querying data
• Updates
• Using POCOs
• Code First

WROX.COM CODE DOWNLOADS FOR THIS CHAPTER

The wrox.com code downloads for this chapter are found at http://www.wrox.com/remtitle.cgi?isbn=1118314425 on the Download Code tab. The code for this chapter is divided into the following major examples:

• Books Demo
• Formula 1 Demo
• Payments Demo
• Query Demo
• POCO Demo
• Code First Demo

PROGRAMMING WITH THE ENTITY FRAMEWORK

The ADO.NET Entity Framework is an object-relational mapping 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 chapter 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 Storage 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 chapter 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 since .NET 1.0, 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.

The namespaces that contain classes from the ADO.NET Entity Framework are listed in the following table:

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.Common	Contains classes shared by .NET data providers. The class DbProviderServices is an abstract base class that must be implemented by an ADO.NET Entity Framework provider.
System.Data.Common.CommandTrees	Contains classes to build an expression tree.
System.Data.Entity	Contains classes for the Code First development model.
System.Data.Entity.Design	Contains classes used by the designer to create Entity Data Model (EDM) files.
System.Data.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.
System.Data.Objects	Contains classes to query and update databases. The class ObjectContext encapsulates the connection to the database and serves as a gateway for create, read, update, and delete methods. The class ObjectQuery represents a query against the store. CompiledQuery is a cached query.
System.Data.Objects.DataClasses	Contains classes and interfaces required for entities.

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 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.

Figure 33-1 shows a simple database schema, with the tables Books and Authors, and an association table BookAuthors that maps the authors to books.

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:

  <edmx:StorageModels>
    <Schema Namespace="BooksModel.Store" Alias="Self" 
        Provider="System.Data.SqlClient"
        ProviderManifestToken="2008"
        xmlns:store=                
        "http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"
        xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
      <EntityContainer Name="BooksModelStoreContainer">
        <EntitySet Name="Authors" EntityType="BooksModel.Store.Authors" 
            store:Type="Tables" Schema="dbo" />
        <EntitySet Name="Books" EntityType="BooksModel.Store.Books" 
            store:Type="Tables" Schema="dbo" />
        <EntitySet Name="BooksAuthors" EntityType="BooksModel.Store.BooksAuthors"
            store:Type="Tables" Schema="dbo" />
        <AssociationSet Name="FK_BooksAuthors_Authors"
            Association="BooksModel.Store.FK_BooksAuthors_Authors">
          <End Role="Authors" EntitySet="Authors" />
          <End Role="BooksAuthors" EntitySet="BooksAuthors" />
        </AssociationSet>
        <AssociationSet Name="FK_BooksAuthors_Books"
            Association="BooksModel.Store.FK_BooksAuthors_Books">
          <End Role="Books" EntitySet="Books" />
          <End Role="BooksAuthors" EntitySet="BooksAuthors" />
        </AssociationSet>
      </EntityContainer>
      <EntityType Name="Authors">
        <Key>
          <PropertyRef Name="Id" />
        </Key>
        <Property Name="Id" Type="int" Nullable="false" 
            StoreGeneratedPattern="Identity" />
        <Property Name="FirstName" Type="nvarchar" Nullable="false" 
            MaxLength="50" />
        <Property Name="LastName" Type="nvarchar" Nullable="false" 
            MaxLength="50" />
      </EntityType>
      <EntityType Name="Books">
        <Key>
          <PropertyRef Name="Id" />
        </Key>
        <Property Name="Id" Type="int" Nullable="false" 
            StoreGeneratedPattern="Identity" />
        <Property Name="Title" Type="nvarchar" Nullable="false" MaxLength="50" />
        <Property Name="Publisher" Type="nvarchar" Nullable="false" 
            MaxLength="50" />
        <Property Name="Isbn" Type="nchar" MaxLength="18" />
      </EntityType>
      <EntityType Name="BooksAuthors">
        <Key>
          <PropertyRef Name="BookId" />
          <PropertyRef Name="AuthorId" />
        </Key>
        <Property Name="BookId" Type="int" Nullable="false" />
        <Property Name="AuthorId" Type="int" Nullable="false" />
      </EntityType>
      <Association Name="FK_BooksAuthors_Authors">
        <End Role="Authors" Type="BooksModel.Store.Authors" Multiplicity="1" />
        <End Role="BooksAuthors" Type="BooksModel.Store.BooksAuthors" 
            Multiplicity="*" />
        <ReferentialConstraint>
          <Principal Role="Authors">
            <PropertyRef Name="Id" />
          </Principal>
          <Dependent Role="BooksAuthors">
            <PropertyRef Name="AuthorId" />
          </Dependent>
        </ReferentialConstraint>
      </Association>
      <Association Name="FK_BooksAuthors_Books">
        <End Role="Books" Type="BooksModel.Store.Books" Multiplicity="1" />
        <End Role="BooksAuthors" Type="BooksModel.Store.BooksAuthors" 
            Multiplicity="*" />
        <ReferentialConstraint>
          <Principal Role="Books">
            <PropertyRef Name="Id" />
          </Principal>
          <Dependent Role="BooksAuthors">
            <PropertyRef Name="BookId" />
          </Dependent>
        </ReferentialConstraint>
      </Association>
    </Schema>
  </edmx:StorageModels>

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. This was created from the Books database:

  <edmx:ConceptualModels>
    <Schema Namespace="BooksModel" Alias="Self" xmlns:annotation=
        "http://schemas.microsoft.com/ado/2009/02/edm/annotation"
        xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
      <EntityContainer Name="BooksEntities" annotation:LazyLoadingEnabled="true">
        <EntitySet Name="Authors" EntityType="BooksModel.Author" />
        <EntitySet Name="Books" EntityType="BooksModel.Book" />
        <AssociationSet Name="BooksAuthors" Association="BooksModel.BooksAuthors">
          <End Role="Authors" EntitySet="Authors" />
          <End Role="Books" EntitySet="Books" />
        </AssociationSet>
      </EntityContainer>
      <EntityType Name="Author">
        <Key>
          <PropertyRef Name="Id" />
        </Key>
        <Property Name="Id" Type="Int32" Nullable="false"
            annotation:StoreGeneratedPattern="Identity" />
        <Property Name="FirstName" Type="String" Nullable="false" MaxLength="50"
            Unicode="true" FixedLength="false" />
        <Property Name="LastName" Type="String" Nullable="false" MaxLength="50"
            Unicode="true" FixedLength="false" />
        <NavigationProperty Name="Books" Relationship="BooksModel.BooksAuthors"
            FromRole="Authors" ToRole="Books" />
      </EntityType>
      <EntityType Name="Book">
        <Key>
          <PropertyRef Name="Id" />
        </Key>
        <Property Name="Id" Type="Int32" Nullable="false"
            annotation:StoreGeneratedPattern="Identity" />
        <Property Name="Title" Type="String" Nullable="false" MaxLength="50" 
            Unicode="true" FixedLength="false" />
        <Property Name="Publisher" Type="String" Nullable="false" MaxLength="50"
            Unicode="true" FixedLength="false" />
        <Property Name="Isbn" Type="String" MaxLength="18" Unicode="true"
            FixedLength="true" />
        <NavigationProperty Name="Authors" Relationship="BooksModel.BooksAuthors"
            FromRole="Books" ToRole="Authors" />
      </EntityType>
      <Association Name="BooksAuthors">
        <End Role="Authors" Type="BooksModel.Author" Multiplicity="*" />
        <End Role="Books" Type="BooksModel.Book" Multiplicity="*" />
      </Association>
    </Schema>
  </edmx:ConceptualModels>

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:

  <edmx:Mappings>
    <Mapping Space="C-S" 
        xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
      <EntityContainerMapping StorageEntityContainer="BooksModelStoreContainer"
          CdmEntityContainer="BooksEntities">
        <EntitySetMapping Name="Authors">
          <EntityTypeMapping TypeName="BooksModel.Author">
            <MappingFragment StoreEntitySet="Authors">
              <ScalarProperty Name="Id" ColumnName="Id" />
              <ScalarProperty Name="FirstName" ColumnName="FirstName" />
              <ScalarProperty Name="LastName" ColumnName="LastName" />
            </MappingFragment>
          </EntityTypeMapping>
        </EntitySetMapping>
        <EntitySetMapping Name="Books">
          <EntityTypeMapping TypeName="BooksModel.Book">
            <MappingFragment StoreEntitySet="Books">
              <ScalarProperty Name="Id" ColumnName="Id" />
              <ScalarProperty Name="Title" ColumnName="Title" />
              <ScalarProperty Name="Publisher" ColumnName="Publisher" />
              <ScalarProperty Name="Isbn" ColumnName="Isbn" />
            </MappingFragment>
          </EntityTypeMapping>
        </EntitySetMapping>
        <AssociationSetMapping Name="BooksAuthors" TypeName=
            "BooksModel.BooksAuthors" StoreEntitySet="BooksAuthors">
          <EndProperty Name="Authors">
            <ScalarProperty Name="Id" ColumnName="AuthorId" />
          </EndProperty>
          <EndProperty Name="Books">
            <ScalarProperty Name="Id" ColumnName="BookId" />
          </EndProperty>
        </AssociationSetMapping>
      </EntityContainerMapping>
    </Mapping>
  </edmx:Mappings>

Connection String

Using the designer, the connection string is stored in the configuration file. The connection string is required for EDM and is different from the normal ADO.NET connection string because mapping information is required. The mapping is defined with the keyword metadata. The connection string requires three parts:

• A metadata keyword with delimited list of mapping files
• A provider for the invariant provider name to access the data source
• A provider connection string to assign the provider-dependent connection string

The following code snippet shows a sample connection string. With the metadata keyword, the delimited list of mapping files references the files BooksModel.csdl, BooksModel.ssdl, and BooksModel.msl, which are contained within resources in the assembly as defined with the res: prefix. In Visual Studio, the designer uses just one file, BooksModel.edmx, which contains CSDL, SSDL, and MSL. Setting the property Custom Tool to EntityModelCodeGenerator creates three files that are contained in resources.

Within the provider connection string setting you can find the connection string to the database with the connection string setting. This part is the same as a simple ADO.NET connection string discussed in Chapter 32, Core “ADO.NET,” and varies according to the provider that is set with the provider setting:

  <connectionStrings>
    <add name="BooksEntities"
      connectionString="metadata=res://*/BooksModel.csdl|res://*/BooksModel.ssdl|
        res://*/BooksModel.msl;provider=System.Data.SqlClient;
        provider connection string=&quot;Data Source=(local);
        Initial Catalog=Books;Integrated Security=True;Pooling=False;
        MultipleActiveResultSets=True&quot;"
      providerName="System.Data.EntityClient" />
  </connectionStrings>

ENTITIES

Entity classes that are created with the designer and CSDL typically derive from the base class EntityObject, as shown with the Book class in the code that follows (code file BooksDemo/BooksModel.Designer.cs).

The Book class derives from the base class EntityObject and defines properties for its data such as Title and Publisher. The set accessors of these properties fire change information in two different ways:

• By invoking the methods ReportPropertyChanging and ReportPropertyChanged of the base class EntityObject. Invoking these methods uses the INotifyPropertyChanging and INotifyPropertyChanged interfaces to inform every client that registers with the events about these interfaces.
• By using partial methods such as OnTitleChanging and OnTitleChanged. By default, these have no implementation but can be implemented in custom extensions of this class.

The Authors property uses the RelationshipManager class to return the Books for an author:

  [EdmEntityTypeAttribute(NamespaceName = "BooksModel", Name = "Book")]
  [Serializable()]
  [DataContractAttribute(IsReference = true)]
  public partial class Book : EntityObject
  {
    public static Book CreateBook(int id, string title, string publisher)
    {
      Book book = new Book();
      book.Id = id;
      book.Title = title;
      book.Publisher = publisher;
      return book;
    }
 
    [EdmScalarPropertyAttribute(EntityKeyProperty = true, IsNullable = false)]
    [DataMemberAttribute()]
    public int Id
    {
      get
      {
        return _Id;
      }
      set
      {
        if (_Id != value)
        {
          OnIdChanging(value);
          ReportPropertyChanging("Id");
          _Id = StructuralObject.SetValidValue(value);
          ReportPropertyChanged("Id");
          OnIdChanged();
        }
      }
    }
    private int _Id;
    partial void OnIdChanging(int value);
    partial void OnIdChanged();
 
    [EdmScalarPropertyAttribute(EntityKeyProperty = false, IsNullable = false)]
    [DataMemberAttribute()]
    public string Title
    {
      get
      {
        return _Title;
      }
      set
      {
        OnTitleChanging(value);
        ReportPropertyChanging("Title");
        _Title = StructuralObject.SetValidValue(value, false);
        ReportPropertyChanged("Title");
        OnTitleChanged();
      }
    }
    private string _Title;
    partial void OnTitleChanging(string value);
    partial void OnTitleChanged();
 
    [EdmScalarPropertyAttribute(EntityKeyProperty = false, IsNullable = false)]
    [DataMemberAttribute()]
    public string Publisher
    {
      get
      {
        return _Publisher;
      }
      set
      {
        OnPublisherChanging(value);
        ReportPropertyChanging("Publisher");
        _Publisher = StructuralObject.SetValidValue(value, false);
        ReportPropertyChanged("Publisher");
        OnPublisherChanged();
      }
    }
    private string _Publisher;
    partial void OnPublisherChanging(string value);
    partial void OnPublisherChanged();
 
    [EdmScalarPropertyAttribute(EntityKeyProperty = false, IsNullable = true)]
    [DataMemberAttribute()]
    public string Isbn
    {
      get
      {
        return _Isbn;
      }
      set
      {
        OnIsbnChanging(value);
        ReportPropertyChanging("Isbn");
        _Isbn = StructuralObject.SetValidValue(value, true);
        ReportPropertyChanged("Isbn");
        OnIsbnChanged();
      }
    }
    private string _Isbn;
    partial void OnIsbnChanging(string value);
    partial void OnIsbnChanged();
 
    [XmlIgnoreAttribute()]
    [SoapIgnoreAttribute()]
    [DataMemberAttribute()]
    [EdmRelationshipNavigationPropertyAttribute("BooksModel", "BooksAuthors", 
        "Authors")]
    public EntityCollection<Author> Authors
    {
      get
      {
        return ((IEntityWithRelationships)this).RelationshipManager.
            GetRelatedCollection<Author>("BooksModel.BooksAuthors", "Authors");
      }
      set
      {
        if ((value != null))
        {
          ((IEntityWithRelationships)this).RelationshipManager.
              InitializeRelatedCollection<Author>("BooksModel.BooksAuthors", 
              "Authors", value);
        }
      }
    }
  }

The classes and interfaces that are important to entity classes are explained in the following table. With the exception of INotifyPropertyChanging and INotifyPropertyChanged, the types are defined in the namespace System.Data.Objects.DataClasses.

CLASS OR INTERFACE	DESCRIPTION
StructuralObject	The base class of the classes EntityObject and ComplexObject. This class implements the interfaces INotifyPropertyChanging and INotifyPropertyChanged.
INotifyPropertyChanging INotifyPropertyChanged	These interfaces define the PropertyChanging and PropertyChanged events to enable subscribing to information when the state of the object changes. Unlike the other classes and interfaces here, these interfaces are defined in the namespace System.ComponentModel.
EntityObject	This class derives from StructuralObject and implements the interfaces IEntityWithKey, IEntityWithChangeTracker, and IEntityWithRelationships. EntityObject is a commonly used base class for objects mapped to database tables that contain a primary key and relationships to other objects.
ComplexObject	You can use this class as a base class for entity objects that do not have a primary key. It derives from StructuralObject but does not implement other interfaces as the EntityObject class does.
IEntityWithKey	This interface defines an EntityKey property that enables fast access to the object.
IEntityWithChangeTracker	This interface defines the method SetChangeTracker whereby a change tracker that implements the interface IChangeTracker can be assigned to get information about state change from the object.
IEntityWithRelationships	This interface defines the read-only property RelationshipManager, which returns a RelationshipManager object that can be used to navigate between objects.

The Book entity class can easily be accessed by using the object context class BooksEntities. The Books property returns a collection of Book objects that can be iterated (code file BooksDemo/Program.cs)

      using (var data = new BooksEntities())
      {
        foreach (var book in data.Books)
        {
          Console.WriteLine("{0}, {1}", book.Title, book.Publisher);
        }
      }

OBJECT CONTEXT

To retrieve data from the database, the ObjectContext class is needed. This class defines the mapping from the entity objects to the database. With core ADO.NET, you can compare this class to the data adapter that fills a DataSet.

The BooksEntities class created by the designer derives from the base class ObjectContext. This class adds constructors to pass a connection string. With the default constructor, the connection string is read from the configuration file. It is also possible to pass an already opened connection to the constructor in the form of an EntityConnection instance. If you pass a connection to the constructor that is not opened, the object context opens and closes the connection; if you pass an opened connection, you also need to close it.

The created class defines Books and Authors properties, which return an ObjectSet<TEntity>. ObjectSet<TEntity> derives from ObjectQuery<TEntity> (code file BooksDemo/BooksModel.Designer.cs):

  public partial class BooksEntities : ObjectContext
  {
    public BooksEntities() : base("name=BooksEntities", "BooksEntities")
    {
      this.ContextOptions.LazyLoadingEnabled = true;
      OnContextCreated();
    }
    public BooksEntities(string connectionString) 
      : base(connectionString, "BooksEntities")
    {
      this.ContextOptions.LazyLoadingEnabled = true;
      OnContextCreated();
    }
    public BooksEntities(EntityConnection connection) 
      : base(connection, "BooksEntities")
    {
      this.ContextOptions.LazyLoadingEnabled = true;
      OnContextCreated();
    }
 
    partial void OnContextCreated();
 
    public ObjectSet<Author> Authors
    {
      get
      {
        if ((_Authors == null))
        {
          _Authors = base.CreateObjectSet<Author>("Authors");
        }
        return _Authors;
      }
    }
    private ObjectSet<Author> _Authors;
 
    public ObjectSet<Book> Books
    {
      get
      {
        if ((_Books == null))
        {
          _Books = base.CreateObjectSet<Book>("Books");
        }
        return _Books;
      }
    }
    private ObjectSet<Book> _Books;
  }

The ObjectContext class provides several services to the caller:

• It keeps track of entity objects that are already retrieved. If the object is queried again, it is taken from the object context.
• It keeps state information about the entities. You can get information about added, modified, and deleted objects.
• You can update the entities from the object context to write the changes to the underlying store.

Methods and properties of the ObjectContext class are listed in the following table:

METHOD OR PROPERTY	DESCRIPTION
Connection	This property returns a DbConnection object that is associated with the object context.
MetadataWorkspace	This property returns a MetadataWorkspace object that can be used to read the metadata and mapping information.
QueryTimeout	With this property you can get and set the timeout value for the queries of the object context.
ObjectStateManager	This property returns an ObjectStateManager. The ObjectStateManager keeps track of entity objects retrieved and object changes in the object context.
CreateQuery()	This method returns an ObjectQuery to get data from the store. The Books and Authors properties shown earlier use this method to return an ObjectQuery.
GetObjectByKey() TryGetObjectByKey()	These methods return the object by the primary key either from the object state manager or the underlying store. GetObjectByKey throws an exception of type ObjectNotFoundException if the primary key does not exist. TryGetObjectByKey returns false.
AddObject()	This method adds a new entity object to the object context.
DeleteObject()	This method deletes an object from the object context.
Detach()	This method detaches an entity object from the object context, so it is no longer tracked if changes occur.
Attach() AttachTo()	The Attach method attaches a detached object to the store. Attaching objects back to the object context requires that the entity object implements the interface IEntityWithKey. The AttachTo method does not have the requirement for a key with the object, but it requires the entity set name to which the entity object needs to be attached.
ApplyPropertyChanges()	If an object was detached from the object context, then the detached object is modified, and afterward the changes should be applied to the object within the object context, you can invoke the ApplyPropertyChanges method to apply the changes. This is useful in a scenario where a detached object was returned from a web service, changed by a client, and passed to the web service in a modified way.
Refresh()	The data in the store can change while entity objects are stored inside the object context. To make a refresh from the store, the Refresh method can be used. With this method you can pass a RefreshMode enumeration value. If the values for the objects are not the same between the store and the object context, passing the value ClientWins changes the data in the store. The value StoreWins changes the data in the object context.
SaveChanges()	Adding, modifying, and deleting objects from the object context does not change the object from the underlying store. Use the SaveChanges method to persist the changes to the store.
AcceptAllChanges()	This method changes the state of the objects in the context to unmodified. SaveChanges invokes this method implicitly.

RELATIONSHIPS

The entity types Book and Author are related to each other. A book can be written by one or more authors, and an author can write one or more books. Relationships are based on the count of types they relate and the multiplicity. The ADO.NET Entity Framework supports several kinds of relationships, some of which are described here, including table-per-hierarchy (TPH) and table-per-type (TPT). Multiplicity can be one-to-one, one-to-many, or many-to-many.

Table per Hierarchy

With TPH, there’s one table in the database that corresponds to a hierarchy of entity classes. For example, the database table Payments shown in Figure 33-3 contains columns for a hierarchy of entity types. Some of the columns are common to all entities in the hierarchy, such as Id and Amount. The Number column is used only by a credit card payment and a check payment.

The entity classes that all map to the same Payments table are shown in Figure 33-4. Payment is an abstract base class to contain properties common to all types in the hierarchy. Concrete classes that derive from Payment are CreditCardPayment, CashPayment, and CheckPayment. CreditCardPayment has a CreditCard property in addition to the properties of the base class; ChequePayment has BankName and BankAccount properties.

This mapping can be defined with the designer. The mapping details can be configured with the Mapping Details dialog shown in Figure 33-5. Selection of the concrete class type is done based on a Condition element as defined with the option Maps to Payments When Type = CREDITCARD. The type is selected based on the value of the Type column. Other options to select the type are also possible; for example, you can verify whether a column is not null.

Now it’s possible to iterate the data from the Payments table, and different types are returned based on the mapping (code file PaymentsDemo/Program.cs):

      using (var data = new PaymentsEntities())
      {
        foreach (var p in data.Payments)
        {
          Console.WriteLine("{0}, {1} - {2:C}", p.GetType().Name, p.Name, 
              p.Amount);
        }
      }

Running the application returns two CashPayment and one CreditCardPayment objects from the database:

CreditCardPayment, Gladstone - $22.00
CashPayment, Donald - $0.50
CashPayment, Scrooge - $80,000.00

Using the OfType method offers an easy way to get the result from a specific type:

        foreach (var p in data.Payments.OfType<CreditcardPayment>())
        {
          Console.WriteLine("{0} {1} {2}", p.Name, p.Amount, p.CreditCard);
        }

The T-SQL statement that’s generated from this query is very efficient to filter the type with the WHERE clause because it is defined from the model:

SELECT
'0X0X' AS [C1],
[Extent1].[Id] AS [Id],
[Extent1].[Amount] AS [Amount],
[Extent1].[Name] AS [Name],
[Extent1].[Number] AS [Number]
FROM [dbo].[Payments] AS [Extent1]
WHERE [Extent1].[Type] = N'CREDITCARD'

Table per Type

With TPT, one table maps to one type. The Formula1 database has a schema with the tables Racers, RaceResults, Races, and Circuits. The RaceResults table has a relationship with the Racers table with the foreign key RacerId; the Races table relates to the Circuits table with the foreign key CircuitId.

Figure 33-6 shows the entity types Racer, RaceResult, Race, and Circuit. There are several one-to-many relationships.

You access the customers and their orders with two iterations shown in the next code block (Formula1Demo/Program.cs). First, the Racer objects are accessed, and the values of the FirstName and LastName properties are written to the console. Then all race results are accessed by using the RaceResults property of the Racer class. The related orders are lazy loaded to access the property because with the ObjectContext, the ContextOptions.LazyLoadingEnabled property is set to true:

      using (var data = new Formula1Entities())
      {
        foreach (var racer in data.Racers)
        {
          Console.WriteLine("{0} {1}", racer.FirstName, racer.LastName);
          foreach (var raceResult in racer.RaceResults)
          {
            Console.WriteLine("	{0} {1:d} {2}", raceResult.Race.Circuit.Name, 
                raceResult.Race.Date,  raceResult.Position);
          }
        }
      }

Behind the scenes, the RelationshipManager class is used to access the relationship. The RelationShipManager instance can be accessed by casting the entity object to the interface IEntityWithRelationships as you can see in the designer-generated property Orders from the class Customer (code file Formula1Demo/Formula1Model.Designer.cs). This interface is explicitly implemented by the class EntityObject. The RelationshipManager property returns a RelationshipManager that is associated with the entity object at one end. The other end is defined by invoking the method GetRelatedCollection. The first parameter Formula1Model.FK_RaceResults_Racers is the name of the relationship: the second parameter RaceResults defines the name of the target role:

    public EntityCollection<RaceResult> RaceResults
    {
      get
      {
        return ((IEntityWithRelationships)this).RelationshipManager
            .GetRelatedCollection<RaceResult>(
            "Formula1Model.FK_RaceResults_Racers", "RaceResults");
      }
      set
      {
        if ((value != null))
        {
          ((IEntityWithRelationships)this).RelationshipManager
              .InitializeRelatedCollection<RaceResult>(
              "Formula1Model.FK_RaceResults_Racers", "RaceResults", value);
        }
      }
    }

Lazy, Delayed, and Eager Loading

With the designer’s default setting, relationships are lazy loaded on request. The designer sets the property LazyLoadingEnabled from the ContextOptions to true. You have other options as well. Relationships can also be eager loaded or delayed loaded.

Eager loading means that the relationship is loaded at the same time the parent objects are loaded. The race results, the associated race with the race result, and the associated circuit with the race are loaded immediately after adding a call to the Include method. The Include method is available with ObjectSet<TEntity> types (the Racers property is of type ObjectSet<Racer>), and receives the relationship name. Accessing the RaceResults property in the foreach loop you can see that all the information up to the circuit information is shown:

        foreach (var racer in data.Racers.Include("RaceResults.Race.Circuit"))
        {
          Console.WriteLine("{0} {1}", racer.FirstName, racer.LastName);
          foreach (var raceResult in racer.RaceResults)
          {
            Console.WriteLine("	{0} {1:d} {2}", raceResult.Race.Circuit.Name, 
                raceResult.Race.Date,  raceResult.Position);
          }
        }

Eager loading has the advantage that if all related objects are needed, then fewer requests to the database are made. Of course, if not all related objects are needed, lazy or delayed loading is preferred.

Delayed loading needs an explicit call to the Load method of the EntityCollection<T> class. Using this method, the option LazyLoadingEnabled can be set to false. With the following code snippet, the orders are loaded with the Load method if they are not loaded with the IsLoaded property:

          if (!racer.RaceResults.IsLoaded)
            racer.RaceResult.Load();

One overload of the Load method accepts a MergeOption enumeration. The possible values are explained in the following table:

MERGEOPTION VALUE	DESCRIPTION
AppendOnly	This is the default value. New entities are appended; existing entities in the object context are not modified.
NoTracking	The ObjectStateManager that tracks changes to entity objects is not modified.
OverwriteChanges	The current values of the entity objects are replaced with the values from the store.
PreserveChanges	The original values of the entity objects in the object context are replaced with the values from the store.

QUERYING DATA

The Entity Framework offers several ways to query the data: Entity SQL, which is an extension to T-SQL; using helper methods to create Entity SQL; and LINQ. All of these variants are discussed in this section.

Entity SQL

Entity SQL enhances T-SQL by adding types. This syntax doesn’t require joins because associations of entities can be used instead. You can use EntityClient, a low-level API to access the Entity Framework. This API is implemented as an ADO.NET provider. EntityClient offers EntityConnection, EntityCommand, EntityParameter, and EntityDataReader classes that derive from the base classes DbConnection, DbCommand, DbParameter, and DbDataReader.

You can use these classes in the same way that you would use the ADO.NET classes described in Chapter 32, except that a special connection string is required and Entity SQL is used instead of T-SQL to access the EDM.

The connection to the database is done with the EntityConnection, which requires an entity connection string. This string is read from the configuration file with the help of the ConfigurationManager class from the System.Configuration namespace. The CreateCommand method of the EntityConnection class returns an EntityCommand. The command text for the EntityCommand is assigned with the CommandText property and requires Entity SQL syntax. Formula1Entities.Racers is defined within the EntityContainer element in the Formula1Entities CSDL definition, and the Racers EntitySet gets all racers from the Racers table. Command.ExecuteReader returns a data reader that reads row by row (code file QueryDemo/Program.cs):

      string connectionString =    
          ConfigurationManager.ConnectionStrings["Formula1Entities"]
          .ConnectionString;
      var connection = new EntityConnection(connectionString);
      await connection.OpenAsync();
      EntityCommand command = connection.CreateCommand();
      command.CommandText = "[Formula1Entities].[Racers]";
      DbDataReader reader = await command.ExecuteReaderAsync(
          CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection);
      while (await reader.ReadAsync())
      {
        Console.WriteLine("{0} {1}", reader["FirstName"], reader["LastName"]);
      }
      reader.Close();

Let’s look at a few more Entity SQL syntax options. Here, just a few are shown to help you get started with Entity SQL. In the MSDN documentation you can find the complete reference.

The preceding example showed how Entity SQL uses definitions from the CSDL in the EntityContainer and EntitySet—for example, Formula1Entities.Racers to get all the racers from the table Racers.

Instead of retrieving all columns, you can also use the Property elements of an EntityType. This might look very similar to the T-SQL queries used in the previous chapter:

      EntityCommand command = connection.CreateCommand();
      command.CommandText = 
        "SELECT Racers.FirstName, Racers.LastName FROM Formula1Entities.Racers";
      DbDataReader reader = await command.ExecuteReaderAsync(
          CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection);
      while (await reader.ReadAsync())
      {
        Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
      }
      reader.Close();

There’s no SELECT * with Entity SQL. All the columns were retrieved earlier by requesting the EntitySet. Using SELECT VALUE you can also get all the columns, as shown in the next snippet. This code uses a filter with WHERE to retrieve only specific publishers with the query. Note that the CommandText specifies the parameter with the @ character—however, the parameter that is added to the Parameters collection does not use the @ character to write a value to the same parameter:

      EntityCommand command = connection.CreateCommand();
      command.CommandText = 
        "SELECT VALUE it FROM [Formula1Entities].[Racers] AS it " +
        "WHERE it.Nationality = @Country";
      command.Parameters.AddWithValue("Country", "Austria");

Now let’s change to the object context and the mapping functionality but stay with Entity SQL. Entity SQL can be created a lot easier with the help of query builder methods, as discussed next.

Object Query

You can define queries with the ObjectQuery<T> class, or the class that derives from it: ObjectSet<T>. The following example (code file Formula1Demo/Program.cs) demonstrates a simple query to access all Racer entities. The Racers property of the generated Formula1Entities class returns an ObjectSet<Racer>:

      using (Formula1Entities data = new Formula1Entities())
      {
        ObjectSet<Racer> racers = data.Racers;
        Console.WriteLine(racers.CommandText);
        Console.WriteLine(racers.ToTraceString());
      }

The Entity SQL statement that is returned from the CommandText property is shown here:

[Formula1Entities].[Racers]

This was exactly the statement that you used earlier with the EntityCommand object. What about the T-SQL statement that is sent to the database? This is shown by the ToTraceString method:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Nationality] AS [Nationality],
[Extent1].[Starts] AS [Starts],
[Extent1].[Wins] AS [Wins],
[Extent1].[Points] AS [Points],
[Extent1].[DateOfBirth] AS [DateOfBirth],
[Extent1].[DateOfDeath] AS [DateOfDeath]

Instead of accessing the Racers property from the object context, you can also create a query with the CreateQuery method:

      ObjectQuery<Racer> racers = 
        data.CreateQuery<Racer>("[Formula1Entities].[Racers]");

This is similar to using the Racers property.

Now it would be interesting to filter the racers based on a condition. This can be done by using the Where method of the ObjectQuery<T> class. Where is one of the query builder methods that creates Entity SQL. This method requires a predicate as a string, and optional parameters of type ObjectParameter. The predicate shown in the following example specifies that only racers from Brazil should be returned. The it specifies the item of the result and Country is the column Country. The first parameter of the ObjectParameter constructor references the @Country parameter of the predicate but doesn’t list the @ sign:

        string country = "Brazil";
        ObjectQuery<Racer> racers = data.Racers.Where("it.Country = @Country",
          new ObjectParameter("Country", country));

The magic behind it can be seen immediately by accessing the CommandText property of the query. With Entity SQL, SELECT VALUE it declares it as the variable to access the columns:

SELECT VALUE it
FROM (
[Formula1Entities].[Racers]
) AS it
WHERE
it.Nationality = @Country

The method ToTraceString shows the generated SQL statement:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Nationality] AS [Nationality],
[Extent1].[Starts] AS [Starts],
[Extent1].[Wins] AS [Wins],
[Extent1].[Points] AS [Points],
[Extent1].[DateOfBirth] AS [DateOfBirth],
[Extent1].[DateOfDeath] AS [DateOfDeath]
FROM [dbo].[Racers] AS [Extent1]
WHERE [Extent1].[Nationality] = @Country

Of course, you can also specify the complete Entity SQL:

      string country = "Brazil";
      ObjectQuery<Racer> racers = data.CreateQuery<Racer>(
        "SELECT VALUE it FROM ([Formula1Entities].[Racers]) AS it " +
        "WHERE it.Nationality = @Country",
        new ObjectParameter("Country", country));

The class ObjectQuery<T> offers several query builder methods, as described in the following table. Many of these methods are very similar to the LINQ extension methods covered in Chapter 11, “Language Integrated Query.” An important difference with the methods here is that instead of parameters of type delegate or Expression<T>, the parameter type with ObjectQuery<T> is usually of type string.

OBJECTQUERY<T> QUERY BUILDER METHOD	DESCRIPTION
Where	Enables you to filter the results based on a condition.
Distinct	Creates a query with unique results.
Except	Returns the result without the items that meet the condition with the except filter.
GroupBy	Creates a new query to group entities based on a specified criterion.
Include	With relations, you saw earlier that related items are delay loaded. It is required to invoke the Load method of the EntityCollection<T> class to get related entities into the object context. Instead of using the Load method, you can specify a query with the Include method to eager fetch-related entities.
OfType	Specifies returning only those entities of a specific type. This is very helpful with TPH relations.
OrderBy	Defines the sort order of the entities.
Select SelectValue	These methods return a projection of the results. Select returns the result items in the form of a DbDataRecord; SelectValue returns the values as scalars or complex types as defined by the generic parameter TResultType.
Skip Top	These methods are useful for paging. Skip a number of items with the Skip method, and take a specified number as defined by the Top method.
Intersect Union UnionAll	These methods are used to combine two queries. Intersect returns a query containing only the results that are available in both of the queries. Union combines the queries and returns the complete result without duplicates. UnionAll also includes duplicates.

The following example (code file QueryDemo/Program.cs) demonstrates how to use these query builder methods. Here, the racers are filtered with the Where method to return only racers from the USA; the OrderBy method specifies descending sort order, first based on the number of wins and next on the number of starts. Finally, using the Top method, only the first three racers are returned in the result:

      using (var data = new Formula1Entities())
      {
        string country = "USA";
        ObjectQuery<Racer> racers = data.Racers.Where("it.Nationality = @Country",
            new ObjectParameter("Country", country))
            .OrderBy("it.Wins DESC, it.Starts DESC")
            .Top("3");
        foreach (var racer in racers)
        {
          Console.WriteLine("{0} {1}, wins: {2}, starts: {3}",
              racer.FirstName, racer.LastName, racer.Wins, racer.Starts);
        }
      }

This is the result from the preceding query:

Mario Andretti, wins: 12, starts: 128
Dan Gurney, wins: 4, starts: 87
Phil Hill, wins: 3, starts: 48

LINQ to Entities

In several chapters of this book, you’ve seen LINQ to Query objects, databases, and XML. Of course, LINQ is also available to query entities. With LINQ to Entities, the source for the LINQ query is ObjectQuery<T>. Because ObjectQuery<T> implements the interface IQueryable, the extension methods selected for the query are defined with the class Queryable from the namespace System.Linq. The extension methods defined with this class have a parameter Expression<T>; that’s why the compiler writes an expression tree to the assembly. You can read more about expression trees in Chapter 11. The expression tree is then resolved from the ObjectQuery<T> class to the SQL query.

As shown in the following example (code file QueryDemo/Program.cs), you can use a simple LINQ query to return the racers who won more than 40 races:

      using (var data = new Formula1Entities())
      {
        var racers = from r in data.Racers
                     where r.Wins > 40
                     orderby r.Wins descending
                     select r;
        foreach (Racer r in racers)
        {
          Console.WriteLine("{0} {1}", r.FirstName, r.LastName);
        }
      }

This is the result of accessing the Formula1 database:

Michael Schumacher
Alain Prost
Ayrton Senna

You can also define a LINQ query to access relationships, as shown in the next example. Variable r references racers, variable rr references all race results. The filter is defined with the where clause to retrieve only racers from Switzerland who had a race position on the podium. To get the podium finishes, the result is grouped, and the podium count calculated. Sorting is done based on the podium finishes:

      using (var data = new Formula1Entities())
      {
        var query = from r in data.Racers
                    from rr in r.RaceResults
                    where rr.Position <= 3 && rr.Position >= 1 &&
                        r.Nationality == "Switzerland"
                    group r by r.Id into g
                    let podium = g.Count()
                    orderby podium descending
                    select new
                    {
                      Racer = g.FirstOrDefault(),
                      Podiums = podium
                    };
        foreach (var r in query)
        {
          Console.WriteLine("{0} {1} {2}", r.Racer.FirstName, r.Racer.LastName,
                            r.Podiums);
        }
      }

The names of three racers from Switzerland are returned when you run the application:

Clay Regazzoni 28
Jo Siffert 6
Rudi Fischer 2

WRITING DATA TO THE DATABASE

Reading, searching, and filtering data from the store are just one part of the work that usually needs to be done with data-intensive applications. Writing changed data back to the store is the other part you need to know. This section covers object tracking, a service and foundation of the object context, how the object context knows about changes of the objects, how to attach and detach objects from the context, and how the object context makes use of the state of objects to save entity objects.

Object Tracking

To enable data read from the store to be modified and saved, the entities must be tracked after they are loaded. This also requires that the object context be aware of whether an entity has already been loaded from the store. If multiple queries are accessing the same records, the object context needs to return already loaded entities. The ObjectStateManager is used by the object context to keep track of entities that are loaded into the context.

The following example demonstrates that indeed if two different queries return the same record from the database, the state manager is aware of that and does not create a new entity. Instead, the same entity is returned. The ObjectStateManager instance that is associated with the object context can be accessed with the ObjectStateManager property. The ObjectStateManager class defines an event named ObjectStateManagerChanged that is invoked every time a new object is added or removed from the object context. Here, the method ObjectStateManager_ObjectStateManagerChanged is assigned to the event to get information about changes.

Two different queries are used to return an entity object. The first query gets the first racer from the country Austria with the last name Lauda. The second query asks for the racers from Austria, sorts the racers by the number of races won, and gets the first result. As a matter of fact, that’s the same racer. To verify that the same entity object is returned, the method Object.ReferenceEquals is used to verify whether the two object references indeed reference the same instance (code file QueryDemo/Program.cs):

    private static void TrackingDemo()
    {
      using (var data = new Formula1Entities())
      {
        data.ObjectStateManager.ObjectStateManagerChanged +=
          ObjectStateManager_ObjectStateManagerChanged;
 
        Racer niki1 = (from r in data.Racers
                       where r.Nationality == "Austria" && r.LastName == "Lauda"
                       select r).First();
 
        Racer niki2 = (from r in data.Racers
                       where r.Nationality == "Austria"
                       orderby r.Wins descending
                       select r).First();
 
        if (Object.ReferenceEquals(niki1, niki2))
        {
          Console.WriteLine("the same object");
        }
      }
    }
 
    private static void ObjectStateManager_ObjectStateManagerChanged(
        object sender, CollectionChangeEventArgs e)
    {
      Console.WriteLine("Object State change — action: {0}", e.Action);
      Racer r = e.Element as Racer;
      if (r != null)
        Console.WriteLine("Racer {0}", r.LastName);
    }

Running the application, you can see that the event of the ObjectStateManagerChanged of the ObjectStateManager occurs only once, and the references niki1 and niki2 are indeed the same:

Object State change — action: Add
Racer Lauda
The same object

Change Information

The object context is also aware of changes with the entities. The following example adds and modifies a racer from the object context and gets information about the change. First, a new racer is added with the AddObject method of the ObjectSet<T> class. This method adds a new entity with the EntityState.Added information. Next, a racer with the Lastname Alonso is queried. With this entity class, the Starts property is incremented and thus the entity is marked with the information EntityState.Modified. Behind the scenes, the ObjectStateManager is informed about a state change in the object based on the interface implementation’s INotifyPropertyChanged. This interface is implemented in the entity base class StructuralObject. The ObjectStateManager is attached to the PropertyChanged event, and this event is fired with every property change.

To get all added or modified entity objects, you can invoke the GetObjectStateEntries method of the ObjectStateManager and pass an EntityState enumeration value as it is done here. This method returns a collection of ObjectStateEntry objects that keeps information about the entities. The helper method DisplayState iterates through this collection to provide detail information.

You can also get state information about a single entity by passing the EntityKey to the GetObjectStateEntry method. The EntityKey property is available with entity objects implementing the interface IEntityWithKey, which is the case with the base class EntityObject. The ObjectStateEntry object returned offers the method GetModifiedProperties for reading all property values that have been changed, and you can access the original and the current information about the properties with the OriginalValues and CurrentValues indexers (code file Formula1Demo/Program.cs):

    private static void ChangeInformation()
    {
      using (var data = new Formula1Entities())
      {
        var jean = new Racer
        {
          FirstName = "Jean-Eric",
          LastName = "Vergne",
          Nationality = "France",
          Starts = 0
        };
        data.Racers.AddObject(jean);
        Racer fernando = data.Racers.Where("it.Lastname='Alonso'").First();
        fernando.Starts++;
        DisplayState(EntityState.Added.ToString(),
            data.ObjectStateManager.GetObjectStateEntries(EntityState.Added));
        DisplayState(EntityState.Modified.ToString(),
            data.ObjectStateManager.GetObjectStateEntries(EntityState.Modified));
        ObjectStateEntry stateOfFernando =
            data.ObjectStateManager.GetObjectStateEntry(fernando.EntityKey);
        Console.WriteLine("state of Fernando: {0}",
                      stateOfFernando.State.ToString());
        foreach (string modifiedProp in stateOfFernando.GetModifiedProperties())
        {
          Console.WriteLine("modified: {0}", modifiedProp);
          Console.WriteLine("original: {0}",
                            stateOfFernando.OriginalValues[modifiedProp]);
          Console.WriteLine("current: {0}",
                            stateOfFernando.CurrentValues[modifiedProp]);
        }
      }
    }
 
    static void DisplayState(string state, IEnumerable<ObjectStateEntry> entries)
    {
      foreach (var entry in entries)
      {
        var r = entry.Entity as Racer;
        if (r != null)
        {
          Console.WriteLine("{0}: {1}", state, r.Lastname);
        }
      }
    }

When you run the application, the added and modified racers are displayed, and the changed properties are shown with their original and current values:

Added: Vergne
Modified: Alonso
state of Fernando: Modified
modified: Starts
original: 181
current: 182

Attaching and Detaching Entities

When returning entity data to the caller, it might be important to detach the objects from the object context. This is necessary, for example, if an entity object is returned from a web service. In this case, if the entity object is changed on the client, the object context is not aware of the change.

With the sample code, the Detach method of the ObjectContext detaches the entity named fernando and thus the object context is not aware of any change to this entity. If a changed entity object is passed from the client application to the service, it can be attached again. Just attaching it to the object context might not be enough, however, because it doesn’t indicate that the object was modified. Instead, the original object must be available inside the object context. The original object can be accessed from the store by using the key with the method GetObjectByKey or TryGetObjectByKey. If the entity object is already inside the object context, the existing one is used; otherwise it is fetched newly from the database. Invoking the method ApplyCurrentValues passes the modified entity object to the object context; if there are changes, then the changes are made within the existing entity with the same key inside the object context, and the EntityState is set to EntityState.Modified. Remember that the method ApplyCurrentValues requires the object to exist within the object context; otherwise the new entity object is added with EntityState.Added (code file Formula1Demo/Program.cs):

      using (var data = new Formula1Entities())
      {
        data.ObjectStateManager.ObjectStateManagerChanged +=
            ObjectStateManager_ObjectStateManagerChanged;
        ObjectQuery<Racer> racers = data.Racers.Where("it.Lastname='Alonso'");
        Racer fernando = racers.First();
        EntityKey key = fernando.EntityKey;
        data.Racers.Detach(fernando);
        // Racer is now detached and can be changed independent of the 
        // object context
        fernando.Starts++;
        Racer originalObject = data.GetObjectByKey(key) as Racer;
        data.Racers.ApplyCurrentValues(fernando);
      }

Storing Entity Changes

Based on all the change information provided with the help of the ObjectStateManager, the added, deleted, and modified entity objects can be written to the store with the SaveChanges method of the ObjectContext class. To verify changes within the object context, you can assign a handler method to the SavingChanges event of the ObjectContext class. This event is fired before the data is written to the store, so you can add some verification logic to determine whether the changes should be done. SaveChanges returns the number of entity objects that have been written.

What happens if the records in the database that are represented by the entity classes have been changed after reading the record? The answer depends on the ConcurrencyMode property that is set with the model. With every property of an entity object, you can configure the ConcurrencyMode to Fixed or None. The value Fixed means that the property is validated at write time to confirm that the value was not changed in the meantime. None, which is the default, ignores any change. If some properties are configured to the Fixed mode, and data changed between reading and writing the entity objects, an OptimisticConcurrencyException occurs.

You can deal with this exception by invoking the Refresh method to read the actual information from the database into the object context. This method accepts two refresh modes configured by a RefreshMode enumeration value: ClientWins or StoreWins. StoreWins means that the actual information is taken from the database and set to the current values of the entity objects. ClientWins means that the database information is set to the original values of the entity objects, and thus the database values will be overwritten with the next SaveChanges. The second parameter of the Refresh method is either a collection of entity objects or a single entity object. You can specify the refresh behavior entity by entity (code file Formula1Demo/Program.cs):

        private static void ChangeInformation()
        {
            //...
                int changes = 0;
                try
                {
                    changes += data.SaveChanges();
                }
                catch (OptimisticConcurrencyException ex)
                {
                    data.Refresh(RefreshMode.ClientWins, ex.StateEntries);
                    changes += data.SaveChanges();
                }
                Console.WriteLine("{0} entities changed", changes);
                //...

USING POCO OBJECTS

If you do not want to derive the entity classes from the base class EntityObject—for example, if you have an existing library with your own POCO (Plain Old CLR Objects) classes—you can use these classes as well. Another scenario for using POCO objects is if you want to send the objects directly across a WCF service.

The sample application that makes use of POCO objects is the same database used previously, which includes the Books, Authors, and BooksAuthors tables. The EDM is created as before. However, now the designer property Code Generation Strategy is changed from Default to None. This way, instead of creating entity objects that derive from the EntityObject base class, no code is generated at all.

Defining Entity Types

Now entity objects can be created manually as shown in the following code snippet (code file POCODemo/Book.cs). The entity objects just need simple properties to map scalar properties from the CSDL definition. To map navigation properties, properties of ICollection<T> are used. The object behind the navigation property is of type HashSet<T>:

  public class Book
  {
    public Book()
    {
      this.Authors = new HashSet<Author>();
    }
 
    public int Id { get; set; }
    public string Title { get; set; }
    public string Publisher { get; set; }
    public string Isbn { get; set; }
 
    public virtual ICollection<Author> Authors { get; set; }
  }

The Author type is implemented similarly to the Book type (code file POCODemo/Author.cs):

  public class Author
  {
    public Author()
    {
      this.Books = new HashSet<Book>();
    }
 
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
 
    public virtual ICollection<Book> Books { get; set; }
  }

The navigation properties are implemented as virtual properties. This way, the DbContext creates a proxy that derives from the entity type to be used, instead of directly using the entity types. This makes features, such as lazy loading of navigation properties, possible. If the navigation properties are not declared virtual, a proxy type is not created, and the entity types are used directly.

Creating the Data Context

Creating POCO objects manually, the context is not created automatically as well. However, it’s an easy task to do. BooksEntities derives from the base class DbContext and defines properties for all the mapped types of type DbSet<T>. In earlier examples we’ve just used ObjectContext and ObjectSet<T> instead of DbContext and DbSet<T>. DbContext and DbSet<T> are wrappers of the other types and make dealing with context and set easier (code file POCODemo/BooksEntities.cs):

  public class BooksEntities : DbContext
  {
    public BooksEntities()
      : base("name=BooksEntities")
    {
    }
 
    public DbSet<Author> Authors { get; set; }
    public DbSet<Book> Books { get; set; }
  }

Queries and Updates

Now you can use the context and entity types in the same way as before. LINQ queries can be used to retrieve data; properties of entity objects are filled by the name; and it’s also possible to make some changes and save them to the database (code file POCODemo/Program.cs):

      using (BooksEntities data = new BooksEntities())
      {
        var books = data.Books.Include("Authors");
        foreach (var b in books)
        {
          Console.WriteLine("{0} {1}", b.Title, b.Publisher);
          foreach (var a in b.Authors)
          {
            Console.WriteLine("	{0} {1}", a.FirstName, a.LastName);
          }
        }
      }

USING THE CODE FIRST PROGRAMMING MODEL

The Entity Framework 5.0 offers another scenario to map objects to the database. With Code First there’s no mapping definition consisting of CSDL, SSDL, and MSL at all. A convention-based mapping can be used. Code First uses convention based programming similar to ASP.NET MVC. With convention-based programming, conventions are used before configuration. For example, instead of using attributes or a configuration file to define a primary key, a property just needs to be named with Id, or the name needs to end with Id, e.g. BooksId. Such a property automatically maps to a primary key.

This section discusses defining entity types for Code First, creating an object context, and customizing the created database mapping.

Defining Entity Types

For this example, two entity types are defined, Menu and MenuCard as shown in Figure 33-7. A Menu is associated with one MenuCard, and a MenuCard contains references to all Menus within the card.

The definition of the Menu class is shown in the following code snippet (code file CodeFirst/Menu.cs). There’s no specific mapping to database keys or any other database-specific definition. It’s just a convention. Because one property has the name Id, a primary key is created from this property. Naming the property MenuId would work as well. The MenuCard property is of type MenuCard. This is going to be a relationship.

  public class Menu
  {
    public int Id { get; set; }
    public string Text { get; set; }
    public decimal Price { get; set; }
    public DateTime? Day { get; set; }
    public MenuCard MenuCard { get; set; }
  }

The MenuCard class looks very similar and has a Menus property that enables access to Menu objects associated with the MenuCard (code file CodeFirst/MenuCard.cs):

  public class MenuCard
  {
    public int Id { get; set; }
    public string Text { get; set; }
    public virtual ICollection<Menu> Menus { get; set; }
  }

Creating the Data Context

Now a data context is needed. The MenuContext derives from the base class DbContext and defines properties for the tables in the same way shown earlier with POCO objects (code file CodeFirst/MenuContext.cs):

  public class MenuContext : DbContext
  {
    public MenuContext()
    {
    }
 
    public DbSet<Menu> Menus { get; set; }
    public DbSet<MenuCard> MenuCards { get; set; }
  }

Creating the Database and Storing Entities

Now, the data context can be used. The following example code (code file CodeFirst/Program.cs) adds objects: one MenuCard and two menu entries. Then the SaveChanges method of the DbContext is called to write the entries to the database:

      using (var data = new MenuContext())
      {
        MenuCard card = data.MenuCards.Create();
        card.Text = "Soups";
        data.MenuCards.Add(card);
        
        Menu m = data.Menus.Create();
        m.Text = "Baked Potato Soup";
        m.Price = 4.80M;
        m.Day = new DateTime(2012, 9, 20);
        m.MenuCard = card;
        data.Menus.Add(m);
 
        Menu m2 = data.Menus.Create();
        m2.Text = "Cheddar Broccoli Soup";
        m2.Price = 4.50M;
        m2.Day = new DateTime(2012, 9, 21);
        m2.MenuCard = card;
        data.Menus.Add(m2);
 
        try
        {
          data.SaveChanges();
        }
        catch (Exception ex)
        {
          Console.WriteLine(ex.Message);
        }
      }
    }

The Database

If the database doesn’t exist, it is created. By default, the database is created with the server name—for example, in SQL Express, it would be (local)sqlexpress. The database is assigned the name of the data context, including the namespace. With the sample application the database has the name CodeFirstDemo.MenuContext. The created tables, with their properties and relationships, are shown in Figure 33-8. For the “*-to-1” relationship between Menus and MenuCards, a foreign key MenuCard_Id is created in the Menus table. The Day column in the Menus table is defined to allow nulls because the entity type Menu defines this property to be nullable. The Text column is created with nvarchar(max) and allows null, as string is a reference type. The Price is a database float type and doesn’t allow null. Value types are required, just if nullable value types are used, they are optional.

Query Data

The following example (code file CodeFirst/Program.cs) demonstrates reading the data from the database. After the context is created, menu cards are accessed in the outer foreach loop, and the inner foreach loop queries menus. Of course, you can also access the data using LINQ queries, which are translated to T-SQL by the context:

      using (var data = new MenuContext())
      {
        foreach (var card in data.MenuCards)
        {
          Console.WriteLine("{0}", card.Text);
          foreach (var menu in card.Menus)
          {
            Console.WriteLine("	{0} {1:d}", menu.Text, menu.Day);
          }
        }
      }

The DbContext has lazy loading enabled by default. Thus, first the menu cards are queried; and then with every request for the menus of the menu card, the SELECT statement to retrieve the menus is created. Similar to what you’ve seen before, you can do eager loading as well:

        data.Configuration.LazyLoadingEnabled = false;
        foreach (var card in data.MenuCards.Include("Menus"))
        {
          Console.WriteLine("{0}", card.Text);
          foreach (var menu in card.Menus)
          {
            Console.WriteLine("	{0} {1:d}", menu.Text, menu.Day);
          }
        } 

Customizing Database Generation

You can perform some simple customizations to generate the database, such as defining the database connection string. In the following code (code file CodeFirst/MenuContext.cs), the database connection string is assigned in the MenuContext class to a constructor of the base class DbContext. To define a connection string, the database with the specified name is created:

  public class MenuContext : DbContext
  {
    private const string connectionString =  
        @"server=(local)sqlexpress;database=WroxMenus;" +  
        "trusted_connection=true";
    public MenuContext()
      : base(connectionString){ }

Data Annotations

To customize the generated tables, some attributes from the namespace System.ComponentModel.DataAnnotations can be used. The following example code (code file CodeFirst/Menu.cs) makes use of the StringLengthAttribute type. This way, the generated column type is nvarchar(50) instead of nvarchar(max):

  public class Menu
  {
    public int Id { get; set; }
    [StringLength(50)] public string Text { get; set; }
    public double Price { get; set; }
    public DateTime? Day { get; set; }
    public MenuCard MenuCard { get; set; }
  }

Other attributes that can be used to customize entities are Key, to define other columns that don’t have the Id in their name, Timestamp, to define the property as a row-version column, ConcurrencyCheck, to use the property for optimistic concurrency, and Association, to mark the property for a relationship.

Model Builder

Using only attributes to customize the generated tables and columns, you fast reach the limitations. There’s a much more flexible option: using the model builder. The model builder offers a fluid API to customize tables, columns, and relationships.

Using the Code First development model you can get access to the model builder features with the DbModelBuilder class. With a context class deriving from DbContext you can override the method OnModelCreating. The method is invoked on creation of the database model. With this method, a model builder of type DbModelBuilder is received. With the model builder you can rename properties, change property types, define constraints, build relations, and do a lot more customization.

In the following code snippet (code file CodeFirst/MenuContext.cs), for the Menus table the Price column is changed to be of type money, the Day column of type date. The Text column is changed to have a maximum string length of 40, and it is set to required. The model builder uses a fluent API. The method Entity returns a EntityTypeConfiguration. Using the result of the Entity method, other methods of the EntityTypeConfiguration can be used. Using the Price property as a result of the Lambda expression with the Property method, this method returns a DecimalPropertyConfiguration. Using this, you can invoke the HasColumnType method, which sets the type for the database column to money. The HasColumnType method again returns a DecimalPropertyConfiguration, and it would be possible to continue there and invoke other methods for configuring the column.

As shown earlier for the MenuCard property in the Menu class, by default a foreign key named MenuCard_Id was created. Now the Menu class is extended by adding the property MenuCardId of type int. To use this property as a foreign key, the HasForeignKey method is used to assign the foreign key to the MenuCardId property. The last statement in the method OnModelCreating sets a cascading delete on the MenuCards table. If a row in the MenuCards table is deleted, all the menus with this ID should be deleted:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      modelBuilder.Entity<Menu>().Property(m => m.Price).HasColumnType("money");
      modelBuilder.Entity<Menu>().Property(m => m.Day).HasColumnType("date");
      modelBuilder.Entity<Menu>().Property(m => m.Text).HasMaxLength(40)
        .IsRequired();
      modelBuilder.Entity<Menu>().HasRequired(m => m.MenuCard)
        .WithMany(c => c.Menus).HasForeignKey(m => m.MenuCardId);
      modelBuilder.Entity<MenuCard>().Property(c => c.Text).HasMaxLength(30)
        .IsRequired();
      modelBuilder.Entity<MenuCard>().HasMany(c => c.Menus).WithRequired()
        .WillCascadeOnDelete();
    }

SUMMARY

This chapter introduced you to the features of the ADO.NET Entity Framework, which is based on mapping that is defined by CSDL, MSL, and SSDL—XML information that describes the entities, the mapping, and the database schema. Using this mapping technique, you can create different relation types to map entity classes to database tables.

You’ve learned how the object context keeps knowledge about entities retrieved and updated, and how changes can be written to the store. You’ve also seen how using POCO objects enables you to use an existing object library to map the objects to the database, and how Code First enables a database to be created on the fly, with mapping information based on conventions.

LINQ to Entities is a facet of the ADO.NET Entity Framework that enables you to use the new query syntax to access entities.

The next chapter is on using XML as data source, creating and querying XML with LINQ to XML.