If you have a lot of experience working with relational databases, especially with databases that have been around for some time and have been through numerous modifications, you must have been puzzled by the actual meaning of some elements in a database. Questions like the following might ring a bell: What is this column used for? Why is this set of data duplicated between tables? Why is this set of columns in a table empty in certain rows?"

A good understanding of your customer's needs and business is crucial for the success of the application you will be developing. This understanding can be written down in the form of requirements and together with the description of the business (or problem domain) will be indispensable for the design of your application.

An important part of the design of many applications is the data structure that the system will use. One of the most popular methods for designing the data is the entity-relationship model (ERM). The ERM is a conceptual representation of data where the problem domain is described in the form of entities and their relationships. In Visual Studio, this model is called the Entity Data Model (EDM), and you will learn how to create the EDM in the next section. Figure 17.1 shows the sample Entity Data Model diagram inside Visual Studio 2010.

Entities generally can be identified by the primary key and have some important characteristics known as attributes. For example, a person entity might have a primary key in the form of their Social Security number (SSN) and attributes First and Last Name. (Although an SSN conceptually fits well in the role of a primary key and therefore I chose it for the primary key in the Person table in the example Books and Authors project later in this chapter, in practice its use is discouraged. See "Using a Social Security Number as a Primary Key" in the following sidebar for more information.)

Figure 17.1. Entity Data Model diagram in Visual Studio 2010's EDM Designer

Real World Scenario: Using a Social Security Number as a Primary Key

Although a Social Security number might seem like the natural first choice for the primary key in a table representing some kind of a "person" entity — a Customer, Client, Employee, or User table, for example — its use in practice is actually strongly discouraged. Because of privacy concerns, security threats like identity theft, and recent regulatory guidelines, SSNs should be kept in a database only in encrypted form — or not kept at all. You should also be aware that people can change their SSNs during their lifetime. As such, an SSN is not a particularly good choice for a primary key.

If there is no good natural key, you can always resort to a surrogate, database-generated key. An artificial key, however, does not resolve the issue of duplicate entries that a number like an SSN seems to resolve. Namely, there is nothing to prevent one person's data from being inserted into the database twice. Inserting duplicate entities can present a serious data consistency flaw, and as such it is best controlled on the database level.

If there is no natural key that can be used to control a duplicate entry, you can resort to placing a UNIQUE constraint on a combination of person attributes. For example, it is highly unlikely that you will find two persons with the same full name, date and place of birth, and telephone number. If you do not have all of these attributes at your disposal, you might need to relinquish the control of duplication to some other application layer or even to the user.

An entity can be in a relationship with another entity. During the analysis, you can often hear this relationship expressed in the form of a simple sentence, such as "A person owns a pet." In such a sentence, nouns are entities, and verbs represent a relationship. In this case, a person is related to a pet. An important characteristic of a relationship is cardinality, or the numeric aspect of the relation between entities. In our example, a person can own many pets, but a pet usually belongs to a single person, thus being a one-to-many relationship between the person and pet entities.

The most popular method used to work with the ERM is an entity-relationship diagram. Many tools have smart entity-relationship diagramming capabilities, including the ERwin Data Modeler, Microsoft Visio, Toad Data Modeler, and Visual Studio. I will describe the Visual Studio entity-relationship capabilities in the "Creating a New Entity Data Model" section. These tools are typically capable of transforming the conceptual to a physical model — generating Data Definition Language (DDL) scripts that can be used to generate a physical database structure.

When working with relational databases on an implementation level, you create tables and columns, constraints, and primary and foreign keys to hold your data, and you create indices to optimize data access and manipulation. Although these database concepts can be related to a problem domain so that table roughly corresponds to entity, column corresponds to attribute, and foreign key constraint corresponds to relationship, they are not as expressive as the entity-relationship model. In addition, the physical design of a relational database is governed by a different set of principles and needs. Databases are very good at preserving data integrity, performing transactions, providing fast access to a data, and reducing data redundancy. As a result, the relational database's physical design is often refined through a process of normalization and denormalization. This process is typically in the domain of database administrators, who use their knowledge of database engines to optimize database performance, often with little regard for the problem domain at hand.

It is during this process that the link between the problem domain (described in the form of an Entity Data Model) and the physical database structure is watered down. Later in the application life cycle, the Entity Data Model is often completely disregarded. As a result, database structure becomes a cryptic artifact, difficult to relate to a problem domain. This often has an adverse effect on application maintainability and evolution. When the link between the two is weakened, small changes to the application can require a huge amount of implementation work just to understand the inner workings of the database.

With the Entity Framework, Microsoft has tackled this problem by making the Entity Data Model an integral part of your application. The Entity Data Model is to generate native .NET classes used to access the data store. These classes are mapped to tables in the database. The Entity Framework uses the Entity Data Model as a basis for .NET code and database structure generation; this sets it apart from typical modeling tools. The model becomes integral part of the project, driving the database and .NET code design.

To represent an entity in a relational database, you use a table construct. The table itself represents an entity type, while each row represents one specific instance of an entity. Columns are used to represent entity attributes. When you define an attribute, you choose a data type for it. To refine attribute definition, you can apply a constraint on an attribute, or you can make the attribute a primary key, meaning that it will uniquely identify the entity. You can relate different entities by defining foreign keys between two tables.

Following this approach (I am sure you are already very familiar with it), you can easily represent customers and product categories in a simple CRM system. The system will store basic customer data and information on a customer's favorite product categories. You can define a Customers table to represent the customers in your database. Important customer attributes are the first and last names, and in order to save this information in the Customer table, you can define FirstName and LastName columns whose data type is varchar with maximum length of 50. You can use a Social Security number or a database-generated integer as a primary key. For product categories, you can define a ProductCategories table with an Id column for the primary key and a Name column as varchar with a maximum length of 20.

In simple scenarios like the one I just described, at first glance objects in relational database will represent your entities fairly well. However, there are many situations where this approach will fall short. Let's examine a few such situations in our simple CRM system.

Many-to-Many as a Simple Relation Between Entities

I am sure that it comes as no surprise that you will need an additional table, called a join table, to relate the customers and product categories. The CustomersProductCategories relation table will have only two columns: SSN and ProductCategoriesId. To complete the solution, two foreign keys are added. The first foreign key is established between the SSN column in Customer and the SSN column in CustomersProductCategories. The second one is between the Id column in ProductCategories and the ProductCategoriesId column in the CustomersProductCategories table. What I have just described is a typical approach used to represent a many-to-many relationship in a relational database. You can see the EDM entities and database tables representing many-to-many relation between customers and product categories in Figure 17.2.

