A couple of years ago I wrote an initial introduction to Language-Integrated Query (LINQ) to SQL (see “LINQ to Your SQL Server Data”). One of LINQ’s original goals was to let application programmers replace the use of T-SQL with Visual Studio and LINQ queries that could be converted into T-SQL–based queries. LINQ to SQL soon fell by the wayside as Microsoft pushed its ADO.NET Entity Framework as the up-and-coming data access technology. However, LINQ still has a place in the application development architecture, and Microsoft continues to support the LINQ to SQL subset—albeit with few, if any, enhancements.
Although Microsoft is no longer moving forward with the specific subset of LINQ that focuses on SQL Server and the integration with a graphical table designer, the core of LINQ to SQL is still supported. In this article I review the core features of LINQ to SQL and discuss some of the new roles of LINQ in the typical application architecture. I take a look at new LINQ features, such as Parallel LINQ and the use of LINQ in SharePoint.
LINQ Capabilities
Although Microsoft isn’t moving forward with the portion of LINQ that generates T-SQL code to run against SQL Server, the company still supports LINQ for processing query results. LINQ tends to be referred to in the context of a second technology group—the provider—such as LINQ to XML, LINQ to SQL, or LINQ to Entities. Each of these categories builds on the core LINQ syntax, and the differences between categories (e.g., LINQ to DataSets versus LINQ to SQL) can be subtle. A developer might initially suggest using LINQ to SQL even if he or she actually means LINQ to Entities or LINQ to DataSets. Some developers will continue leveraging LINQ to SQL as a familiar tool in certain situations, although LINQ’s larger focus is on data already returned from SQL Server. The area in which LINQ continues to have a robust role is in processing data in the form of entities and the results from processing stored procedures against a database.
As Figure 1 shows, LINQ provides six standard methods for accessing data (through Objects, Entities, SQL, DataSets, CAML, and XML). Of course there are literally dozens of third-party providers for LINQ, which is one of the strengths of LINQ as a technology. For SQL access, Microsoft provides LINQ to SQL. Microsoft also supports other technologies such as straight ADO.NET, the Entity Framework, and WCF data services for database access. Although most of these technologies have unique capabilities, several of them also overlap at some level. For example, the capabilities of LINQ to SQL are essentially a subset of the Entity Framework 4.0 capabilities. However, the Entity Framework was designed from the ground up to provide a more abstract layer than what's provided by LINQ to SQL. The Entity Framework abstraction adds some complexity but with the benefit of greater adaptability.
Each of these data access technologies has different characteristics that make its use appropriate. Some of those characteristics are technical—for example, LINQ to SQL supports only SQL Server databases; if you need to query an Oracle database, LINQ to SQL isn’t an option. Others depend on application characteristics—for example, if you want to create abstractions of the core tables (i.e., convert the relational data into an object model), the Entity Framework is much more powerful than the other options, particularly LINQ to SQL.
LINQ to SQL
The logical starting point for getting into the nuts and bolts of LINQ and SQL Server is the core LINQ to SQL capability. Unlike old database technologies (e.g., Data Access Objects—DAOs), although Microsoft hasn’t been developing enhancements for LINQ to SQL, the company hasn’t completely abandoned it. For example, the version of LINQ that shipped with .NET Framework 4.0 includes approximately 50 changes, with a couple of them being potentially ground-breaking updates. (For more information, see Damien Guard’s blog post “LINQ to SQL changes in .NET 4.0.”) However, if you read the list, you’ll see that most of the enhancements focus on addressing issues rather than improving the tool or adding new data type support. As Guard notes in his blog, Microsoft’s official policy is that the company will improve the core of LINQ and might implement some customer requests, but the Entity Framework is the company’s primary focus.
Clarifying the true core LINQ to SQL technologies is important. LINQ to SQL has essentially two execution models. One such model is its support for the dynamic creation and submission of T-SQL queries to SQL Server. The second model is the execution of existing T-SQL such as stored procedures from which it returns a data set. The generation and execution of T-SQL commands is the heart of what's unique and might be better described as LINQ to SQL Server. The logic used is specific to SQL Server as opposed to any other database management system (DBMS) and is part of the reason LINQ to SQL (unlike the Entity Framework) is SQL Server–specific. However, the core of this logic is really tied to the generation of dynamic SQL and was considered one of the initial strengths of LINQ.
LINQ to SQL’s unique element isn’t its use of a data context, but rather what LINQ is doing as part of a dynamic query. For example, when you query a SQL Server data table with a LINQ query, LINQ actually generates T-SQL and submits that query to the database. However, if you instead use LINQ against the same context to execute a stored procedure, there’s no T-SQL to generate. Instead, your query will process the return from the stored procedure—a data set. Thus, although most people associate the use of stored procedures with LINQ to SQL and we talk about it and implement it in that context, the results of a query using a stored procedure could just as easily be run from an Entity Framework context or even from straight ADO.NET. The code is actually using LINQ to Datasets as opposed to LINQ to SQL.
Because the DataContext object (e.g., System.Data.Linq.DataContext) lets you reference a stored procedure, the initial and functional impression is that LINQ supports stored procedures. However, just using LINQ to execute a stored procedure is overkill. Some application developers might have only stored procedure access to their databases, in which case LINQ to SQL probably isn’t the best data access technology. You would probably leverage the Entity Framework in a .NET Framework 4.0 application, using the Entity Framework for other tasks.
Another scenario you might face is that you’re using .NET Framework 3.5 or 3.0 but aren’t yet on .NET Framework 4.0. In that case the Entity Framework is less than optimal because although previous versions support the Entity Framework, it isn’t really a viable choice until .NET Framework 4.0. You might opt to work with LINQ in such a case, or you might prefer to work with ADO.NET and data sets instead, leveraging LINQ in the middle tier.
On the other side, there’s the question of moving forward. What if you’ve already invested heavily in LINQ—are you stranded? The answer is no. When you’re ready to move forward, you should migrate to the Entity Framework and create a new data context based on your entities. As long as you have entities that match the tables you used with LINQ to SQL, you can simply change the data context for your LINQ query. Although the process isn’t automatic, taken in the larger context of moving to the Entity Framework the transition isn’t too painful and lets you continue to leverage the effort spent creating your LINQ to SQL queries