Language Integrated Query (LINQ) to SQL

By Mark Strawmyer

Querying Data

The following example code uses LINQ syntax to query datafrom the database and then loop through it to print it tothe console. You'll notice how the query expression syntaxis the same as what you would find whether it be LINQ to XMLor another form of LINQ. Also notice how theNorthwindDataClassesDataContext object is wrapped within ausing construct. TheNorthwindDataClassesDataContext is an object thatextends the DataContext class and represents a stronglytyped connection to our database. TheNorthwindDataClassesDataContext is a disposabletype, so wrapping it in a using block ensures it is properlydisposed 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 themsimple. The object model used to manipulate the data is themodel generated in one of the earlier sections. After eachoperation we'll use the SubmitChanges() method to save thechanges to the database. We'll execute some simple queryexpressions with the Count() method to verify themodifications were made as desired. As the example willshow, you can use instance of objects, make modifications tothem, and the changes that you make are tracked in a changeset 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();

Page 2 of 3