Querying Mapped Entities with LINQ to SQL

Once you have a DataContext and mapped entities all you need to do is create an instance of the DataContext and query it with LINQ. Switching back to the CurrentProductList generated with SqlMetal Listing 2 demonstrates.

Listing 2: Products that start with ‘C’.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq.Mapping;
using System.IO;
using System.Data.Linq;
using System.Reflection;
namespace LinqtoSql
{
  class Program
  {
    static void Main(string[] args)
    {
      string connectionString =         
        @"Data Source=.\SQLExpress;Initial Catalog=northwind;" + 
        "Integrated Security=True";
      global::Northwnd northwind = new global::Northwnd(connectionString);
      var productsThatStartWithC =
        from product in northwind.CurrentProductList
        where product.ProductName.StartsWith("C")
        select product;
      Array.ForEach(productsThatStartWithC.ToArray(), 
          p => Console.WriteLine(p.ProductName));
      Console.ReadLine();
    }
  }
}

The Main function includes the connection string. You could add this to the DataContext or store it in an App.config file. The connection string was added here to simplify the demo.

The next statement creates an instance of the Northwnd DataContext. The global qualifier was used because no namespace was specified with SqlMetal, so no namespace was generated. To generate a namespace use the /namespace: <name> parameter with SqlMetal.

The statement beginning with var is the LINQ query. With LINQ the from comes first because it sets the context for Intellisense. The word after from is called the range. The range is like the control variable in a for loop. After in is the enumerable collection containing the data to search.

After the from clause is the where clause. where works like where in SQL. The where clause filters the result set; in this instance the where clause is filtering on products that start with ‘C’.

The select clause is at the end of LINQ queries. The select clause indicates the data to return. In the example the select clause is returning the whole product, but you could use select new and specify just parts of the product to return. select new creates what is referred to as a projection, as in projecting a new anonymous type.

Finally, the Array.ForEach method is used to send the results to the console. ForEach is shorthand for a for loop. The first argument is an array representing the input data, and the second argument is a Lambda Expression indicating what to do with each object in the array. Lambda Expressions are condensed anonymous delegates for all intents and purposes. On the left hand side of the => (goes to operator) are the inputs or parameters and the right hand side is the method body. In the example the behavior is to print the product name.

For a detailed exploration of LINQ and all of this plumbing, like Lambda Expressions, check out my imminent book LINQ Unleashed for C# from Sams.

Summary

With LINQ to SQL all you have to do is learn how to write LINQ queries. By defining a DataContext, representing the database, and classes that map to tables, views, or stored procedures you can query the database and let LINQ write the SQL for you.

Of course, LINQ to SQL supports reading and writing from and to the database, and as mentioned in the chapter opener, there are tools like SqlMetal that will code generate the necessary plumbing. Enjoy!

Biography

Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object-oriented programming and .NET. Check out his upcoming book LINQ Unleashed for C# due July 2008. Paul is an application architect for Electronic Data Systems in Lansing, Michigan. You may contact him for technology questions at pkimmel@softconcepts.com.

If you are interested in joining or sponsoring a .NET Users Group, check out www.glugnet.org. Glugnet opened a users group branch in Flint, Michigan in August 2007. If you are interested in attending, check out the www.glugnet.org web site for updates.