2.5. LINQ to SQL in Visual Studio 2008

Visual Studio 2008 provides functionality to support LINQ application development. The compiler is upgraded to support LINQ query syntax, and IntelliSense supports almost every LINQ component. Further, a really great tool has been added to Visual Studio: the Linq to SQL Classes Designer. It is similar to SQLMetal in that it produces the code to manage entity classes related to database tables, but it has these advantages:

  • It produces entity classes just for specified tables, not for the full database.

  • It produces entity class associations using a visual tool.

  • It customizes the entity class behavior, letting us choose the stored procedures to run when insert/update/delete commands occur.

  • It supports entity class hierarchies.

  • It is completely a visual tool and the final result offers a visual representation of classes, associations, and so on within a colored diagram.

2.5.1. A Linq to SQL File Designer Example

Using Linq to SQL File Designer is very easy. Starting from a Windows Application project, you have to add a new Linq to SQL Classes item to the solution. Visual Studio then shows you the Linq to SQL Classes Designer together with a new toolbox.

Follow these steps to add database support using Linq to SQL Classes Designer:

  1. Launch Visual Studio 2008 and create a new project with File New Project.

  2. Choose the Windows Forms Application template, as shown in Figure 2-13.

    Figure 2-13. Creating a new Windows Forms application from Visual Studio 2008

  3. From the Solution Explorer, right-click on the solution name and choose Add New Item from the context menu as shown in Figure 2-14.

    Figure 2-14. Adding a new item to the solution

  4. From the Add New Item dialog box, select the Linq to SQL Classes template and give it a significant name, as shown in Figure 2-15.

    Figure 2-15. Adding a new Linq to SQL File template to the solution

  5. At this point Visual Studio will present the Linq to SQL Classes Designer and provide a new toolbox section (see Figure 2-16). You can use the new toolbox to graphically specify the structure of your table.

    Figure 2-16. The new Linq to SQL Classes toolbox provided by the Linq to SQL Classes Designer

Double-click on the Class item; a new empty entity class is added to the designer, allowing us to start manipulating it. By right-clicking on the entity class we can add new properties or delete them, as shown in Figure 2-17.

Figure 2-17. After adding a new Class we can add new properties or delete the class itself

  1. Specify properties in the Properties window (see Figure 2-18). You should be accustomed to the property names because they are the same as column attributes.

    Figure 2-18. The Properties window shows the Property item properties.

  2. Because Linq to SQL Classes Designer supports drag-and-drop from Server Explorer, we are not going to manually create each class from the database. If you can't see Server Explorer, select View Server Explorer. From Server Explorer select the Connect to Database button, as shown in Figure 2-19.

    Figure 2-19. Connecting to a database from Server Explorer

  3. From the Add Connection dialog box (shown in Figure 2-20) you specify every parameter—server name, database, and so on—to connect to a database. Select the database server where the People database has been stored.

    Figure 2-20. Add a connection to a database to manage it from the Visual Studio.

  4. Choose a table from Server Explorer and drag it into the Linq to SQL Classes Designer tool. For example, using the People database, drag the Role table and drop it into the designer. Visual Studio will present the diagram in Figure 2-21.

    Figure 2-21. The Role table is transformed into the Role entity class after dragging and dropping the table from Server Explorer.

  5. This simple operation has generated a diagram with the dragged table and some code (which you can view by selecting the related ,cs file in Solution Explorer). Now you can drag the Person table from Server Explorer and drop it into the Linq to SQL Classes Designer. The final result is shown in Figure 2-22. Because the Role and Person tables have defined a foreign key relation, the Linq to SQL Classes Designer creates an association between the two entity classes automatically.

    Figure 2-22. The association between the Role and Person tables

  6. We want to show two views containing roles and person rows. When we select a row from the Role view the Person view is refreshed to show related person rows. To accomplish this we can add two DataGridView controls to the main form—one called dgRole that will contain role rows, and the other called dgPerson to contain related person rows.

  7. In the Form1 constructor we have to create an object from the PeopleDataContext class generated by the tool to query the database within the source code.

    private PeopleDataContext db;

