Introduction to LINQ, Part 3: LINQ to SQL

In the previous articles of the series, I have introduced LINQ to Objects and LINQ to XML. LINQ stands for Language-INtegrated Queries and basically makes queries first citizens of languages such as C# and VB.NET. The topic of this article is another component of LINQ, called LINQ to SQL; it provides a run-time environment that enables the managing of relational databases as objects, with great support for querying.

LINQ to SQL is actually a sub-component of LINQ to ADO.NET, which includes:

• LINQ to DataSet, for querying the DataSet family of objects
• LINQ to Entities, for querying EMD entities
• LINQ to SQL, for querying relational databases

In this article, I will give you an introduction to LINQ to SQL, which covers some basic elements. Notice that you need SQL Server 2005 installed and the UCL.mdf database to run the demo application provided with this article. I also recommend first reading about LINQ to Objects first.

Decorating Attributes

Objects are linked to relational data by decorating normal classes with attributes. Two of the most important attributes are Table and Column.

The Table attribute is used to decorate the class. One of its properties is Name; it is used to specify the name of the table to which an object of the class is linked. If the Name property is missing, the name of the class will be used for the name of the table. A class decorated with the Table attribute is called an entity class, and an instance of an entity class is called an entity. Only entities are stored in a database.

The Column attribute is used to decorate fields or properties of an entity class, to associate them with columns of a table. The Column attribute has also several properties, one of them being the Name property. As in the case of the Name property of Table, it is used to specify the field in the table to which the field or property of the entity class is matched. If the property is not specified, the name of the field or property of the entity class will be assumed as the name of the field in the table.

In the previous two articles, I have performed queries on various sequences with winners of the UEFA Champions League. I will do the same in this article. My former Winner class, in its simplest form, looked like this:

public class Winner
{
   public string Name { get; set; }

   public string Country { get; set; }

   public int Year { get; set; }
}

In this article, you will associate this class with a table from an SQL server database. The database is available for download, and is called UCL.mdb. It has four tables, but the first you will take into consideration is called Winners. This table has three fields:

• Year, an integer, the primary key for the table
• Country, a varchar, the country of the winner team
• Name, a varchar, the name of the team

To transform the normal Winner class above in an entity class mapped on this table, it has to be decorated with the Table and Column attributes:

[Table(Name = "Winners")]
public class Winner
{
   [Column]
   public string Name { get; set; }

   [Column]
   public string Country { get; set; }

   [Column(IsPrimaryKey = true)]
   public int Year { get; set; }
}

The Table.Name property specifies that the entity class Winner is mapped on the Winners table in the database. The Column attributes specifies that the Name, Country, and Year properties are mapped on columns with the same name from the table. In addition, the Column.IsPrimaryKey property being set to true for Year, indicates that this column is the primary key in the table.

The DataContext and Table Classes

The channel through which objects are retrieved from the database, and changes are submitted to the database, is the class called DataContext. It can be used like an ADO.NET connection. The overloaded constructor takes either a connection or a string connection. What this class does is translate requests for objects into SQL queries and assemble the objects from the result of queries.

Tables in a relational database are represented as Table collections (which implements interfaces such as IQueryable and IEnumerable). DataContext has a method called GetTable<>(); it represents a potential interaction with the table of view. The query is not actually executed until iteration over the result is performed. The type parameter of GetTable() identifies the table in the database.

Retrieving the winners from the database can be done in the following way:

public void PrintWinners()
{
   // creates a data context that takes the path of the database
   DataContext dc = new DataContext(@"C:\Program Files\
      Microsoft SQL Server\MSSQL.1\MSSQL\Data\UCL.mdf");

   // retrieves a Table of Winner
   Table<Winner> winners = dc.GetTable<Winner>();

   // creates a sequence of winners ordered descending by the
   // winning year
   var result = from w in winners
                orderby w.Year descending
                select w;

   // prints the sequence of winners
   foreach (var w in result)
   {
      Console.WriteLine("{0} {1}, {2}",
      w.Year, w.Name, w.Country);
   }
}

Running the code prints out:

2006 Barcelona, Spain
2005 Liverpool, England
2004 FC Porto, Portugal
2003 AC Milan, Italy
2002 Real Madrid, Spain
2001 Bayern Munchen, Germany
2000 Real Madrid, Spain
1999 Manchester Utd., England
1998 Real Madrid, Spain
1997 Borussia Dortmund, Germany
1996 Juventus, Italy
1995 AFC Ajax, Netherlands
1994 AC Milan, Italy
1993 Olympique de Marseille, France

Note: In the code above, the path to the database is hard-coded and corresponds to the location on my machine. If you run the sample application, make sure you use the correct path on your machine.

When first iterating over the result of the query, the DataContext translates the query into an SQL statement:

SELECT [t0].[Country], [t0].[Name], [t0].[Year]
FROM [Winners] as [t0]
ORDER BY [t0].[Year] ASC

As I said earlier, DataContext allows you to submit changes to the database. In the example, you first need to make the changes to the collection of winners, and then call the method SubmitChanges(). To add a new winner, you can do this:

public void AddWinner()
{
   // creates a data context that takes the path of the database
   DataContext dc = new DataContext(@"C:\Program Files\
      Microsoft SQL Server\MSSQL.1\MSSQL\Data\UCL.mdf");

   // retrieves a Table of Winner
   Table<Winner> winners = dc.GetTable<Winner>();

   // adds a new winner to the table
   winners.Add(new Winner { Name = "AC Milan",
                            Country = "Italy",
                            Year = 2007});

   // submites the changes
   dc.SubmitChanges();
}

If you look into the database after running this function, or if you call PrintWinners(), you will see that the new entry was added to the Winners table.

Updating or deleting can be performed in the same way. The following example removes all the winners from Italy:

public void DeleteWinners()
{
   // creates a data context that takes the path of the database
   DataContext dc = 
      new DataContext(@"C:\Program Files\Microsoft SQL Server\
         MSSQL.1\MSSQL\Data\UCL.mdf");

   // retrieves a Table of Winner
   Table<Winner> winners = dc.GetTable<Winner>();

   // remove a sequence of winners
   winners.RemoveAll(from w in winners
                     where w.Country == "Italy"
                     select w);

   // submites the changes
   dc.SubmitChanges();
}

Marius Bancila is a Microsoft MVP for VC++. He works as a software developer for a Norwegian-based company. He is mainly focused on building desktop applications with MFC and VC#. He keeps a blog at www.mariusbancila.ro/blog, focused on Windows programming. He is the co-founder of codexpert.ro, a community for Romanian C++/VC++ programmers.