Data Access with LINQ to SQL

New C# and VB.NET Language Features

To get LINQ to SQL to work, Microsoft had to introduce several new language features to both C# and VB.NET. Many of these features make C# and VB.NET behave more like a dynamic language (think JavaScript). Although the primary motivation for introducing these new features was to support LINQ, the new features are also interesting in their own right.

To use these new language features, you’ll need to make sure your website is targeting .NET Framework 3.5. Ensure that you have a web.config file in your project. Next, select the menu option Website, Start Options and then select the Build tab. For Target Framework, select .NET Framework 3.5. Performing these steps will modify your web.config file so that it references the necessary assemblies and uses the right version of the C# or VB.NET compiler.

Understanding Automatic Properties

The first of these new language features we will explore is called automatic properties. Unfortunately, this feature is supported only by C# and not VB.NET. Automatic properties provide you with a shorthand method for defining a new property.

You can’t add any logic to the Getters and Setters for an automatic property. You also can’t create read-only automatic properties. Why are automatic properties relevant to LINQ to SQL? When working with LINQ to SQL, you often use classes to represent nothing more than the list of columns you want to retrieve from the database (the shape of the data) like the select list in a SQL query. In those cases, you just want to do the minimum amount of work possible to create a list of properties, and automatic properties allow you to do this. You can quickly add an automatic property to a class or page when using Visual Web Developer/Visual Studio by typing prop and hitting the Tab key twice.

Understanding Initializers

You can use initializers to reduce the amount of work it takes to create a new instance of a class.

Understanding Type Inference

Here’s a new feature that makes C# and VB.NET look much more like a dynamic language such as JavaScript: local variable type inference. When you take advantage of type inference, you allow the C# or VB.NET compiler to determine the type of a variable at compile time.

Here’s an example of how you use type inference with C#:

var message = “Hello World!”;

And here is how you would use type inference with VB.NET:

Dim message = “Hello World!”

Notice that the message variable is declared without specifying a type. The C# and VB.NET compilers can infer the type of the variable (it’s a String) from the value you use to initialize the variable. No performance impact results from using type inference (the variable is not late bound). The compiler does all the work of figuring out the data type at compile time. Notice that a new keyword has been introduced into C# to support type inference: the var keyword. You declare a variable as type var when you want the compiler to figure out the variable’s data type all by itself.