Figure 17.2. Many-to-many relationship table structure (left) and Entity Data Model representation (right)

As you can see, the additional table in this case is necessary to represent a relation between two entities. Sometimes this relation can grow into a full-blown entity, such as when it needs to be described with some attributes. For example, imagine you need to store an amount spent on each category for each customer. Such a need would result in adding an AmountSpent column to the CustomersProductCategories table and would warrant treating CustomersProductCategories as an entity. However, in the scenario I just described, a relation between customers and product categories is just a relation and should be represented as such. In relational databases, you are left with no choice but to use the table to represent a many-to-many relation, even though tables are generally used to represent full-blown entities.

In the Entity Framework, as long as you do not need to store any relation attributes, the relation will be treated as such. You will see how the many-to-many relation is created in the "Creating a New Entity Data Model" section later in this chapter.

Standard ADO .NET classes are doing a good job of encapsulating access to different data stores. If you are careful enough and you follow the "Program to an interface, not an implementation" principle, you will significantly reduce the amount of the application code you need to modify in case you need to change the data store used by your application. The "Program to an abstraction" principle applied to ADO .NET means writing code using top-level interfaces from the System.Data namespace, like in the following example:

Dim connection As System.Data.IDbConnection = CreateConnection(connectionString)
Dim command As System.Data.IDbCommand = connection.CreateCommand()

As long you do not reference any class from any concrete ADO .NET provider namespace (like System.Data.SqlClient in the case of a Microsoft SQL Server provider), switching your application to another data store can be as simple as changing the connection string — that is, as long as you are able to write your SQL code in a dialect that all data stores are able to understand. If you write command text along the same lines as the previous example, like this:

command.CommandText = "Select top 10 * from Customers"

you might find that your database does not support the TOP keyword. Although there are different standards trying to regulate SQL, the truth is that there are many proprietary extensions to the language. Writing portable SQL is difficult and often impractical.

With the Entity Framework, you have a number of query options. You can use Entity SQL (eSQL), LINQ, or Query Builder methods. Whatever your choice, you are guaranteed that the query will return the same result no matter the data store under scrutiny. Thanks to the ADO.NET Entity Framework provider architecture, new data stores can be easily incorporated and made available to .NET programmers. What's more, there is no restriction on the underlying data store technology. Most will be relational databases, but as long as the appropriate provider is available, other technologies such as object-oriented databases, databases based on BigTable technology, Excel spreadsheets, and so on, will be available through the Entity Framework. Now you know why I insisted on using the term data store instead of database so far in this chapter.

During the application lifetime, the data and programmatic layers are generally exposed to different forces governing their evolution. The object-oriented layer accommodates evolution by preserving modularity and providing extensibility, while the data layer is influenced by forces such as referential integrity, normalization, and performance optimization.

As a database is exposed to more intensive use and the quantity of the stored data increases, the database structure often has to be re-accommodated to respond to an increase in demand. One such common scenario is table partitioning.

A table might be split so that rarely used columns containing less used but weighty pieces of information are placed in a separate table. This type of data partitioning strategy is known as vertical partitioning. By contrast, horizontal partitioning involves placing rows into different, identically structured tables. It is often used as a form of archiving; historic data that cannot be deleted but is rarely used is placed in a separate table.

The Entity Framework supports a number of mapping scenarios. It is capable of mapping a single entity to multiple tables and can use any or all of the following forms:

With all these mapping options at your disposal, many of the typical database modifications, especially those that are the result of performance tuning, can be accommodated at the mapping layer. This way, even though the database structure changes, no changes need be applied to your .NET code. The Entity Framework's mapping capability can isolate your code from structural changes in the database layer.

The EDM Designer is displayed by default when you add a new ADO .NET EDM to your project or click an EDM file (.edmx extension) in Visual Studio. Figure 17.3 shows the EDM Designer with the Northwind EDM open.

Figure 17.3. The EDM Designer in Visual Studio 2010

The EDM diagram is displayed in the central window. You can access many options of the EDM Designer through a context menu that appears if you right-click anywhere on the empty surface of an EDM diagram.

You can add new items to the EDM diagram by dragging and dropping tools from the Toolbox window. The Toolbox window is the window shown on the left side in Figure 17.3. Once you select an item in the EDM diagram, you can change its properties in the Properties window, pictured on the right side in Figure 17.3 and positioned below the Model Browser window.

You can see elements of the EDM grouped by type in the Model Browser window. If an EDM is complex, then right-clicking a relationship or an entity in the Model Browser and selecting Show In Designer from the context menu can be a much more practical option for finding your way around the model.

Finally, at the bottom of the Figure 17.3 you can see a Mapping Details window. In this window, you can define how entities and relations from your conceptual model are mapped to tables in the logical model. Let's start by creating a new project with a fresh EDM.

You can add an EDM to a majority of project types supported by Visual Studio 2010. For this exercise, you will start by creating a new Windows Forms project:

You have just created a new EDM. After you created a new EDM, Visual Studio displays the EDM Designer with your BooksAndAuthors.edmx file open in the active window.

You can create and model your entities in the EDM Designer on a conceptual level without ever using it to connect to a real database. This way, however, your model will be no more than a dead diagram. To breathe some life into your EDM, you need to connect it to a database. Start by creating a new BooksAndAuthors database in SQL Server 2005 or newer. Use the instructions that follow:

Check the Solution Explorer. You should see that a new BooksAndAuthors.edmx.sql file has been added to the MyEFProject. This SQL file contains a Data Definition Language (DDL) script that can be used to create a database structure that can accommodate the BooksAndAuthors EDM.

Note that the EDM Designer only creates the DDL file; it does not execute it against the database. Don't execute it just yet. Let's add some entities to our model first.

You can now add your first entity to BooksAndAuthors EDM. In this exercise, you will create an entity model for a publishing company. It will contain information on book titles and authors. Start by creating a new Book entity:

Notice that an important characteristic of an entity is that it can be uniquely identified. An entity is generally identified by an attribute or a combination of attributes known as a primary key. The EDM Designer uses the term property for attributes.

In the case of the Book entity, the EDM Designer automatically created an Id property and marked it as a primary key. If you select the Id property in the EDM Designer, the Properties window will display characteristics of the Id property of the Book entity. The important characteristics of the Id property are Type and Entity Key. The Type = Int32 entry in the Properties windows indicates that the data type of the Id property is Integer. The Entity Key = True entry tells you that Id is a primary key.

Although you could use an artificial primary key, in the case of a Book entity, there is another property that is a better candidate for the primary key. All book titles can be uniquely identified by their ISBN numbers.

