LINQ to Relational Data: Who’s Who? With the combined launch of Visual Studio 2008, SQL Server 2008, and Windows Server 2008, Microsoft is introducing five implementations of .NET Language Integrated Query (LINQ). Of these five implementations, two specifically target access to relational databases: LINQ to SQL and LINQ to Entities. Microsoft Language Integrated Query (LINQ) offers developers a new way to query data using strongly-typed queries and strongly-typed results, common across a number of disparate data types including relational databases, .NET objects, and XML. By using strongly-typed queries and results, LINQ improves developer productivity with the benefits of IntelliSense and compile-time error checking.
LINQ to SQL, released with the Visual Studio 2008 RTM, is designed to provide strongly-typed LINQ access for rapidly developed applications across the Microsoft SQL Server family of databases. LINQ to Entities, released in an update to Visual Studio 2008 in the first half of 2008, is designed to provide strongly-typed LINQ access for enterprise-grade applications across Microsoft SQL Server and third-party databases. What Is LINQ to SQL? LINQ to SQL is an object-relational mapping (ORM) implementation that allows the direct 1-1 mapping of a Microsoft SQL Server database to .NET classes, and query of the resulting objects using LINQ. More specifically, LINQ to SQL has been developed to target the rapid development scenario against Microsoft SQL Server. 
Figure 1: Database diagram for the Northwind database.
Figure 2: LINQ to SQL mapping diagram for a simple scenario using the Northwind database and the associated database diagram. Notice the use of an intermediary table to map the many-to-many relationship between Employees and Territories. Figure 1 and Figure 2 combined with the code snippet below demonstrate a simple LINQ to SQL scenario. Figure 1 shows the LINQ to SQL mapping, and Figure 2 shows the associated database diagram, using the Northwind database. This code snippet shows a simple LINQ query against the Northwind database. DataContext db = new DataContext();
var customers = from c in db.Customers
where c.City == "London"
select c;
With this knowledge, you can see that many aspects of LINQ to SQL have been architected with simplicity and developer productivity in mind. APIs have been designed to “just work” for common application scenarios. Examples of this design include the ability to replace unfriendly database naming conventions with friendly names, map SQL schema objects directly to classes in the application [a table or view maps to a single class; a column maps to a property on the associated class], implicitly load data that has been requested but has not previously been loaded into memory, and use common naming conventions and partial methods to provide custom business or update logic. | " |
Many aspects of LINQ to SQL have been architected with simplicity and developer productivity in mind. APIs have been designed to “just work” for common application scenarios.
| " |
Partial methods, a new feature of C# and Visual Basic in Visual Studio 2008, allow one part of a partial class to define and call methods that are invoked, if implemented in another part of the class, otherwise the entire method call is optimized away during compilation. By using common naming conventions in conjunction with these new partial methods and partial classes, introduced in Visual Studio 2005, LINQ to SQL allows application developers to provide custom business logic when using generated code. Using partial classes allows developers the flexibility to add methods, non-persistent members, etc., to the generated LINQ to SQL object classes. These partial methods can add logic for insert, update, and delete by simply implementing the associated partial method. Similarly, developers can use the same concepts to implement partial methods that hook up eventing in the most common scenarios, for example OnValidate, OnStatusChanging or OnStatusChanged. Microsoft developed LINQ to SQL with a minimally intrusive object model. Developers can choose not to make use of generated code and instead create their own classes, which do not need to be derived from any specific base class, meaning that you can create classes that inherit from your own base class. Inheritance, an important feature of object-oriented programming, does not translate directly into the relational database. Given this, the ability to map in inheritance is very important. LINQ to SQL supports one of the most common database inheritance mappings, where multiple classes in a hierarchy are mapped to a single table, view, stored procedure, or table valued function using a discriminator column to determine the specific type of each row/instance. As with any application framework, developers must also have the ability to optimize the solution to best fit their scenario. LINQ to SQL offers a number of opportunities to optimize, including using load options to control database trips and compiled queries to amortize the overhead inherent in SQL generation. By default, LINQ to SQL enables deferred loading. This means that if, for example, I query for my Customer data using the Northwind model in Figure 2, I do not automatically pull the associated Order information into memory. However, if I try to access the associated Order information via a navigation property from a Customer instance, the associated Order information is automatically pulled into memory for me in a second database round trip. In the following code snippet the Order information is not loaded into memory until I access it from the second foreach statement. var customers = from c in db.Customers
where c.Orders.Count > 5
select c;
foreach(var row in customers)
{
Console.WriteLine("Customer ID = " + row.CustomerID);
foreach(var order in row.Orders)
Console.WriteLine("Order ID = " + order.OrderID);
}
In the above example, a separate query is executed to retrieve the Orders for each Customer. If you know in advance that you need to retrieve the orders for all customers, you can use LoadOptions to request that the associated Orders be retrieved along with the Customers, in a single request. Also by default, LINQ to SQL enables ObjectTracking, which controls the automatic change tracking and identity management of objects retrieved from the database. In some scenarios, specifically where you are accessing the data in a read-only manner, you may wish to disable ObjectTracking as a performance optimization. Compiled queries offer another opportunity to further optimize query performance. In many applications you might have code that repeatedly executes the same query, possibly with different argument values. By default, LINQ to SQL parses the language expression each time to build the corresponding SQL statement, regardless of whether that expression has been seen previously. Compiled queries allow LINQ to SQL to avoid reparsing the expression and regenerating the SQL statement for each repeated query. DataContext db = new DataContext();
var customers =
CompiledQuery.Compile(
(DataContext context, string filterCountry )
=>
from c in db.Customers
where c.Orders.Count > 5
select c;
foreach(var row in customers(db, "USA"))
{
Console.WriteLine(row);
}
foreach(var row in customers(db, "Spain"))
{
Console.WriteLine(row);
}
The above code snippet shows an example of a simple compiled query, executed twice with varying parameters. | & | | 
By: Elisa Flasko
Elisa is a Program Manager with the Data Programmability team at Microsoft focused on a number of Data Platform Development technologies including ADO.NET, XML and SQL Native Client products. Prior to joining Data Programmability, Elisa worked as a Technical Presenter Business Development Manager for Microsoft, travelling across North America. Elisa has previously worked in both large and small companies, in positions ranging from sales to quality assurance and from software development to program management.
elisa.flasko@microsoft.com | Fast Facts | | With the introduction of Language Integrated Query in both C# and Visual Basic and the release of Visual Studio 2008, Microsoft is introducing FIVE different implementations of LINQ: LINQ to Objects, LINQ to SQL, LINQ to Entities, LINQ to XML, and LINQ to DataSet. | |
|