You can take advantage of type inference only when you provide a local variable with an initial value. For example, this won’t work (C#):

var message;

message = “Hello World!”;

The C# compiler will refuse to compile this code because the message variable is not initialized when it is declared. The following code will work in VB.NET (but it won’t do what you want):

Dim message

message = “Hello World!”

In this case, VB.NET will treat the message variable as type Object. At runtime, it will cast the value of the variable to a string when you assign the string to the variable. This is not good from a performance perspective. VB.NET 9.0 includes a new option called Option Infer. Option Infer must be enabled in order for the implicit typing feature to work. You can enable it for a particular class file by adding the line Option Infer On at the very top of a code file.

The relevance of type inference to LINQ to SQL will be apparent after you read the next section. In many circumstances when using LINQ to SQL, you won’t actually know the name of the type of a variable, so you have to let the compiler infer the type.

Understanding Anonymous Types

Anonymous types is another idea that might be familiar to you from dynamic languages. Anonymous types are useful when you need a transient, fleeting type and you don’t want to do the work to create a class. Here’s an example of creating an anonymous type in C#:

var customer = new {FirstName = “Stephen”, LastName = “Walther”};

Here’s how you would create the same anonymous type in VB.NET:

Dim customer = New With {.FirstName = “Stephen”, .LastName = “Walther”}

Notice that the customer variable is used without specifying a type (this looks very much like JavaScript or VBScript). However, it is important to understand that customer does have a type, you just don’t know its name: It’s anonymous. In a single line of code, we’ve managed to both create a new class and initialize its properties. The terseness brings tears to my eyes.

Anonymous types are useful when working with LINQ to SQL because you’ll discover that you’ll often need to create new types on the fly. For example, you might want to return a class that represents a limited set of database columns when performing a particular query. You’ll need to create a transient class that represents the columns.

Understanding Generics

Yes, I realize that generics are not new to .NET 3.5. However, they are such an important aspect of LINQ to SQL that it is worth using a little space to review this feature. To use generics, you need to import the System.Collections.Generic namespace. I most often use generics by taking advantage of generic collections. For example, if you want to represent a list of strings, you can declare a list of strings like this (in C#):

List<string> stuffToBuy = new List<string>();

stuffToBuy.Add(“socks”);

stuffToBuy.Add(“beer”);

stuffToBuy.Add(“cigars”);

Here’s how you would declare the list of strings in VB.NET:

Dim stuffToBuy As New List(Of String)

stuffToBuy.Add(“socks”)

stuffToBuy.Add(“beer”)

stuffToBuy.Add(“cigars”)

And, by taking advantage of collection initializers, you can now declare a strongly typed list of strings in a single line like this (in C#):

List<string> stuffToBuy2 = new List<string> {“socks”, “beer”, “cigars”};

Unfortunately, VB.NET does not support collection intializers or array initializers.

The List class is an example of a generic because you specify the type of object that the class will contain when you declare the List class. In C#, you specify the type in between the alligator mouths (< >), and in VB.NET you use the Of keyword. Alternatively, we could have  created a List class that contains integers or a custom type such as products or customers represented by a Product or Customer class. A generic collection like a List is superior to a nongeneric collection like an ArrayList because a generic is strongly typed. An ArrayList stores everything as an object. A generic stores everything as a particular type. When you pull an item out of an ArrayList, you must cast it to a particular type before you use it. An item pulled from a generic, on the other hand, does not need to be cast to a type. Generics are not limited solely to collections. You can create generic methods, generic classes, and generic interfaces.

Understanding Lambda Expressions

Lambda expressions, another new language feature introduced with .NET Framework 3.5, provide you with an extremely terse way of defining methods. Imagine, for example, that you want to programmatically wire up a Click event handler to a button control. Listing 18.6 is an example of one way of doing this. Lambda expressions take the notion of the anonymous method one step further. Lambda expressions reduce the amount of syntax required to define a method to its semantic minimum.

Understanding Extension Methods

The idea behind extension methods should also be familiar to anyone who has worked with JavaScript (think prototype). By taking advantage of extension methods, you can add new methods to existing classes. For example, you can make up any method you want and add the method to the String class. I’m constantly HTML-encoding strings because I am paranoid about JavaScript injection attacks. In .NET Framework 2.0, you HTML-encode a string by calling the Server.HtmlEncode() static method, like this:

string evilString = “<script>alert(‘boom!’)<” + “/script>”;

ltlMessage.Text = Server.HtmlEncode(evilString);

In this statement, the static HtmlEncode() method is called on the Server class. Wouldn’t

it be nice if we could just call HtmlEncode() on a string directly like this:

string evilString = “<script>alert(‘boom!’)<” + “/script>”;

ltlMessage.Text = evilString.HtmlEncode();

Using extension methods, we can do exactly that. We can add any methods to a class that we feel like. You create an extension method by creating a static class and declaring a static method that has a special first parameter.

Understanding LINQ

Finally, we get to the topic of LINQ—the last topic we need to examine before we can dive into the true subject of this chapter: LINQ to SQL. LINQ stands for Language Integrated Query. LINQ consists of a set of new language features added to both the C# and VB.NET languages that enable you to perform queries. LINQ enables you to use SQL query–like syntax within C# or VB.NET.

Here’s a simple example of a LINQ query:

var words = new List<string> {“zephyr”, “apple”, “azure”};

var results = from w in words

where w.Contains(“z”)

select w;

The first statement creates a generic List of three strings named “words.” The second statement is the LINQ query. The LINQ query resembles a backward SQL statement. It retrieves all the words from the List that contain the letter z. After you execute the query, the results variable will contain the following list of two words:

zephyr

azure

You can perform a standard LINQ query against any object that implements the IEnumerable<T> interface. An object that implements this interface is called a sequence. Notable examples of sequences are both the generic List class and the standard Array class (so anything you can dump into an array, you can query with LINQ). The C# language supports the following clauses that you can use in a query:

• from—Enables you to specify the data source and a variable for iterating over the data source (a range variable).
• where—Enables you to filter the results of a query.
• select—Enables you to specify the items included in the results of the query.
• group—Enables you to group related values by a common key.
• into—Enables you to store the results of a group or join into a temporary variable.
• orderby—Enables you to order query results in ascending or descending order.
• join—Enables you to join two data sources using a common key.
• let—Enables you to create a temporary variable to represent subquery results.

Building a LINQ query is like building a backward SQL query. You start by specifying a from clause that indicates where you want to get your data. Next, optionally, you specify a where clause that filters your data. Finally, you specify a select clause that gives shape to your data (determines the objects and properties you want to return). Under the covers, standard LINQ queries are translated into method calls on the System.Linq.Enumerable class. The Enumerable class contains extension methods that are

applied to any class that implements the IEnumerable<T> interface.

So, the query

var results = from w in words

where w.Contains(“z”)

select w;

is translated into this query by the C# compiler:

var results = words.Where( w => w.Contains(“z”) ).Select( w => w );

The first query uses query syntax and the second query uses method syntax. The two queries are otherwise identical. Notice that the query using method syntax accepts lambda expressions for its Where() and Select() methods. The lambda expression used with the Where() method filters the results so that only words that contain the letter z are returned. The Select() method indicates the object and property to return. If we had passed the lambda expression

w => w.Length to the Select() method, the query would return the length of each word instead of the word itself.

The choice of whether to use query or method syntax when building LINQ queries is purely a matter of preference. Query syntax uses language-specific syntax (C# or VB.NET). Method syntax is language independent. I find that I use method syntax more than query syntax because query syntax is a subset of method syntax. In other words, you can do more with method syntax. That said, in some cases, writing a query in method syntax is just too verbose. For example, writing left outer joins with LINQ to SQL is much easier using query syntax than method syntax. At the end of the day, the choice of whether to use method or query syntax doesn’t really matter because all the query syntax statements get translated by the compiler into method syntax. In the case of standard LINQ, those method calls are calls on methods of the Enumerable class. Lookup the System.Linq.Enumerable class in the SDK documentation to view the full list of methods that the Enumerable class supports. Here is a list of some of the more interesting

and useful methods:

• Aggregate()—Enables you to apply a function to every item in a sequence.
• Average()—Returns the average value of every item in a sequence.
• Count()—Returns the count of items from a sequence.
• Distinct()—Returns distinct items from a sequence.
• Max()—Returns the maximum value from a sequence.
• Min()—Returns the minimum value from a sequence.
• Select()—Returns certain items or properties from a sequence.
• Single()—Returns a single value from a sequence.
• Skip()—Enables you to skip a certain number of items in a sequence and return the remaining elements.
• Take()—Enables you to return a certain number of elements from a sequence.
• Where()—Enables you to filter the elements in a sequence.

Creating LINQ to SQL Entities

LINQ to SQL enables you to perform LINQ queries against database data. Currently, you can use LINQ to SQL with Microsoft SQL Server 2000 or Microsoft SQL Server 2005 (including the SQL Server Express editions). Other databases—such as Oracle, DB2, and Access databases—might be supported in the future, but they are not right now. 

To use LINQ to SQL, you need to add a reference to the System.Data.Linq.dll assembly. Select the menu option Website, Add Reference and, beneath the .NET tab, select System.Data.Linq.dll. Performing this action will add a new assembly reference to the <assemblies> section of your web.config file. If you use the Object Rational Designer, this reference is added automatically.

In this section, you learn how to create LINQ to SQL entities. An entity is a C# or VB.NET class that represents a database table (or view). You can use a set of standard custom attributes to map classes and properties to tables and columns. You learn how to create entities both by hand and by using the Object Rational Designer.

Building Entities by Hand

Before you can start performing queries using LINQ to SQL, you need to create one or more entity classes that represent the data you are querying. In this section, you learn how to code these classes by hand.

The Column and Table attribute classes live in the System.Data.Linq.Mapping namespace. Furthermore, notice that the class itself is decorated with a Table attribute. This attribute marks the class as representing a database table. The Column attribute supports the following properties:

• AutoSync—Indicates whether the value of the property is synchronized with the value of the database column automatically. Possible values are OnInsert, Always, and None.
• CanBeNull—Indicates whether the property can represent a null value.
• DbType—Indicates the database column data type.
• Expression—Indicates the expression used by a computed database column.
• IsDbGenerated—Indicates that the value of the property is generated in the database (for example, an identity column).
• IsDiscriminator—Indicates whether the property holds the discriminator value for an inheritance hierarchy.
• IsPrimaryKey—Indicates whether the property represents a primary key column.
• IsVersion—Indicates whether the property represents a column that represents a row version (for example, a timestamp column).
• Name—Indicates the name of the database column that corresponds to the property.
• Storage—Indicates a field where the value of the property is stored.
• UpdateCheck—Indicates whether the property participates in optimistic concurrency comparisons.

The Table attribute supports the following single property:

• Name—Indicates the name of the database table that corresponds to the class.

Some comments about these attributes are needed. First, you don’t need to specify a Name property when your property or class name corresponds to your database column or table name. If, on the other hand, your database table were named Movies and your class were named Movie, you would need to supply the Name property for the Table attribute to map the correct table to the class. Second, you always want to specify the primary key column by using the IsPrimaryKey property. For example, if you don’t specify a primary key column, you can’t do updates against your database using LINQ.

Finally, even though we didn’t do this in our Movie class, you almost always want to include a timestamp column in your database table and indicate the timestamp column by using the IsVersion property. If you don’t do this, LINQ to SQL will check whether the values of all the properties match the values of all the columns before performing an update command to prevent concurrency conflicts. If you specify a version property, LINQ to SQL can check the value of this single property against the database rather than all the columns.

Building Entities with the Object Relational Designer

As an alternative to building entities by hand, you can use the Object Relational Designer. You can simply drag database tables from the Database Explorer (Server Explorer) onto the Designer. The Designer generates the entity classes with the correct attributes automatically. Follow these steps to use the Object Relational Designer:

1. Select the menu option Website, Add New Item to open the Add New Item dialog box.

2. Select the LINQ to SQL Classes template, give it the name MyDatabase, and click the Add button.

3. When prompted to create the LINQ to SQL classes in the App_Code folder, click the Yes button.

4. After the Object Relational Designer opens, drag one or more database tables from the Database Explorer/Server Explorer window onto the Designer surface.

You can view the code that the Designer generates by expanding the MyDatabase.dbml node in the App_Code folder and double-clicking the MyDatabase.designer.cs file. The Designer generates a strongly typed DataContext class named MyDatabaseContext. Each database table that you drag onto the Designer surface gets exposed by the DataContext class as a strongly typed property. The Designer, furthermore, generates a distinct class for each database table you drag onto the Designer. For example, after you drag the Movie table onto the Designer, a new class named Movie is created in the MyDatabase.designer.cs file. The Object Relational Designer attempts to pluralize table names automatically when you add them to the Designer. So, when you drag the Movie table onto the Designer, the Designer generates a DataContext property named Movies. Most of the time, but not all of the time, it gets the pluralization right. You can turn off this feature by selecting the menu option Tools, Options and selecting the Database Tools, O/R Designer tab.

Building Entity Associations

One entity can be associated with another entity. For example, a MovieCategory entity might be associated with one or more Movie entities. If you have defined foreign key relationships between your database tables, these relationships are preserved when you drag your tables onto the Object Relational Designer. The Object Relational Designer will generate entity associations based on the foreign key relationships automatically.

For example, the MovieCategory entity is related to the Movie entity through the Movie entity’s CategoryId property. As long as you have defined a foreign key relationship between Movie.CategoryId and MovieCategory.Id, you can use a query like this following:

MyDatabaseDataContext db = new MyDatabaseDataContext();

var category = db.MovieCategories.Single( c => c.Name == “Drama” );

var query = category.Movies;

The second statement grabs the Drama movie category. The third statement returns all movies associated with the Drama movie category. In this case, we’ve followed a one-tomany relationship and got a list of movies that match a movie category.

You can also go the opposite direction and retrieve the one and only movie category that matches a particular movie:

string categoryName = db.Movies.Single(m=>m.Id==1).MovieCategory.Name;

This query retrieves the name of the movie category associated with the movie that has an ID of 1.

Under the covers, the Object Relational Designer creates the entity relationships by adding association attributes to entity properties. The Object Relational Designer also adds some tricky synchronization logic to keep the properties of associated entities synchronized. Although I wish that I could code all my entities by hand, adding all the logic necessary to get the entity associations to work correctly is too much work. For that reason, I use the Object Relational Designer.

Using the LinqDataSource Control

I want to briefly describe the LinqDataSource control. You can use this control to represent LINQ queries. For example, the page in Listing 18.16 contains a simple search form for searching movies by director. The page uses a LinqDataSource to represent the LINQ query.

Performing Standard Database Commands with LINQ to SQL

In this section, you learn how to use LINQ to SQL as a replacement for working directly with SQL. We’ll start by discussing how LINQ to SQL queries differ from standard LINQ queries. Next, we’ll examine how you can perform standard database queries and commands using LINQ to SQL such as Select, Update, Insert, and Delete commands. We’ll also discuss how you can create dynamic queries with LINQ. Finally, we’ll investigate the very important topic of how you can debug LINQ to SQL queries.

LINQ to Objects versus LINQ to SQL

You can use standard LINQ (LINQ to Objects) with any object that implements the IEnumerable<T> interface. You can use LINQ to SQL, on the other hand, with any object that implements the IQueryable<T> interface. Standard LINQ is implemented with the extension methods exposed by the System.Linq.Enumerable class. LINQ to SQL, on the other hand, uses the extension methods exposed by the System.Linq.Queryable class.

Why the difference?

When you build a query using standard LINQ, the query executes immediately. When you build a query using LINQ to SQL, on the hand, the query does not execute until you start enumerating the results. In other words, the query doesn’t execute until you use a foreach loop to walk through the query results.

Selecting with LINQ to SQL

If you want to perform a simple, unordered select, you can use the following query (assuming that you have an entity named Movie that represents the Movie database table):

MyDatabaseDataContext db = new MyDatabaseDataContext();

var query = db.Movies;

Notice that no LINQ extension methods are used in this query. All the items are retrieved from the Movies table. If you prefer, you can use query syntax instead of method syntax, like this:

MyDatabaseDataContext db = new MyDatabaseDataContext();

var query = from m in db.Movies select m;

Selecting Particular Columns

If you want to select only particular columns, and not all the columns, from a database table, you can create an anonymous type on the fly, like this:

MyDatabaseDataContext db = new MyDatabaseDataContext();

var query = db.Movies.Select( m => new {m.Id, m.Title} );

The expression new {m.Id, m.Title} creates an anonymous type that has two properties: Id and Title. Notice that the names of the properties of the anonymous type are inferred. If you want to be more explicit, or if you want to change the names of the anonymous type’s properties, you can construct your query like this:

MyDatabaseDataContext db = new MyDatabaseDataContext();

var query = db.Movies.Select( m => new {Id = m.Id, MovieTitle = m.Title} );

Selecting Particular Rows If you want to select only particular rows from a database table and not all the rows, you can take advantage of the Where() method. The following LINQ to SQL query retrieves all the movies directed by George Lucas with box office totals greater than $100,000 dollars:

MyDatabaseDataContext db = new MyDatabaseDataContext();

var query = db.Movies

.Where( m => m.Director == “George Lucas” && m.BoxOfficeTotals > 100000.00m)

.Select( m => new {m.Title, m.Director, m.BoxOfficeTotals});

Remember to always call the Where() method before the Select() method. You need to filter your data with Where() before you shape it with Select(). Selecting Rows in a Particular Order You can use the following methods to control the order in which rows are returned from a LINQ to SQL query:

• OrderBy()—Returns query results in a particular ascending order.
• OrderByDescending()—Returns query results in a particular descending order.
• ThenBy()—Returns query results using in an additional ascending order.
• ThenByDescending()—Returns query results using an additional descending order.

The OrderBy() and OrderBy() methods return an IOrderedQueryable<T> collection instead of the normal IQueryable<T> collection type. If you want to perform additional sorting, you need to call either the ThenBy() or ThenByDescending() method.

Selecting a Single Row

If you want to select a single row from the database, you can use one of the following two query methods:

• Single()—Selects a single record.
• SingleOrDefault()—Selects a single record or a default instance.

The first method assumes there is at least one element to be returned (if not, you get an exception). The second method returns null (for a reference type) when no matching element is found.

Performing a LIKE Select

You can perform the equivalent of a LIKE Select with LINQ to SQL in several ways. First, you can use String methods such as Length, Substring, Contains, StartsWith, EndsWith, IndexOf, Insert, Remove, Replace, Trim, ToLower, ToUpper, LastIndexOf, PadRight, and PadLeft with LINQ to SQL queries. For example, the following query returns all movies that start with the letter t:

MyDatabaseDataContext db = new MyDatabaseDataContext();

var query = db.Movies.Where(m=>m.Title.StartsWith(“t”));

Paging Through Records

Doing database paging right when working with ADO.NET is difficult. The SQL language is not designed to make it easy to retrieve a range of records. Doing database paging using LINQ to SQL queries, on the other hand, is trivial. You can take advantage of the following two query methods to perform database paging:

• Skip()—Enables you to skip a certain number of records.
• Take()—Enables you to take a certain number of records.

Joining Records from Different Tables

You can perform joins when selecting entities just like you can when joining database tables. For example, imagine that you want to join the Movie and MovieCategory tables on the CategoryId key. Assuming that you have both a Movie and MovieCategory entity, you can use the following query:

MyDatabaseDataContext db = new MyDatabaseDataContext();

var query = db.MovieCategories

.Join(db.Movies, c=>c.Id, m=>m.CategoryId, (c,m)=>new {c.Id,c.Name,m.Title});

Caching Records Getting caching to work with LINQ to SQL is a little tricky. Remember that a LINQ to SQL query represents a query expression and not the actual query results. The SQL command is not executed, and the results are not retrieved until you start iterating through the query results.

Inserting with LINQ to SQL

There are two steps to adding and inserting a new record with LINQ to SQL. First, you need to use the InsertOnSubmit() method to add an entity to an existing table. Next, you call SubmitChanges() on the DataContext to execute the SQL INSERT statement against the database.

Updating with LINQ to SQL

You can use the Attach() method to attach an entity back into a data context. There are three overloads of the Attach() method:

• Attach(Object)—Enables you to attach an unmodified entity to the data context.
• Attach(Object, Boolean)—Enables you to attach a modified entity to the data context. The second parameter represents whether or not the entity has been modified. To use this overload, the entity must have a version/timestamp property.
• Attach(Object, Object)—Enables you to attach a modified entity to the data context. The first parameter represents the modified entity. The second parameter represents the original entity.

Deleting with LINQ to SQL

You can delete an entity with LINQ to SQL by using code like the following:

MyDatabaseDataContext db = new MyDatabaseDataContext();

Movie movieToDelete = db.Movies.Single(m=>m.Id==1);

db.Movies.DeleteOnSubmit( movieToDelete );

db.SubmitChanges();

This code starts by retrieving the record with an Id of 1 from the Movie database table. Next, the Movie entity is removed from the Movies collection by calling the DeleteOnSubmit() method. Finally, this change is submitted to the database.