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();
  }

Language Integrated Query (LINQ) to SQL

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();

The stored procedure example above is pretty straight forward. You can create your own stored procedures, including those that have input parameters, and use them in a similar manner.

Summary

You have now seen LINQ to SQL and the simplicity that it can bring to working with SQL Server based databases. We have examined the object model involved, retrieving and altering data through dynamically generated SQL statements, and calling stored procedures.

Future Columns

The topic of the next column is likely to be LINQ to Entities, which is another form of LINQ for accessing relational data. If you have something else in particular that you would like to see explained here please e-mail me.



About the Author

Mark Strawmyer

Mark Strawmyer is a Senior Architect of .NET applications for large and mid-size organizations. He specializes in architecture, design and development of Microsoft-based solutions. Mark was honored to be named a Microsoft MVP for application development with C# for the fifth year in a row. You can reach Mark at mark.strawmyer@crowehorwath.com.

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: September 10, 2014 Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild." This loop of continuous delivery and continuous feedback is how the best mobile …

  • A modern mobile IT strategy is no longer an option, it is an absolute business necessity. Today's most productive employees are not tied to a desk, an office, or a location. They are mobile. And your company's IT strategy has to be ready to support them with easy, reliable, 24/7 access to the business information they need, from anywhere in the world, across a broad range of communication devices. Here's how some of the nation's most progressive corporations are meeting the many needs of their mobile workers …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds