This query is converted into an SQL query that is sent to the relational database:

SELECT CustomerID, CompanyName, City FROM Customers WHERE Country = 'USA' AND Region = 'WA'

At this point, you might be asking a few questions. First, how can the LINQ query be written using object names that are validated by the compiler? Second, when is the SQL query generated from the LINQ query? Third, when is the SQL query executed? To understand the answers to these questions, you need to understand the entity model in LINQ to SQL and then the deferred query evaluation.

Entities in LINQ to SQL

Any external data must be described with appropriate metadata bound to class definitions. Each table must have a corresponding class decorated with particular attributes, which corresponds to a row of data and describes all columns in terms of data members of the defined type. The type can be a complete or partial description of an existing physical table, view, or stored procedure result. Only the described fields can be used inside a LINQ query for both projection and filtering. Listing 5-1 shows a small and simple entity definition.

Listing 5-1: Entity definition for LINQ to SQL

[Table(Name="Customers")] public class Customer { [Column] public string CustomerID; [Column] public string CompanyName; [Column] public string City; [Column(

Name

="Region")] public string State; [Column] public string Country; }

The Customer type defines the content of a row, and each field or property decorated with Column corresponds to a column of the relational table. The Name parameter can specify a column name that is different from the data member name (in this example, State corresponds to the Region table column). The Table attribute specifies that the class is an entity representing data of a database table; its Name property can specify a table name that is different from the entity name. It is common to use the singular form for the name of the class (a single row) and the plural form for the name of the table (a set of rows).

You need a Customers table to build a LINQ to SQL query over Customers data. The Table<T> generic class is the right way to create such a type:

Table<Customer>

Customers = ...; // ... var query = from c in Customers // ...

The Customers table object has to be instantiated . To do that, we need an instance of a Data Context class, which defines the bridge between the LINQ world and the external relational database. The nearest concept to DataContext that comes to mind is a database connection-in fact, a mandatory parameter needed to create a DataContext instance is the connection string or the Connection object. Its GetTable method returns a corresponding Table<T> for the specified type:

DataContext

db = new DataContext("Database=Northwind"); Table<Customer> Customers = db.

GetTable

<Customer>();

Listing 5-2 shows the resulting code when you put all the pieces together.

Listing 5-2: Simple LINQ to SQL query

DataContext db = new DataContext( ConnectionString ); Table<Customer> Customers = db.GetTable<Customer>(); var query = from c in Customers where c.Country == "USA" && c.State == "WA" select new {c.CustomerID, c.CompanyName, c.City }; foreach( var row in query ) { Console.WriteLine( row ); }

The query variable is initialized with a query expression that forms an expression tree. As we noted in Chapter 2, “C# Language Features,” an expression tree maintains a representation of the expression in memory instead of pointing to a method through a delegate. When the foreach loop enumerates data selected by the query, the expression tree is used to generate the corresponding SQL query, using all metadata and information we have in the entity classes and in the referenced DataContext instance.

The data returned from the SQL query accessing row and placed into the foreach loop is then used to fill the projected anonymous type following the select keyword. In this sample, the Customer class is never instantiated, and it is used only by LINQ to analyze its metadata.

We can explore the generated SQL query by using the GetQueryText method of the Data Context class:

Console.WriteLine( db.

GetQueryText

( query ) );

The previous simple LINQ to SQL query generates the following GetQueryText output:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[City] FROM [Customers] AS [t0] WHERE ([t0].[Country] = @p0) AND ([t0].[Region] = @p1)

An alternative way to get a trace of all SQL statements sent to the database is to assign a value to the Log property of DataContext :

db.

Log

= Console.Out;

In the next section, you will see in more detail how to generate entity classes for LINQ to SQL.

Data Modeling

The set of entity classes that LINQ to SQL requires is a thin abstraction layer over the relational model. Each entity class defines an accessible table of data, which can be queried and modified. Entity instances that are modified can apply their changes on data contained in the relational database. You will see the options for data updates in the “Data Update” section of this chapter. In this section, you will learn how to build a data model for LINQ to SQL.

DataContext

The DataContext class handles the communication between LINQ and external relational data sources. Each instance has a single Connection to a relational database. Its type is IDb Connection ; therefore, it is not specific to a particular database product.

DataContext uses metadata information to map the physical structure of relational data, on which the SQL code generation is based. DataContext can also be used to call a stored procedure and persist data changes in entity class instances in the relational database.

Classes that specialize access for a particular database can be derived from DataContext . Such classes offer an easier way to access relational data, including members that represent available tables. You can define the existing tables simply by declaring them, without a specific initialization, as in the following code:

public class SampleDb : DataContext { public SampleDb(IDbConnection connection) : base( connection ) {} public SampleDb(string fileOrServerOrConnection) : base( fileOrServerOrConnection ) {} public SampleDb(IDbConnection connection, MappingSource mapping) : base( connection, mapping ) {} public Table<Customer> Customers; }

Entity Classes

An entity class has two roles. The first is to provide metadata to LINQ queries; for this purpose, an entity class is not instantiated. The second is to provide storage for data read from the relational data source, as well as to track possible updates and support their submission back to the relational data source.

An entity class is any reference type definition decorated with the Table attribute. A struct (value type) cannot be used for this. The Table attribute can have a Name parameter that defines the name of the corresponding table in the database. If Name is omitted, the name of the class is used as the default:

[Table(Name="Products")]

public class Product { ... }

Inside an entity class, there can be any number and type of members. Only data members or properties decorated with the Column attribute are significant in defining the mapping between the entity class and the corresponding table in the database:

[Column]

public int ProductID;

An entity class should have a unique key. This key is necessary to support unique identity (more on this later), to identify corresponding rows in database tables, and to generate SQL statements that update data. If you do not have a primary key, instances of the entity class can be created but are not modifiable. The Boolean IsPrimaryKey property of the Column attribute, set to true , states that the column belongs to the primary key of the table. If the primary key used is a composite key, all the columns that form the primary key will have IsPrima ryKey=true in their parameters:

[Column(

IsPrimaryKey=true

)] public int ProductID;

By default, a column is mapped using the same name of the member to which the Column attribute is applied. You can use a different name, specifying a value for the Name parameter. For example, the following Price member corresponds to the UnitPrice field in the database table:

[Column(

Name="UnitPrice"

)] public decimal Price;

If you want to filter data access through member property accessors, you have to specify the underlying storage member with the Storage parameter. If you specify a Storage parameter, LINQ to SQL bypasses the public property accessor and interacts directly with the underlying value. Understanding this is particularly important if you want to track only the modifications made by your code and not the read/write operations made by the LINQ framework. In the following code, the ProductName property is accessed for each read/write operation made by your code; a direct read/write operation on the _ProductName data member is made when a LINQ operation is executed:

[Column(

Storage="_ProductName"

)] public string ProductName { get { return this._ProductName; } set { this.OnPropertyChanging("ProductName"); this._ProductName = value; this.OnPropertyChanged("ProductName"); } }

The correspondence between relational type and .NET type is made assuming a default relational type corresponding to the used .NET type. Whenever you need to define a different type, you can use the DBType parameter, specifying a valid type by using a valid SQL syntax for the relational data source. This property is used only if you want to create a database schema starting from entity class definitions:

