Although using SQL-like syntax with your in-memory collections is the more interesting and unusual use of LINQ, it’s natural to use the SQL-like syntax with SQL databases. With LINQ, instead of using the DataAdapter and DataSet classes you learned about in Chapter 20, you can treat the tables in a SQL database as classes, and work with the data directly, as though the tables were objects created in your code.

Create a new console application to see how this works. To use the LINQ data features, you’ll need to add a reference to the System.Data.Linq namespace, which is something you haven’t done before, but it’s simple. Right-click on the References folder of your project in the Solution Explorer. You’ll see the Add Reference dialog shown in Figure 21-1.

Figure 21-1. You’ll need to add a reference to the System.Data.Linq namespace before you can use LINQ with a SQL database.

Now that you have the reference, you need to add some using statements to take advantage of them in your program:

using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;

As we mentioned earlier, when you’re using LINQ and SQL, you can treat the database tables as classes, and the columns as members. It just requires a bit of extra work on your part. You’ll retrieve some simple information from Northwind’s Employees table in this example.

Create the following class in your application:

[Table(Name = "Employees")]
public class Employee
{
    [Column]
    public int EmployeeID { get; set; }
    [Column]
    public string FirstName { get; set; }
    [Column]
    public string LastName { get; set; }
}

You’ve probably noticed the extra code in square brackets that’s unfamiliar to you. These are called attributes, and they’re used in a lot of different places in C# to provide extra modifiers to your classes. In this case, you’re using the Table attribute to indicate that this class is drawn from a table, specifically the Employees table in the associated database. Each of the public properties in the class has a Column attribute to indicate that the property is associated with a column in the table.

As you probably remember from Chapter 20, you always need a data context before you can work with a database. With LINQ, creating the data context is much easier. Add the following line to Main( ):

DataContext db = new DataContext("Data Source = .\SQLExpress;" +
   "Initial Catalog=Northwind;Integrated Security=True");

Notice that the DataContext object’s constructor takes a string as its parameter—the same connection string that you used to connect to the database in Chapter 20.

The DataContext object has an important method, GetTable( ), which is how you retrieve the data table from the database and assign it to a generic Table collection. The collection holds the type of objects that you defined earlier in the application. So, add this Table<Employee> declaration to your application (after the DataContext line):

Table<Employee> employees = db.GetTable<Employee>( );

That’s all you need to retrieve the data. Now, though, you’ll need a query. For this example, you’ll simply retrieve all the employee rows in the table. That’s where the LINQ syntax you saw earlier in this chapter comes in. You don’t need to create a query string; just use the LINQ syntax directly:

var dbQuery = from emp in employees select emp;

Although dbQuery is declared using the var keyword, it returns an IEnumerable collection, which means that you can iterate over it with a foreach loop, just like you would any other collection. Add this code to output some of the data:

foreach (var employee in dbQuery)
{
    Console.WriteLine("{0}	{1} {2}", employee.EmployeeID,
        employee.FirstName, employee.LastName);
}

Simple, right? No more worrying about DataSet or DataRow objects. Example 21-6 shows the complete code for this example.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace Example_21_6_ _ _ _LINQ_and_SQL
{
    [Table(Name = "Employees")]
    public class Employee
    {
        [Column] public int EmployeeID { get; set; }
        [Column] public string FirstName { get; set; }
        [Column] public string LastName { get; set; }
    }

    class Program
    {
        static void Main( )
        {
            DataContext db = new DataContext("Data Source = .\SQLExpress;
               Initial Catalog=Northwind;Integrated Security=True");

            Table<Employee> employees = db.GetTable<Employee>( );
            var dbQuery = from emp in employees select emp;

            foreach (var employee in dbQuery)
            {
                Console.WriteLine("{0}	{1} {2}", employee.EmployeeID,
                    employee.FirstName, employee.LastName);
            }
        }
    }
}

The results look like this:

1    Nancy Davolio
2    Andrew Fuller
3    Janet Leverling
4    Margaret Peacock
5    Steven Buchanan
6    Michael Suyama
7    Robert King
8    Laura Callahan
9    Anne Dodsworth