LINQ to the Future

Microsoft's new Language Integrated Query (LINQ—pronounced link) project is the next step in database-development technology. LINQ addresses the current database-development model's disconnect between the object-oriented programming model and procedural, T-SQL?based data-access code. Today, database-application developers use an object-oriented language such as C# or Visual Basic.NET to develop applications. ADO.NET serves as the object-oriented data-access middleware that connects the application with the database. However, even though the language and the data-access technology are all object oriented, developers still typically write data-access code in procedural T-SQL. For data access, ADO.NET provides an object-oriented wrapper around the TSQL code that the developer writes. This development paradigm requires developers to know not only the object-oriented .NET language but also T-SQL, and it forces developers to use two technologies to develop applications. Although Visual Studio's IntelliSense and code-completion features can help developers write correct .NET code, the IDE provides no design help with the T-SQL part of the development. For example, the IDE can't show the available databases and doesn't prompt the developer for the available tables or columns. Further, the only time the IDE even knows whether the query has been built with the correct syntax is at runtime, when a syntax error generates an exception.

LINQ technology addresses this problem through a set of .NET language extensions that let database developers write database queries and updates in either Visual Basic (VB) or C# without requiring them to drop back to T-SQL to write their data-access code. LINQ lets query expressions benefit from the rich metadata, compile-time syntax checking, type checking, and IntelliSense that until now were available only to native .NET code. LINQ has two implementations: one for XML (XLinq) and the other for databases (DLinq). XLinq enhances the .NET language with Xpath and XQuery functionality, and DLinq enhances the host .NET language with SQL-like query-expression statements. In this article, I give you a preview of the new DLinq technology you'll see in the beta and show you some early DLinq code that can connect to SQL Server to query and update a SQL Server database.

The Blue Pill

In the movie "The Matrix," characters swallowed a blue pill that altered their perception of reality. Although LINQ technology will change the way you think about developing applications for the database world, you don't need to swallow a blue pill.You just need to download the prerelease code from Microsoft. For details about obtaining LINQ, see the sidebar "Getting Started with LINQ." Once you've installed LINQ support, you can create a LINQ project by starting Visual Studio 2005, then selecting the File, New, Project option to display a New Project dialog box like the one that Figure 1 shows.

As you can see in Figure 1, the early LINQ release supports building console applications, Windows applications, LINQ libraries, or WinFX applications for Vista. To build a Windows application, select the LINQ Windows Application template from the Visual Studio New Project dialog box, give the project a name, and click OK to generate a starter project. The LINQ Windows Application template adds a reference to the System.Data.DLinq assembly and the System.XML.XLinq assembly. In addition, the template adds declarations for the XLinq and DLinq namespaces. For answers to some basic LINQ questions, including availability, see the sidebar "LINQ FAQs."

Connect

DLinq is a layer on top of ADO.NET that lets you freely mix DLinq code with ADO.NET code. To create a connection to a SQL Server database, you can instantiate an ADO.NET Connection object, then pass that connection object to the DLinq Data-Context object—thus connecting your DLinq application to SQL Server. All the code that uses the DLinq DataContext will in turn use the underlying ADO.NET connection.

The C# code in Listing 1 shows an example that connects DLinq to SQL Server through ADO.NET. At the top of this listing, you can see import declarations for the two LINQ namespaces and the declaration for the SQL Server .NET Data Provider. The code inside the DLinqConnect function connects to SQL Server by first creating an ADO.NET Connection object named cn, then passes that Connection object to the constructor of the DLinq DataContext.

Query

The really interesting parts of DLinq come out when you begin to use it for database access. DLinq relies on a database-to-object mapping that you must set up in your program.You can perform this mapping manually for simple one-off tables; however, manual mapping would be too tedious for most databases. To ease the process, Microsoft includes the SQLMetal utility as a part of the LINQ downloadable code. SQLMetal is a command-line utility that reads the contents of a database and creates an object-mapping file for your DLinq application. To use SQLMetal to create a class file containing the database mappings for the sample AdventureWorks database, use the following line:

C:Program FilesLINQ   PreviewBin>SqlMetal  /server:sql2005  /database:AdventureWorks  /namespace:Adventureworks  /code:AdventureWorks.cs 

Although the entire contents of the AdventureWorks.cs file are too big to list, you can get a taste for the relational object mapping that DLinq uses by looking at the code in Listing 2, which shows part of the mapping for the Department table. The Table decoration at the top of this listing identifies the name of the table in the target database as HumanResources.Department, and the class name that the DLinq application code uses is Department. Next, the code declares four private member variables for internal storage of the column variables. Then, you see a Column decoration for each column in the table. This code snippet shows the Column decorations for only the first two columns, DeaprtmentID and Name. For each column, the code defines the data type along with get and set methods that access the column's data values.

After you create the object mapping, you're ready to use DLinq to query the database. You can add the Adventure-Works.cs class file to your project by using the Project, Add Existing Item option from the Visual Studio menu.You can add a using statement (e.g., using Adventureworks;) to your other class files that need to access the database.

