LINQ: I only speak in SQL

LINQ to SQL (Language Integrated Query to Structured Query Language) is probably the best known use of LINQ. It gives us an easy to use way of communicating with SQL databases.

CREATE TABLE People (
  Id INT IDENTITY,
  Age INT NULL,
  Name VARCHAR(50) NULL
);

First we create a simple table in our database. Out of the box LINQ to SQL only supports SQL Server but you can use LINQ to DataSet or other LINQ providers to get around this.

[Table(Name = "People")]
public class Person
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int Id;
    [Column]
    public int Age;
    [Column]
    public string Name;
}

In the code we then create a class that the table can be mapped against. We use attributes to tell LINQ that the class represents a table. In this case we also tell it the table name since it’s different from the class name. It makes sense to name tables in plural and classes in singular. In order for LINQ to work properly the table must have a primary key, which in this case is generated by the database.

Visual Studio can generate this class for us, or it can generate the database from the class or even both of them from a schema. In this case I have created both the class and the table manually.

DataContext database = new DataContext(
    @"Data Source     = 127.0.0.1,15096;
      Network Library = DBMSSOCN;
      Initial Catalog = linqtest;
      User Id         = sa;
      Password        = hunter2;");
Table<Person> people = database.GetTable();

Before we can use the database we must connect to it. We do this by creating a new DataContext and giving it a connection string. The DataContext takes care of connecting to the database as needed. It also keeps track of changes which we will come to later.

We must also create a table object in order to have something to query against. In practice you should create a strongly typed DataContext instead. That is, a class which inherits from DataContext and declares all tables as public fields.

from p in people select p.Age + " " + p.Name
"28 Edgar", "34 Marit", "14 Trulf"
from p in people orderby p.Age select p.Age + " " + p.Name
"14 Trulf", "28 Edgar", "34 Marit"
people.Where(p => p.Age > 20).Select(p => p.Age + " " + p.Name)
"28 Edgar", "34 Marit"

We can now use LINQ to run queries against the table. These queries are translated to expression trees which are in turn translated to SQL and run against the database.

foreach (Person p in people)
    p.Age++;
database.SubmitChanges();
 
from p in people orderby p.Age select p.Age + " " + p.Name
"15 Trulf", "29 Edgar", "35 Marit"

Part of the DataContext’s responsibilities is to keep track of changes to objects that correspond to table rows. Because of this we can make our changes permanent by submitting them to the database.

Person alan = new Person() { Age = 41, Name = "Alan" };
people.InsertOnSubmit(alan);
Person marit = people.Single(p => p.Name == "Marit");
people.DeleteOnSubmit(marit);
database.SubmitChanges();

people.Select(p => p.Name)
"Edgar", "Trulf", "Alan"

Naturally, we can also add or remove rows from the table. This also requires that we submit our changes. If you create associations to other tables additions and removals will be tracked automatically.