[Column(

DBType="NVARCHAR(20)"

)] public string QuantityPerUnit;

If a column value is auto-generated by the database (which is a service offered by the IDENTITY keyword in SQL Server), you might want to synchronize the entity class member with the generated value whenever you insert an entity instance into the database. To get this behavior, you need to set the IsDBGenerated parameter to true , and you also need to adapt the DBType accordingly -for example, by adding the IDENTITY modifier for SQL Server tables:

[Column(

DBType="INT NOT NULL IDENTITY",

IsPrimaryKey=true,

IsDBGenerated=true

)] public int ProductID;

Other parameters that are relevant in updating data are IsVersion and UpdateCheck . You will see a deeper explanation of IsDBGenerated , IsVersion , and UpdateCheck later in the “Data Update” section.

Entity Inheritance

Sometime a single table contains many types of entities. For example, imagine a list of contacts-some of them can be customers, others can be suppliers, and others can be company employees. From a data point of view, each entity can have some specific fields. (For example, a customer can have a discount field, which is not relevant for employees and suppliers.) From a business logic point of view, each entity can implement different business rules. The best way to model this kind of data in an object-oriented environment is by leveraging inheritance to create a hierarchy of specialized classes. LINQ to SQL allows a set of classes derived from the same base class to map the same relational table.

The InheritanceMapping attribute decorates the base class of a hierarchy, indicating the corresponding derived classes that are based on the value of a special discriminator column . The Code parameter defines a possible value, and the Type parameter defines the corresponding derived type. The discriminator column is defined by the IsDiscriminator argument being set to true in the Column attribute specification. Listing 5-3 provides an example of a hierarchy based on the Contacts table of the Northwind sample database.

Listing 5-3: Hierarchy of classes based on contacts

[Table(Name="Contacts")]

[InheritanceMapping(Code = "Customer", Type = typeof(CustomerContact))]


[InheritanceMapping(Code = "Supplier", Type = typeof(SupplierContact))]


[InheritanceMapping(Code = "Shipper", Type = typeof(ShipperContact))]


[InheritanceMapping(Code = "Employee", Type = typeof(Contact), IsDefault = true)]



public class Contact { [Column(IsPrimaryKey=true)] public int ContactID; [Column(Name="ContactName")] public string Name; [Column] public string Phone; [Column(

IsDiscriminator = true

)] public string ContactType; } public class CompanyContact : Contact { [Column(Name="CompanyName")] public string Company; } public class CustomerContact : CompanyContact { } public class SupplierContact : CompanyContact { } public class ShipperContact : CompanyContact { public string Shipper { get { return Company; } set { Company = value; } } }

Contact is the base class of the hierarchy. If the contact is a Customer , a Supplier , or a Shipper , the corresponding classes derive from an intermediate CompanyContact , which defines the Company field corresponding to the CompanyName column in the source table. The Company Contact intermediate class is necessary because you cannot reference the same column (CompanyName) in more than one field, even if this happens in different classes in the same hierarchy. The ShipperContact class defines a Shipper property that exposes the same value of Company , with a different semantic meaning.

The level of abstraction offered by having different entity classes in the same hierarchy is well described by the sample queries shown in Listing 5-4. The queryTyped query uses the OfType operator, while queryFiltered relies on a standard where condition to filter out contacts that are not customers.

Listing 5-4: Queries using a hierarchy of entity classes

var queryTyped = from c in contacts

.OfType<CustomerContact>()

select c; var queryFiltered = from c in contacts

where c is CustomerContact

select c; foreach( var row in queryTyped ) { Console.WriteLine( row.Company ); } // We need an

explicit cast

to access the CostumerContact members foreach(

CustomerContact

row in queryFiltered ) { Console.WriteLine( row.Company ); }

The SQL queries produced by these LINQ queries are functionally identical to the following one. (The actual query is different because of generalization coding.)

SELECT [t0].[ContactType], [t0].[CompanyName] AS [Company], [t0].[ContactID], [t0].[ContactName] AS [Name], [t0].[Phone] FROM [Contacts] AS [t0] WHERE [t0].[ContactType] = 'Customer'

The difference between queryTyped and queryFiltered queries lies in the returned type. A query Typed query returns a sequence of CustomerContact instances, while queryFiltered returns a sequence of the base class Contact . With queryFiltered , you need to explicitly cast the result into a CustomerContact type if you want to access the Company property.

Unique Object Identity

An instance of an entity class stores an in-memory representation of table row data. If you try to instantiate two different entities containing the same row, you obtain a reference to the same inmemory object. In other words, object identity (same references) maintains data identity (same table row) using the entity unique key. The LINQ to SQL engine ensures that the same object reference is used when an entity instantiated from a query result is already in memory. This check does not happen if you create an instance of an entity by yourself. In Listing 5-5, you can see that c1 and c2 reference the same Contact instance, even if they originate from two different queries, while c3 is a different object, even if its content is equivalent to the others.

Listing 5-5: Object identity

var queryTyped = from c in contacts.OfType<CustomerContact>() orderby c.ContactID select c; var queryFiltered = from c in contacts where c is CustomerContact orderby c.ContactID select c; Contact c1 = null; Contact c2 = null; foreach( var row in queryTyped.Take(1) ) { c1 = row; } foreach( var row in queryFiltered.Take(1) ) { c2 = row; } Contact c3 = new Contact(); c3.ContactID = c1.ContactID; c3.ContactType = c1.ContactType; c3.Name = c1.Name; c3.Phone = c1.Phone; Debug.Assert(

c1 == c2

); // same instance Debug.Assert(

c1 != c3

); // different objects

Entity Constraints

The entity classes cannot represent all possible check constraints of a relational table. No attributes are available to specify the same alternate keys (unique constraint), triggers, and check expressions that can be defined in a relational database. This fact is relevant when you start to manipulate data using entity classes, because you cannot guarantee that an updated value will be accepted by the underlying database. (For example, it could have a duplicated unique key.) However, because you can load into entity instances only parts (rows) of the whole table, these kinds of checks are not possible without accessing the relational database, anyway.

There is partial support for describing a primary key, unique constraint, and other indexes only through XML external metadata specification. You will see this discussed later in the “External Mapping” section. This support is useful only to generate a database starting from LINQ to SQL metadata.

More complete support is available for maintaining valid relationships between entities, just like the support offered by foreign keys in a standard relational environment.

Associations Between Entities

Relationships between entities in a relational database are modeled on the concept of foreign keys referring to primary keys of a table. Class entities can use the same concept through the Association attribute, which can describe both sides of a one-to-many relationship described by a foreign key.

EntityRef   Let us start with the concept of lookup , which is the typical operation used to get the customer related to one order. Lookup can be seen as the direct translation into the entity model of the foreign key relationship existing between the CustomerID column of the Orders table and the primary key of the Customers table. In our entity model, the Order entity class will have a Customer property (of type Customer ), that shows the customer data. This property is decorated with the Association attribute and stores its information in an EntityRef<Customer> member (named _Customer ), which enables the deferred loading of references that you will see shortly. Listing 5-6 shows the definition of this association.