Now, you're ready to run DLinq queries that access the AdventureWorks database. Listing 3, shows a simple query that returns the contents of the HumanResources.Department table in the AdventureWorks database. Here, the DataContext object uses a connection string to connect to the SQL Server AdventureWorks database. This connection string is the same as a standard ADO .NET connection string. The DataContext object is responsible for translating the DLinq query operators to SQL, which the DLinq code will send to the target database.

Then, the GetTable method creates a table variable that a DLinq query expression can use. In this example, the code creates the Department variable for the Department table. Next, the new var keyword creates a variable named qry. The var keyword is different from a standard declaration because var doesn't directly create a given type. Instead, it instructs the compiler to determine the variable type. You can see the actual query operators in the assignment to the qry variable.

Unlike in standard ADO.NET, you don't need to specify the SELECT statement in a text string that's passed to a command object. Instead, the query operators are integrated directly into the .NET language. The query must begin with the from clause, which is a generator for an iterator variable that the application uses over the specified table object. The query can have optional where, orderby, ascending, and descending clauses to filter and sort the results. Next, the foreach statement iterates over the content of the result set, adding the values of the DepartmentID and Name columns to a ListBox.

When the foreach statement executes, the DLinq code accesses SQL Server and begins returning results to the application. Figure 2 shows the result of the simple DLinq query.

DLinq can do the same type of joins, selections, and ordering of result sets that TSQL can. The example in Listing 4 illustrates how to use DLinq to join the Employee and Contacts tables. The DataContext object creates the connection to the SQL Server AdventureWorks database. Then, the Get-Table method creates variables for the Employee and Contacts tables. Next, the code sets up the qry variable with a join query. In this query, the from clause specifies the two tables, separated by a comma, that will be joined. Just as in T-SQL, you can assign a shorthand alias to each table. The code will reference the Employee table as e and the Contact table as c.The where clause specifies the join condition. The code will join the Employee and Contact tables by using the ContactID column from each table.

The select new clause shapes the results that will be returned. This example returns the EmployeeID column from the Employee table and the values from the FirstName and LastName columns from the Contact table.The foreach statement iterates over the results, adding the values to a ListBox. Figure 3 shows the results of the DLinq join query.

Insert/Update/Delete

In addition to performing queries, DLinq can also insert, update, and delete data from the target database. As you saw in the previous query examples, you don't have to change programming paradigms or drop back into T-SQL.All the update actions are fully object oriented.

As Listing 5 shows, adding rows to a table is a straightforward process. Creating the connection and the variable for the Department table is the same as the process you saw in the previous query listings. To add a new row a new instance of the Department table, you create an object named newDept by using the new keyword. Then, you assign values to the newDept object's properties. Remember that each property represents a column in the target table. In this example, the Name column is set to "New Department," the GroupName column is set to "Research and Development," and the ModifiedDate column is set to the current date and time. After you assign the values, you use the Add method to add the row to the Department table object. This action updates the object but not the database.The HumanResources.Department table isn't updated until the SubmitChanges method executes. The reminder of the code in the listing queries the table to retrieve the new values, as you saw in the previous examples. Figure 4 shows the new row that the code added to the HumanResource.Department table.

The DLinq code that you need to update a row is a bit different.The code in Listing 6 retrieves the newly added row and changes the value of the Name column from "New Department" to "Updated Department". The code to connect to the database and create the table variable is the same as in the previous examples. The biggest change in this example is the use of the First() method in the DLinq query. The First() method returns one object instead of a collection of objects.

The update query that Listing 6 shows retrieves the row in which the value of the Department table's Name column is "New Department." After the singleton object is returned, you can update the object's value by using a standard assignment operator, which will result in the execution of the column object's Set method. You can then update the target database table by using the Submit-Changes method. The reminder of the code in Listing 6 queries the table again to return the updated values.

The DLinq code for deleting a row is similar to the previous Listing's update code, as Listing 7 shows. After creating a connection and the database table variable, a DLinq query that use the First() method returns one object representing the row that will be deleted. This query goes on to retrieve the row from the Department table in which the value of the Name column is "Updated Department. "To actually delete the row, you use the Department object's Remove() method.

Up LINQ

LINQ is quite possibility the biggest paradigm change for database developers since the advent of ODBC. LINQ eliminates the language-database disconnect and enables object-oriented database access through SQL-like extensions to VB or C# languages. Although it's not supported in the early release code, one of the natural uses for DLinq (and one that I expect will be supported in the final release) is writing SQLCLR objects. Considering that LINQ is still in its early stages, it might be a while before you have to start worrying about converting your applications to use LINQ, but there's little doubt that LINQ and DLinq represent the future of database development.

Michael Otey ([email protected]), technical director for SQL Server Magazine, is president of TECA, a software-development and consulting company in Portland, Oregon, and coauthor of The SQL Server 2005 Developer's Guide (Osborne/McGraw-Hill).