LINQ to SQL and Visual Studio 2008

Introduction

LINQ is one of the hottest buzz words out there today. LINQ is the new query language that can query any collection-based mechanism, whether it's an enumerable collection, XML, or a database. LINQ uses a structure very similar to an ANSI SQL query, with the select statement at the bottom instead of the top. The purpose of this article isn't to discuss LINQ queries (although it will to a point), but to discuss LINQ to SQL capabilities in the Visual Studio 2008 designer.
When looking at LINQ, pay attention to the System.Linq, System.Data.Linq, and the System.Xml.Linq namespaces for all of your data querying needs. The System.Data.Linq namespace has a lot of the core objects used with LINQ-to-SQL, and System.Linq has the extension methods and lambda expressions that make querying data even easier.

Visual Studio 2008 Support

I'm sure, unless you are new to .NET, that you are familiar with the code generation capabilities of .NET data storage mechanisms, such as VS 2003 Datasets and VS 2005 Table Adapters. LINQ to SQL is very similar to this. It generates business objects that support change notifications based on the table structures. If you are familiar with IdeaBlade DevForce software, it has a slight resemblance of this, though not as functional.
The tables used in the example are shown in figure 1.

Figure 1: The Samples database

LINQ mimics the above structure in its own designer. Each table is rendered as a class, with all of its columns represented by properties. For instance, the Orders table has OrderID, CustomerID, OrderDate and OrderTotal properties. Any relationships are also maintained; in the Customers/Orders relationship, the Order class has a Customer property, which references the Customer business object. The Customer business object, therefore, will have an Orders collection of Order objects.

Figure 2: The LINQ classes for the Samples database

As you can see, the names are similar to those of the database tables, except they are in singular form. However, this is optional; the LINQ designer allows you to rename either the class or the property name however you see fit. Below is what happens when you click twice on a name:

Figure 3: Renaming the Customer class

LINQ classes can be created manually in the designer, though it's better to drag and drop the table from the Server Explorer window. LINQ has several properties for each class's property that maps the data type, null status, identity, primary key, and read only status as defined in the database. The following figure shows these various properties.

Figure 4: The LINQ attributes of the Customer.CustomerID property

The designer uses a connection string that is stored in the settings file for the project, and the connection string of the configuration file. The connection string is reported in listing 1. Be aware that you may have to maintain the connection string in both places.

Listing 1: Connection String created from LINQ-To-SQL designer

view sourceprint?

1.<add name="LinqToSqlExample.Properties.Settings.SamplesConnectionString"

2.    connectionString="Data Source=**server**;Initial Catalog=Samples;Integrated Security=True;Pooling=False"

3.    providerName="System.Data.SqlClient" />

So, we have these classes, how do we access it? Whatever you name the class designer file, it generates the name DataContext. For example, I created a SampleData.dbml file that generates a SampleDataDataContext class. This context already defines everything it needs to know in order to connect to the underlying database, from that, it has an empty constructor. However, it also takes a connection string as a parameter, permitting a change to the database repository (as long as this new database matches in structure).
To understand some of the objects LINQ-to-SQL generates, take a look below:

• <Object> class - This is the LINQ class that is auto-generated. It implements the INotifyPropertyChanging and INotifyPropertyChanged interfaces; outside of that, there isn't a base object that the LINQ types inherit from. This can prove to be a challenge when bridging certain object-oriented hurdles.
• Table<Entity> class - The Customers table in the database is represented by a Customer class; however, the customer class more closely resembles a row; the actual Customers table is represented by the Table<Customer> collection class.
• EntitySet<Entity> class - This class is often used to represent child collections of a parent class. For instance, a customer can have many orders. The EntitySet<Order> class is a child definition of the Customer class. This makes it useful to interact with orders, but it's also functional in that change-tracking happens at this level as well.
• IQueryable<Entity> interface - When querying data using a LINQ query, this is the result that returns.
• ISingleResult<Entity> interface - When querying data using a LINQ method (usually a stored procedure), this is the result that returns. The entity is a custom entity type and doesn't match any of the existing classes; rather, it creates its own.

In addition to all of this, it is possible to setup the designer to use stored procedures. In the designer, there are two panes; a left and right pane. The right pane is for methods (stored procedures) that can be called. I created an update proc for example sake as shown below:

Figure 5: UpdateCustomer stored procedure

The Customer class can be configured to use the proc by clicking on the class. In the property window, the Update property is now available. Click on the ellipse:

Figure 6: Update function for Customer Class

The following dialog appears. Selecting the customize radio button and the stored procedure name created a mapping between the proc's parameter listing and the property name of the class, as shown below:

Figure 7: The LINQ update stored procedure mapping