Listing 5-6: Association EntityRef

[Table(Name="Orders")] public class Order { [Column(IsPrimaryKey=true)] public int OrderID; [Column] private string CustomerID;

[Association(Storage="_Customer", ThisKey="CustomerID")]

public Customer Customer { get { return this._Customer.Entity; } set { this._Customer.Entity = value; } }

private EntityRef<Customer> _Customer;

}

As you can see, the CustomerID column must be defined in Order because otherwise it would not be possible to obtain the related Customer . The ThisKey argument or the Association attribute indicates the “foreign key” column (which would be a comma-separated list if more columns were involved for a composite key) that is used to define the relationship between entities. If you want to hide this detail in the entity properties, you can declare that column as private, just as in the Order class shown earlier.

Using the Order class in a LINQ query, you can specify a Customer property in a filter without the need to write a join between Customer and Order entities. In the following query, the Country member of the related Customer is used to filter orders that come from customers of a particular Country :

Table<Order> Orders = db.GetTable<Order>(); var query = from o in Orders where

o.Customer.Country == "USA"

select o.OrderID;

The previous query is translated into an SQL JOIN like the following one:

SELECT [t0].[OrderID] FROM [Orders] AS [t0]

LEFT JOIN [Customers] AS [t1]


ON [t1].[CustomerID] = [t0].[CustomerID]


WHERE [t1].[Country] = "USA"

Until now, we have used entity relationships only for their metadata-building LINQ queries. When an instance of an entity class is created, a reference to another entity (such as the previous Customer property) works with a technique called deferred loading . The related Customer entity is not instantiated and loaded into memory from the database until it is accessed either in read or write mode.

In other words, to generate an SQL query to populate the Customer related entity when the Country property is accessed, you would use the following code:

var query = from o in Orders where o.OrderID == 10528 select o; foreach( var row in query ) { Console.WriteLine( row

.Customer.Country

); }

The process of accessing the Customer property involves checking to determine whether the related Customer entity is already in memory. If it is, that entity is accessed; otherwise, the following SQL query is executed and the corresponding Customer entity is loaded in memory and then accessed:

SELECT [t0].[Country], [t0].[CustomerID], [t0].[CompanyName] FROM [Customers] AS [t0] WHERE [t0].[CustomerID] = "GREAL"

The GREAL string is the CustomerID value for order 10528. As you can see, the SELECT statement queries all columns declared in the Customer entity, even if they are not used in the expression that accessed the Customer entity. (In this case, the executed code never referenced the CompanyName member.)

EntitySet   The other side of an association is a table that is referenced from another table through its primary key. Although this is an implicit consequence of the foreign key constraint in a relational model, you need to explicitly define this association in the entity model. If the Customers table is referenced from the Orders table, you can define an Orders property in the Customer class that represents the set of Order entities related to a given Customer . The relationship is implemented by an instance of EntitySet<Order> , which is a wrapper class over the sequence of related orders. You might want to directly expose this EntitySet<T> type, as in the code shown in Listing 5-7. In that code, the OtherKey argument of the Association attribute specifies the name of the member on the related type ( Order ) that defines the association between Customer and the set of Order entities.

Listing 5-7: Association EntitySet (visible)

[Table(Name="Customers")] public class Customer { [Column(IsPrimaryKey=true)] public string CustomerID; [Column] public string CompanyName; [Column] public string Country;

[Association(OtherKey="CustomerID")]


public EntitySet<Order> Orders;

}

You might also decide to hide this implementation detail, making only an ICollection<Order> visible outside of the Customer class, as in the declaration shown in Listing 5-8. In this case, the Storage argument of the Association attribute specifies the EntitySet<T> physical storage.

Listing 5-8: Association EntitySet (hidden)

public class Customer { [Column(IsPrimaryKey=true)] public string CustomerID; [Column] public string CompanyName; [Column] public string Country; private EntitySet<Order> _Orders; [Association(OtherKey="CustomerID", Storage="_Orders")] public ICollection<Order> Orders { get { return this._Orders; } set { this._Orders.Assign(value); } } }

With both models of association declaration, you can use the Customer class in a LINQ query, accessing the related Order entities without the need to write a join. You simply specify the Orders property. The next query returns the names of customers who placed more than 20 orders:

Table<Customer> Customers = db.GetTable<Customer>(); var query = from c in Customers where

c.Orders.Count > 20

select c.CompanyName;

The previous LINQ query is translated into an SQL query like the following one:

SELECT [t0].[CompanyName] FROM [Customers] AS [t0]

WHERE ( SELECT COUNT(*)


FROM [Orders] AS [t1]


WHERE [t1].[CustomerID] = [t0].[CustomerID]


) > 20

In this case, no instances of the Order entity are created. The Orders property serves only as a metadata source to generate the desired SQL query. If you return a Customer entity from a LINQ query, you can access the Orders of a customer on demand:

var query = from c in Customers where c.Orders.Count > 20

select c

; foreach( var row in query ) { Console.WriteLine( row.CompanyName ); foreach( var order in

row.Orders

) { Console.WriteLine( order.OrderID ); } }

In the previous code, you are using deferred loading. Each time you access the Orders property of a customer for the first time (as indicated by the highlighted code in the preceding code sample), a query like the following one (which uses @p0 as the parameter to filter CustomerID ) is sent to the database:

SELECT [t0].[OrderID], [t0].[CustomerID] FROM [Orders] AS [t0]

WHERE [t0].[CustomerID] = @p0

If you want to load all orders for all customers into memory using only one query to the database, you need to request immediate loading instead of deferred loading. To do that, you have two options. The first approach, which is demonstrated in Listing 5-9, is to force the inclusion of an EntitySet using a DataShape instance and the call of its LoadWith<T> method.

Listing 5-9: Use of DataShape and LoadWith<T>

DataContext db = new DataContext( ConnectionString ); Table<Customer> Customers = db.GetTable<Customer>();

DataShape ds = new DataShape();


ds.LoadWith<Customer>( c => c.Orders );


db.Shape = ds;

var query = from c in Customers where c.Orders.Count > 20 select c;

The second option is to return a new entity that explicitly includes the Orders property for the Customer :

var query = from c in Customers where c.Orders.Count > 20 select

new { c.CompanyName, c.Orders }

;

These LINQ queries send an SQL query to the database to get all customers who placed more than 20 orders, including the whole order list for each customer. That SQL query might be similar to the one shown in the following code:

SELECT [t2].[CompanyName], [t3].[OrderID], [t3].[CustomerID], ( SELECT COUNT(*) FROM [Orders] AS [t4] WHERE [t4].[CustomerID] = [t2].[CustomerID] ) AS [count] FROM ( SELECT [t0].[CustomerID], [t0].[CompanyName] FROM [Customers] AS [t0] WHERE ( SELECT COUNT(*) FROM [Orders] AS [t1] WHERE [t1].[CustomerID] = [t0].[CustomerID] ) > 20 ) AS [t2] LEFT OUTER JOIN [Orders] AS [t3] ON [t3].[CustomerID] = [t2].[CustomerID] ORDER BY [t2].[CustomerID], [t3].[OrderID]

