LINQ to SQL is a powerful tool which allows developers to access databases as objects in C#. With LINQ to SQL, you are able to use LINQ query operators and methods instead of learning SQL. LINQ to SQL has an API for connecting and manipulating a database. LINQ queries and calls to the API methods are then translated to SQL commands which are executed to provide changes or to retrieve queries from the database. LINQ to SQL is a more modern way of accessing database using C# and .NET. Please do note that LINQ to SQL is only applicable when you are using SQL Server as your database.

We will be using the Northwind database as our sample database. If you are using the free Visual C# Express then we need to access the actual database file which has a .mdf file extension. If you installed the Northwind database correctly, the file can be found in C:SQL Server 2000 Sample Databases. If the file extension is not visible, go to Control Panel, choose Folder Options and click the View tab, and uncheck “Hide extensions for known file types”, then click OK. We will be using the Northwind.mdf database file. Database files are created with .mdf extensions whenever you create a database in SQL Server. SQL Server Express 2008 has a default folder for storing database files and it is located at C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData. So if you executed the scripts for creating the Northwind database as instructed in the first lessons, then you can also find a copy of Northwind.mdf here. We can proceed if once you have possession of the Northwind.mdf file.

Visual Studio has a great tool for generating LINQ to SQL classes with the use of Object Relational Designer. You can simply drag and drop tables there and Visual Studio will automatically create the necessary classes the corresponds to each table and rows of the specified table and database. You will then see the tables with properties corresponding to its columns or fields. Arrows will also be visible representing relationships between tables.

Figure 1 – Object Relational Designer showing tables, fields and relationships.

Classes will be created that represents the rows of each table.For example, we have an Employees table. Visual Studio will automatically singularize the table’s name and an object named Employee will be created that will represent each row or records of the particular table.

A corresponding class of type Table<TEntity> of the System.Data.Linq namespaces will be created for every table included in the LINQ to SQL Designer. The TEntity is replaced with the class of the row it contains. For example, the Employees table will have a corresponding class of Table<Employee> class. An object of that class will then be created containing a collection of objects for each of its rows or records. Table<TEntity> implements IQueryable<TEntity> interface of the System.Linq namespace. When a LINQ queries an object that implements this interface and obtains results from a database, the results are automatically stored to the corresponding LINQ to SQL classes.

For related tables which are connected to each other via foreign keys, for each foreign key a table has, Visual Studio creates a corresponding property with the type and name similar to each row of the table the foreign key points to. Additionally, for a table whose primary key(s) are used as a foreign key by other tables, for each of those foreign tables, a property is also created. These additional properties allow you to call the properties of the foreign table of a current table. For example, two tables named Employees and Companies table both have CompanyID fields. The CompanyID is the primary key of the Companies table, and the CompanyID field of the Employees table is a foreign key pointing to the CompanyID of the Companies table. When Visual Studio creates the corresponding row class for each table, it will also consider the foreign keys. The Employee class for the Employees table will have an additional Company property since one of its columns points to the Companies table. The Company class of the Companies table will have an additional Employee property because the Employees table points to the Categories table.

LINQ to SQL also creates a DataContext class which inherits from the System.Data.Linq.DataContext. This class will be responsible for connecting the program and the database. The objects created for each table that you include in the LINQ to SQL designer becomes a property of this class. Visual Studio will automatically create the DataContext in a format <Database>DataContext where <Database> is the name of the database. For example, using our Northwind database, a NorthwindDataContext will be created with properties corresponding to each table we have included. These properties contain collections of objects representing the rows of each table. For example, our NorthwindDataContext class will have an Employees property which corresponds to the Employees table. This property is a collection of Employee objects representing each row of the table.

The next lesson will show you an example of using LINQ to SQL and connecting your application to the Northwind database using this technology.