Language Integrated Query (LINQ) to SQL

Introducing LINQ to Relational Data

LINQ introduced the concept of a unified data access technique to eliminate challenges of accessing data from a disparity of sources. LINQ has been extended to include the ability to access relational data through LINQ to SQL and LINQ to Entities. LINQ to SQL offers a direct mapping to the Microsoft SQL Server family of databases and allows you to query and manipulate objects associated with database tables. LINQ to Entities offers a more flexible mapping to relational data stored in other databases beyond just SQL Server. You build against a conceptual schema that is mapped to an actual schema. The remainder of this article will concentrate on LINQ to SQL.

LINQ to SQL Object Model

LINQ to SQL allows you to query and manipulate objects associated with database tables. Creating an object model involves creating a typed object that represents the database connection along with classes that map to database entities. The following list of objects are involved:

There are a few different ways to create an object model. The two most straight forward approaches are to use the provided designer within Visual Studio 2008 to auto-generate the objects and mappings or adding attributes to existing objects. For this example I downloaded the Northwind sample database and loaded it in my SQL Server so that I could retrieve data from it for testing. I created a new solution and added a console application. I then added a LINQ to SQL data class to the console application. I used the Server Explorer to setup a new connection to the Northwind database my local SQL Server, and then dragged all of the tables from the Server Explorer on to the design surface for the data class. Finally I right clicked on the design surface and chose "Layout Diagram" to auto arrange all of the newly added tables and relationships. Refer to Figure 1 for an example of the completed design surface


Test Driving LINQ to SQL through Examples

Now that we've covered the background let's use a couple of examples of LINQ to SQL. Our examples will demonstrate the use of the DataContext object along with an example of querying the Northwind sample database using expressions.

Querying Data

The following example code uses LINQ syntax to query data from the database and then loop through it to print it to the console. You'll notice how the query expression syntax is the same as what you would find whether it be LINQ to XML or another form of LINQ. Also notice how the NorthwindDataClassesDataContext object is wrapped within a using construct. The NorthwindDataClassesDataContext is an object that extends the DataContext class and represents a strongly typed connection to our database. The NorthwindDataClassesDataContext is a disposable type, so wrapping it in a using block ensures it is properly disposed after use.

using (NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext()) {
var results = from customers in context.Customers where customers.City == "London" orderby customers.CompanyName select customers;
foreach (var customer in results)
{
Console.WriteLine("Company is {0} and contact is {1}", customer.CompanyName, customer.ContactName
);
} // Pause to see the output Console.ReadLine(); }

Modifying Data Using the Created Object Model

The previous example focused on the retrieval of data. We'll now examine the basic data manipulation, create, update, and delete operations and how LINQ to SQL makes them simple. The object model used to manipulate the data is the model generated in one of the earlier sections. After each operation we'll use the SubmitChanges() method to save the changes to the database. We'll execute some simple query expressions with the Count() method to verify the modifications were made as desired. As the example will show, you can use instance of objects, make modifications to them, and the changes that you make are tracked in a change set and pushed back to the database.

using (NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext()) {
// Add a new record and verify it exists through Count
var customer = new Customer()
{
CompanyName = "Drama Cafe", CustomerID = "DRACA", ContactName = "Tom Smith", City = "Beverly Hills", Address = "123 Melrose Place", PostalCode = "90210"
};
context.Customers.InsertOnSubmit(customer);
context.SubmitChanges();
Console.WriteLine("Number of DRACA records: {0}", context.Customers.Where(c => c.CustomerID == "DRACA").Count()); // Modify the record and verify it is changed through Count customer.ContactName = "Joe Smith";
context.SubmitChanges();
Console.WriteLine("Number of Joe Smith records: {0}", context.Customers.Where(c => c.ContactName == "Joe Smith").Count()); // Delete a record and verify it is removed through Count context.Customers.DeleteOnSubmit(customer);
context.SubmitChanges();
Console.WriteLine("Number of DRACA records: {0}", context.Customers.Where(c => c.CustomerID == "DRACA").Count());
// Pause to see the output Console.ReadLine();

Calling Stored Procedures

The previous retrieve, create, update, and delete operations involved dynamically generated SQL statements. It is also possible to call stored procedures in place of dynamically generated SQL. The sample code below demonstrates how to call stored procedures. The Northwind database has a stored procedure called "Ten Most Expensive Products" we will use for this example. Once again using the Server Explorer navigate to the Stored Procedures in the tree view and drag the stored procedure to the design surface. You'll want to right click on the design surface and select "Show Methods Pane" if it isn't already available. You should now see a method, Ten_Most_Expensive_Products(), that has been created and we'll use in our next code example to get the list of top products and display the prices to the console.

using (NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext()) {
// Use the ten most expensive products stored procedure
var results = from products in context.Ten_Most_Expensive_Products() select products;
foreach
(var product in results)
{
Console.WriteLine("Product price is {0}", product.UnitPrice);
} // Pause to see the output Console.ReadLine();

0 Responses to "Language Integrated Query (LINQ) to SQL"