LINQ to Your SQL Server Data

Language Integrated Query (LINQ), which is now available in Microsoft Visual Studio 2008, lets developers use native syntax with traditional programming languages, such as C# and Visual Basic (VB), to reference database objects as native language objects and create queries against these objects. Visual Studio 2008 and LINQ queries convert procedural code into T-SQL–based database calls, which are then executed against SQL Server. LINQ is an excellent tool for rapid application development (RAD) and generates reasonably efficient queries, although a qualified DBA can often optimize such queries further. The three versions of LINQ that are currently available are LINQ for SQL, LINQ for XML, and LINQ for Objects. Let’s take an in-depth look at LINQ for SQL, which I’ll refer to simply as LINQ.

As a new .NET technology, LINQ is part of the Microsoft .NET Framework 3.5 libraries. To leverage LINQ, your Visual Studio 2008 projects will need to target the .NET Framework 3.5. LINQ has some specific data type requirements that previous .NET versions didn’t support, causing Microsoft to make some major changes in the core .NET languages. For example, C# now includes the var type, and VB now includes inferred types. These .NET object types enable you to specify a type when you don’t know the data type of a query’s results before you execute the query.

LINQ Basics

Let’s quickly look at how LINQ works. Visual Studio 2008 provides developers with integrated tools for data-access technologies in SQL Server 2005. Note that Microsoft didn’t release a service pack for SQL Server 2005 to support the .NET Framework 3.5 and LINQ because, behind the scenes, LINQ leverages ADO.NET and the existing data-access methods that the .NET Framework 2.0 already supports.

In LINQ, an object model represents a data source. LINQ then references that data source as a DataContext object (e.g., System.Data.Linq.DataContext). The Data- Context object encapsulates the ADO.NET Connection string for your database. The DataContext object is then used with a set of object definitions (e.g., System.Data .Linq.Mapping.TableAttribute) for the tables, stored procedures, and functions in your database. Each database object that you define requires a DataContext object.

Creating Classes Using SQLMetal

You have a couple of options for creating the classes that you need to leverage LINQ within your application code. You can use Visual Studio’s (VS’s) object-relational mapper to manually type each of the necessary classes. Or you can use VS’s typed dataset tools to handle the data access and retrieval, and then use LINQ to query the result sets created by your typed datasets. Of course, using object-relational mapping limits you to one-toone (1:1) relationships between tables in your database and the objects you create. As you’ll see in the Load method, 1:1 relationships aren’t always going to meet your requirements. Therefore, I’m going to show you how to use SQLMetal to generate entity classes for your database objects.

SQLMetal.exe is a free database-mapping utility that’s included in Visual Studio 2008. You can find this command-line tool under Program FilesMicrosoft SDKsWindows V6.0abin. SQLMetal generates the necessary data entity and DataContext object for LINQ as either a .vb or .cs source file. To create VB source files for a database on your local SQL Server and include stored procedures in them, open the command window, navigate to the installed location (or reference the SQLMetal tool in that directory), and run the following command:

 SQLMetal.exe /server:.SQLEXPRESS  /database:AdventureWorks /  sprocs /functions /language:vb /  code:AdventureWorks.vb

This command creates the AdventureWorks.vb file in the current directory. Note that you’ll want to change the server reference because .SQLEXPRESS references the SQL Express instance on my local server. You’ll want this parameter to contain either a shortcut to the local machine or the name of your database server. The database:AdventureWorks parameter specifies the database that’s being processed. The sprocs and function parameters indicate that you want SQLMetal to generate entity files to support the stored procedures and functions within your database. The language:vb and code:AdventureWorks.vb parameters specify your programming language and target source file, respectively. To create the AdventureWorks file in the current directory in C#, you must replace the language:vb parameter with the language:cs parameter and the .vb extension with the .cs extension. The resulting source file when I ran this command on my test machine consisted of about 20,000 lines of source code.

Using LINQ

Once you’ve generated the database entities, you can add the AdventureWorks.vb file to any project. To do so, create a new VB Windows Forms project. Next, right-click your project in the Solution Explorer and click Add Existing Item in the context menu to open the Add Existing Item dialog box, which you can use to navigate to the AdventureWorks.vb file. Then add this file to your project.

When you add the AdventureWorks.vb file to the project, VB’s background compiler will generate hundreds of errors because Visual Studio 2008 doesn’t add the references necessary for LINQ to SQL. To make these errors disappear, go to the project properties, click the References tab, and add System.Data.LINQ as a reference to your project. Then, click the Load Web Settings tab and use the interface shown in Figure 1 to add the AdvWorksDB Connection string setting for your database. This Connection string setting should use the same settings you used with SQLMetal to generate your source file. Note that you’ll need to reference security (e.g., Integrated Security in Figure 1) in your Connection string setting.

Next, create a simple form. For this example, I’ve placed a DataGridView control at the top of the form and four command buttons below it. Although the locations of the buttons aren’t important, you should name the buttons ButtonLoad, ButtonAdd, ButtonEdit, and ButtonDelete. Next, double-click each button in the design view so that Visual Studio 2008 will automatically generate the Click event handler for each button.

Now, double-click the design surface of the form to generate a Load event for the form. Then, modify the Load event with the code shown in Listing 1. Callout A in Listing 1 shows the definition of the DataContext object named AdvWorksDC that leverages the database Connection string you defined earlier. This DataContext object has been defined in the scope of the form, which enables the reuse of the DataContext object across the event handlers on the form. The second line of code in Callout A, Listing 1, defines an entity object (i.e., Department) for the HumanResources_Department table from the AdventureWorks database, which will also be used across multiple event handlers.

