Introducing LINQ to Relational Data
LINQ introduced the concept of a unified data access technique to eliminate challenges of accessing data from a disparity of sources. LINQ has been extended to include the ability to access relational data through LINQ to SQL and LINQ to Entities. LINQ to SQL offers a direct mapping to the Microsoft SQL Server family of databases and allows you to query and manipulate objects associated with database tables. LINQ to Entities offers a more flexible mapping to relational data stored in other databases beyond just SQL Server. You build against a conceptual schema that is mapped to an actual schema. The remainder of this article will concentrate on LINQ to SQL.
LINQ to SQL Object Model
LINQ to SQL allows you to query and manipulate objects associated with database tables. Creating an object model involves creating a typed object that represents the database connection along with classes that map to database entities. The following list of objects are involved:- DataContext – A base class that is a disposable type that gets extended to represent a strongly typed connection to the database.
- Classes and Collections – Represent objects and collection of objects that map to database entities.
- Methods – Are used to extend classes or to represent stored procedure calls.
There are a few different ways to create an object model. The two most straight forward approaches are to use the provided designer within Visual Studio 2008 to auto-generate the objects and mappings or adding attributes to existing objects. For this example I downloaded the Northwind sample database and loaded it in my SQL Server so that I could retrieve data from it for testing. I created a new solution and added a console application. I then added a LINQ to SQL data class to the console application. I used the Server Explorer to setup a new connection to the Northwind database my local SQL Server, and then dragged all of the tables from the Server Explorer on to the design surface for the data class. Finally I right clicked on the design surface and chose “Layout Diagram” to auto arrange all of the newly added tables and relationships. Refer to Figure 1 for an example of the completed design surface.
Click here for larger image
Figure 1 – LINQ to SQL Data Class Design Surface
Test Driving LINQ to SQL through Examples
Now that we’ve covered the background let’s use a couple of examples of LINQ to SQL. Our examples will demonstrate the use of the DataContext object along with an example of querying the Northwind sample database using expressions.Querying Data
The following example code uses LINQ syntax to query data from the database and then loop through it to print it to the console. You’ll notice how the query expression syntax is the same as what you would find whether it be LINQ to XML or another form of LINQ. Also notice how the NorthwindDataClassesDataContext object is wrapped within a using construct.The NorthwindDataClassesDataContext
is an object that extends the DataContext class and represents a strongly typed connection to our database. The NorthwindDataClassesDataContext
is a disposable type, so wrapping it in a using block ensures it is properly disposed after use.using (NorthwindDataClassesDataContext context =
new NorthwindDataClassesDataContext())
{
var results = from customers in context.Customers
where customers.City == “London”
orderby customers.CompanyName
select customers;foreach (var customer in results)
{
Console.WriteLine(“Company is {0} and contact is {1}”,
customer.CompanyName, customer.ContactName);
}// Pause to see the output
Console.ReadLine();
}