To use the ISBN for a primary key of Book entity, follow these steps.

The most important property of the Book entity is the title. The title is a simple string, so it can be well represented as a scalar property of the Book entity. Let's add a Title scalar property to the Book entity.

While you are at it, use the same process to add another scalar property called PublishingDate to the Book entity. Select DateTime as the property type.

Yet another important property for a book is the page count. It is a good idea to preserve this information, so add another scalar property named PageCount to the Book entity, and select Int32 as the Type.

Most of the time, you will be interacting with the EDM through the EDM Designer. Nevertheless, you should have a basic understanding of the artifacts that comprise the EDM and its structure. The EDM native format is XML, and it can also be viewed and edited manually, as can any XML file. To see the Visual Studio–generated EDM XML, first refresh the model and then open the EDM file in the Visual Studio XML Editor:

Listing 17.1 shows the content of the BooksAndAuthors.edmx file. Although the content might look bewildering at first, it is actually not that complex; it is even easier to understand if you ignore the XML namespace declaration. You can see that the content is divided into four main sections:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0"
 xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
    <Schema Namespace="BooksAndAuthors.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="BooksAndAuthorsStoreContainer">
    <EntitySet Name="Books" EntityType="BooksAndAuthors.Store.Books"
     store:Type="Tables" Schema="dbo" />
  </EntityContainer>
  <EntityType Name="Books">
    <Key>
      <PropertyRef Name="ISBN" />
    </Key>
    <Property Name="ISBN" Type="varchar" Nullable="false" MaxLength="13" />
    <Property Name="Title" Type="nvarchar" Nullable="false" MaxLength="4000" />
    <Property Name="PublishingDate" Type="datetime" Nullable="false" />
    <Property Name="PageCount" Type="int" Nullable="false" />
  </EntityType>
</Schema></edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema xmlns="http://schemas.microsoft.com/ado/2008/09/edm"
      xmlns:store="http://schemas.microsoft.com
      /ado/2007/12/edm/EntityStoreSchemaGenerator"
      Namespace="BooksAndAuthors" Alias="Self">
        <EntityContainer Name="BooksAndAuthorsContainer" >
          <EntitySet Name="Books"
                    EntityType="BooksAndAuthors.Book" />
        </EntityContainer>
        <EntityType Name="Book">
          <Key>
            <PropertyRef Name="ISBN" /></Key>
          <Property Type="String" Name="ISBN" Nullable="false"
                  MaxLength="13" Unicode="false" FixedLength="false" />
          <Property Type="String" Name="Title"
                    Nullable="false" MaxLength="4000" />
          <Property Type="DateTime" Name="PublishingDate"
                    Nullable="false" Precision="29" />
          <Property Type="Int32" Name="PageCount"
                    Nullable="false" /></EntityType></Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
    <Mapping Space="C-S" xmlns="http://schemas.microsoft.com
                               /ado/2008/09/mapping/cs">
  <EntityContainerMapping StorageEntityContainer="BooksAndAuthorsStoreContainer"
   CdmEntityContainer="BooksAndAuthorsContainer">
    <EntitySetMapping Name="Books">
      <EntityTypeMapping TypeName="IsTypeOf(BooksAndAuthors.Book)">
        <MappingFragment StoreEntitySet="Books">
          <ScalarProperty Name="ISBN" ColumnName="ISBN" />
          <ScalarProperty Name="Title" ColumnName="Title" />
          <ScalarProperty Name="PublishingDate" ColumnName="PublishingDate" />

<ScalarProperty Name="PageCount" ColumnName="PageCount" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
  </EntityContainerMapping>
</Mapping></edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2008/10/edmx">
    <edmx:Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing"
         Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </edmx:Connection>
    <edmx:Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
      </DesignerInfoPropertySet>
    </edmx:Options>
    <!-- Diagram content (shape and connector positions) -->
    <edmx:Diagrams>
      <Diagram Name="BooksAndAuthors" >
        <EntityTypeShape EntityType="BooksAndAuthors.Book"
                         Width="1.5" PointX="3.375" PointY="2"
                         Height="1.592306315104167" />
      </Diagram>
    </edmx:Diagrams>
  </edmx:Designer>
</edmx:Edmx>

So far, the file contains information on a single entity. As we continue working on the model, it will grow and become more complex. Fortunately, you can edit most of the model details through the EDM Designer and rarely need to edit the file manually.

Dim context As New BooksAndAuthorsContainer
Dim books = context.Books
Dim myBook As Book = From book In books
 Where (book.ISBN = "455454857")
           Select book

Public Partial Class BooksAndAuthorsContainer
    Inherits ObjectContext
    '...
    Public ReadOnly Property Books() As ObjectSet(Of Book)
        Get
            If (_Books Is Nothing) Then
                _Books = MyBase.CreateObjectSet(Of Book)("Books")
            End If
            Return _Books
        End Get
    End Property

    Private _Books As ObjectSet(Of Book)
    '...
End Class

<EdmEntityTypeAttribute(NamespaceName:="BooksAndAuthors", Name:="Book")>
<Serializable()>
<DataContractAttribute(IsReference:=True)>
Public Partial Class Book
    Inherits EntityObject
    #Region "Factory Method"

    '" <summary>
    '" Create a new Book object.
    '" </summary>
    Public Shared Function CreateBook(iSBN As Global.System.String,
                                      title As Global.System.String,
                                      publishingDate As Global.System.DateTime,
                                      pageCount As Global.System.Int32) As Book
        Dim book as Book = New Book
        book.ISBN = iSBN

        book.Title = title

        book.PublishingDate = publishingDate

book.PageCount = pageCount

        Return book
    End Function

    <EdmScalarPropertyAttribute(EntityKeyProperty:=true, IsNullable:=false)>
    <DataMemberAttribute()>
    Public Property ISBN() As Global.System.String
        Get
            Return _ISBN
        End Get
        Set
            If (ISBN <> value) Then
                OnISBNChanging(value)
                ReportPropertyChanging("ISBN")
                _ISBN = StructuralObject.SetValidValue(value, False)
                ReportPropertyChanged("ISBN")
                OnISBNChanged()
            End If
        End Set
    End Property

    Private _ISBN as Global.System.String
    Private Partial Sub OnISBNChanging(value As Global.System.String)
    End Sub

    Private Partial Sub OnISBNChanged()
    End Sub

    <EdmScalarPropertyAttribute(EntityKeyProperty:=false, IsNullable:=false)>
    <DataMemberAttribute()>
    Public Property Title() As Global.System.String
        Get
            Return _Title
        End Get
        Set
            OnTitleChanging(value)
            ReportPropertyChanging("Title")
            _Title = StructuralObject.SetValidValue(value, False)
            ReportPropertyChanged("Title")
            OnTitleChanged()
        End Set
    End Property

    Private _Title as Global.System.String
    Private Partial Sub OnTitleChanging(value As Global.System.String)
    End Sub