In Callout B in Listing 1, the code defines the Form Load event for the display. Within the Load event in Callout B in Listing 1, the code disables the Add, Edit, and Delete buttons. Because the Load event occurs only once, now is a good time to create an initial query of some data to populate the grid. Rather than use a simple query that’s easy to add, update, and delete, this complex query illustrates more of the format of LINQ queries. LINQ queries differ from SQL queries in that they start with the FROM clause. This clause lets you specify a target in-memory table to hold your query definition. The In portion of the FROM clause lets you identify where in the DataContext object you intend to make your query. Once you’ve defined the context of your query, the LINQ engine can provide IntelliSense for the table(s) and columns that are available in your query.

The query in Callout B in Listing 1 uses a Join statement, which names a second table and specifies which columns will be used to join the two tables. The query also includes a WHERE clause to limit the number of results returned. The result is the DS object, which is created as a query object based on type inference. This query object maintains the underlying query and provides an enumerator that lets you retrieve each row of results. The query object is then assigned as the data source for the datagrid. The resulting grid (shown in Web Figure 1) doesn’t support editing or adding entries at runtime. Instead you have created the functional equivalent of a T-SQL statement in which the results have been assigned to a data set.

Adding Rows to the Grid

Now you can compile and run the application, and it will execute the query defined in the Load event and that data is loaded into the datagrid. However, that query references a table with multiple relationships. To illustrate the Insert, Update, and Delete operations, I’m going to use a query that references a table with no relationships. Listing 2 shows the implementation of three methods key to this process: ButtonLoad_Click, Bind- Grid, and ButtonAdd_Click. The ButtonLoad_Click method reloads the grid with a different table (i.e., the HumanResources_Department table). The BindGrid method creates the actual LINQ query against the DataContext object for the entries in the table and then updates the DataGridView data source with this new query. The query demonstrates the ORDER BY clause, which should be familiar to T-SQL developers. Finally, the ButtonAdd_Click method activates the Add button, which Web Figure 2 shows.

The Add button calls the ButtonAdd_Click method in Listing 2 to add a new entry to the current table. This method leverages the Department entity object created as part of your form definition in Listing 1. The entity object is then associated with a newly created instance of a department as shown on the first line of the ButtonAdd_ Click method in Listing 2. This line of code leverages one of the new VB syntax elements in that it assigns values to the properties of an object when the object is created. The With {.PropertyName = value} syntax lets you assign values to an object’s properties when the object is created using the New statement. This line shows the creation of a new Department entity that will reflect a row when it’s updated in the database.

Next, you need to insert the new department entity into the table. Updating the table is a two-step process: First, you need to associate the new department object with your database object’s list of queued insertion statements by using the InsertOnUpdate method. This method tells LINQ that the object is to be inserted into the table, and lets you potentially define multiple new objects before updating the database. With LINQ, these and other updates are kept local until you execute the SubmitChanges method. Second, the SubmitChanges method tells LINQ to take those cached data updates and apply the generated T-SQL code associated with each to your SQL data source. If you want to add a collection of entities, you can postpone updating the database until all of the entities are created. Once you click the Add button, the display is updated, with the newly created department appearing at the top of the list, as shown in Web Figure 3.

Updating and Deleting Rows

Now that you’ve inserted a row, let’s look at how you can update it. Because your entity object was created from your current DataContext object, all you need to do is update one of the properties of your entity object to contain the value you want to place in the database. In my example, the name of the department will change from Bike Computers to Fitness Computers.

To update the database, call the SubmitChanges method on your DataContext object. Web Figure 4 shows how the department name changes from Bike Computers to Fitness Computers after the Submit- Changes method has been executed. The new department name replaces the original department name. Now only the Delete button is available for use.

In theory, it’s just as easy to delete a row as it is to update one; however, behind the scenes there’s a challenge. By default, LINQ uses an optimistic locking scheme. If LINQ thinks that the data underlying your object was changed, it will refuse to update that object and will return an error message saying that the row can’t be found or changed. This error message will also be displayed if you edit the same entity more than once or attempt to delete the entity after editing it.

One solution to this problem is to replace the instance of your DataContext object with a new DataContext instance. The first two lines of the ButtonDelete_Click event handler (shown in Listing 3) recreate your Data- Context object and associate your in-memory entity object with the newly created DataContext object. However, this solution can affect performance because it requires you to create a new database connection.

Once you have the call to refresh the DataContext object in place, the code calls the DeleteOnSubmit method to remove the current entity from the table and then submit changes to actually remove the entity’s row from the table. After you click the Delete button, you’ll find that the display looks like Web Figure 2 once again, and you can repeat the process. Note that the DeleteOn- Submit method replaces the Remove method, which was found in earlier versions of LINQ.

How Developers View LINQ

From the standpoint of a developer who is focused on back-end work and familiar with T-SQL, LINQ is yet another query interface that has to be mastered but that doesn’t seem to provide a lot of value. From the standpoint of a UI developer who is more accustomed to data binding and data manipulation, LINQ is a wonderful tool that simplifies data-access work.

I think these two views represent the best evaluation of LINQ. As a RAD tool, LINQ is a powerful addition to the .NET developer toolkit. It provides a discoverable interface that automates the building of potentially complex queries. However, if you’re a data professional looking to optimize your data-access strategy, you’re likely to find that LINQ doesn’t really affect your daily tasks.