public Form1()
{
    InitializeComponent();
    db = new PeopleDataContext();
}

  8. Now we have to add the Load event handler in the code, and specify the following code:

    private void Form1_Load(object sender, EventArgs e)
{
    var query = from r in db.Roles
                          select r;

    dgRole.DataSource = query;
    dgPerson.DataSource = dgRole.DataSource;
    dgPerson.DataMember = "Persons";
}

The query retrieves all the roles from the database and fills the dgRole data grid. The rest of the code is really interesting because it uses the association between the two entity classes to show only the person-related rows in the data grid. It uses just two simple lines of code!

  1. Press CTRL + F5 to build and execute the code. Selecting the role rows shows that the dgPerson grid displays related rows (see Figure 2-23).

    Figure 2-23. The Windows form application in execution

2.5.2. Debugging LINQ Applications

From ScottGu's blog it is possible to download a Visual Studio debugger add-in: the SQL Server Query Visualizer. Follow this thread to install it within your Visual Studio 2008: http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx. This add-in is really useful because it allows us to check the query syntax built by LINQ before it's sent to the database. Moreover, it allows us to execute the query from the debugger to discover if the result is what we expect. We can also modify the query.

To use the visualizer we have to put a breakpoint just before the LINQ query definition and press the little magnifying-glass icon that appears when we mouse over the query variable (see Figure 2-24).

Figure 2-24. Pressing the magnifying-glass icon to use the SQL Server Query Visualizer

After pressing the magnifying-glass icon the Query Visualizer tool will appear within the debugger (see Figure 2-25).

Figure 2-25. The SQL Server Query Visualizer in action

The window is divided in two sections; the upper section is a read-only text area where the LINQ query is displayed after the compiler has transformed it using lambda expressions and methods. The lower section is a writable text area containing the SQL to be executed against the database. The SQL can be executed by clicking the Execute button (see Figure 2-26).

Figure 2-26. Pressing the Execute button provided by the SQL Server Query Visualizer executes the query, showing the result in the QueryResult window while the application is waiting on a breakpoint.

Placing the breakpoint just after the query variable definition illustrates that LINQ to SQL uses deferred query execution. Remember that query execution starts only after the query is iterated through a foreach statement or when a caching method such as ToList() is used. The debugger allows us to discover an additional aspect during query execution. Consider the code snippet in Listing 2-25.

Example 2-25. Iterating Through Role and Person Rows
PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;

var query = from r in people.Roles
            select r;

foreach (var role in query)
{
    foreach(var person in role.People)
    {
        Console.WriteLine("Person: {0} {1}", person.FirstName,
            person.LastName);
    }
}

The code uses the association declared in Role and Person entity classes to iterate through role rows and, with an inner foreach statement, to print the persons that have each role.

If you put a breakpoint on the query variable within the foreach statement and press F5 to start the debugger, you'll see that no query has been sent to the database. Press F10 to go a step further; the first query will be printed in the console application (see Figure 2-27).

Figure 2-27. The first query is sent to the database just after the query variable is iterated.

Continue pressing F10 to see that a SELECT statement is sent to the database to select person rows each time a new role is processed (see Figure 2-28).

Figure 2-28. Each time a new role row is processed a new SELECT statement is sent to the database to retrieve related person rows.

As you can imagine, when you process many rows the database is queried too many times and performance is far from optimal. In such cases, to avoid the deferred loading of rows you can use the LoadWith method provided by the DataLoadOptions class, as shown in Listing 2-26.

Example 2-26. Using LoadWith<T>() to Preload the person Rows, Thereby Avoiding the Deferred Loading of Rows
PeopleDataContext people = new PeopleDataContext();
people.Log = Console.Out;

DataLoadOptions shape = new DataLoadOptions();
shape.LoadWith<Role>(r => r.People);

people.LoadOptions = shape;

var query = from r in people.Roles
            select r;

foreach (var role in query)
{
    foreach (var person in role.People)
    {
        Console.WriteLine("Person: {0} {1}",
                          person.FirstName,
                          person.LastName);
    }
}

If you execute the debugger after setting a breakpoint on the query variable you'll see that just two queries are sent to the database: one to retrieve all the roles and one to retrieve all the people (see Figure 2-29).

Figure 2-29. When using LoadWith<T>() just two SELECT statements are sent to the database.

The DataLoadOptions class provides a new method called AssociateWith<>, which is useful to define a subquery against the prefetched records.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.147.28.9