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();
}

Introduction to LINQ, Part 3: LINQ to SQL

Strongly-Typed DataContext

It is recommended that you use a so-called strongly-typed version of DataContext, which is a derived of DataContext that has all the tables as members. This way, the tables are considered to be part of the context, and explicit calls to GetTable<>() are not required.

The same UCL database contains three additional tables, called Teams, Countries, and WinnersRel. The Teams table contains the names of the teams that won the competition, Countries contains the names of the countries of the teams that won, and WinnersRel keep record of the winners for each year.

Table Teams has:

  • ID, integer, unique primary key
  • Name, varchar

Table Countries has:

  • ID, integer, unique primary key
  • Name, varchar

Table WinnersRel has:

  • Year, integer, unique primary key
  • CountryID, integer, representing the ID of a country
  • TeamID, integer, representing the ID of a team

To work with these tables, you need the three entity classes shown below:

/// <summary>
/// entity class mapped on the WinnersRel table
/// </summary>
[Table(Name = "WinnersRel")]
public class WinnerRel
{
   [Column]
   public int TeamID { get; set; }

   [Column]
   public int CountryID { get; set; }

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

/// <summary>
/// entity class mapped on the Teams table
/// </summary>
[Table(Name="Teams")]
public class Team
{
   [Column]
   public string Name { get; set; }

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

/// <summary>
/// entity class mapped on the Country class
/// </summary>
[Table(Name = "Countries")]
public class Country
{
   [Column]
   public string Name { get; set; }

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

A strongly-typed database, called UCLDataContext, derived from DataContext, contains Table collections of these entity classes.

/// <summary>
/// strongly-typed data context, with the tables part of the
/// context
/// </summary>
public class UCLDataContext : DataContext
{
   public Table<WinnerRel target="new" Winners;
   public Table<Team target="new" Teams;
   public Table<Country target="new" Countries;

   public UCLDataContext(string connection)
      :
      base(connection)
   {}
}

In this case, retrieving the winners from the database can be done by first creating a channel to the database, then defining a query, and lastly iterating over the result of the query. That basically triggers the translation of the query into an SQL query, its execution, and the assembly of objects from the result.

public void PrintWinners()
{
   // creates a strongly-typed data context
   UCLDataContext dc = new UCLDataContext(@"C:\Program Files\
      Microsoft SQL Server\MSSQL.1\MSSQL\Data\UCL.mdf");

   // creates a query that projects a sequence of an anonymous type
   var result = from w in dc.Winners
                from t in dc.Teams
                from c in dc.Countries
                where w.TeamID == t.ID && w.CountryID == c.ID
                orderby w.Year descending
                select new { TeamName = t.Name,
                             CountryName = c.Name, w.Year };

   // prints the sequence of objects of the anonymous type
   foreach (var w in result)
   {
      Console.WriteLine("{0} {1}, {2}",
      w.Year, w.TeamName, w.CountryName);
   }
}

The SQL statement that is generated looks like this:

SELECT [t0].[Name], [t1].[Name], [t2].[Year]
FROM [Countries] as [t0], [Teams] as [t1], [WinnersRel] as [t2]
WHERE [t2].[TeamID] = [t1].[ID] AND [t2].[CountryID] = [t0].[ID]
ORDER BY [t2].[Year]

Conclusions

LINQ to SQL has much more to offer than what was shown in this introductory article. I suggest additional reading such as:

In this series, you have seen how querying support can help you produce simpler, more readable, and faster-to-write code, whether it's about objects, XML, or relational data. Much of the logic of specifying how you want to do something is no longer left to the developer. By bringing functional programming techniques into procedural languages, you can focus on what you want to do, not how to do that. That is why LINQ is such an invaluable addition to the .NET programming environment.



About the Author

Marius Bancila

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.

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: August 19, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT You deployed your app with the Bluemix PaaS and it's gaining some serious traction, so it's time to make some tweaks. Did you design your application in a way that it can scale in the cloud? Were you even thinking about the cloud when you built the app? If not, chances are your app is going to break. Check out this upcoming webcast to learn various techniques for designing applications that will scale successfully in Bluemix, for the …

  • As everyone scrambles to protect customers and consumers from the Heartbleed virus, there will be a variety of mitigating solutions offered up to address this pesky bug. There are a variety of points within the data path where solutions could be put into place to mitigate this (and similar) vulnerabilities and customers must choose the most strategic point in the network at which to deploy their selected mitigation. Read this white paper to learn the ins and outs of mitigating the risk of Heartbleed and the …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds