Language Integrated Query (LINQ) to SQL

Welcome to this installment of the .NET Nuts & Bolts column. The prior two articles have been an introduction to LINQ and LINQ to XML. This article will focus on another variation of LINQ, which is LINQ to SQL. LINQ to SQL allows you to query and manipulate objects associated with relational database tables and is another valuable part of the LINQ family.

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:

• DataContext – A base class that is a disposable type that gets extended to represent a strongly typed connection to the database.
  
• Classes and Collections – Represent objects and collection of objects that map to database entities.
  
• Methods – Are used to extend classes or to represent stored procedure calls.

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.

Click here for larger image
Figure 1 – LINQ to SQL Data Class 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();
  }