FREE BOOK

Chapter 8 : Reading Objects with LINQ to SQL

Posted by Addison Wesley Free Book | LINQ July 28, 2009
It uses the mapping of classes to tables to translate LINQ queries to SQL commands and then materializes objects from the rows returned. The objects can be related to each other in a graph of objects that is managed by LINQ to SQL on your behalf.

Retrieving Objects: Entities and Projections

As you saw in the preceding chapter, the class is mapped to the Customers table in the Northwind database so that LINQ to SQL can retrieve instances from the rows retrieved from the Customers table. As in the case of the Customers table, a table often has a primary key or a unique key. When a class is mapped to such a table, it is called an entity class or entity type, and its instances are called entities. In the example in the preceding chapter, the Customer class is an entity class, and  objects are entities because the ? member is mapped to the primary key column with the same name in the following mapping:

[Table(Name="Customers")]

public class Customer

{

[Column(IsPrimaryKey=true)] public string CustomerlD; [Column]

public string City; [Column]

public string Country;

}

This class is mapped to a table in Northwind that has a schema with a primary key ?. Hence, each entity has a unique value for the ? member. A query to retrieve entities simply selects the results of the entity type as follows:

var EntityQuery = from c in db.Customers where c.Country == "Spain" select c;
ObjectDumper.Write(EntityQuery,O);

ObjectDumper is a utility for printing object graphs to the console in a stylized fashion. It ships as a sample with Visual Studio 2008. You need to build it into a DLL so that it can be added as a reference in your project. You can think of E > " ? % 5 as an object-graph-enabled version of 5 . The optional second argument lets you control the level of navigation in the object graph. So the default value L doesn't go beyond top-level entities, and value 0 lets you see  E as well. Many results in this book, such as the following one, are output by ObjectDumper.

The execution of the preceding query produces the following results:

CustomerID=BOLID City=Madrid Country=Spain
CustomerID=FISSA City=Madrid Country=Spain
CustomerID=GALED City=Barcelona Country=Spain
CustomerID=GODOS City=Sevilla Country=Spain
CustomerID=ROMEY City=Madrid Country=Spain

In this result, each Customer entity represents a unique customer identified by a CustomerID. Hence, each entity has identity and can be updated. We will consider more implications of identity in the following sections, but first let's look at objects that don't have identity.

Sometimes we are interested in only a subset of the data. For example, to find out all the cities and countries where we have customers, the following class and query can be used. The query uses the " syntax available in LINQ to project only the interesting members:

public class CustomerCitylnfo

{

public string City;

public string Country;

}

var ProjectionQuery = from c in db.Customers where c.Country == "Spain" select new CustomerCitylnfo {

City = c.City,

Country = c.Country

};

var ProjectionQuery = from c in db.Customers where c.Country == "Spain" select new CustomerCitylnfo {

City = c.City,

Country = c.Country

};

This query is translated into the following SQL. Notice that exactly the projected information is retrieved. ? is not projected, so it is omitted from the SQL clause.

SELECT [tO].[City], [tO].[Country] FROM [dbo].[Customers] AS [tO] WHERE [tO].[Country] = IpO

----  IpO: lnput NrarChar (Size = 5; Prec = O; Scale = O) [Spain]

This code produces the following results:

City=Madrid Country=Spain
City=Madrid Country=Spain
City=Barcelona Country=Spain
City=Sevilla Country=Spain
City=Madrid Country=Spain

The same query could also be performed using an anonymous type as
follows:

var ProjectionQuery = from c in db.Customers where c.Country == "Spain" select new {

City = c.City, Country = c.Country

};

In either case, we get a set of objects of type or an anonymous type-both subsets of the entity type. The objects represent just a projection and don't have any identity by themselves. The translated SQL queries for these objects include appropriate filters and projections to retrieve a minimal amount of data from the server.

Thus, LINQ to SQL can be used to retrieve entities or projections efficiently. It lets you return entities or shape them to bring a subset as necessary. The next section further explores the difference between the two.

The Importance of Object Identity

When we retrieve a Customer entity, we may want to update it. If multiple copies of the entity exist-more than one instance with the same primary key values-confusion can occur about which instance is the authoritative one for the purpose of updates. Hence, it is important to ensure that no duplicates exist.

However, multiple queries can return overlapping results. For example, a query for customers with IDs that start with the letter B and customers from Spain overlap, as the previous query results show.

In this case, each of the two queries must return the appropriate results, and yet we must not have any duplicates for the customer with ID BE ?. LINQ to SQL implements object identity to ensure that the entity created for ID BE ? when the first query is executed is reused in the result for the second query. Object identity is scoped to a ? = instance. Within a ? = instance, no more than one object reference is associated with a primary key value of a given type. Conceptually you can think of each ? = instance maintaining a hash table of object references indexed by the primary key values. For each row returned by the database, if the key value is not found in the hash table, a new entity instance with that key value is created and inserted into the hash table.

Thereafter, if a row with the same key value is returned, the row is discarded, and the existing entity with the same key value is returned. In the previous example, conceptually, the first time a row with ID BE ? is returned, a Customer entity with that ID is created and inserted into the logical hash table. Thereafter, the same instance is returned for a row with ? BE ?; additional instances are not created for that ID by that ? = instance. Object reference is how identity is expressed in the .NET framework. Primary or unique key value is how identity is expressed in relational databases.

LINQ to SQL bridges the two concepts of reference identity and value identity across the object-relational divide. The ) D property of the mapping attribute describes the value identity for the containing entity class. LINQ to SQL maintains the reference identity within the scope of a ? = instance by not creating another entity instance (such as a different object reference) for a given identity value defined by the database columns.

A primary key may be composed of one or more columns. Each member mapped to a primary key column must have ) D set to . If the primary key has multiple columns, such a key is often called a composite key. LINQ to SQL uniformly supports single-column and composite keys to ensure object identity.

Object identity is a property of an entity. It is not applicable to projections because projections don't have a well-defined identity. The query from the previous section for city information returns duplicate results containing multiple objects with the same , value.

Object identity relies on immutability of members mapped to a unique key in the database. If these members that form the identity are changed, a loss of identity would occur. If the customer ID is changed from BE ? to something else, it is no longer the same customer. Hence, changes to members mapped to keys are not permitted. Any attempt to change key values results in undefined behavior. If you do need to update a primary key value, you can think of it as a pair of operations-deletion of the entity with the old key value and creation of a new entity with the new key value. However, for an entity, the identity must not change over the life of the entity.

This key concept of object identity allows updates and also enables relationships to be used effectively.

Consider Entity, Identity, and Updatability
An entity has identity defined by one or more key members. LINQ to
SQL ensures that duplicate entity instances are not created for a given
key value. Hence, entities typically are updatable. Arbitrary projections
produce results of nonentity types and are not updatable.

Total Pages : 9 12345

comments