You can filter the subquery produced by relationship navigation. Suppose you want to see only customers who placed at least five orders in 1997, and you want to load and see only these orders. You can use the AssociateWith<T> method of the DataShape class to do that, as demonstrated in Listing 5-10.

Listing 5-10: Use of DataShape and AssociateWith<T>

DataShape ds = new DataShape();

ds.AssociateWith<Customer>(


c => c.Orders.Where(


o => o.OrderDate.Value.Year == 1997 ) );

db.Shape = ds; var query = from c in Customers where c.Orders.Count > 5 select c;

You will appreciate that the C# filter condition ( o.OrderDate.Value.Year == 1997 ) is translated into the following SQL expression:

(DATEPART(Year, [t2].[OrderDate]) = 1997)

Using AssociateWith<T> alone does not apply the immediate loading behavior. If you want both immediate loading and filtering through a relationship, you have to call both the Load With<T> and AssociateWith<T> methods. The order of these calls is not relevant. For example, you can write the following code:

DataShape ds = new DataShape();

ds.AssociateWith<Customer>

( c => c.Orders.Where( o => o.OrderDate.Value.Year == 1997 ) );

ds.LoadWith<Customer>

( c => c.Orders ); db.Shape = ds;

Loading all data into memory using a single query might be a better approach if you are sure you will access all data that is loaded, because you will spend less time in round-trip latency. However, this technique will consume more memory and bandwidth when the typical access to a graph of entities is random. Think about these details when you decide how to query your data model.

Other Association Attributes   The Association attribute can also have other parameters. Name corresponds to the foreign key constraint name, Unique defines a real one-to-one relationship, and OtherKey can specify the comma-separated value of members that forms the primary key of the related entity.

Graph Consistency   Relationships are bidirectional between entities-when an update is made on one side, the other side should be kept synchronized. LINQ to SQL does not automatically manage this kind of synchronization, which has to be done by the class entity implementation. LINQ to SQL offers an implementation pattern that is also used by code-generation tools such as SQLMetal, a tool that is part of the Microsoft .NET 3.5 Software Development Kit (SDK), which will be described later in this chapter. This pattern is based on the EntitySet<T> class on one side and on the complex setter accessor on the other side. The product documentation offers a detailed explanation of how to implement this pattern if you do not want to rely on tools-generated code.

Change Notification   You will see in the “Data Update” section that LINQ to SQL is able to track changes in entities, submitting equivalent changes to the database. This process is implemented by default through an algorithm that compares an object’s content with its original values, requiring a copy of each tracked object. The memory consumption can be high, but it can be optimized if entities participate in the change tracking service by announcing when an object has been changed.

The implementation requires an entity to expose all its data through properties, and each property setter needs to call the PropertyChanging method of DataContext . Entities that implement this pattern should also implement the System.Data.Linq.INotifyPropertyChanging interface. Further details are available in the product documentation. Tools-generated code for entities (such as that emitted by SQLMetal) already implements this pattern.

Relational Model vs. Hierarchical Model

The entity model used by LINQ to SQL defines a set of objects that maps the database tables into objects that can be used and manipulated by LINQ queries. The resulting model makes a paradigm shift that has been revealed in describing associations between entities. We moved from a relational model (tables in a database) to a hierarchical or graph model (objects in memory).

A hierarchical/graph model is the natural way to manipulate objects in a program written in C# or Microsoft Visual Basic. When you try to consider how to translate an existing SQL query into a LINQ query, this is the major conceptual obstacle you encounter. In LINQ, you can write a query using joins between separate entities, just as you do in SQL. However, you can also write a query leveraging the existing relationships between entities, as we did with EntitySet and EntityRef associations.

Data Querying

A LINQ to SQL query is sent to the database only when the program needs to read data. For example, the following foreach loop iterates rows returned from a table:

var query = from c in Customers where c.Country == "USA" select c.CompanyName;

foreach( var company in query ) {


Console.WriteLine( company );


}

The code generated by the foreach statement is equivalent to the code shown next. The exact moment the query is executed corresponds to the call of GetEnumerator :

// GetEnumerator sends the query to the database IEnumerator<string> enumerator =

query.GetEnumerator();

while (enumerator.MoveNext()) { Console.WriteLine( enumerator.Current ); }

Writing more foreach loops of the same query generates as many calls to GetEnumerator , and thus an equal number of repeated executions of the same query. If you want to iterate the same data many times, you might prefer to cache data in memory. Using ToList or ToArray , you convert the results of a query into a List or an Array , respectively. When you call these methods, the SQL query is sent to the database:

// ToList()

sends the query to the database

var companyNames = query

.ToList();

You might want to send the query to the database several times when you manipulate the LINQ query between data iterations. For example, you might have an interactive user interface that allows the user to add a new filter condition for each iteration of data. In Listing 5-11, the DisplayTop method shows only the first few rows of the result; query manipulation between DisplayTop calls simulates a user interaction that ends in a new filter condition each time.

Listing 5-11: Query manipulation

static void QueryManipulation() { DataContext db = new DataContext( ConnectionString ); Table<Customer> Customers = db.GetTable<Customer>(); db.Log = Console.Out; // All Customers var

query

= from c in Customers select new {c.CompanyName, c.State, c.Country }; DisplayTop( query, 10 ); // User interaction add a filter // to the previous query // Customers from USA

query

= from c in

query

where c.Country == "USA" select c; DisplayTop( query, 10 ); // User interaction add another // filter to the previous query // Customers from WA, USA

query

= from c in

query

where c.State == "WA" select c; DisplayTop( query, 10 ); } static void

DisplayTop<T>( IQueryable<T> query, int rows )

{ foreach( var row in query

.Take(rows)

) { Console.WriteLine( row ); } }

A common query used for accessing a database is the read of a single row from a table, defining a condition that is guaranteed to be unique, such as a record key. Here is a typical query:

var query = from c in

db.Customers

where

c.CustomerID == "ANATR"

select c; var

enumerator = query.GetEnumerator

(); if (

enumerator.MoveNext()

) {

var customer = enumerator.Current;

Console.WriteLine( "{0} {1}", customer.CustomerID, customer.CompanyName ); }

In this case, it might be shorter and more explicit to state your intention by using the Single operator. The previous query can be written in this more compact way:

var customer = db.Customers.Single( c => c.CustomerID == "ANATR" );

Console.WriteLine( "{0} {1}", customer.CustomerID, customer.CompanyName );

Projections

The transformation from an expression tree to an SQL query requires the complete understanding of the query operations sent to the LINQ to SQL engine. This transformation affects the use of object initializers. You can use projections through the select keyword, as in the following example:

var query = from c in Customers where c.Country == "USA" select

new {c.CustomerID, Name = c.CompanyName.ToUpper()} into r

orderby r.Name select r;

The whole LINQ query is translated into this SQL statement:

SELECT [t1].[CustomerID], [t1].[value] AS [Name] FROM ( SELECT [t0].[CustomerID],

UPPER([t0].[CompanyName])

AS [value], [t0].[Country] FROM [Customers] AS [t0] ) AS [t1] WHERE [t1].[Country] = "USA" ORDER BY [t1].[value]