Private Partial Sub OnTitleChanged()
    End Sub

    <EdmScalarPropertyAttribute(EntityKeyProperty:=false, IsNullable:=false)>
    <DataMemberAttribute()>
    Public Property PublishingDate() As Global.System.DateTime
        Get
            Return _PublishingDate
        End Get
        Set
            OnPublishingDateChanging(value)
            ReportPropertyChanging("PublishingDate")
            _PublishingDate = StructuralObject.SetValidValue(value)
            ReportPropertyChanged("PublishingDate")
            OnPublishingDateChanged()
        End Set
    End Property

    Private _PublishingDate as Global.System.DateTime
    Private Partial Sub OnPublishingDateChanging(value As Global.System.DateTime)
    End Sub

    Private Partial Sub OnPublishingDateChanged()
    End Sub

    <EdmScalarPropertyAttribute(EntityKeyProperty:=false, IsNullable:=false)>
    <DataMemberAttribute()>
    Public Property PageCount() As Global.System.Int32
        Get
            Return _PageCount
        End Get
        Set
            OnPageCountChanging(value)
            ReportPropertyChanging("PageCount")
            _PageCount = StructuralObject.SetValidValue(value)
            ReportPropertyChanged("PageCount")
            OnPageCountChanged()
        End Set
    End Property

    Private _PageCount as Global.System.Int32
    Private Partial Sub OnPageCountChanging(value As Global.System.Int32)
    End Sub

    Private Partial Sub OnPageCountChanged()
    End Sub

End Class

Now that you are finished creating the Book entity, as you might expect, the Author entity is the next to be created. It will be important to collect and store author data, such as name, contact information, and so forth, in this entity. Before you create the Author entity, however, a bit of analysis is in order.

As it happens, the publishing company in question also works with foreign language titles. In the case of a foreign language title, it is important to collect and store information about the translator: name, contact information, languages translated, and the like. Surely, this warrants another entity in our system — a Translator entity.

If you compare the Author and the Translator entities, you will see that they have a lot of common properties. If we were to add the Translator and Author entities to the EDM now, we would have to duplicate these properties on each. There must be some more efficient way to deal with replicated properties.

The solution is the same as it would be in a situation when you design classes where there "is a kind of" relationship between the entities in Visual Basic. A common parent entity can be extracted; that parent entity will contain the common properties. Let's call this entity Person. Once the Person entity exists, you can add Author and Translator entities and make them inherit the Person entity. Start by adding a new Person entity to the EDM:

You have just created an inheritance hierarchy with one base entity, Person, and two child entities, Translator and Author. Let's add a few more details to our model.

Since you will never instantiate a Person entity in your code because you will always work with a more specific type, a Translator or an Author, you can mark the Person entity as abstract. In the Properties window, select the Person entity, and set the Abstract property to True.

There are a few more bits of information you need to store for each child type. For authors, the publishing company would like to store the date that the author first signed with the company. To accommodate this, add a new property called Signed (Type: DateTime) to the Author entity. For translators, you will also want to keep the information on languages that they translate from. Since this requires defining another related table, you can create this table after you see how many-to-many associations are defined in the "Adding a Many-to-Many Association" section later in this chapter.

The inheritance feature you have just used is one of the Entity Framework's most powerful capabilities. It solves the problem of mapping the inheritance relationship in an object-oriented sense to tables in a relational database and lets you leverage the inheritance and polymorphism capacities of .NET code without any friction with the data store.

