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,

Decorating it with Table and column like this

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:

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

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<>().

The PrintWinnes function would have to change to:

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:

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

1 Reply to “LINQ to SQL”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.