As you can see, the ToUpper method has been translated into an UPPER T-SQL function call. To do that, the LINQ to SQL engine needs a deep knowledge of the meaning of any operation in the expression tree. Consider this query:

var queryBad = from c in Customers where c.Country == "USA"

select new CustomerData( c.CustomerID, c.CompanyName.ToUpper()) into r

orderby r.Name select r;

In this case, we call a constructor of the CustomerData type that can do anything a piece of Intermediate Language (IL) code can do. In other words, there is no semantic value in calling a constructor other than the initial assignment of the instance created. The consequence is that LINQ to SQL cannot correctly translate this syntax into an equivalent SQL code, and it throws an exception if you try to execute the query. However, you can safely use a parameterized constructor in the final projection of a query, as in the following sample:

var queryParamConstructor = from c in Customers where c.Country == "USA" orderby c.CompanyName

select new CustomerData( c.CustomerID, c.CompanyName.ToUpper() );

If you only need to initialize an object, use the object initializers instead of a parameterized constructor call, as in following query:

var queryGood = from c in Customers where c.Country == "USA" select

new CustomerData { CustomerID = c.CustomerID,


Name = c.CompanyName.ToUpper() } into r

orderby r.Name select r;

Stored Procedures

Accessing data through stored procedures and user-defined functions requires the definition of corresponding methods decorated with attributes. This enables you to write LINQ queries in a strongly typed form. From the LINQ point of view, there is no difference if a stored procedure or user-defined function is written in T-SQL or SQLCLR.

Consider the Customers by City stored procedure:

CREATE PROCEDURE [dbo].[

Customers By City

](

@param1 NVARCHAR(20)

) AS BEGIN SET NOCOUNT ON; SELECT CustomerID, ContactName, CompanyName, City FROM Customers AS c WHERE c.City =

@param1

END

You can define a method decorated with a StoredProcedure attribute that calls the stored procedure through the ExecuteMethodCall<T> method of the DataContext class. In Listing 5-12, we define CustomersByCity as a member of a class derived from DataContext .

Listing 5-12: Stored procedure declaration

class SampleDb : DataContext { // ...

[StoredProcedure( Name = "dbo.[Customers By City]" )]

public

IEnumerable<CustomerInfo>

CustomersByCity( string param1 ) { return (IEnumerable<CustomerInfo>) this.

ExecuteMethodCall<CustomerInfo>

( this, ((MethodInfo) (MethodInfo.GetCurrentMethod())), param1 ); } }

The returned type implements IEnumerable<CustomerInfo> and can be enumerated in a foreach statement like this one:

SampleDb db = new SampleDb( ConnectionString ); foreach( var row in db.CustomersByCity( "Seattle" )) { Console.WriteLine( "{0} {1}", row.CustomerID, row.CompanyName ); }

You will find many more details on stored procedure declarations in the product documentation. You can have output parameters, integer results, and multiple resultsets. You always need to know the metadata of all possible returned resultsets. Whenever you have multiple resultsets from a stored procedure, you will use the IMultipleResults return type, calling one GetResult<T> method for each resultset sequentially and specifying the right T type for the expected result. Consider the following stored procedure that returns two resultsets with different structures:

CREATE PROCEDURE TwoCustomerGroups AS BEGIN SELECT CustomerID, ContactName, CompanyName, City FROM Customers AS c WHERE c.City = 'London' SELECT CustomerID, CompanyName, City FROM Customers AS c WHERE c.City = 'Torino' END

The declaration of the LINQ counterpart should be like the one shown in Listing 5-13.

Listing 5-13: Stored procedure with multiple results

class SampleDb : DataContext { // ... [StoredProcedure(Name="TwoCustomerGroups")] public

IMultipleResults

TwoCustomerGroups() { return (IMultipleResults) this.ExecuteMethodCallWithMultipleResults( this, (MethodInfo) (MethodInfo.GetCurrentMethod()) ); } }

Each resultset has a different type. When calling each GetResult<T> , you need to specify the right type, which needs at least a public member with the same name of each returned column. If you specify a type with more public members than available columns, these “missing” members will have a default value. In the next sample, the first resultset must match the CustomerInfo type, while the second resultset must correspond to the CustomerShortInfo type:

IMultipleResults

results = db.TwoCustomerGroups(); foreach( var row in

results.GetResult<CustomerInfo>()

){ // Access to CustomerInfo instance } foreach( var row in

results.GetResult<CustomerShortInfo>())