-- Creating table 'Persons'
CREATE TABLE [dbo].[Persons] (
    [SSN] char(9)  NOT NULL,
    [FirstName] nvarchar(50)  NOT NULL,
    [LastName] nvarchar(50)  NOT NULL
);
GO
-- Creating table 'Persons_Translator'
CREATE TABLE [dbo].[Persons_Translator] (
    [SSN] char(9)  NOT NULL
);
GO
-- Creating table 'Persons_Author'
CREATE TABLE [dbo].[Persons_Author] (
    [Signed] datetime  NOT NULL,
    [SSN] char(9)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all Primary Key Constraints
-- --------------------------------------------------

-- Creating primary key on [SSN] in table 'Persons'
ALTER TABLE [dbo].[Persons] WITH NOCHECK
ADD CONSTRAINT [PK_Persons]
    PRIMARY KEY CLUSTERED ([SSN] ASC)
    ON [PRIMARY]
GO
-- Creating primary key on [SSN] in table 'Persons_Translator'
ALTER TABLE [dbo].[Persons_Translator] WITH NOCHECK
ADD CONSTRAINT [PK_Persons_Translator]
    PRIMARY KEY CLUSTERED ([SSN] ASC)
    ON [PRIMARY]
GO
-- Creating primary key on [SSN] in table 'Persons_Author'
ALTER TABLE [dbo].[Persons_Author] WITH NOCHECK
ADD CONSTRAINT [PK_Persons_Author]
    PRIMARY KEY CLUSTERED ([SSN] ASC)
    ON [PRIMARY]
GO

-- --------------------------------------------------
-- Creating all Foreign Key Constraints
-- --------------------------------------------------

-- Creating foreign key on [SSN] in table 'Persons_Translator'
ALTER TABLE [dbo].[Persons_Translator] WITH NOCHECK
ADD CONSTRAINT [FK_Translator_inherits_Person]
    FOREIGN KEY ([SSN])
    REFERENCES [dbo].[Persons]
        ([SSN])
    ON DELETE NO ACTION ON UPDATE NO ACTION
GO
-- Creating foreign key on [SSN] in table 'Persons_Author'
ALTER TABLE [dbo].[Persons_Author] WITH NOCHECK
ADD CONSTRAINT [FK_Author_inherits_Person]
    FOREIGN KEY ([SSN])
    REFERENCES [dbo].[Persons]
        ([SSN])
    ON DELETE NO ACTION ON UPDATE NO ACTION
GO

One important piece of contact information for authors and translators is a telephone number. The publishing house needs to maintain a record of one telephone number for each author and translator. This requirement can be easily solved by adding another property to the Person entity. By adding a Telephone property to the Person entity, both the Author and Translator entities will inherit it.

Instead of simply adding a string property to a Person entity, you can provide additional integrity and meaning to data if you structure the telephone number information. Typically, a telephone number consists of a country code, an area code, and a local number. Sometimes, you also need additional information, such as an extension number or daytime/evening qualifier.

In the EDM Designer, you can use a complex property to structure the information you do not want to model as a separate entity. You can add a scalar property to the Person entity by following these steps:

Select the Person entity in the EDM Designer, and take a look at the Mapping Details window. You will see that properties of the Phone property are mapped to the same Persons table as rest of the Person scalar properties. The only difference is the column name. The Phone_ prefix is used to mark them as belonging to the Phone complex type. This will suffice to keep telephone numbers for authors and translators. Note that in the case where more than one telephone number for each Author/Translator needs to be kept in a database, you can model Telephone as a separate entity in a one-to-many relationship with Person, as described in the next section.

Another important part of contact information is an address. The publisher often needs to keep multiple addresses for authors and translators. The next section explains just how to do that.

Start by adding a new entity called Address to the Books and Authors EDM. As with telephone numbers, you should bear in mind the international character of address information. Keep the Id primary key added by default, and set the StoreGeneratedPatter property to Identity. Now add the following scalar properties to the Address entity:

Now perform the following steps:

You will see the line connecting the Person and Address entities with 1, which is used to signify one side in a one-to-many association on the side of Person, and an asterisk, which is used to signify the many side in a one-to-many association on the side of Address.

Now, select the line in the EDM diagram, and take a look at Properties window. You will see that the name of the association is PersonAddress. Since the association is one-to-many, rename it to PersonAddresses so that the cardinality of association can be easily understood from its name. You have just created a one-to-many association in your model.

Entities in a one-to-many association expose a stronger type of relationship. They often have a dependant life cycle. For example, if you eliminate a person from the database, it makes no sense to keep the addresses that belong to that person in the database. The Entity Framework can take care of this issue for you. If you set the End1 OnDelete property to Cascade, the Entity Framework will delete all addresses belonging to the deleted person automatically. Make sure that the End1 OnDelete property of the PersonAddresses association is set to Cascade.

At this point, you have created the main entities in your model. You have modeled the Author, Translator, and Book entities. But, a crucial piece of the puzzle is still missing. You must have a means to connect authors and translators with their books. Typically, you expect to have a single author per book. However, it is not uncommon (especially for nonfiction titles) to have several authors per book. And it is to be expected that an author will deliver more than one title in his lifetime. There is a similar relationship between translators and titles.

You can say that the nature of the relation between books and authors and between books and translators is many-to-many. Since the relation with books is common to both authors and translators, the best way to represent it in our model without incurring repetition is to associate the Person entity with the Book entity using a many-to-many association.

The diagram in the EDM Designer should show the diamond and an asterisk on both sides of the line connecting the Person and Book entities. Also, both the Person entity and the Book entity should display some newly created navigation properties: Books for the Person entity and Persons for the Book entity. Navigation properties are a special kind of properties in a sense that they hold a reference to another entity or set of entities.

If you select the line connecting the Person and Book entities, you will see in the Mapping Details window that it maps to a separate table called PersonBook.

Now that you have seen how to establish a many-to-many association between entities, you can add a new entity called Language, with Id and Name scalar properties. Create a many-to-many association between the Language and Translator entities. This way, you will be able to store information on the languages that an individual translator works with, making translator information much more useful to the publisher.

So far, you only used the EDM Designer to generate the DDL code. You haven't actually executed the DDL against the database. By following these steps, you can easily use Visual Studio 2010 to execute the DDL script.

At this point, it would be interesting to compare the structure of the EDM with the structure of the database generated for the model. You can take a look at the database structure in Figure 17.4.

Figure 17.4. Books and Authors database structure

Figure 17.5 shows the EDM.

Figure 17.5. Books and Authors EDM

Although there is a correspondence between the diagrams, there are some significant differences. The EDM diagram shows the many-to-many association as a line between the two tables, while on the database diagram the many-to-many relation materializes in a joined table. Although the EDM diagram shows a single complex property, Phone, in a database, these are materialized as a group of columns clustered around the same Phone_ prefix.

In conclusion, the EDM is capable of providing a cleaner conceptual view of the data, can hide many low-level implementation details, and provides a much richer set of features for working with data.

With this, you have successfully generated your first Entity Data Model and used most of the available constructs while doing so. The model itself, though, is not much use if you are not able to interact with it and use it to store the data. In the next section, you will see how you can use the Entity Framework to obtain data from the data store and how you can modify that same data with the help of the Entity Framework.

In Chapter 14, "An Introduction to LINQ," you familiarized yourself with LINQ. Thanks to the Object Services layer and the .NET code generated by the Entity Framework (shown in Listing 17.3), you can query the data in the EDM as objects and unleash all the power of LINQ syntax in your Visual Basic code.

It is time you put to use the form added automatically to MyEFProject that contains the BooksAndAuthors EDM. Start by renaming the form to QueryEDM.vb, and add a button named ListAllBooks.

Private Sub ListAllBooks_Click(ByVal sender As System.Object,
            ByVal e As System.EventArgs) Handles ListAllBooks.Click
    Dim context As New BooksAndAuthorsContainer
    For Each book In context.Books
        Console.WriteLine("Title: " & book.Title)
    Next
End Sub

Private Sub LongestBook_Click(ByVal sender As System.Object,
        ByVal e As System.EventArgs) Handles LongestBook.Click

Dim context As New BooksAndAuthorsContainer
    Dim books = context.Books
    Dim longestBook As Book = (From book In books
                    Order By book.PageCount).First
    Console.WriteLine("The longest book is: " +
    longestBook.ISBN + " " + longestBook.Title)
End Sub

SELECT TOP (1)
[Extent1].[ISBN] AS [ISBN],
[Extent1].[Title] AS [Title],
[Extent1].[PublishingDate] AS [PublishingDate],
[Extent1].[PageCount] AS [PageCount]
FROM [dbo].[Books] AS [Extent1]
ORDER BY [Extent1].[PageCount] ASC

Private Sub FetchTranslators_Click(ByVal sender As System.Object,
        ByVal e As System.EventArgs) Handles FetchTranslators.Click
    Dim context As New BooksAndAuthorsContainer
    Dim translators = context.Persons.OfType(Of Translator)()

For Each Translator In translators
        Console.WriteLine("Translator: " & Translator.FirstName &
                          " " & Translator.LastName)
    Next
End Sub

SELECT
'1X0X' AS [C1],
[Extent1].[SSN] AS [SSN],
[Extent2].[FirstName] AS [FirstName],
[Extent2].[LastName] AS [LastName],
1 AS [C2],
[Extent2].[Phone_CountryCode] AS [Phone_CountryCode],
[Extent2].[Phone_AreaCode] AS [Phone_AreaCode],
[Extent2].[Phone_Number] AS [Phone_Number],
[Extent2].[Phone_Extension] AS [Phone_Extension]
FROM  [dbo].[Persons_Translator] AS [Extent1]
INNER JOIN [dbo].[Persons] AS [Extent2] ON [Extent1].[SSN] = [Extent2].[SSN]

If you have spent some time writing the SQL code, you will immediately feel familiar with eSQL. You can think of it as a data store–agnostic SQL used to query the EDM. Take a look at the code in Listing 17.10. It shows a simple query issued to the Books and Authors EDM using the Entity Client classes. The code has the familiar, traditional, ADO.NET "open connection, execute command" feel to it.

Private Sub FindBookByISBN_Click(ByVal sender As System.Object,
        ByVal e As System.EventArgs) Handles FindBookByISBN.Click
    Using connection As New EntityConnection("Name=BooksAndAuthorsContainer")
        connection.Open()
        Dim command = connection.CreateCommand()

command.CommandText = "SELECT VALUE book FROM" &
                               "BooksAndAuthorsContainer.Books " &
                               "As book Where book.ISBN = @isbn"
        command.Parameters.AddWithValue("isbn", "9780470187425")
        Dim reader = command.ExecuteReader(CommandBehavior.SequentialAccess)
        reader.Read()
        Console.WriteLine("Book title is: " & reader("Title"))
    End Using
End Sub

If you take a look at the SQL Profiler trace, you will see how eSQL was transformed to the parameterized SQL query. Listing 17.11 shows the trace.

exec sp_executesql N'SELECT
[Extent1].[ISBN] AS [ISBN],
[Extent1].[Title] AS [Title],
[Extent1].[PublishingDate] AS [PublishingDate],
[Extent1].[PageCount] AS [PageCount]
FROM [dbo].[Books] AS [Extent1]
WHERE [Extent1].[ISBN] = @isbn',N'@isbn nvarchar(4000)',@isbn=N'9780470187425'

eSQL is a programming language in its own right, and, as such, getting into more details about eSQL programming is outside the scope of this book. For more information on eSQL, try the Microsoft Developer Network at http://msdn.microsoft.com/en-us/library/bb399560.aspx.

Real World Scenario: Advanced Search Forms with eSQL Dynamic Queries

Advanced search forms typically present a user with a number of search criteria. The user will generally employ only a few criteria options with each query, but the exact combination of criteria options cannot be known beforehand. Nor does it make sense programming all the possible criteria combinations, since there are too many.

A good alternative to implementing queries based on numerous criteria options is to use dynamic queries. With each new search, a new query is constructed "on the fly." The "where" portion of the query is generated by concatenating comparison statements expressing the condition on the form. Only those chosen by the user are included in the generated eSQL code.

The book search form, shown here, can be used to search for titles in the Books and Authors Entity Data Model.

It offers four different criteria for book search: book ISBN, book title, and a date range for publishing date. At each click of the Find button, each criteria is inspected and included in the query only if the user has entered the search criteria. To make the search more flexible, the ISBN and title are included in the query using wildcards and the LIKE condition. This way, if the user enters Mastering in the Title text box, all books containing the word Mastering in the title are selected by the query. Here's the complete code to make this form work; thanks to dynamic query construction, the whole sample can fit the single screen:

Imports System.Data.Objects
Imports System.Data.EntityClient

Public Class eSQLDynamicQuery

    Dim command As EntityCommand
    Dim eSqlSelect = "SELECT book.ISBN, book.Title, book.PublishingDate " &
                    "from BooksAndAuthorsContainer.Books as book"
    Dim eSqlWhere As String

    Private Sub btnFind_Click(ByVal sender As System.Object,
                              ByVal e As System.EventArgs) Handles btnFind.Click
        gridResult.Rows.Clear()
        Using connection As New EntityConnection("Name=BooksAndAuthorsContainer")

connection.Open()
            command = connection.CreateCommand()
            AddISBNCondition()
            AddTitleCondition()
            AddPublishedBeforeCondition()
            AddPublishedAfterCondition()
            command.CommandText = eSqlSelect & eSqlWhere
            Dim reader = command.ExecuteReader(CommandBehavior.SequentialAccess)
            While (reader.Read())
                gridResult.Rows.Add(New String() {reader("ISBN"), reader("Title"),
                    reader("PublishingDate")})
            End While
        End Using
    End Sub

    Private Sub AddISBNCondition()
        If String.IsNullOrEmpty(txtISBN.Text) Then Exit Sub
        ConcatenateAndOrWhere()
        eSqlWhere = eSqlWhere & "book.ISBN LIKE @isbn"
        command.Parameters.AddWithValue("isbn", "%" + txtISBN.Text + "%")
    End Sub

    Private Sub AddTitleCondition()
        If String.IsNullOrEmpty(txtTitle.Text) Then Exit Sub
        ConcatenateAndOrWhere()
        eSqlWhere = eSqlWhere & "book.Title LIKE @title"
        command.Parameters.AddWithValue("title", "%" + txtTitle.Text + "%")
    End Sub

    Private Sub AddPublishedAfterCondition()
        If txtAfter.Text.Equals("  /  /") Then Exit Sub
        ConcatenateAndOrWhere()
        eSqlWhere = eSqlWhere & "book.PublishingDate > @after"
        command.Parameters.AddWithValue("after", CDate(txtAfter.Text))
    End Sub

    Private Sub AddPublishedBeforeCondition()
        If txtBefore.Text.Equals("  /  /") Then Exit Sub
        ConcatenateAndOrWhere()
        eSqlWhere = eSqlWhere & "book.PublishingDate < @before"
        command.Parameters.AddWithValue("before", CDate(txtBefore.Text))
    End Sub

    Private Sub ConcatenateAndOrWhere()
        If Not (String.IsNullOrEmpty(eSqlWhere)) Then
            eSqlWhere = eSqlWhere & " AND "
        Else

eSqlSelect = eSqlSelect & " WHERE "
        End If

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object,
                              ByVal e As System.EventArgs) Handles Button2.Click
        gridResult.Rows.Clear()
    End Sub
