Standard Database Operations
While I will be covering the details of performing LINQ to SQL queries in detail in subsequent LINQ to SQL chapters, I want to give you a glimpse of how to perform the rudimentary database operations without the complexity of the complications. These examples are meant to merely demonstrate the basic concepts. As such, they will not include error checking or exception handling.
For example, since many of the basic operations I will discuss make changes to the database, those that make changes should detect and resolve concurrency conflicts. But, for the sake of simplicity, these examples will not demonstrate these principles. However, in Chapter 17, I will discuss concurrency conflict detection and resolution.
Inserts
Instantiating an entity class, such as the Customer class, is not enough to perform an insert into the database. An entity object must also either be inserted into a table collection of type Table<T>, where T is the type of the entity class stored in the table, or be added to an EntitySet<T> on an entity object already being tracked by the DataContext, where T is the type of an entity class.
The first step to insert a record into a database is to create a DataContext. That is the first step for every LINQ to SQL query. Second, an entity object is instantiated from an entity class. Third, that entity object is inserted into the appropriate table collection. And fourth, the SubmitChanges method is called on the DataContext.
Listing 14-1 contains an example of inserting a record into the database.
Example. Inserting a Record by Inserting an Entity Object into Table<T>
// 1. Create the DataContext.
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
// 2. Instantiate an entity object.
Customer cust =
new Customer
{
CustomerID = "LAWN",
CompanyName = "Lawn Wranglers",
ContactName = "Mr. Abe Henry",
ContactTitle = "Owner",
Address = "1017 Maple Leaf Way",
City = "Ft. Worth",
Region = "TX",
PostalCode = "76104",
Country = "USA",
Phone = "(800) MOW-LAWN",
Fax = "(800) MOW-LAWO"
};
// 3. Add the entity object to the Customers table.
db.Customers.InsertOnSubmit(cust);
// 4. Call the SubmitChanges method.
db.SubmitChanges();// 5. Query the record.
Customer customer = db.Customers.Where(c => c.CustomerID == "LAWN").First();
Console.WriteLine("{0} - {1}", customer.CompanyName, customer.ContactName);
// This part of the code merely resets the database so the example can be
// run more than once.
Console.WriteLine("Deleting the added customer LAWN.");
db.Customers.DeleteOnSubmit(cust);
db.SubmitChanges(); |
NOTE
In the Visual Studio 2008 Beta 2 release and earlier, the InsertOnSubmit method called in the preceding code was named Add and the DeleteOnSubmit method was named Remove.
There really isn't much to this example. First, I instantiate a Northwind object so that I have a DataContext for the Northwind database. Second, I instantiate a Customer object and populate it using the new object initialization feature of C# 3.0. Third, I insert the instantiated Customer object into the Customers table, of type Table<Customer>, in the Northwind DataContext class. Fourth, I call the SubmitChanges method to persist the newly created Customer object to the database. Fifth, I query the customer back out of the database just to prove it was inserted.
NOTE
If you run this example, a new record will be temporarily added to the Northwind Customers table for customer LAWN. Please notice that after the newly added record is queried and displayed, it is then deleted. I do this so that the example can be run more than once and so the newly inserted record does not affect subsequent examples. Any time one of my examples changes the database, the database needs to be returned to its original state so that no examples are impacted. If any example that modifies the database is unable to complete for some reason, you should manually reset the database to its original state.
Here are the results of Listing 14-1.
Lawn Wranglers - Mr. Abe Henry Deleting the added customer LAWN.
As you can see from the output, the inserted record was found in the database.
Alternatively, to insert a record into the database, we can add a new instance of an entity class to an already existing entity object being tracked by the DataContext object as demonstrated in Listing 14-2.
Example. Inserting a Record into the Northwind Database by Adding It to EntitySet<T>
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
Customer cust = (from c in db.Customers
where c.CustomerID == "LONEP"
select c).Single<Customer>();
// Used to query record back out.
DateTime now = DateTime.Now;Order order = new Order
{
CustomerID = cust.CustomerID,
EmployeeID = 4,
OrderDate = now,
RequiredDate = DateTime.Now.AddDays(7),
ShipVia = 3,
Freight = new Decimal(24.66),
ShipName = cust.CompanyName,
ShipAddress = cust.Address,
ShipCity = cust.City,
ShipRegion = cust.Region,
ShipPostalCode = cust.PostalCode,
ShipCountry = cust.Country
};
cust.Orders.Add(order);
db.SubmitChanges();
IEnumerable<Order> orders =
db.Orders.Where(o => o.CustomerID == "LONEP" && o.OrderDate.Value == now);
foreach (Order o in orders)
{
Console.WriteLine("{0} {1}", o.OrderDate, o.ShipName);
}
// This part of the code merely resets the database so the example can be
// run more than once.
db.Orders.DeleteOnSubmit(order);
db.SubmitChanges(); |
NOTE
In the Visual Studio 2008 Beta 2 release and earlier, the DeleteOnSubmit method called in the preceding code was named Remove. You may find it odd that in Listing 14-1 I call the InsertOnSubmit method, but in Listing 14-2, I call the Add method. This discrepancy is caused by the fact that those methods are called on two different object types. In Listing 14-1, the InsertOnSubmit method is called on an object of type Table<T>. In Listing 14-2, the Add method is called on an object of type EntitySet<T>.
In Listing 14-2, I created a Northwind DataContext, retrieved a customer, and added a newly constructed order entity object to the Orders EntitySet<Order> of the Customer entity object. I then queried for the new record and displayed it to the console.
NOTE
Again, please notice that at the end of the example I am deleting, via the DeleteOnSubmit method, the order record added to the Orders table. If the example code does not complete, you will need to manually delete this record to maintain the state of the database for the subsequent examples.
In case this example doesn't seem to be doing anything significantly different than Listing 14-1, in Listing 14-1, the inserted object, which was a Customer, was inserted into a variable of type Table<Customer>. In Listing 14-2, the inserted object, which is an Order, is added to a variable of type EntitySet<Order>.
Here are the results of Listing 14-2.
9/2/2007 6:02:16 PM Lonesome Pine Restaurant
This output proves that the order record was indeed inserted into the database.
Inserting Attached Entity Objects
One of the niceties of inserting records is that the DataContext detects any associated dependent entity class objects that are attached so that they will be persisted too when the SubmitChanges method is called. By dependent, I mean any entity class object containing a foreign key to the inserted entity class object. Listing 14-3 contains an example.
Example. Adding Attached Records
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
Customer cust =
new Customer {
CustomerID = "LAWN",
CompanyName = "Lawn Wranglers",
ContactName = "Mr. Abe Henry",
ContactTitle = "Owner",
Address = "1017 Maple Leaf Way",
City = "Ft. Worth",
Region = "TX",
PostalCode = "76104",
Country = "USA",
Phone = "(800) MOW-LAWN",
Fax = "(800) MOW-LAWO",
Orders = {
new Order {
CustomerID = "LAWN",
EmployeeID = 4,
OrderDate = DateTime.Now,
RequiredDate = DateTime.Now.AddDays(7),
ShipVia = 3,
Freight = new Decimal(24.66),
ShipName = "Lawn Wranglers",
ShipAddress = "1017 Maple Leaf Way",
ShipCity = "Ft. Worth",
ShipRegion = "TX",
ShipPostalCode = "76104",
ShipCountry = "USA"
}
}
};db.Customers.InsertOnSubmit(cust);
db.SubmitChanges();
Customer customer = db.Customers.Where(c => c.CustomerID == "LAWN").First();
Console.WriteLine("{0} - {1}", customer.CompanyName, customer.ContactName);
foreach (Order order in customer.Orders)
{
Console.WriteLine("{0} - {1}", order.CustomerID, order.OrderDate);
}
// This part of the code merely resets the database so the example can be
// run more than once.
db.Orders.DeleteOnSubmit(cust.Orders.First());
db.Customers.DeleteObSubmit(cust);
db.SubmitChanges(); |
NOTE
In the Visual Studio 2008 Beta 2 release and earlier, the InsertOnSubmit method called in the preceding code was named Add and the DeleteOnSubmit method was named Remove.
In Listing 14-3, I created a new Customer object with an assigned Orders collection containing one newly instantiated Order. Even though I only inserted the Customer object cust into the Customers table, and specifically, I do not add the orders to the Orders table, because the new Order is attached to the new Customer, the new Order will be persisted in the database when the SubmitChanges method is called.
There is one additional point I would like to make about this example. Please notice that, in the cleanup code at the end of Listing 14-3, I call the DeleteOnSubmit method for both the new Order and the new Customer. In this case, I only delete the first Order, but since the Customer was new, I know this is the only Order. It is necessary that I manually delete the orders, because while new attached associated entity objects are automatically inserted into the database when a parent entity object is inserted, the same is not true when deleting an entity object. Deleting a parent entity object will not cause any attached entity objects to automatically be deleted from the database. Had I not deleted the orders manually, an exception would have been thrown. I will discuss this in more detail in the "Deletes" section of this chapter.
Let's take a look at the output of Listing 14-3 by pressing Ctrl+F5.
Lawn Wranglers - Mr. Abe Henry LAWN - 9/2/2007 6:05:07 PM
From this output, you can see that the new Customer was indeed inserted into the database, albeit temporarily because of the database restoration code at the end of the example.
Queries
Performing LINQ to SQL queries is about like performing any other LINQ query with a few exceptions. I will cover the exceptions very shortly.
To perform a LINQ to SQL query, I need to first create a DataContext. Then I can perform the query on a table in that DataContext, as Listing 14-4 demonstrates.
Example. Performing a Simple LINQ to SQL Query on the Northwind Database
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind"); Customer cust = (from c in db.Customers where c.CustomerID == "LONEP" select c).Single<Customer>(); |
When that code is executed, the customer whose CustomerID is "LONEP" will be retrieved into the cust variable. You should be aware, though, as was mentioned in Chapter 5, that the Single standard query operator will throw an exception if the sequence it is called on contains no matching elements. So, using this code, you had better know that customer "LONEP" exists. In reality, the SingleOrDefault standard query operator provides better protection for the possibility of no record matching the where clause.
There are a couple additional points worth mentioning in this example. First, notice that the query is using C# syntax when comparing the CustomerID to "LONEP". This is evidenced by the fact that double quotes are used to contain the string "LONEP" as opposed to single quotes that SQL syntax requires. Also, the C# equality test operator, ==, is used instead of the SQL equality test operator, =. This demonstrates the fact that the query is indeed integrated into the language, since after all, this is what LINQ is named for: Language Integrated Query. Second, notice that I am mixing both query expression syntax and standard dot notation syntax in this query. The query expression syntax portion is contained within parentheses, and the Single operator is called using standard dot notation syntax.
Now, here is a question for you. I have discussed deferred query execution many times in the book so far. The question is, will just executing the preceding code cause the query to actually be performed? Don't forget to consider deferred query execution when selecting your answer. The answer is yes; the Single standard query operator will cause the query to actually execute. Had I left off that operator call and merely returned the query minus the call to the Single operator, the query would not have executed.
Listing 14-4 provides no screen output, so just for verification that the code does indeed retrieve the appropriate customer, Listing 14-5 is the same code, plus output to the console has been added to display the customer that is retrieved.
Example. Performing the Same Query with Console Output
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
Customer cust = (from c in db.Customers
where c.CustomerID == "LONEP"
select c).Single<Customer>();
Console.WriteLine("{0} - {1}", cust.CompanyName, cust.ContactName); |
Here is the output for Listing 14-5.
Lonesome Pine Restaurant - Fran Wilson
Exceptions to the Norm
Earlier I mentioned that LINQ to SQL queries are like typical LINQ queries with some exceptions. Now I will discuss the exceptions.
LINQ to SQL Queries Return an IQueryable<T>
While LINQ queries performed on arrays and collections return sequences of type IEnumerable<T>, a LINQ to SQL query that returns a sequence, returns a sequence of type IQueryable<T>. Listing 14-6 contains an example of a query returning a sequence of type IQueryable<T>.
Example. A Simple LINQ to SQL Query Returning an IQueryable<T> Sequence
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
IQueryable<Customer> custs = from c in db.Customers
where c.City == "London"
select c;
foreach(Customer cust in custs)
{
Console.WriteLine("Customer: {0}", cust.CompanyName);
} |
As you can see, the return type for this query is IQueryable<Customer>. Here are the results of Listing 14-6.
Customer: Around the Horn Customer: B's Beverages Customer: Consolidated Holdings Customer: Eastern Connection Customer: North/South Customer: Seven Seas Imports
However, as I stated in Chapter 12, since IQueryable<T> implements IEnumerable<T>, you can typically treat a sequence of type IQueryable<T> as though it were a sequence of type IEnumerable<T>. If you are trying to treat an IQueryable<T> sequence like an IEnumerable<T> sequence and you are having trouble, don't forget the AsEnumerable operator.
LINQ to SQL Queries Are Performed on Table<T> Objects
While most normal LINQ queries are performed on arrays or collections that implement the IEnumerable<T> or IEnumerable interfaces, a LINQ to SQL query is performed on classes that implement the IQueryable<T> interface, such as the Table<T> class.
This means that LINQ to SQL queries have additional query operators available, as well as the standard query operators, since IQueryable<T> implements IEnumerable<T>.
LINQ to SQL Queries Are Translated to SQL
As I discussed in Chapter 2, because LINQ to SQL queries return sequences of type IQueryable<T>, they are not compiled into .NET intermediate language code the way that normal LINQ queries are. Instead, they are converted into expression trees, which allows them to be evaluated as a single unit, and translated to appropriate and optimal SQL statements. Please read the section titled "SQL Translation" at the end of this chapter to learn more about the SQL translation that takes place with LINQ to SQL queries.
LINQ to SQL Queries Are Executed in the Database
Unlike normal LINQ queries which are executed in local machine memory, LINQ to SQL queries are translated to SQL calls and actually executed in the database. There are ramifications because of this, such as the way projections are handled, which cannot actually occur in the database since the database knows nothing about your entity classes, or any other classes for that matter.
Also, since the query actually executes in the database, and the database doesn't have access to your application code, what you can do in a query must be translated and is therefore limited in some ways based on the translator's capabilities. You can't just embed a call to a method you wrote in a lambda expression and expect SQL Server to know what to do with the call. Because of this, it is good to know what can be translated, what it will be translated to, and what happens when it cannot be translated.
Associations
Querying an associated class in LINQ to SQL is as simple as accessing a member variable of an entity class. This is because an associated class is a member variable of the related entity class or stored in a collection of entity classes, where the collection is a member variable of the related entity class. If the associated class is the many (child) side of a one-to-many relationship, the many class will be stored in a collection of the many classes, where the type of the collection is EntitySet<T>, and T is the type of the many entity class. This collection will be a member variable of the one class. If the associated class is the one (parent) side of a one-to-many relationship, a reference to the one class will be stored in a variable of type EntityRef<T>, where T is the type of the one class. This reference to the one class will be a member variable of the many class.
For example, consider the case of the Customer and Order entity classes that were generated for the Northwind database. A customer may have many orders, but an order can have but one customer. In this example, the Customer class is the one side of the one-to-many relationship between the Customer and Order entity classes. The Order class is the many side of the one-to-many relationship. Therefore, a Customer object's orders can be referenced by a member variable, typically named Orders, of type EntitySet<Order> in the Customer class. An Order object's customer can be referenced with a member variable, typically named Customer, of type EntityRef<Customer> in the Order class (see Figure 14-1).
A parent and child entity class association relationship
If you have a difficult time remembering which end of the relationship is stored in which variable type, just remember that a child has one parent, so therefore it is stored in a single reference. So, the child stores the associated parent in a variable of type EntityRef<T>. Since a parent can have many children, it needs to store the references to the children in a collection. So, the parent stores the references to the children in a variable of type EntitySet<T>.
Classes are associated by specifying the Association attribute on the class property that contains the reference to the associated class in the entity class definition. Since both the parent and child have a class property referencing the other, the Association attribute is specified in both the parent and child entity classes. I will discuss the Association attribute in depth in Chapter 15.
Listing 14-7 is an example where I query for certain customers and display the retrieved customers and each of their orders.
Example. Using an Association to Access Related Data
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
IQueryable<Customer> custs = from c in db.Customers
where c.Country == "UK" &&
c.City == "London"
orderby c.CustomerID
select c;
foreach (Customer cust in custs)
{
Console.WriteLine("{0} - {1}", cust.CompanyName, cust.ContactName);
foreach (Order order in cust.Orders)
{
Console.WriteLine(" {0} {1}", order.OrderID, order.OrderDate);
}
} |
As you can see, I enumerate through each customer, display the customer, enumerate through each customer's orders, and display them. I never even specified that I wanted orders in the query. Here are the truncated results for Listing 14-7.
Around the Horn - Thomas Hardy
10355 11/15/1996 12:00:00 AM
10383 12/16/1996 12:00:00 AM
10453 2/21/1997 12:00:00 AM
10558 6/4/1997 12:00:00 AM
10707 10/16/1997 12:00:00 AM
10741 11/14/1997 12:00:00 AM
10743 11/17/1997 12:00:00 AM
10768 12/8/1997 12:00:00 AM
10793 12/24/1997 12:00:00 AM
10864 2/2/1998 12:00:00 AM
10920 3/3/1998 12:00:00 AM
10953 3/16/1998 12:00:00 AM
11016 4/10/1998 12:00:00 AM
...
Consolidated Holdings - Elizabeth Brown
10435 2/4/1997 12:00:00 AM
10462 3/3/1997 12:00:00 AM
10848 1/23/1998 12:00:00 AM
...You may be thinking that is pretty cool. The orders are there, yet I never even explicitly queried for them. At this point, you might be thinking, isn't this terribly inefficient if I never access the customer's orders?
The answer is no. The reason is that the orders were not actually retrieved until they were referenced. Had the code not accessed the Orders property of the customer, they would have never been retrieved. This is known as deferred loading, which should not be confused with deferred query execution, which I have already discussed.
Deferred Loading
Deferred loading is the term used to describe the type of loading in which records are not actually loaded from the database until absolutely necessary, which is when they are first referenced; hence, the loading of the records is deferred.
In Listing 14-7, had I not referenced the Orders member variable, the orders would never have been retrieved from the database. That's pretty slick. For most situations, deferred loading is a good thing. It prevents needless queries from occurring and unnecessary data from eating up network bandwidth.
However, a problem can occur. Listing 14-8 is the same as Listing 14-7 except I have turned on the logging feature provided by the DataContext.Log object to reveal the problem.
Example. An Example Demonstrating Deferred Loading
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
IQueryable<Customer> custs = from c in db.Customers
where c.Country == "UK" &&
c.City == "London"
orderby c.CustomerID
select c;
// Turn on the logging.
db.Log = Console.Out;
foreach (Customer cust in custs)
{
Console.WriteLine("{0} - {1}", cust.CompanyName, cust.ContactName);
foreach (Order order in cust.Orders)
{
Console.WriteLine(" {0} {1}", order.OrderID, order.OrderDate);
}
} |
I will run the example by pressing Ctrl+F5. I am going to severely truncate the output:
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],
[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],
[t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[Country] = @p0) AND ([t0].[City] = @p1)
ORDER BY [t0].[CustomerID]
-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [UK]
-- @p1: Input String (Size = 6; Prec = 0; Scale = 0) [London]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1Around the Horn - Thomas Hardy SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry] FROM [dbo].[Orders] AS [t0] WHERE [t0].[CustomerID] = @p0 -- @p0: Input String (Size = 5; Prec = 0; Scale = 0) [AROUT] -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1 10355 11/15/1996 12:00:00 AM 10383 12/16/1996 12:00:00 AM 10453 2/21/1997 12:00:00 AM 10558 6/4/1997 12:00:00 AM 10707 10/16/1997 12:00:00 AM 10741 11/14/1997 12:00:00 AM 10743 11/17/1997 12:00:00 AM 10768 12/8/1997 12:00:00 AM 10793 12/24/1997 12:00:00 AM 10864 2/2/1998 12:00:00 AM 10920 3/3/1998 12:00:00 AM 10953 3/16/1998 12:00:00 AM 11016 4/10/1998 12:00:00 AM B's Beverages - Victoria Ashworth SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry] FROM [dbo].[Orders] AS [t0] WHERE [t0].[CustomerID] = @p0 -- @p0: Input String (Size = 5; Prec = 0; Scale = 0) [BSBEV] -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1 10289 8/26/1996 12:00:00 AM 10471 3/11/1997 12:00:00 AM 10484 3/24/1997 12:00:00 AM 10538 5/15/1997 12:00:00 AM 10539 5/16/1997 12:00:00 AM 10578 6/24/1997 12:00:00 AM 10599 7/15/1997 12:00:00 AM 10943 3/11/1998 12:00:00 AM 10947 3/13/1998 12:00:00 AM 11023 4/14/1998 12:00:00 AM Consolidated Holdings - Elizabeth Brown ...
I have marked the SQL queries in bold to make them stand out from the customer and order output data. In the first SQL query, you can see that a query is created to query the customers, and you can see that nothing in the query is querying the orders table. Then you can see that the company name and contact name for the first company are displayed, and then another SQL query is output. In that second SQL query, you can see that the Orders table is queried with a specific customer's CustomerID in the where clause. So, a query is generated and executed just for the specific customer that I just displayed to the console. Next, you will see a list of orders displayed for that previously listed customer, followed by the next customer. Next, another SQL query appears for a specific customer's orders.
As you can see, a separate query is performed to retrieve each customer's orders. The orders are not queried, and therefore not loaded, until the Orders EntityRef<T> variable is referenced in the second foreach loop, which is immediately after the customer information is displayed to the console. Because the orders are not retrieved until they are referenced, their loading is deferred.
Since a separate query is generated and performed for each customer, potentially, a lot of SQL queries will be going back and forth to the database. This could be a performance problem.
Because I wrote the code, I know that I am going to access the orders for the customers I retrieve. In this case, it may provide better performance if I could retrieve the orders when I retrieve the customers. What I need is immediate loading.
Immediate Loading with the DataLoadOptions Class
While deferred loading is the default behavior for associated classes, we do have the ability to perform immediate loading. Immediate loading causes associated classes to be loaded prior to being referenced. This may provide performance benefits. We can use the DataLoadOptions class's LoadWith<T> operator to instruct the DataContext to immediately load the associated class specified in the LoadWith<T> operator's lambda expression. By using the LoadWith<T> operator, when the query is actually executed, not only will the primary class be retrieved, so will the specified associated class.
In Listing 14-9, I will use the same basic example code as in Listing 14-8 except I will instantiate a DataLoadOptions object, call the LoadWith<T> operator on that DataLoadOptions object, passing the Orders member as a class to immediately load when a Customer object is loaded, and assign the DataLoadOptions object to the Northwind DataContext. Also, to eliminate any doubt that the associated classes, the orders, are being loaded prior to being referenced, I will omit the code that enumerates through the customer's orders, so there will be no reference to them.
Example. An Example Demonstrating Immediate Loading Using the DataLoadOptions Class
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind"); DataLoadOptions dlo = new DataLoadOptions(); dlo.LoadWith<Customer>(c => c.Orders); db.LoadOptions = dlo; IQueryable<Customer> custs = (from c in db.Customers where c.Country == "UK" && c.City == "London" orderby c.CustomerID select c); // Turn on the logging. db.Log = Console.Out; foreach (Customer cust in custs) { Console.WriteLine("{0} - {1}", cust.CompanyName, cust.ContactName); } |
Again, the only differences between this listing and Listing 14-8 are the instantiation of the DataLoadOptions object, the call to the LoadWith<T> operator, the assignment of the DataLoadOptions object to the Northwind DataContext, and the removal of any reference to each customer's orders. In the call to the LoadWith<T> operator, I instruct the DataLoadOptions to immediately load Orders whenever a Customer object is loaded. Now, let's take a look at the output of Listing 14-9.
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],
[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],
[t0].[Country], [t0].[Phone], [t0].[Fax], [t1].[OrderID], [t1].[CustomerID] AS
[CustomerID2], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate],
[t1].[ShippedDate], [t1].[ShipVia], [t1].[Freight], [t1].[ShipName],
[t1].[ShipAddress], [t1].[ShipCity], [t1].[ShipRegion], [t1].[ShipPostalCode],
[t1].[ShipCountry], (
SELECT COUNT(*)
FROM [dbo].[Orders] AS [t2]
WHERE [t2].[CustomerID] = [t0].[CustomerID]
) AS [count]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
WHERE ([t0].[Country] = @p0) AND ([t0].[City] = @p1)
ORDER BY [t0].[CustomerID], [t1].[OrderID]
-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [UK]
-- @p1: Input String (Size = 6; Prec = 0; Scale = 0) [London]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
Around the Horn - Thomas Hardy
B's Beverages - Victoria Ashworth
Consolidated Holdings - Elizabeth Brown
Eastern Connection - Ann Devon
North/South - Simon Crowther
Seven Seas Imports - Hari KumarJust like in the output of Listing 14-8, in the output for Listing 14-9, I have marked the SQL queries with bold type. I am really not interested in the output of the customers; I want to see the SQL queries that were executed.
As you can see, a single SQL query was executed to retrieve all the customers matching my query's where clause. You can also see that, despite the fact that I never even referenced a customer's orders, the single SQL query joined each customer retrieved with that customer's orders. Since the orders were loaded prior to being referenced, their loading was not deferred and therefore is considered to be immediate. Instead of having a number of SQL queries equal to one (for the customers), plus the number of customers (for each customer's orders), there is a single SQL query. If there are a lot of customers, this can make a huge difference.
Using the DataLoadOptions class, you are not limited to the immediate loading of a single associated class or a single hierarchical level of class. However, immediately loading more than one associated class does affect the way immediate loading works.
When Immediate Loading Is Not So Immediate
When classes are not loaded until they are referenced, their loading is said to be deferred. If they are loaded prior to being referenced, their loading is said to be immediate. However, sometimes, immediate is not as immediate as other times.
With the code in Listing 14-9, we saw that, by specifying an associated class as the argument to the DataLoadOptions class's LoadWith<T> method, we could get immediate loading to cause the orders to be loaded along with the customers. Not only is this not deferred, since it was prior to them being referenced, it was indeed immediate. If we call the LoadWith<T> method multiple times to have multiple classes loaded immediately, only one of the classes will be joined with the original entity class, and the others will be loaded upon referencing that original entity class. When this happens, since the associated classes not joined with the original entity class are still loaded prior to being referenced, they are still considered immediately loaded, but a separate query is still made for them as you reference each original entity class. In this way, while their loading is still considered to be immediate, it feels less immediate than when they are joined.
The decision as to which associated classes should be joined versus which should just be loaded prior to being referenced is made by LINQ to SQL. It is an optimized decision based on general principles applied to your entity class model, though; it is not an optimization made by the database. It will join the association lowest in the hierarchy of the immediately loaded classes. This will be more easily understood when I get to the section about immediately loading a hierarchy of associated classes.
To better understand this behavior, I will discuss this for each approach where more than one association is immediately loaded. The two approaches are loading multiple associated classes of the original entity class and loading a hierarchy of associated classes.
Immediate Loading of Multiple Associated Classes
With the DataLoadOptions class, it is possible to instruct it to immediately load multiple associated classes of an entity class.
Notice that in Listing 14-9, the generated SQL query made no reference to the customer's associated customer demographics. Had I referenced the customer demographics on the retrieved customers, additional SQL statements would have been executed for each customer whose customer demographics were referenced.
In Listing 14-10, I will instruct the DataLoadOptions to immediately load the customer's customer demographics as well as its orders.
Example. Immediately Loading Multiple EntitySets
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Customer>(c => c.Orders);
dlo.LoadWith<Customer>(c => c.CustomerCustomerDemos);
db.LoadOptions = dlo;
IQueryable<Customer> custs = (from c in db.Customers
where c.Country == "UK" &&
c.City == "London"
orderby c.CustomerID
select c);
// Turn on the logging.
db.Log = Console.Out;
foreach (Customer cust in custs)
{
Console.WriteLine("{0} - {1}", cust.CompanyName, cust.ContactName);
} |
In Listing 14-10, I am not only specifying to immediately load the orders but to immediately load the customer's customer demographics as well. Notice that nowhere am I actually referencing either. So, any loading of these associated classes is immediate as opposed to deferred. I am really not interested in the returned data so much as the executed SQL statements. Let's examine the output of Listing 14-10.
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],
[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],
[t0].[Country], [t0].[Phone], [t0].[Fax], [t1].[CustomerID] AS [CustomerID2],
[t1].[CustomerTypeID], (
SELECT COUNT(*)
FROM [dbo].[CustomerCustomerDemo] AS [t2]
WHERE [t2].[CustomerID] = [t0].[CustomerID]
) AS [count]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[CustomerCustomerDemo] AS [t1] ON [t1].[CustomerID] =
[t0].[CustomerID]
WHERE ([t0].[Country] = @p0) AND ([t0].[City] = @p1)
ORDER BY [t0].[CustomerID], [t1].[CustomerTypeID]
-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [UK]
-- @p1: Input String (Size = 6; Prec = 0; Scale = 0) [London]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate],
[t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight],
[t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion],
[t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[CustomerID] = @x1
-- @x1: Input StringFixedLength (Size = 5; Prec = 0; Scale = 0) [AROUT]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
Around the Horn - Thomas Hardy
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate],
[t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight],
[t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion],
[t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[CustomerID] = @x1
-- @x1: Input StringFixedLength (Size = 5; Prec = 0; Scale = 0) [BSBEV]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
B's Beverages - Victoria Ashworth
...As you can see in the SQL queries that were generated, the customer demographics were joined with the customers when they were queried, but a separate SQL query was generated to load each customer's orders. That separate query for orders was performed when each customer was actually referenced, which is in the foreach statement. Notice that in the output, the query for the orders of a customer is output before the customer information is displayed to the console.
Since neither the customer demographics nor the orders are referenced in the code, other than when calling the LoadWith<T> method, since they were in fact loaded, the loading is not deferred and is therefore immediate. However, it certainly feels like the customer demographics are a little more immediate than the orders.
Immediate Loading of Hierarchical Associated Classes
In the previous section, I discussed how to cause multiple associated entity classes to be immediately loaded. In this section, I will discuss how to cause a hierarchy of associated entity classes to be immediately loaded. To demonstrate this, in Listing 14-11, I will make the query not only immediately load the orders but each order's order details.
Example. Immediate Loading of a Hierarchy of Entity Classes
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Customer>(c => c.Orders);
dlo.LoadWith<Order>(o => o.OrderDetails);
db.LoadOptions = dlo;
IQueryable<Customer> custs = (from c in db.Customers
where c.Country == "UK" &&
c.City == "London"
orderby c.CustomerID
select c);
// Turn on the logging.
db.Log = Console.Out;
foreach (Customer cust in custs)
{
Console.WriteLine("{0} - {1}", cust.CompanyName, cust.ContactName);
foreach (Order order in cust.Orders)
{
Console.WriteLine(" {0} {1}", order.OrderID, order.OrderDate);
}
} |
Notice that, in Listing 14-11, I am immediately loading the customer's orders, and for each order, I am immediately loading its order details. Here is the output for Listing 14-11.
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],
[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],
[t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[Country] = @p0) AND ([t0].[City] = @p1)
ORDER BY [t0].[CustomerID]
-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [UK]
-- @p1: Input String (Size = 6; Prec = 0; Scale = 0) [London]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate],
[t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight],
[t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion],
[t0].[ShipPostalCode], [t0].[ShipCountry], [t1].[OrderID] AS [OrderID2],
[t1].[ProductID], [t1].[UnitPrice], [t1].[Quantity], [t1].[Discount], (
SELECT COUNT(*)
FROM [dbo].[Order Details] AS [t2]
WHERE [t2].[OrderID] = [t0].[OrderID]
) AS [count]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN [dbo].[Order Details] AS [t1] ON [t1].[OrderID] = [t0].[OrderID]
WHERE [t0].[CustomerID] = @x1
ORDER BY [t0].[OrderID], [t1].[ProductID]
-- @x1: Input StringFixedLength (Size = 5; Prec = 0; Scale = 0) [AROUT]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
Around the Horn - Thomas Hardy
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate],
[t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight],
[t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion],
[t0].[ShipPostalCode], [t0].[ShipCountry], [t1].[OrderID] AS [OrderID2],
[t1].[ProductID], [t1].[UnitPrice], [t1].[Quantity], [t1].[Discount], (
SELECT COUNT(*)
FROM [dbo].[Order Details] AS [t2]
WHERE [t2].[OrderID] = [t0].[OrderID]
) AS [count]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN [dbo].[Order Details] AS [t1] ON [t1].[OrderID] = [t0].[OrderID]
WHERE [t0].[CustomerID] = @x1
ORDER BY [t0].[OrderID], [t1].[ProductID]
-- @x1: Input StringFixedLength (Size = 5; Prec = 0; Scale = 0) [BSBEV]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
B's Beverages - Victoria Ashworth
...Again, I am not interested in the retrieved data, merely the executed SQL statements. Notice that this time, the query for the customers joined neither the orders nor the order details. Instead, as each customer was referenced, an additional SQL query was made that joined the orders and order details. Since neither was referenced, they were still loaded prior to being referenced and are still considered to be immediately loaded.
From this example, you can see that LINQ to SQL does perform the single join for the association at the lowest level in the hierarchy of the immediately loaded files, as I previously mentioned.
Filtering and Ordering
While I am discussing the DataLoadOptions class, I want you to be aware of its AssociateWith method, which can be used to both filter associated child objects and order them.
In Listing 14-8, I retrieve some customers and enumerate through them displaying the customer and its orders. You can see in the results that the orders' dates are in ascending order. To demonstrate how the AssociateWith method can be used to both filter associated classes and order them, in Listing 14-12 I will do both.
Example. Using the DataLoadOptions Class to Filter and Order
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind"); DataLoadOptions dlo = new DataLoadOptions(); dlo.AssociateWith<Customer>(c => from o in c.Orders where o.OrderID < 10700 orderby o.OrderDate descending select o); db.LoadOptions = dlo; IQueryable<Customer> custs = from c in db.Customers
where c.Country == "UK" &&
c.City == "London"
orderby c.CustomerID
select c;
foreach (Customer cust in custs)
{
Console.WriteLine("{0} - {1}", cust.CompanyName, cust.ContactName);
foreach (Order order in cust.Orders)
{
Console.WriteLine(" {0} {1}", order.OrderID, order.OrderDate);
}
} |
Notice that in Listing 14-12, I embed a query for the lambda expression passed to the AssociateWith method. In that query, I filter out all records where the OrderID is not less than 10700, and I sort the orders by OrderDate in descending order. Let's examine the results of Listing 14-12.
Around the Horn - Thomas Hardy
10558 6/4/1997 12:00:00 AM
10453 2/21/1997 12:00:00 AM
10383 12/16/1996 12:00:00 AM
10355 11/15/1996 12:00:00 AM
B's Beverages - Victoria Ashworth
10599 7/15/1997 12:00:00 AM
10578 6/24/1997 12:00:00 AM
10539 5/16/1997 12:00:00 AM
10538 5/15/1997 12:00:00 AM
10484 3/24/1997 12:00:00 AM
10471 3/11/1997 12:00:00 AM
10289 8/26/1996 12:00:00 AM
Consolidated Holdings - Elizabeth Brown
10462 3/3/1997 12:00:00 AM
10435 2/4/1997 12:00:00 AM
Eastern Connection - Ann Devon
10532 5/9/1997 12:00:00 AM
10400 1/1/1997 12:00:00 AM
10364 11/26/1996 12:00:00 AM
North/South - Simon Crowther
10517 4/24/1997 12:00:00 AM
Seven Seas Imports - Hari Kumar
10547 5/23/1997 12:00:00 AM
10523 5/1/1997 12:00:00 AM
10472 3/12/1997 12:00:00 AM
10388 12/19/1996 12:00:00 AM
10377 12/9/1996 12:00:00 AM
10359 11/21/1996 12:00:00 AMAs you can see in the preceding results, only the orders whose OrderID is less than 10700 are returned, and they are returned in descending order by date.
Coincidental Joins
One of the benefits of associations is that they are, in effect, performing joins for us automatically. When we query customers from the Northwind database, each customer has a collection of orders that is accessible via the Customer object's Orders property. So, retrieving orders for customers is automatic. Normally, you would have to perform a join to get that type of behavior. The reverse is also true. When we retrieve orders, the Order class has a Customer property that references the appropriate customer.
While we have this automatic join happening, it is, as the late painter Bob Ross would say, merely a happy little accident. The join happens because when we have an object, say a child object, that has a relationship to another object, say a parent object, we expect to be able to access it via a reference in the initial, child object.
For example, when working with XML, when we have a reference to a node, we expect to be able to obtain a reference to its parent by the child node having a member variable that references the parent. We don't expect to have to perform a query on the entire XML structure and provide the child node as a search key. Also, when we have a reference to a node, we expect to be able to access its children with a reference on the node itself as well.
So, while the automatic join is certainly convenient, the implementation has more to do with the nature of object relationships, and our expectations of how they should behave, than an intentional effort to make joins happen automatically. In this way, the joins are really coincidental.
Joins
I just discussed the fact that many relationships in the database are specified to be associations, and that we can access the associated objects by simply accessing a class member. However, only those relationships that are defined using foreign keys will get mapped this way. Since every type of relationship is not defined using foreign keys, you will sometimes need to explicitly join tables.
Inner Joins
We can perform an inner equijoin by using the join operator. As is typical with an inner join, any records in the outer results set will be omitted if a matching record does not exist in the inner results set. Listing 14-13 contains an example.
Example. Performing an Inner Join
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
var entities = from s in db.Suppliers
join c in db.Customers on s.City equals c.City
select new
{
SupplierName = s.CompanyName,
CustomerName = c.CompanyName,
City = c.City
};
foreach (var e in entities)
{
Console.WriteLine("{0}: {1} - {2}", e.City, e.SupplierName, e.CustomerName);
} |
In Listing 14-13, I performed an inner join on the suppliers and the customers. If a customer record doesn't exist with the same city as a supplier, the supplier record will be omitted from the results set. Here are the results of Listing 14-13.
London: Exotic Liquids - Around the Horn London: Exotic Liquids - B's Beverages London: Exotic Liquids - Consolidated Holdings London: Exotic Liquids - Eastern Connection London: Exotic Liquids - North/South London: Exotic Liquids - Seven Seas Imports Sao Paulo: Refrescos Americanas LTDA - Comércio Mineiro Sao Paulo: Refrescos Americanas LTDA - Familia Arquibaldo Sao Paulo: Refrescos Americanas LTDA - Queen Cozinha Sao Paulo: Refrescos Americanas LTDA - Tradiçao Hipermercados Berlin: Heli Süßwaren GmbH & Co. KG - Alfred Futterkiste Paris: Aux joyeux ecclésiastiques - Paris spécialités Paris: Aux joyeux ecclésiastiques - Spécialités du monde Montréal: Ma Maison - Mère Paillarde
As you can see, despite the fact that some suppliers are in the output with multiple matching customers, some suppliers are not in the list at all. This is because there were no customers in the same city as the missing suppliers. If we need to still see the supplier regardless of whether there is a matching customer or not, we need to perform an outer join.
Outer Joins
In Chapter 4, I discussed the DefaultIfEmpty standard query operator and mention that it can be used to perform outer joins. In Listing 14-14, I will use the into clause to direct the matching join results into a temporary sequence that I will subsequently call the DefaultIfEmpty operator on. This way, if the record is missing from the joined results, a default value will be provided. I will use the DataContext logging feature so we can see the generated SQL statement.
Example. Performing an Outer Join
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind"); db.Log = Console.Out; var entities = from s in db.Suppliers join c in db.Customers on s.City equals c.City into temp from t in temp.DefaultIfEmpty() select new { SupplierName = s.CompanyName, CustomerName = t.CompanyName, City = s.City }; foreach (var e in entities) { Console.WriteLine("{0}: {1} - {2}", e.City, e.SupplierName, e.CustomerName); } |
Notice that in the join statement in Listing 14-14, I direct the join results into the temporary sequence named temp. That temporary sequence name can be whatever you want, as long as it doesn't conflict with any other name or keyword. Then I perform a subsequent query on the results of the temp sequence passed to the DefaultIfEmpty operator. Even though I haven't covered it yet, the DefaultIfEmpty operator called in Listing 14-14 is not the same operator that was discussed in Chapter 4. As I will explain shortly, LINQ to SQL queries are translated into SQL statements, and those SQL statements are executed by the database. SQL Server has no way to call the DefaultIfEmpty standard query operator. Instead, that operator call will be translated into the appropriate SQL statement. This is why I wanted the DataContext logging to be enabled.
Also, notice that I access the city name from the Suppliers table instead of the temp collection. I did this because I know there will always be a record for the supplier, but for suppliers without a matching customer, there will be no city in the joined results in the temp collection. This is different than the previous example of the inner join where I obtained the city from the joined table. In that example, it didn't matter which of the tables I got the city from, because if a matching customer record didn't exist, there would be no record anyway since an inner join was performed.
Let's look at the results of Listing 14-14.
SELECT [t0].[CompanyName], [t1].[CompanyName] AS [value], [t0].[City] FROM [dbo].[Suppliers] AS [t0] LEFT OUTER JOIN [dbo].[Customers] AS [t1] ON [t0].[City] = [t1].[City] -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1 London: Exotic Liquids - Around the Horn London: Exotic Liquids - B's Beverages London: Exotic Liquids - Consolidated Holdings London: Exotic Liquids - Eastern Connection London: Exotic Liquids - North/South London: Exotic Liquids - Seven Seas Imports New Orleans: New Orleans Cajun Delights - Ann Arbor: Grandma Kelly's Homestead - Tokyo: Tokyo Traders - Oviedo: Cooperativa de Quesos 'Las Cabras' - Osaka: Mayumi's - Melbourne: Pavlova, Ltd. - Manchester: Specialty Biscuits, Ltd. - Göteborg: PB Knäckebröd AB - Sao Paulo: Refrescos Americanas LTDA - Comércio Mineiro Sao Paulo: Refrescos Americanas LTDA - Familia Arquibaldo Sao Paulo: Refrescos Americanas LTDA - Queen Cozinha Sao Paulo: Refrescos Americanas LTDA - Tradiçao Hipermercados Berlin: Heli Süßwaren GmbH & Co. KG - Alfreds Futterkiste Frankfurt: Plutzer Lebensmittelgroßmärkte AG - Cuxhaven: Nord-Ost-Fisch Handelsgesellschaft mbH - Ravenna: Formaggi Fortini s.r.l. - Sandvika: Norske Meierier - Bend: Bigfoot Breweries - Stockholm: Svensk Sjöföda AB - Paris: Aux joyeux ecclésiastiques - Paris spécialités Paris: Aux joyeux ecclésiastiques - Spécialités du monde Boston: New England Seafood Cannery - Singapore: Leka Trading - Lyngby: Lyngbysild - Zaandam: Zaanse Snoepfabriek - Lappeenranta: Karkki Oy - Sydney: G'day, Mate - Montréal: Ma Maison - Mère Paillarde Salerno: Pasta Buttini s.r.l. - Montceau: Escargots Nouveaux - Annecy: Gai pâturage - Ste-Hyacinthe: Forêts d'érables -
As you can see in the output of Listing 14-14, I got at least one record for every supplier, and you can see that some suppliers do not have a matching customer, thereby proving the outer join was performed. But, if there is any doubt, you can see the actual generated SQL statement and that clearly is performing an outer join.
To Flatten or Not to Flatten
In the examples in Listing 14-13 and Listing 14-14, I projected my query results into a flat structure. By this, I mean an object was created from an anonymous class where each field requested is a member of that anonymous class. Contrast this with the fact that, instead of creating a single anonymous class containing each field I wanted, I could have created an anonymous class composed of a Supplier object and matching Customer object. In that case, there would be the topmost level of the anonymous class, and a lower level containing a Supplier object and either a Customer object or the default value provided by the DefaultIfEmpty operator, which would be null.
If I take the flat approach, as I did in the two previous examples, because the projected output class is not an entity class, I will not be able to perform updates to the output objects by having the DataContext object manage persisting the changes to the database for me. This is fine for data that will not be changed. However, sometimes you may be planning on allowing updates to the retrieved data. In this case, using the nonflat approach would allow you to make changes to the retrieved objects and have the DataContext object manage the persistence. I will cover this in more depth in Chapter 16. For now, let's just take a look at Listing 14-15, which contains an example that isn't flat.
Example. Returning Nonflat Results so the DataContext Can Manage Persistence
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
var entities = from s in db.Suppliers
join c in db.Customers on s.City equals c.City into temp
from t in temp.DefaultIfEmpty()
select new { s, t };
foreach (var e in entities)
{
Console.WriteLine("{0}: {1} - {2}", e.s.City,
e.s.CompanyName,
e.t != null ? e.t.CompanyName : "");
} |
In Listing 14-15, instead of returning the query results into a flat anonymous object with a member for each desired field, I return the query results in an anonymous object composed of the Supplier and potentially Customer entity objects. Also notice that in the Console.WriteLine method call, I still have to be concerned that the temporary result can be a null if no matching Customer object exists. Let's take a look at the results of Listing 14-15.
London: Exotic Liquids - Around the Horn London: Exotic Liquids - B's Beverages London: Exotic Liquids - Consolidated Holdings London: Exotic Liquids - Eastern Connection London: Exotic Liquids - North/South London: Exotic Liquids - Seven Seas Imports New Orleans: New Orleans Cajun Delights - Ann Arbor: Grandma Kelly's Homestead - Tokyo: Tokyo Traders - Oviedo: Cooperativa de Quesos 'Las Cabras' - Osaka: Mayumi's - Melbourne: Pavlova, Ltd. - Manchester: Specialty Biscuits, Ltd. - Göteborg: PB Knäckebröd AB - Sao Paulo: Refrescos Americanas LTDA - Comércio Mineiro Sao Paulo: Refrescos Americanas LTDA - Familia Arquibaldo Sao Paulo: Refrescos Americanas LTDA - Queen Cozinha Sao Paulo: Refrescos Americanas LTDA - Tradiçao Hipermercados Berlin: Heli Süßwaren GmbH & Co. KG - Alfreds Futterkiste Frankfurt: Plutzer Lebensmittelgroßmärkte AG - Cuxhaven: Nord-Ost-Fisch Handelsgesellschaft mbH - Ravenna: Formaggi Fortini s.r.l. - Sandvika: Norske Meierier - Bend: Bigfoot Breweries - Stockholm: Svensk Sjöföda AB - Paris: Aux joyeux ecclésiastiques - Paris spécialités Paris: Aux joyeux ecclésiastiques - Spécialités du monde Boston: New England Seafood Cannery - Singapore: Leka Trading - Lyngby: Lyngbysild - Zaandam: Zaanse Snoepfabriek - Lappeenranta: Karkki Oy - Sydney: G'day, Mate - Montréal: Ma Maison - Mère Paillarde Salerno: Pasta Buttini s.r.l. - Montceau: Escargots Nouveaux - Annecy: Gai pâturage - Ste-Hyacinthe: Forêts d'érables -
In the output for Listing 14-15, you can see that some suppliers do not have customers in their cities. Unlike the sequence of anonymous objects returned by the query in Listing 14-14, the anonymous objects returned by the query in Listing 14-15 contain entity objects of type Supplier and Customer. Because these are entity objects, I can take advantage of the services provided by the DataContext to manage the changes to them, and their persistence to the database.
Deferred Query Execution
I know by now you have probably read my explanation of deferred query execution a dozen times. But, being neurotic, I am always paranoid that you may have skipped some pertinent part of a previous chapter. In this case, I am concerned you might have missed the explanation of deferred query execution.
Deferred query execution refers to the fact that a LINQ query of any type—be it a LINQ to SQL query, a LINQ to XML query, or a LINQ to Objects query—may not actually be executed at the time it is defined. Take the following query, for example:
IQueryable<Customer> custs = from c in db.Customers
where c.Country == "UK"
select c;The database query is not actually performed when this statement is executed; it is merely defined and assigned to the variable custs. The query will not be performed until the custs sequence is enumerated. This has several repercussions.
Repercussions of Deferred Query Execution
One repercussion of deferred query execution is that your query can contain errors that will cause exceptions but only when the query is actually performed, not when defined. This can be very misleading when you step over the query in the debugger and all is well, but then, farther down in the code, an exception is thrown when enumerating the query sequence. Or, perhaps you call another operator on the query sequence that results in the query sequence being enumerated.
Another repercussion is that, since the SQL query is performed when the query sequence is enumerated, enumerating it multiple times results in the SQL query being performed multiple times. This could certainly hamper performance. The way to prevent this is by calling one of the standard query operator conversion operators, ToArray<T>, ToList<T>, ToDictionary<T, K>, or ToLookup<T, K>, on a sequence. Each of these operators will convert the sequence on which it is called to a data structure of the type specified, which in effect, caches the results for you. You can then enumerate that new data structure repeatedly without causing the SQL query to be performed again and the results potentially changing.
Taking Advantage of Deferred Query Execution
One advantage of deferred query execution is that performance can be improved while at the same time allowing you to reuse previously defined queries. Since the query is executed every time the query sequence is enumerated, you can define it once and enumerate it over and over, whenever the situation warrants. And, if the code flow takes some path that doesn't need to actually examine the query results by enumerating them, performance is improved because the query is never actually executed.
Another of the benefits of deferred query execution is that, since the query isn't actually performed by merely defining it, we can append additional operators programmatically as needed. Imagine an application that allows the user to query customers. Also imagine that the user can filter the queried customers. Picture one of those filter-type interfaces that have a drop-down list for each column in the customer table. There is a drop-down list for the City column and another for the Country column. Each drop-down list has every city and country from all Customer records in the database. At the top of each drop-down list is an [ALL] option, which is the default for its respective database column. If the user hasn't changed the setting of either of those drop-down lists, no additional where clause is appended to the query for the respective column. Listing 14-16 contains an example programmatically building a query for such an interface.
Example. Programmatically Building a Query
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind"); // Turn on the logging. db.Log = Console.Out; // Pretend the values below are not hardcoded, but instead, obtained by accessing // a dropdown list's selected value. string dropdownListCityValue = "Cowes"; string dropdownListCountryValue = "UK"; IQueryable<Customer> custs = (from c in db.Customers select c); if (!dropdownListCityValue.Equals("[ALL]")) { custs = from c in custs where c.City == dropdownListCityValue select c; } if (!dropdownListCountryValue.Equals("[ALL]")) { custs = from c in custs where c.Country == dropdownListCountryValue select c; } foreach (Customer cust in custs) { Console.WriteLine("{0} - {1} - {2}", cust.CompanyName, cust.City, cust.Country); } |
In Listing 14-16, I simulate obtaining the user selected city and country from their drop-down lists, and only if they are not set to "[ALL]", I append an additional where operator to the query. Because the query is not actually performed until the sequence is enumerated, I can programmatically build it, a portion at a time.
Let's take a look at the results of Listing 14-16.
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax] FROM [dbo].[Customers] AS [t0] WHERE ([t0].[Country] = @p0) AND ([t0].[City] = @p1) -- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [UK] -- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [Cowes] -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1 Island Trading - Cowes - UK
Notice that since I specified that the selected city was Cowes and the selected country was UK, I got the records for the customers in Cowes in the United Kingdom. Also notice that there is a single SQL statement that was performed. Because the query's execution is deferred until it is actually needed, I can continue to append to the query to further restrict it, or perhaps order it, without the expense of multiple SQL queries taking place.
You can see that both of the filter criteria, the city and country, do appear in the where clause of the executed SQL statement.
For another test, in Listing 14-17, I'll change the value of the dropdownListCityValue variable to "[ALL]" and see what the executed SQL statement looks like then and what the results are. Since the default city of "[ALL]" is specified, the SQL query shouldn't even restrict the results set by the city.
Example. Programmatically Building Another Query
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind"); // Turn on the logging. db.Log = Console.Out; // Pretend the values below are not hardcoded, but instead, obtained by accessing // a dropdown list's selected value. string dropdownListCityValue = "[ALL]"; string dropdownListCountryValue = "UK"; IQueryable<Customer> custs = (from c in db.Customers select c); if (!dropdownListCityValue.Equals("[ALL]")) { custs = from c in custs where c.City == dropdownListCityValue select c; } if (!dropdownListCountryValue.Equals("[ALL]")) { custs = from c in custs where c.Country == dropdownListCountryValue select c; }xs foreach (Customer cust in custs) { Console.WriteLine("{0} - {1} - {2}", cust.CompanyName, cust.City, cust.Country); } |
Let's examine the output of Listing 14-17.
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax] FROM [dbo].[Customers] AS [t0] WHERE [t0].[Country] = @p0 -- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [UK] -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1 Around the Horn - London - UK B's Beverages - London - UK Consolidated Holdings - London - UK Eastern Connection - London - UK Island Trading - Cowes - UK North/South - London - UK Seven Seas Imports - London - UK
You can see that the where clause of the SQL statement no longer specifies the city, which is exactly what I wanted. You can also see in the output results that there are now customers from different cities in the United Kingdom.
Of course, you can always append a call to the ToArray<T>, ToList<T>, ToDictionary<T, K>, or ToLookup<T, K> standard query operators to force the query to execute when you want.
I hope you can see that deferred query execution can be your friend. I also hope by now that you can see the usefulness of the DataContext.Log.
The SQL IN Statement with the Contains Operator
One of the SQL query capabilities that early incarnations of LINQ to SQL lacked was the ability to perform a SQL IN statement, such as the one in the following SQL query:
Example. A SQL Query with an IN Statement
SELECT *
FROM Customers
WHERE (City IN ('London', 'Madrid')) |
To alleviate this problem, Microsoft added the Contains operator. This operator is used differently, though, than may be immediately obvious. To me, it seems to work backward of the way I would expect an implementation of the SQL IN statement to work. I would expect to be able to say some member of an entity class must be IN some set of values. Instead, it works in the opposite manner. Let's take a look at Listing 14-18 where I demonstrate the Contains operator.
Example. The Contains Operator
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
db.Log = Console.Out;
string[] cities = { "London", "Madrid" };
IQueryable<Customer> custs = db.Customers.Where(c => cities.Contains(c.City));
foreach (Customer cust in custs)
{
Console.WriteLine("{0} - {1}", cust.CustomerID, cust.City);
} |
As you can see in Listing 14-18, instead of writing the query so that the customer's city must be in some set of values, you write the query so that some set of values contains the customer's city. In the case of Listing 14-18, I create an array of cities named cities. In my query, I then call the Contains operator on the cities array and pass it the customer's city. If the cities array contains the customer's city, true will be returned to the Where operator and that will cause the Customer object to be included in the output sequence.
Let's take a look at the output of Listing 14-18.
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],
[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],
[t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[City] IN (@p0, @p1)
-- @p0: Input String (Size = 6; Prec = 0; Scale = 0) [London]
-- @p1: Input String (Size = 6; Prec = 0; Scale = 0) [Madrid]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1AROUT - London BOLID - Madrid BSBEV - London CONSH - London EASTC - London FISSA - Madrid NORTS - London ROMEY - Madrid SEVES - London
Looking at the generated SQL statement, you can see that the Contains operator was translated into a SQL IN statement.
Updates
Making database updates with LINQ to SQL is as easy as changing properties on an object, calling the DataContext object's SubmitChanges method, and handling any concurrency conflicts that may occur. Don't let the concurrency conflict handling intimidate you, there are several options for handling conflicts, and none of them are too painful. I will cover detecting and handling conflicts in detail in Chapter 17.
Of course, this simplicity is only true if you have properly written entity classes that are mapped to the database properly and maintain graph consistency. For more information about mapping the entity classes to the database, read the section titled "Entity Class Attributes and Attribute Properties" in Chapter 15. For more information about graph consistency, read the section titled "Graph Consistency" in that same chapter. However, SQLMetal and the Object Relational Designer handle all of the necessary plumbing to make all this just happen.
For a simple example of making an update to the database, you merely need to look at the first example in Chapter 12, Listing 12-1.
Updating Associated Classes
By design, LINQ to SQL allows you to update either side of associated classes to remove the relationship between them. You could update a parent object's reference to one of its children, or you could update that child's reference to the parent. Obviously, the references at each end of that relationship must be updated, but you only need to update one side or the other.
It is not LINQ to SQL that keeps your object model's graph consistent when updating one side; it is the responsibility of the entity class to make this happen. Please read the section titled "Graph Consistency" in Chapter 15 for more information on how this should be implemented.
However, SQLMetal and the Object Relational Designer handle this for you if you allow them to create your entity classes.
Updating a Child's Parent Reference
Since we can update either side of the relationship, we could choose to update a child's parent reference. So, as an example, let's see how I would change the employee that gets credit for an order in the Northwind database by examining Listing 14-19. Because this example is more complex than many of the others, I will explain it as I go.
Example. Changing a Relationship by Assigning a New Parent
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
Order order = (from o in db.Orders
where o.EmployeeID == 5
orderby o.OrderDate descending
select o).First<Order>();
// Save off the current employee so I can reset it at the end.
Employee origEmployee = order.Employee; |
In the preceding code, after obtaining the DataContext, I query for the most recent order of the employee whose EmployeeID is 5 by ordering that person's orders by date in descending order and calling the First operator. This will provide me the most recent order. Next, just so I will have a reference to the original employee this order was credited to, so that I can restore it at the end of the example, I save the reference in a variable named origEmployee.
Console.WriteLine("Before changing the employee.");
Console.WriteLine("OrderID = {0} : OrderDate = {1} : EmployeeID = {2}",
order.OrderID, order.OrderDate, order.Employee.EmployeeID);Next, I display a line to the console letting you know I haven't changed the employee for the retrieved order yet, followed by displaying the order's ID, date, and credited employee to the screen. We should see that the order is credited to employee 5, since that is the employee I queried to obtain the order.
Employee emp = (from e in db.Employees
where e.EmployeeID == 9
select e).Single<Employee>();
// Now I will assign the new employee to the order.
order.Employee = emp;
db.SubmitChanges();Next, I query for some other employee, the one whose EmployeeID is 9, that I then set to be the credited employee for the previously queried order. Then, I persist the changes by calling the SubmitChanges method.
Now, to prove the change was really made at both ends, I could just show you the credited employee for the queried order, but that would be anticlimactic, since you just saw me set the Employee property of the order, and it wouldn't really prove to you that the change was made on the employee side of the relationship. It would be much more satisfying for me to find the order I just changed in the new employee's collection of orders, so that is what I will do.
Order order2 = (from o in emp.Orders
where o.OrderID == order.OrderID
select o).First<Order>();In the preceding code, I query for the order I changed by its OrderID in the new employee's Orders. If it is found, that will prove the relationship between the employee and order was updated on both ends of the relationship.
Console.WriteLine("{0}After changing the employee.", System.Environment.NewLine);
Console.WriteLine("OrderID = {0} : OrderDate = {1} : EmployeeID = {2}",
order2.OrderID, order2.OrderDate, order2.Employee.EmployeeID);In the preceding code, I merely display to the console that I am about to display the order after changing it to the new employee emp. I then display that order. We should see that its employee is the employee whose EmployeeID is 9. Prior to the change, the EmployeeID was 5.
// Now I need to reverse the changes so the example can be run multiple times. order.Employee = origEmployee; db.SubmitChanges();
The last two lines of code, as well as the line that saves the order's original employee, are merely for resetting the database so the example can be run multiple times.
Now, let's examine the output for Listing 14-19.
Before changing the employee. OrderID = 11043 : OrderDate = 4/22/1998 12:00:00 AM : EmployeeID = 5 After changing the employee. OrderID = 11043 : OrderDate = 4/22/1998 12:00:00 AM : EmployeeID = 9
As you can see, the employee for the order before the change was the employee whose EmployeeID is 5. After the change of the order's credited employee, the order's credited EmployeeID is 9. What is significant is that I didn't just display the order's credited employee on the same order variable, order. I retrieved that order from the employee whose EmployeeID is 9. This proves that the order was indeed changed on the employee side of the relationship.
In this example, I updated the child object's parent reference, where the child was the order and the parent was the employee. There is yet another approach I could have taken to achieve the same result. I could have updated the parent object's child reference.
Updating a Parent's Child Reference
Another approach to changing the relationship between two objects is to remove the child object from the parent object's EntitySet<T> collection and add it to a different parent's EntitySet<T> collection. In Listing 14-20, I will remove the order from the employee's collection of orders. Because this example is similar to Listing 14-19, I will be far briefer in the explanation, but the significant differences will be in bold.
Example. Changing a Relationship by Removing and Adding a Child to a Parent's EntitySet
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
Order order = (from o in db.Orders
where o.EmployeeID == 5
orderby o.OrderDate descending
select o).First<Order>();
// Save off the current employee so I can reset it at the end.
Employee origEmployee = order.Employee;
Console.WriteLine("Before changing the employee.");
Console.WriteLine("OrderID = {0} : OrderDate = {1} : EmployeeID = {2}",
order.OrderID, order.OrderDate, order.Employee.EmployeeID);
Employee emp = (from e in db.Employees
where e.EmployeeID == 9
select e).Single<Employee>();// Remove the order from the original employee's Orders. origEmployee.Orders.Remove(order); // Now add it to the new employee's orders. emp.Orders.Add(order); db.SubmitChanges(); Console.WriteLine("{0}After changing the employee.", System.Environment.NewLine); Console.WriteLine("OrderID = {0} : OrderDate = {1} : EmployeeID = {2}", order.OrderID, order.OrderDate, order.Employee.EmployeeID); // Now I need to reverse the changes so the example can be run multiple times. order.Employee = origEmployee; db.SubmitChanges(); |
In Listing 14-20, I retrieve the most recent order for the employee whose EmployeeID is 5, and I save off the retrieved order's employee in origEmployee so that I can restore it at the end of the example. Next, I display the order before the employee is changed. Then, I retrieve the employee whose EmployeeID is 9 and store the reference in the variable named emp. At this point, this code is the same as Listing 14-19.
Then, I remove the order from the original employee's collection of orders and add it to the new employee's collection of orders. I then call the SubmitChanges method to persist the changes to the database. Next, I display the order after the changes to the console. Last, I restore the order to its original condition so the example can be run more than once.
Let's examine the results of Listing 14-20.
Before changing the employee. OrderID = 11043 : OrderDate = 4/22/1998 12:00:00 AM : EmployeeID = 5 After changing the employee. OrderID = 11043 : OrderDate = 4/22/1998 12:00:00 AM : EmployeeID = 9
Deletes
To delete a record from a database using LINQ to SQL, you must delete the entity object from the Table<T> of which it is a member with the Table<T> object's DeleteOnSubmit method. Then, of course, you must call the SubmitChanges method. Listing 14-21 contains an example.
|
Example. Deleting a Record by Deleting It from Its Table<T>
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
// Retrieve a customer to delete.
Customer customer = (from c in db.Customers
where c.CompanyName == "Alfreds Futterkiste"
select c).Single<Customer>();
db.OrderDetails.DeleteAllOnSubmit(
customer.Orders.SelectMany(o => o.OrderDetails));
db.Orders.DeleteAllOnSubmit(customer.Orders);
db.Customers.DeleteOnSubmit(customer);
db.SubmitChanges();
Customer customer2 = (from c in db.Customers
where c.CompanyName == "Alfreds Futterkiste"
select c).SingleOrDefault<Customer>();
Console.WriteLine("Customer {0} found.", customer2 != null ? "is" : "is not"); |
NOTE
In the Visual Studio 2008 Beta 2 release and earlier, the DeleteOnSubmit method called in the preceding code was named Remove, and the DeleteAllOnSubmit method was named RemoveAll.
This example is pretty straightforward, but there are some interesting facets to it. First, since the Order table contains a foreign key to the Customer table, you cannot delete a customer without first deleting the customer's orders. And, since the Order Details table contains a foreign key to the Orders table, you cannot delete an order without first deleting the order's order details. So, to delete a customer, I must first delete the order details for all of the orders for the customer, and then I can delete all the orders, and finally I can delete the customer.
Deleting all the orders is not difficult thanks to the DeleteAllOnSubmit operator that can delete a sequence of orders, but deleting all the order details for each order is a little trickier. Of course, I could enumerate through all the orders and call the DeleteAllOnSubmit operator on each order's sequence of order details, but that would be boring. Instead, I call the SelectMany operator to take a sequence of sequences of order details to create a single concatenated sequence of order details that I then pass to the DeleteAllOnSubmit operator. Man, it's like I am drunk on the power of LINQ.
After deleting the order details, orders, and the customer, I merely call the SubmitChanges method. To prove the customer is actually gone, I query for it and display a message to the console.
Let's take a look at the output of Listing 14-21.
Customer is not found.
That's not very exciting output, but it does prove the customer no longer exists. While the point of Listing 14-21 is to demonstrate that to delete an entity object you must delete it from the appropriate Table<T>, I think the example became a cheerleader for the SelectMany operator as well.
NOTE
Remember that this example did not restore the database at the end, so you should manually restore it now.
Deleting Attached Entity Objects
Unlike when an attached associated dependent entity object was automatically inserted into the database by the DataContext when the dependent entity object's associated parent object was inserted, as happened in Listing 14-3, our attached dependent entity objects are not automatically deleted if the parent entity object is deleted. By dependent, I mean the entity objects containing the foreign key. You saw this fully demonstrated in Listing 14-21, where I had to delete the Order Details records before the Orders records and the Orders records before the Customers record.
So, for example, with the Northwind database, if you attempt to delete an order, its order details will not automatically be deleted. This will cause a foreign key constraint violation when you attempt to delete the order. Therefore, before you can delete an entity object, you must delete all its attached associated child entity objects.
For examples of this, please examine Listing 14-21 and Listing 14-3. In each of these listings, I had to delete the associated attached entity objects before I could delete their parent object.
Deleting Relationships
To delete a relationship between two entity objects in LINQ to SQL, you merely reassign the entity object's reference to the related object to a different object or null. By assigning the reference to null, the entity object will have no relationship to an entity of that type. However, removing the relationship altogether by assigning the reference to null will not delete the record itself. Remember, to actually delete a record, its corresponding entity object must be deleted from the appropriate Table<T>. Listing 14-22 contains an example of removing the relationship.
Example. Removing a Relationship Between Two Entity Objects
Northwind db = new Northwind(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind");
// Retrieve an order to unrelate.
Order order = (from o in db.Orders
where o.OrderID == 11043
select o).Single<Order>();
// Save off the original customer so I can set it back.
Customer c = order.Customer;
Console.WriteLine("Orders before deleting the relationship:");
foreach (Order ord in c.Orders)
{
Console.WriteLine("OrderID = {0}", ord.OrderID);
}
// Remove the relationship to the customer.
order.Customer = null;
db.SubmitChanges();
Console.WriteLine("{0}Orders after deleting the relationship:",
System.Environment.NewLine);
foreach (Order ord in c.Orders)
{
Console.WriteLine("OrderID = {0}", ord.OrderID);
}
// Restore the database back to its original state.
order.Customer = c;
db.SubmitChanges(); |
In Listing 14-22, I query a specific order, the one whose OrderID is 11043. I then save that order's customer, so I can restore it at the end of the example. I next display all of that customer's orders to the console and assign the retrieved order's customer to null and call the SubmitChanges method to persist the changes to the database. Then, I display all the customer's orders again, and this time, the order whose OrderID is 11043 is gone. Let's examine the output for Listing 14-22.
Orders before deleting the relationship: OrderID = 10738 OrderID = 10907 OrderID = 10964 OrderID = 11043 Orders after deleting the relationship: OrderID = 10738 OrderID = 10907 OrderID = 10964
As you can see, once I remove the relationship to the customer for the order whose OrderID is 11043, the order is no longer in the customer's collection of orders.