{ // Access to CustomerShortInfo instance }

The declaration for CustomerInfo and CustomerShortInfo does not require any attribute. You can also use inheritance if the same structure is present in different resultsets, but remember that in this case the use is driven by GetResult usage and not by the InheritanceMapping attribute that you have seen in entity class declaration:

public class CustomerShortInfo { public string CustomerID; public string CompanyName; public string City; } public class CustomerInfo : CustomerShortInfo{ public string ContactName; }

User-Defined Functions

A user-defined function (UDF) needs the same kind of declaration you have just seen for stored procedures. A UDF can be used inside a LINQ query; thus, it must be considered from the LINQ to SQL engine in the SQL statement construction. Remember that there is no difference if a UDF is written in T-SQL or SQLCLR.

Listing 5-14 provides an example of a LINQ declaration of the scalar-valued UDF MinUnit PriceByCategory that is defined in the sample Northwind database.

Listing 5-14: Scalar-valued user-defined function

class SampleDb : DataContext { // ...

[Function(Name="dbo.MinUnitPriceByCategory")]

public

decimal

MinUnitPriceByCategory( int

categoryID

) { IExecuteResults mc = this.ExecuteMethodCall( this, (MethodInfo) (MethodInfo.GetCurrentMethod()), categoryID ); return (decimal)mc.ReturnValue; } }

The call of a UDF as an isolated expression generates a single SQL query invocation. You can also use a UDF in a LINQ query like the following one:

var query = from c in Categories select new { c.CategoryID, c.CategoryName,

MinPrice = db.MinUnitPriceByCategory( c.CategoryID )

};

The generated SQL query looks like this:

SELECT [t0].[CategoryID], [t0].[CategoryName],

dbo.MinUnitPriceByCategory([t0].[CategoryID])

AS [value] FROM [Categories] AS [t0]

A table-valued UDF has a different declaration that must match the returned type, as demonstrated in Listing 5-15.

Listing 5-15: Table-valued user-defined function

class SampleDb : DataContext { // ... [Function(Name="dbo.CustomersByCountry")] public

IQueryable<Customer>

CustomersByCountry( string country ) { return

(IQueryable<Customer>)

this.ExecuteMethodCall<Customer>( this, (MethodInfo) MethodInfo.GetCurrentMethod(), country ); } }

A table-valued UDF can be used like any other table in a LINQ query. For example, you can join customers returned by the previous UDF with the orders made by them, as in the following query:

Table<Order> Orders = db.GetTable<Order>(); var queryCustomers = from c in db.CustomersByCountry( "USA" ) join o in Orders on c.CustomerID equals o.CustomerID into orders select new { c.CustomerID, c.CompanyName, orders };

The generated SQL query will be similar to this one:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[City], [t0].[Region], [t0].[Country] FROM

dbo.CustomersByCountry('USA')

AS [t0]

Compiled Queries

If you need to repeat the same query many times, eventually with different argument values, you might be worried about the multiple query construction. Several databases, such as SQL Server, try to auto-parameterize received SQL queries to optimize the compilation of the query execution plan. However, the program that sends a parameterized query to SQL Server will get better performance, because SQL Server does not spend time to analyze it if the query is similar to another one already processed . LINQ already does a fine job of query optimization, but each time that the same query tree is evaluated, the LINQ to SQL engine parses the query tree to build the equivalent SQL code. You can optimize this behavior by using the CompiledQuery class.

To compile a query, you can use the CompiledQuery.Compile static method. This approach passes the LINQ query as a parameter in the form of a lambda expression, and then obtains a delegate with arguments corresponding to both the DataContext on which you want to operate and the parameters of the query. Listing 5-16 illustrates compiled query declaration and use.

Listing 5-16: Compiled query in a local scope

static void CompiledQueriesLocal() { DataContext db = new DataContext( ConnectionString ); Table<Customer> Customers = db.GetTable<Customer>(); var query =

CompiledQuery.Compile(


( DataContext context, string filterCountry ) =>

from c in Customers where c.Country == filterCountry select new { c.CustomerID, c.CompanyName, c.City } ); foreach (var row in

query( db, "USA" ))

{ Console.WriteLine( row ); } foreach (var row in

query( db, "Italy" )

){ Console.WriteLine( row ); } }

As you can see in the preceding example, the Compile method requires a lambda expression whose first argument is a DataContext instance. That argument defines the connection over which the query will be executed. Assigning the CompiledQuery.Compile result to a local variable is easy (because you declare that variable with var ) but probably not very frequent. Chances are that you need to store the delegate returned from CompiledQuery.Compile in an instance or a static member to easily reuse it several times. To do that, you need to know the right declaration syntax.

A compiled query is stored in a Func delegate, where the first argument must be an instance of DataContext (or a derived class) and the last argument must be the type returned from the query. You can define other arguments in the middle that will be arguments of the compiled query and will need to be specified for each compiled query invocation. Listing 5-17 shows the syntax you can use in this scenario to create the compiled query and then use it.

Listing 5-17: Compiled query assigned to a static member

public static Func< nwind.Northwind, string, IQueryable<nwind.Customer>>


CustomerByCountry =


CompiledQuery.Compile(

( nwind.Northwind db, string filterCountry ) => from c in db.Customers where c.Country == filterCountry select c ); static void CompiledQueriesStatic() { nwind.Northwind db = new nwind.Northwind( ConnectionString ); foreach (var row in CustomerByCountry( db, "USA" )) { Console.WriteLine( row.CustomerID ); } foreach (var row in CustomerByCountry( db, "Italy" )) { Console.WriteLine( row.CustomerID ); } }

Different Approaches to Querying Data

When using LINQ to SQL entities, you have two approaches to querying the same data. The classical way to navigate a relational schema is to write associative queries, just as you can do in SQL. The alternative way offered by LINQ to SQL is through graph traversal. Given the same result, we might obtain different SQL queries and a different level of performance.

Consider this SQL query that calculates the total quantity of orders for a product (in this case, Chocolade, which is a localized name in the Northwind database):

SELECT SUM( od.Quantity ) AS TotalQuantity FROM [Products] p LEFT JOIN [Order Details] od ON od.[ProductID] = p.[ProductID] WHERE p.ProductName = 'Chocolade' GROUP BY p.ProductName

The natural conversion into a LINQ query is shown in Listing 5-18. The Single operator gets the first row and puts it into quantityJoin , which is used to display the result.

Listing 5-18: Query with Join

var queryJoin = from p in db.Products

join o in db.Order_Details


on p.ProductID equals o.ProductID


into OrdersProduct

where p.ProductName == "Chocolade" select

OrdersProduct.Sum( o => o.Quantity );

var quantityJoin = queryJoin.Single(); Console.WriteLine( quantityJoin );

As you can see, the associative query in LINQ can explicitly require the join between Products and Order_Details through ProductID equivalency. By leveraging entities, you can implicitly use the relationship between Products and Order_Details defined in the Product class, as shown in Listing 5-19.

Listing 5-19: Query using Association

var queryAssociation = from p in db.Products where p.ProductName == "Chocolade" select

p.Order_Details.Sum( o => o.Quantity );

var quantityAssociation = queryAssociation.Single(); Console.WriteLine( quantityAssociation );

The single SQL queries produced by both of these LINQ queries are identical. The LINQ query with join is more explicit about the access to data, while the query that uses the association between Product and Order_Details is more implicit in this regard. Using implicit associations results in shorter queries that are less error-prone (because you cannot be wrong about the join condition). At first, you might find that a shorter query is not easier to read. However, this perception might arise because you are accustomed to seeing lengthier queries, and your comfort level with shorter ones could change over time.

Looking further, you can observe that reading a single product does not require a query expression. You can apply the Single operator directly on the Products table, as shown in Listing 5-20.

Listing 5-20: Access through Entity

var chocolade = db.Products.Single( p => p.ProductName == "Chocolade" ); var quantityValue = chocolade.Order_Details.Sum( o => o.Quantity ); Console.WriteLine( quantityValue );

This is a two-step operation that sends two SQL queries to the database. The first one retrieves the Product entity, and the second one accesses the Order Details table to calculate the total quantity for the required product. At first glance, this kind of access is shorter to write compared to a query, but its performance is worse . This conclusion is true if you consider the single operation isolated from any other aspect. It might be false in a real-world scenario.

The unique identity management of LINQ to SQL grants that a single instance of an entity exists in memory. If you know that a Product entity is already in memory for Chocolade when you need to calculate the total quantity of orders, or that the same Product entity will be used later for other purposes, this double access can be balanced by fewer instances of accessing the whole program. The previous queries did not create Product instances because only the total for the product was required as output. From this point of view, if we already had a Product instance for Chocolade in memory, the performance of queries would be worse because they make a useless join with Product just to transform the product name (Chocolade) into its corresponding ProductID .

A final thought on the number of generated queries: You might think that we generated two queries when accessing data through the Product entity because we had two distinct statements-one to assign the chocolade variable, and the other to assign a value to quantity Entity . This assumption is not completely true. Even if you write a single statement, the use of a Product entity (the results from the Single operator call) generates a separate query. Listing 5-21 produces the same results (in terms of memory objects and SQL queries) as Listing 5-20.

Listing 5-21: Access through Entity with a single statement

var quantityChocolade = db.Products.Single( p => p.ProductName == "Chang" ) .Order_Details.Sum( o => o.Quantity ); Console.WriteLine( quantityChocolade );

Finding a better way to access data really depends on the whole set of operations performed by a program. If you extensively use entities in your code to store data in memory, access to data through graph traversal based on entity access might offer better performance. On the other hand, if you always transform query results in anonymous types and never manipulate entities in memory, you might prefer an approach based on LINQ queries. As always, the right answer is, “It depends.”

Direct Queries

Sometime you might need access to some database SQL features that are not available with LINQ. For example, imagine that you want to use Common Table Expressions (CTE) or the PIVOT command with SQL Server. LINQ does not have an explicit constructor to do that, even if (in the future) its SQL Server provider could use these features to optimize some queries. Listing 5-22 shows how you can use the ExecuteQuery<T> method of the DataContext class to send a query directly to the database. The T in ExecuteQuery<T> is an entity class that represents a returned row.

Listing 5-22: Direct query

var query = db.

ExecuteQuery<EmployeeInfo>

( @" WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ReportsTo, HierarchyLevel) AS ( SELECT EmployeeID,LastName, FirstName, ReportsTo, 1 as HierarchyLevel FROM Employees WHERE ReportsTo IS NULL UNION ALL SELECT e.EmployeeID, e.LastName, e.FirstName, e.ReportsTo,eh.HierarchyLevel + 1 AS HierarchyLevel FROM Employees e INNER JOIN EmployeeHierarchy eh ON e.ReportsTo = eh.EmployeeID ) SELECT * FROM EmployeeHierarchy ORDER BY HierarchyLevel, LastName, FirstName" ); foreach (var row in query) { Console.WriteLine( row ); }

Read-Only DataContext Access

If you need to access data only in a read-only way, you might want to improve performance by disabling a DataContext service that supports data modification:

DataContext db = new DataContext( ConnectionString );

db.ObjectTracking = false;

var query = ...

The ObjectTracking property controls the change tracking service that we will describe in the next section.

Data Update

All entity instances are tracked by the identity management service of LINQ to SQL to keep a unique instance of a row of data. This service is guaranteed only for objects created or handled by DataContext . (This has implications that you will see shortly.) Keeping a single instance of a row of data allows the manipulation of in-memory objects without concern for potential data inconsistencies or duplication in memory. We will analyze how to deal with concurrent operations later.

Entity Updates

Changing data members and properties of an entity instance is an operation tracked by the change tracking service of LINQ to SQL. This service retains the original value of a modified entity. With this information, the service generates a corresponding list of SQL statements that make the same changes on the database. You can see these SQL statements by calling the GetChangeText method on DataContext :

var customer = db.Customers.Single( c => c.CustomerID == "FRANS" ); customer.ContactName = "Marco Russo"; Console.WriteLine(

db.GetChangeText()

);

The output from the previous code is similar to that shown here:

UPDATE [Customers] SET [ContactName] = "Marco Russo" FROM [Customers] WHERE ...

We will discuss the WHERE condition later. Remember that SQL statements in the list are not sent to the database until the call to the SubmitChanges method is made.

If you want to add a record to a table or remove a record from a table, creating or deleting an object in memory is not enough. The DataContext instance must be notified also. This can be done directly by calling Add or Remove on the corresponding Table collection (these methods operate on the in-memory copy of the data; a subsequent SubmitChanges call will forward the SQL commands to the database):

var newCustomer = new Customer { CustomerID = "DLEAP", CompanyName = "DevLeap", Country = "Italy" };

db.Customers.Add

( newCustomer ); var oldCustomer = db.Customers.Single( c => c.CustomerID == "FRANS" );

db.Customers.Remove

( oldCustomer );

Looking at the generated SQL statements, you will see that a single INSERT is generated to add a new customer, while the deletion of a customer might generate many UPDATE statements before the DELETE one. This is because of the need to “break” the relationship between a customer and its orders. Related orders are not deleted, but simply “disconnected” from the customer that was tied to them.

INSERT INTO [Customers](CustomerID, CompanyName, ...) VALUES("DEVLEAP", "DevLeap", ...) UPDATE [Orders] SET [CustomerID] = NULL FROM [Orders] WHERE ([OrderID] = @p1) AND ... -- Other UPDATE statements here, one for each Order of the removed customer DELETE FROM [Customers] WHERE [CustomerID] = "FRANS"

Another way to notify the DataContext of a new entity is to attach the new entity to an existing object already tracked by DataContext :

var newCustomer = new Customer { CustomerID = "DLEAP", CompanyName = "DevLeap", Country = "Italy" }; var order = db.Orders.Single( o => o.OrderID == 10248 );

order.Customer

= newCustomer;

The examples just shown introduced the need to understand how relationships between entities work when updates are applied to the database. Relationships are bidirectional between entities, and when an update is made on one side, the other side should be kept synchronized. This has to be done by the class entity implementation. Entity classes generated by code-generation tools (such as SQLMetal) usually offer this level of service.

The previous operation inserted a customer tied to order 10248. If you explore the new Customer entity after the order.Customer assignment, you will see that its Orders properties contain order 10248. Executing the following code will display one row containing the order 10248:

foreach( var o in newCustomer.Orders ) { Console.WriteLine( "{0}-{1}", o.CustomerID, o.OrderID ); }

You can work in the opposite way, assigning an order to the Orders properties of a customer. Consequently, the Customer property of the moved order will be updated.

var oldCustomer = db.Customers.Single( c => c.CustomerID == "VINET" ); var newCustomer = new Customer { CustomerID = "DLEAP", CompanyName = "DevLeap", Country = "Italy" }; db.Customers.Add( newCustomer ); var order = oldCustomer.Orders.Single( o => o.OrderID == 10248 );

oldCustomer.Orders.Remove( order )

;

newCustomer.Orders.Add( order );

You have seen that there are two ways to add a record to a table (one direct and one indirect). However, if you need to remove a row, you always have to do this in a direct way, calling the Remove method on the corresponding Table collection. When you remove an object, related entities are unbound (that is, the foreign key is set to NULL), but this might throw an exception if constraints do not allow NULL values. If you also want to remove the “child” objects of a removed object, you have to call the Remove method on them. You can do that by leveraging the RemoveAll method:

var order = db.Orders.Single( o => o.OrderID == 10248 );

db.Order_Details.RemoveAll( order.Order_Details )

; db.Orders.Remove( order );

This update at the moment of calling SubmitChanges will generate SQL statements that respect the referential integrity constraints shown in the following statements:

DELETE FROM [Order Details] WHERE ([OrderID] = 10248) AND ([ProductID] = 11) DELETE FROM [Order Details] WHERE ([OrderID] = 10248) AND ([ProductID] = 42) DELETE FROM [Order Details] WHERE ([OrderID] = 10248) AND ([ProductID] = 72) DELETE FROM [Orders] WHERE [OrderID] = 10248

After a call to SubmitChanges , all tracked changes history is thrown away.

Database Updates

With LINQ to SQL, many SQL queries are sent to the database in a transparent and implicit way. On the other hand, all SQL commands that modify the state of the database are sent only when you decide to do that, calling SubmitChanges on the DataContext object (which is eventually derived), as shown in Listing 5-23.

Listing 5-23: Submit changes to the database

Northwind db = new Northwind( Program.ConnectionString ); var customer = db.Customers.Single( c => c.CustomerID == "FRANS" ); customer.ContactName = "Marco Russo";

db.SubmitChanges();

Concurrent Operations

Operating with in-memory entities in LINQ is a form of disconnected operation on data. In these cases, you always have to deal with concurrent operations made by other users or connections between the read of data and its successive updates. Usually, you operate with optimistic concurrency. In the case of a conflict, a ChangeConflictException error is thrown by default. This exception contains a Conflicts collection that explains the reasons for the error. (There can be several conflicts on different tables in a single SubmitChanges call.) Listing 5-24 provides a demonstration.

Listing 5-24: Retry loop for a concurrency conflict

Northwind db2 = new Northwind( Program.ConnectionString ); for( int retry = 0; retry < 4; retry++ ) { var customer2 = db2.Customers.Single( c => c.CustomerID == "FRANS" );

// Another connection updates database table here

customer2.ContactName = "Paolo Pialorsi"; try { db2.SubmitChanges(); // May throw exception break; // Exit the "for" loop if submit succeed }

catch (ChangeConflictException ex) {

Console.WriteLine( ex.Message ); db2.Refresh( customer2, RefreshMode.KeepChanges ); } }

SubmitChanges can have a parameter specifying whether you want to stop at the first conflict or try all updates regardless of the conflict. The default is to stop at the first conflict:

db.SubmitChanges(ConflictMode.FailOnFirstConflict);

db.SubmitChanges(ConflictMode.ContinueOnConflict);

You can control how the concurrency conflict is determined through entity class definition. Each Column attribute can have an UpdateCheck argument that can have one of the following three values:

• Always   Always use this column (which is the default) for conflict detection.

• Never   Never use this column for conflict detection.

• WhenChanged   Use this column only when the member has been changed by the application.

Other options in column definitions are represented by two Boolean flags: IsDBGenerated identifies that the value is auto-generated by the database, and IsVersion identifies a database timestamp or a version number. If a column has IsVersion set to true , the concurrency conflict is identified and only the entity unique key and its timestamp/version column are compared.

Transactions

A SubmitChanges call automatically starts a database explicit transaction, using IDbConnection .BeginTransaction and applying all changes made in memory to the database inside the same transaction. Using the TransactionScope class contained in the System.Transactions library since .NET 2.0, you can add any standard command to the database or change any other transactional resource in the same transaction, which eventually will be transparently promoted to a distributed transaction. Listing 5-25 is an example of a transaction controlled in this way.

Listing 5-25: Transaction controlled by TransactionScope

using(TransactionScope ts = new TransactionScope()) {

Product prod = db.Products.Single(p => p.ProductID == 42); if (prod.UnitsInStock > 0) { prod.UnitsInStock--; } db.SubmitChanges();

ts.Complete();

}

In the case of an exception, the database transaction is canceled . If you have an existing ADO.NET application that does not use System.Transactions , you can control database transactions by accessing the Transaction property of DataContext .

Stored Procedures

You can override the default insert, update, and delete SQL statements generated by LINQ to SQL when submitting changes. To do that, you can define one or more methods with specific signatures and pattern names. This is the syntax to use-note that you need to replace the name of the modified type to TYPE :

public void UpdateTYPE(TYPE original, TYPE current) { ... } public void InsertTYPE(TYPE inserted) { ... } public void DeleteTYPE(TYPE deleted) { ... }

Usually, this particular override is used to call stored procedures instead of sending SQL statements to execute data manipulation on the database. These methods have to be defined on the DataContext derived class. Because a derived class is already generated by some tool (such as SQLMetal or the LINQ to SQL designer in Microsoft Visual Studio), you can add your methods by using the partial class syntax, as shown in Listing 5-26.

Listing 5-26: Stored procedure to override an update

public

partial

class Northwind : DataContext { public void UpdateProduct(Product original, Product current) { // Execute the stored procedure for UnitsInStock update if (original.UnitsInStock != current.UnitsInStock) { int rowCount = this.ExecuteCommand( "

exec

UpdateProductStock " + "@id={0}, @originalUnits={1}, @decrement={2}", original.ProductID, original.UnitsInStock, (original.UnitsInStock - current.UnitsInStock) ); if (rowCount < 1) { throw new OptimisticConcurrencyException(); } } } }

Binding Metadata

The mapping between LINQ to SQL entities and database structures has to be described through metadata information. Until now, you have seen attributes on entity definition fulfilling this rule. There is an alternative way to do this (using external XML mapping file), and there are tools and methods that automate the generation of entity classes starting from a database and vice versa.

Creating a Database from Entities

An application that auto- installs itself on a machine might be interested in creating a database that can persist its objects graph. This is a typical situation-you need to handle simple configurations that are represented by a graph of objects.

If you have a class derived from DataContext that contains entity definitions decorated with Table and Column attributes, you can create the corresponding database by calling the Create Database method. This method sends the necessary CREATE DATABASE statement as well as the subsequent CREATE TABLE and ALTER TABLE statements:

const string ConnectionString = "Database=Test_Northwind;Trusted_Connection=yes"; public static void Create() { Northwind db = new Northwind( ConnectionString );

db.CreateDatabase();

}

You can also drop a database and check for its existence. The name of the database is inferred from the connection string. You can duplicate a database schema into several databases simply by changing the connection string:

if (

db.DatabaseExists()

) {

db.DeleteDatabase();

// Send a DROP DATABASE } db.CreateDatabase();

Creating Entities from a Database

If you already have an existing physical data layer, you might want to create a set of entity classes for an existing database. You can use two available tools: SQLMetal and the LINQ to SQL designer integrated in Visual Studio.

SQLMetal is a command-line tool. It generates a source file containing entity class declarations based on metadata read from the specified database. This process can be split into two steps: First, an XML file describing the database structure is created. That XML file can be edited by adding or removing desired entities, constraints, or both. At the end, the XML file is used to generate source code files for entity classes. The following commands create the XML file from database metadata and the source code from the XML file, respectively:

SqlMetal /server:localhost /database:Northwind /

pluralize

/xml:Northwind.xml SqlMetal /namespace:northwind /code:Northwind.cs Northwind.xml

See the product documentation for details about SQLMetal and the related XML file. This same XML file can be used as a basis for the LINQ external mapping feature.

The LINQ to SQL designer integrated in Visual Studio allows the creation of LINQ entity classes in a more interactive way-you have a graphical editor that allows the design of a graphical schema of entities. To open the designer, use the Project / Add New Item command in Visual Studio 2005 and choose the Linq to SQL File template.

External Mapping

An XML file similar to the one created by the SQLMetal utility can be used as a mapping file to decorate entity classes instead of using attributes. The additions to the automatically generated file are a Type element and a Member attribute that bind the entity classes and columns to their respective mapping information. An XML mapping file will appear that looks like the following code sample:

<Database Name="Northwind"> <Table Name="Products">

<Type Name=" Product">

<Column Name="ProductID"

Member="ProductID"

Storage="_ProductID" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="True" IsDbGenerated="True" />

The XML file can be loaded using an XmlMappingSource instance generated by calling its FromXml static method. The following example shows how to use such syntax:

string path = "Northwind.xml"; XmlMappingSource prodMapping = XmlMappingSource.FromXml(File.ReadAllText(

path

)); Northwind db = new Northwind( @"Database=Test_Northwind;Trusted_Connection=yes", prodMapping );

One possible use of this technique is a scenario in which different databases must be mapped to a specific data model. Differences in databases might be table and field names (for example, localized versions of the database). In general, consider this option when you need to realize “light” decoupling of mapping between entity classes and the physical data structure of the database.