End Class

Yet another alternative you have at your disposal for querying the EDM is the ObjectQuery class. You can build your queries in a standard object-oriented manner using the ObjectQuery builder methods. The ObjectQuery API permits writing chained methods following the Builder pattern. It is best to illustrate this with an example. Listing 17.12 demonstrates query construction employing the Object Query API.

Private Sub QueryBuilder_Click(ByVal sender As System.Object,
            ByVal e As System.EventArgs) Handles QueryBuilder.Click
    Dim context As New BooksAndAuthorsContainer
    Dim query As ObjectQuery(Of Book)
    query = context.Books.
        Where("it.PublishingDate > DATETIME'1999-01-01 00:00'").
        OrderBy("it.PublishingDate").
        Top(10)
    Console.WriteLine(query.ToTraceString())
    For Each book As Book In query.ToList()
        Console.WriteLine(book.Title)
    Next
End Sub

ObjectQuery has a ToTraceString method that permits visualizing the SQL that is being issued to the database directly inside the Visual Studio. Using ToTraceString is a more simple SQL debug method than monitoring the trace in SQL Server Profiler. Listing 17.13 shows the SQL trace.

SELECT TOP (10)
[Extent1].[ISBN] AS [ISBN],
[Extent1].[Title] AS [Title],

[Extent1].[PublishingDate] AS [PublishingDate],
[Extent1].[PageCount] AS [PageCount]
FROM [dbo].[Books] AS [Extent1]
WHERE [Extent1].[PublishingDate] > convert(datetime2,
'1999-01-01 00:00:00.0000000', 121)
ORDER BY [Extent1].[PublishingDate] ASC