LINQ Queries

Below is a sample using a LINQ query with a custom data context class. Note that this article isn't meant to be a discussion of LINQ queries, so I'll refrain from going into detail about the query:

Listing 2: LINQ Query

view sourceprint?

1.SampleDataDataContext context = new SampleDataDataContext();

2. 

3.var results = (from c in context.Customers

4.          where c.City == "Pittsburgh"

5.          select c);

First, as the data context has the connection string defined in the designer, it doesn't need to be manually defined. When creating the context, none of the tables are loaded; each is loaded in a deferred fashion. The data is loaded at the time the context.Customers table is queried (internally it calls the DataContext.GetTable<Customer> method).
Again, LINQ needs to have all of the data loaded in order to filter it. This may be a drawback in certain situations. It's possible to use a stored procedure for this, but again, a stored procedure creates its own business object when used as a method.
For more information on immediate loading of related tables, please consult this MSDN article .

LINQ Classes

LINQ defined three classes, as shown above: Customer, Order, and OrderItem. These classes are defined as partial classes, meaning that it would be possible to do something like:

Listing 3: Customer partial class

view sourceprint?

1.public partial class Customer

2.{

3.    public void DoSomething()

4.    {

5.        this._CustomerID = Guid.NewGuid();

6.    }

7.}

In addition, with .NET framework 3.5, static extensions provide additional capabilities. In the example below, the GetOrdersInRange method gets appended to the customer class, as long as the extension class's namespace is available wherever it is being referenced. This article isn't about extension methods, but you can find out more about them on my blog .

Listing 4: Customer Extension Class

view sourceprint?

01.public static class CustomerExtensions

02.{

03.    public static IQueryable<Order> GetOrdersInRange(this Customer customer,

04.         DateTime minDate, DateTime maxDate)

05.    {

06.        return (from o in customer.Orders

07.             where o.OrderDate >= minDate && o.OrderDate <= maxDate

08.             select o);

09.       }

10.}

LINQ classes track their updates. To update a LINQ class, just get a reference to it, and change any of its properties. These changes are tracked and the SampleDataDataContext knows about the change. However, the change isn't submitted until the call to DataContext.SubmitChanges(), which actually pushes the changes to the database.
Insertions work in a very similar fashion; the class can simply be created, then inserted using the DataContext.InsertOnSubmit() method. This adds the instance of the class, but it doesn't get inserted into the database until the call to SubmitChanges(). Lastly, DataContext.DeleteOnSubmit marks the entity as deleted and will be removed when changes are submitted.

Validation

Because LINQ to SQL is using strongly-typed business classes, these classes can play well with the validation application block. The validation application block uses an object type to lookup any validations defined as attributes on the business object's property declarations, or by pulling in validations listed in the application's configuration file. This is all done using reflection techniques; if you are unsure about how the validation block works, here is a simple example .
A type is passed into the Validation facade's Validate<T> method. This type matches the type of the LINQ object. Like I mentioned before, the Validation Application Block requires that validators (classes that inherit from the base Validator class) be defined either on the business object's property declaration, or in the configuration file of the target application.
Because LINQ creates business objects, each class is a distinct type. This means that validations can be easily setup against these classes. However, the best option would be to use the configuration file approach. This is because the designer could affect all of the validations you setup inside the designer code, meaning that whenever you update a reference to the table, your validations could be lost.

LINQ Designer

There are some issues with the designer that you need to be aware of. Though convenient, there isn't any in-built find-replace functionality, so if your model is big, it's hard to find a table you need to update. In addition, the LINQ designer doesn't auto-refresh with any database changes, and there isn't an in-built way of performing an update, at least at the time of writing.
The way to refresh a table layout in the LINQ-to-SQL class designer is to delete and re-add it to the model. Deleting the whole model and adding all the tables into the diagram works pretty well, provided the application code isn't affected by any table changes (which happens any way you refresh the designer). These approaches will regenerate the LINQ classes; note that this was the concern with using the validation application block and defining the validation attributes on the LINQ business object properties within the classes in the designer, because deleting tables would also delete the attributes.
Lastly, some other designer challenges are how it assigns connection strings. For instance, if you have a connection setup, and one of the parameters of that connection string doesn't match the connection to the server established in the Server Explorer, any tables dragged/dropped in the explorer causes the designer to override the connection string with one that matches the server explorer's credentials. This can be a pain if the names do not match up.

Conclusion

LINQ and LINQ-to-SQL class designer can help develop applications very rapidly, and save time during the development processes. There are some drawbacks to the designer and the setup of the classes, but overall, it works pretty effectively, and uses the LINQ approach which is a very powerful tool in querying data.