Marius Bancila’s Blog

LINQ to SQL is an API that allows querying relational databases. The samples in this post are based on the concepts of:

• Entity classes (which are instances of Entity Types). Such a class is a regular .NET class that is decorated with the attribute Table and its properties and fields with the attribute Column
• DataContext, is the channel for doing operations with the database; it is used like an ADO.NET connection; actually its constructor takes either a connection string or an ADO.NET connection

Considering the known Winner class from the previous posts,

public class Winner
{
     public string Name { get; set; }
     public string Country { get; set; }
     public int Year { get; set; }
}

Decorating it with Table and column like this

[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; }
}

will create a direct mapping between Winner and the table call Winners, and between the fields Name, Country and Year of the class and the columns with the same name from the table. Both Table and Column have several properties. One of them is Name, which specifies the actual name of the table or column corresponding to the class or property. If the property Name is not specified, the same name as for the class and properties is used.

Assuming we have a SQL Server database, located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\UCL.mdf, with a table Winners that has three columns Year (which is also the primary key), Name and Country, and that this table is populated with the winners of UEFA Champions League, we could write the following code to retrieve and show the winners:

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 winners = dc.GetTable();

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

First, we must instantiate a DataContext, passing as argument the path to the database. DataContext has a method GetTable, that returns a Table. To get the winners we call it as shown above. On this table, we can perform a query and show the results:

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

It is however recommended that we use a so called strongly-typed version of DataContext. In other words, a derived class from DataContext that keeps as members all the table collections. In this case we don’t have to call directly GetTable<>();

public class UCLDataContext : DataContext
{
    public Table Winners;

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

The PrintWinnes function would have to change to:

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

    // creates a sequence of winners ordered descending by the winning year
    var result = from w in dc.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);
    }
}

Only querying the database is not enough. DataContext also allow us to submit changes to the database.

The following function shows how to add a winner to the table Winners:

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

     // adds a new winner to the table
     dc.Winners.Add(new Winner { Name = "Manchester United", Country = "England", Year = 2007});

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

If you want for instance to remove all the winners from Spain, we can do the following:

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

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

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