So far, I haven't demonstrated how you can use navigation properties to obtain a reference of a related entity. Now that you cannot use navigation properties (not using navigation properties would pretty much defeat the purpose of having the EDM), you just need to be explicit about the relations you will be using on entities obtained from your queries. There are several ways to tell the Entity Framework to fetch related entities as well as the resulting entity. For example, you can use the Include method of the ObjectQuery class, you can explicitly load related entities, or you can activate the deferred loading feature of the Entity Framework.

A very common scenario with the Books and Authors EDM would be to list all books authored by a certain author. Had I not spoiled the surprise in the previous paragraph, you might have expected the code in Listing 17.14 to list all books authored by the randomly selected first author in the database.

Private Sub DefferedLoading_Click(ByVal sender As System.Object,
                                  ByVal e As System.EventArgs) Handles
                                  DefferedLoading.Click
    Dim context As New BooksAndAuthorsContainer
    Dim author = context.Persons.OfType(Of Author).First()
    For Each book As Book In author.Books
        Console.WriteLine(book.Title)
    Next
End Sub

If you take a look at the Output window after executing the code in Listing 17.14, you will notice it lists no books, even though all authors have at least one book that they have authored in the database.

Private Sub DefferedLoading_Click(ByVal sender As System.Object,
                                  ByVal e As System.EventArgs) Handles
                                  DefferedLoading.Click

Dim context As New BooksAndAuthorsContainer
    Dim author = context.Persons.OfType(Of Author).Include("Books").First()
    For Each book As Book In author.Books
        Console.WriteLine(book.Title)
    Next
End Sub

SELECT
[Limit1].[Signed] AS [Signed],
[Limit1].[SSN] AS [SSN],
[Limit1].[SSN1] AS [SSN1],
[Limit1].[FirstName] AS [FirstName],
[Limit1].[LastName] AS [LastName],
[Limit1].[Phone_CountryCode] AS [Phone_CountryCode],
[Limit1].[Phone_AreaCode] AS [Phone_AreaCode],
[Limit1].[Phone_Number] AS [Phone_Number],
[Limit1].[Phone_Extension] AS [Phone_Extension],
[Limit1].[C1] AS [C1],
[Limit1].[C2] AS [C2],
[Limit1].[C3] AS [C3],
[Project2].[C1] AS [C4],
[Project2].[ISBN] AS [ISBN],
[Project2].[Title] AS [Title],
[Project2].[PublishingDate] AS [PublishingDate],
[Project2].[PageCount] AS [PageCount]
FROM   (SELECT TOP (1)
    [Extent1].[Signed] AS [Signed],
    [Extent1].[SSN] AS [SSN],
    [Extent2].[SSN] AS [SSN1],
    [Extent2].[FirstName] AS [FirstName],
    [Extent2].[LastName] AS [LastName],
    [Extent2].[Phone_CountryCode] AS [Phone_CountryCode],
    [Extent2].[Phone_AreaCode] AS [Phone_AreaCode],
    [Extent2].[Phone_Number] AS [Phone_Number],
    [Extent2].[Phone_Extension] AS [Phone_Extension],
    '1X0X' AS [C1],
    1 AS [C2],
    1 AS [C3]
    FROM  [dbo].[Persons_Author] AS [Extent1]
    INNER JOIN [dbo].[Persons] AS [Extent2] ON
        [Extent1].[SSN] = [Extent2].[SSN] ) AS [Limit1]
LEFT OUTER JOIN  (SELECT

[Extent3].[Persons_SSN] AS [Persons_SSN],
    [Extent4].[ISBN] AS [ISBN],
    [Extent4].[Title] AS [Title],
    [Extent4].[PublishingDate] AS [PublishingDate],
    [Extent4].[PageCount] AS [PageCount],
    1 AS [C1]
    FROM  [dbo].[PersonBook] AS [Extent3]
    INNER JOIN [dbo].[Books] AS [Extent4] ON
        [Extent4].[ISBN] = [Extent3].[Books_ISBN] )
        AS [Project2] ON [Limit1].[SSN] = [Project2].[Persons_SSN]
ORDER BY [Limit1].[SSN] ASC, [Limit1].[SSN1] ASC, [Project2].[C1] ASC

Private Sub DefferedLoading_Click(ByVal sender As System.Object,
                                  ByVal e As System.EventArgs) Handles
                                  DefferedLoading.Click
    Dim context As New BooksAndAuthorsContainer
    Dim author = context.Persons.OfType(Of Author).First()
    author.Books.Load()
    For Each book As Book In author.Books
        Console.WriteLine(book.Title)
    Next
End Sub

-- first trace
SELECT
SELECT
[Limit1].[C1] AS [C1],
[Limit1].[SSN] AS [SSN],

[Limit1].[FirstName] AS [FirstName],
[Limit1].[LastName] AS [LastName],
[Limit1].[C2] AS [C2],
[Limit1].[Phone_CountryCode] AS [Phone_CountryCode],
[Limit1].[Phone_AreaCode] AS [Phone_AreaCode],
[Limit1].[Phone_Number] AS [Phone_Number],
[Limit1].[Phone_Extension] AS [Phone_Extension],
[Limit1].[Signed] AS [Signed]
FROM (SELECT TOP (1)
    [Extent1].[Signed] AS [Signed],
    [Extent1].[SSN] AS [SSN],
    [Extent2].[FirstName] AS [FirstName],
    [Extent2].[LastName] AS [LastName],
    [Extent2].[Phone_CountryCode] AS [Phone_CountryCode],
    [Extent2].[Phone_AreaCode] AS [Phone_AreaCode],
    [Extent2].[Phone_Number] AS [Phone_Number],
    [Extent2].[Phone_Extension] AS [Phone_Extension],
    '1X0X' AS [C1],
    1 AS [C2]
    FROM  [dbo].[Persons_Author] AS [Extent1]
    INNER JOIN [dbo].[Persons] AS [Extent2] ON [Extent1].[SSN] = [Extent2].[SSN]
)  AS [Limit1]

-- second trace
exec sp_executesql N'SELECT
[Extent2].[ISBN] AS [ISBN],
[Extent2].[Title] AS [Title],
[Extent2].[PublishingDate] AS [PublishingDate],
[Extent2].[PageCount] AS [PageCount]
FROM  [dbo].[PersonBook] AS [Extent1]
INNER JOIN [dbo].[Books] AS [Extent2] ON
[Extent1].[Books_ISBN] = [Extent2].[ISBN]
WHERE [Extent1].[Persons_SSN] =
@EntityKeyValue1',N'@EntityKeyValue1 char(9)',@EntityKeyValue1='568845586'

Private Sub DefferedLoading_Click(ByVal sender As System.Object,
                                  ByVal e As System.EventArgs) Handles
                                  DefferedLoading.Click
    Dim context As New BooksAndAuthorsContainer
    context.ContextOptions.DeferredLoadingEnabled = True
    Dim author = context.Persons.OfType(Of Author).First()
    For Each book As Book In author.Books
        Console.WriteLine(book.Title)
    Next
End Sub

We can insert a new instance of an entity in several ways. Each entity class in the EDM has a static CreateEntityName method. For example, the Book entity has a CreateBook static method that can be used for the purpose of adding a new book to a context. Alternatively, you can create a new entity using the New operator and then use the AddObject method that's available on each EntitySet class. Listing 17.20 shows the latter method where first a new instance of the Language entity is created, then it is added to the Languages entity set, and finally it is committed to the database by calling ObjectContext.SaveChanges().

Private Sub InsertNewLanguage_Click(ByVal sender As System.Object,
                                ByVal e As System.EventArgs) Handles
                                InsertNewLanguage.Click
    Dim context As New BooksAndAuthorsContainer
    Dim arabic As New Language
    arabic.Name = "Arabic"
    context.Languages.AddObject(arabic)
    context.SaveChanges()
End Sub

Deletion of an entity instance can be easily performed with the help of the Delete method of ObjectContext. Listing 17.21 shows how this is performed using the instance of the Language entity inserted in the previous listing.

Private Sub DeleteLanguage_Click(ByVal sender As System.Object,
                                 ByVal e As System.EventArgs) Handles
                                 DeleteLanguage.Click
    Dim context As New BooksAndAuthorsContainer
    Dim arabic As Language = (From language In context.Languages
                             Where language.Name = "Arabic").
                             FirstOrDefault()
    context.DeleteObject(arabic)
    context.SaveChanges()
End Sub

You will not be surprised by the code included in Listing 17.22. It follows a pattern that is similar to that found in the entity insertion and deletion code. An Entity property is updated, and changes are committed by calling the ObjectContext SaveChanges method.

Private Sub UpdateLanguage_Click(ByVal sender As System.Object,
                                 ByVal e As System.EventArgs) Handles
                                 UpdateLanguage.Click
    Dim context As New BooksAndAuthorsContainer
    Dim arabic As Language = (From language In context.Languages
                             Where language.Name = "Arabic").
                             FirstOrDefault()
    arabic.Name = "Arabic Language"
    context.SaveChanges()
End Sub

The final piece of the data modification puzzle has to do with establishing the relationship between the entity instances. If I have a Book instance and an Author instance, how do I relate them so that the book appears in the list of books authored by that specific author?

You can perform this in a standard object-oriented manner. Just add the Book instance to the set of books belonging to the Author, and call SaveChanges on the ObjectContext. Listing 17.23 shows that exact scenario.

Private Sub InsertsInTransaction_Click(ByVal sender As System.Object,
                                       ByVal e As System.EventArgs) Handles
                                       InsertsInTransaction.Click
    Dim context As New BooksAndAuthorsContainer

    Dim book As New Book With {.ISBN = "9780470179796",
                .Title = "Professional Refactoring in Visual Basic",
                .PageCount = "517", .PublishingDate = "April 7, 2008"}

    Dim author As New Author With {.SSN = "423235332",
                .FirstName = "Danijel",
                .LastName = "Arsenovski",
                .Phone = New PhoneNumber With {.CountryCode = "56",
                    .AreaCode = "2", .Number = "8588656", .Extension = " "},
                .Signed = "January 1, 2007"}

    context.Persons.AddObject(author)
    context.Books.AddObject(book)

author.Books.Add(book)
    context.SaveChanges()
End Sub

By calling the Add method on the set of Books belonging to an Author — author.Books.Add(book) — the Entity Framework becomes aware that two instances are related and adds the record to the PersonBook join table in the database.

If you look back at Listing 17.23, you might wonder what exactly happens if the Entity Framework is not capable of performing all the operations in the listing. If you analyze the listing, you can see that the code will be resolved as three Insert operations on the database level: the insert of an author instance to the Author table, the insert of a book instance into the Book table, and the insert of the record relating the book and the author into the PersonBook table. So, what happens if one of these operations fails?

The Entity Framework generates implicit transactions for all changes performed from one SaveChanges method call to the next. In our example, all inserts will be committed to the database under the same transaction. If any of them fails, the others are automatically rolled back.

You can test this by executing the code in Listing 17.23 twice in a row. For the second execution, modify the author's Social Security number, but leave the book data as it is. Since you have modified the SSN, you will be able to insert the author, but the book insert will fail. ISBN is the primary key on the Book entity, and the same value cannot be inserted twice. The execution will end up in an error. If you then take a look at the database, you will observe that neither the book nor the author instance was inserted.

Using transaction As New TransactionScope()
    context.Persons.AddObject(author)
    context.Books.AddObject(book)
    author.Books.Add(book)
    context.SaveChanges()
    'Do some external operation that can be enroled inside the transaction
    transaction.Complete()
End Using