Chapter 24. LINQ to SQL

How many times did you face runtime errors when sending SQL instructions to your databases for querying or manipulating data? Your answer is probably “several times.” Sending SQL instructions in the form of strings has been, for years, the way of accessing data in the .NET development, but one of the main disadvantages was the lack of compile time control over your actions. Experienced developers can also remember when they had to work against databases in a connected environment taking care of everything that happened. LINQ to SQL solves several of these issues, providing both a disconnected way for working with data, where data is mapped into an object model that you work with until you decide to save back data, and a strongly typed programming fashion that improves your productivity by checking queries and CRUD operations at compile time. In this chapter you explore the most important LINQ to SQL functionalities, learning the basics of data access with such technology.

Introducing LINQ to SQL

LINQ to SQL is an object relational mapping engine for Microsoft SQL Server databases. In has been the first built-in LINQ provider for SQL Server when LINQ first shipped in the .NET Framework 3.5, offering not only the capability of querying data (as instead LINQ to DataSet is limited to) but also a complete infrastructure for manipulating data, including connections, queries, and CRUD (Create/Read/Update/Delete) operations. LINQ to SQL is another layer in the data access architecture, but it is responsible for everything starting from opening the connection until closing. One of the advantages from LINQ to SQL is that you will query your data using the usual LINQ syntax thanks to the unified programming model offered by the technology. But this is not the only advantage. Being an object relational mapping engine makes LINQ to SQL mapping databases’ tables and relationships into .NET objects. This allows working in a disconnected way and in a totally strongly typed fashion so that you can get all the advantages of the CLR management. Each table from the database is mapped into a .NET class, whereas relationships are mapped into .NET properties, providing something known as abstraction. This enables you to work against a conceptual model instead of against the database, and you will work with objects until you finish your edits that will be persisted to the underlying data source only when effectively required. This provides several advantages: First, you work with strongly typed objects, and everything is managed by the Common Language Runtime (CLR). Second, you do not work connected to the database, so your original data will be secure until you send changes after validations. According to the LINQ terminology, classes mapping tables are called entities. A group of entities is referred to as an entity set. Relationships are instead called associations. You access LINQ to SQL features by creating specific classes that are described in next section.

Prerequisites and Requirements for This Book

This chapter assumes that you have installed Microsoft SQL Server 2012, at least the Express Edition, or possibly the “With Tools” version that also includes SQL Server Management Studio Express. If you did not install it yet, you can download it from here: http://www.microsoft.com/express/sql/default.aspx.

If you installed SQL Server Management Studio (Express or higher), it is a good idea to attach the Northwind database to the SQL Server instance so you can simulate a production environment.

Understanding LINQ to SQL Classes

To access SQL Server databases with LINQ to SQL, you need a LINQ to SQL class. This kind of class is generated by Visual Studio when you select members for the new object model and contains all the Visual Basic code that represents tables, columns, and relationships. Adding a LINQ to SQL class is also necessary to enable the Visual Studio OR/M Designer for LINQ to SQL. To understand what these sentences mean, follow these preliminary steps:

• Create a new project for the Console and name it LinqToSql.

• Establish a connection to the Northwind database either via the Server Explorer tool window or via the new SQL Server Object Explorer tool window.

• In Solution Explorer, right-click the project name and select Add, New Item. When the Add New Item dialog box appears move to the Data folder and select the LINQ to SQL Classes item, replacing the default name with Northwind.dbml. Figure 24.1 shows this scenario.

Figure 24.1. Adding a new LINQ to SQL class to the project.

When you click Add, after a few seconds the Visual Studio 2012 IDE shows the LINQ to SQL Object Relational Designer that appears empty, as shown in Figure 24.2.

Figure 24.2. The LINQ to SQL designer popping up for the first time.

The designer provides a brief description of its job, requiring you to pick items from either the Server Explorer window or from the toolbox. You need to pick tables from the Northwind database, passing them to Visual Studio to start the mapping process. Look at Figure 24.3 and then expand Server Explorer (or SQL Server Object Explorer) to show the Northwind database structure; then expand the Tables folder.

Figure 24.3. Preparing to pick tables from the Northwind database.

Now keep the Ctrl key pressed and click both the Categories and Products tables. Our goal is to provide an example of a master-details relationship. When selected, drag the tables onto the designer surface until you get the result shown in Figure 24.4.

Figure 24.4. The LINQ to SQL designer is now populated.

At this point, we can begin making some considerations. Visual Studio generated a diagram that is the representation of Visual Basic code. This diagram contains the definition of two entities, Category and Product. Each of them is mapped to Visual Basic classes with the same name. If you inspect the diagram, you notice that both classes expose properties. Each property maps a column within the table in the database. Figure 24.4 also shows the Properties window opened to show you a new, important concept, the System.Data.Linq.DataContext class. Every LINQ to SQL object model defines a class that inherits from DataContext and which is the main entry point of a LINQ to SQL class. It is, in other words, an object-oriented reference to the database. It is responsible for

• Opening and closing connections

• Handling relationships between entities

• Keeping track, with a single instance, of all changes applied to entities during all the object model lifetimes

• Translating Visual Basic code into the appropriate SQL instructions

• Managing entities’ lifetimes, no matter how long

Visual Studio generates a new DataContext class by forming its name and concatenating the database name with the DataContext phrase. So in our example, the class is named NorthwindDataContext. This class, as you can see in Figure 24.4, exposes some properties including the connection string, base class, and access modifier.

Now click the Category item in the designer that represents an entity described in Figure 24.5 within the Properties window.

Figure 24.5. Examining the Category class.

It is interesting to understand that such a class has public access that requires code (Use Runtime definition) to support Insert/Update/Delete operations. The Source property also tells us what the source table in the database is. Now click on the arrow that establishes the relationship. Figure 24.6 shows how the Properties window describes such an object.

Figure 24.6. Examining associations.

Notice how a one-to-many relationship is represented. The Child property shows the “many” part of the one-to-many relationship, whereas the Parent property shows the “one” part of the relationship.

Now that you have a clearer idea about LINQ to SQL classes in a graphical way, it’s time to understand the architecture. This kind of a class is referred via a .dbml file that groups nested files. To see nested files, you need to activate the View All Files view in Solution Explorer. The first nested file has a .dbml.layout extension and contains the XML definition for the class diagram that we just saw in the Visual Studio Designer. All edits, including Visual Studio-generated items, are performed onto the designer and then reflected into a .designer.vb file (in our example, Northwind.designer.vb). This file is fundamental because it stores code definitions for the DataContext, entities, and associations classes. Understanding how this file is defined is important, although you should never edit it manually. Listing 24.1 shows the definition of the NorthwindDataContext class:

Listing 24.1. The NorthwindDataContext Class Definition

---

<Global.System.Data.Linq.Mapping.DatabaseAttribute(Name:="Northwind")>  _
Partial Public Class NorthwindDataContext
    Inherits System.Data.Linq.DataContext

    Private Shared mappingSource As System.Data.Linq.Mapping.MappingSource = _
                                    New AttributeMappingSource()

    Partial Private Sub OnCreated()
    End Sub
    Partial Private Sub InsertCategory(instance As Category)
    End Sub
    Partial Private Sub UpdateCategory(instance As Category)
    End Sub
    Partial Private Sub DeleteCategory(instance As Category)
    End Sub
    Partial Private Sub InsertProduct(instance As Product)
    End Sub
    Partial Private Sub UpdateProduct(instance As Product)
    End Sub
    Partial Private Sub DeleteProduct(instance As Product)
    End Sub

    Public Sub New()
        MyBase.New(Global.LinqToSql.My.MySettings.Default.
                   NorthwindConnectionString, mappingSource)
        OnCreated
    End Sub

    Public Sub New(ByVal connection As String)
        MyBase.New(connection, mappingSource)
        OnCreated
    End Sub

    Public Sub New(ByVal connection As System.Data.IDbConnection)
        MyBase.New(connection, mappingSource)
        OnCreated
    End Sub

    Public Sub New(ByVal connection As String,
                   ByVal mappingSource As System.Data.Linq.
                   Mapping.MappingSource)
        MyBase.New(connection, mappingSource)
        OnCreated()
    End Sub

    Public Sub New(ByVal connection As System.Data.IDbConnection,
                   ByVal mappingSource As System.Data.Linq.
                   Mapping.MappingSource)
        MyBase.New(connection, mappingSource)
        OnCreated()
    End Sub

    Public ReadOnly Property Categories() As System.Data.Linq.Table(Of Category)
        Get
            Return Me.GetTable(Of Category)
        End Get
    End Property
    Public ReadOnly Property Products() As System.Data.Linq.Table(Of Product)
        Get
            Return Me.GetTable(Of Product)
        End Get
    End Property
End Class

---

The class is marked with the DataBase attribute and inherits from DataContext, meaning that it has to be a managed reference to the database. The constructor provides several overloads, most of them accepting a connection string if you do not want it to be stored in the configuration file (which is the default generation). Two properties are important, Categories and Products of type System.Data.Linq.Table(Of T). This type offers a .NET representation of a database table. The GetTable method invoked within properties creates Table(Of T) objects based on entities. Notice how several partial methods for Insert/Update/Delete operations are defined and can be extended later. Similar to the DataContext class, both Product and Category classes have a Visual Basic definition within the same file. As a unified example, Listing 24.2 shows the definition of the Category class.

Listing 24.2. The Category Class Definition

---

<Global.System.Data.Linq.Mapping.TableAttribute(Name:="dbo.Categories")>  _
Partial Public Class Category
    Implements System.ComponentModel.INotifyPropertyChanging,
               System.ComponentModel.INotifyPropertyChanged

    Private Shared emptyChangingEventArgs As PropertyChangingEventArgs = _
                   New PropertyChangingEventArgs(String.Empty)

    Private _CategoryID As Integer

    Private _CategoryName As String

    Private _Description As String

    Private _Picture As System.Data.Linq.Binary

    Private _Products As EntitySet(Of Product)

    Partial Private Sub OnLoaded()
    End Sub
    Partial Private Sub OnValidate(action As System.Data.Linq.ChangeAction)
    End Sub
    Partial Private Sub OnCreated()
    End Sub
    Partial Private Sub OnCategoryIDChanging(value As Integer)
    End Sub
    Partial Private Sub OnCategoryIDChanged()
    End Sub
    Partial Private Sub OnCategoryNameChanging(value As String)
    End Sub
    Partial Private Sub OnCategoryNameChanged()
    End Sub
    Partial Private Sub OnDescriptionChanging(value As String)
    End Sub
    Partial Private Sub OnDescriptionChanged()
    End Sub
    Partial Private Sub OnPictureChanging(value As System.Data.Linq.Binary)
    End Sub
    Partial Private Sub OnPictureChanged()
    End Sub

    Public Sub New()
        MyBase.New
        Me._Products = New EntitySet(Of Product)(AddressOf Me.attach_Products,
                                                 AddressOf Me.detach_Products)
        OnCreated
    End Sub

    <Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_CategoryID",
            AutoSync:=AutoSync.OnInsert, DbType:="Int NOT NULL IDENTITY",
            IsPrimaryKey:=True, IsDbGenerated:=True)> _
    Public Property CategoryID() As Integer
        Get
            Return Me._CategoryID
        End Get
        Set(ByVal value As Integer)
            If ((Me._CategoryID = Value) _
               = False) Then
                Me.OnCategoryIDChanging(Value)
                Me.SendPropertyChanging()
                Me._CategoryID = Value
                Me.SendPropertyChanged("CategoryID")
                Me.OnCategoryIDChanged()
            End If
        End Set
    End Property

    <Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_CategoryName",
            DbType:="NVarChar(15) NOT NULL", CanBeNull:=False)> _
    Public Property CategoryName() As String
        Get
            Return Me._CategoryName
        End Get
        Set(ByVal value As String)
            If (String.Equals(Me._CategoryName, Value) = False) Then
                Me.OnCategoryNameChanging(Value)
                Me.SendPropertyChanging()
                Me._CategoryName = Value
                Me.SendPropertyChanged("CategoryName")
                Me.OnCategoryNameChanged()
            End If
        End Set
    End Property

    <Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_Description",
            DbType:="NText", UpdateCheck:=UpdateCheck.Never)> _
    Public Property Description() As String
        Get
            Return Me._Description
        End Get
        Set(ByVal value As String)
            If (String.Equals(Me._Description, Value) = False) Then
                Me.OnDescriptionChanging(Value)
                Me.SendPropertyChanging()
                Me._Description = Value
                Me.SendPropertyChanged("Description")
                Me.OnDescriptionChanged()
            End If
        End Set
    End Property

    <Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_Picture",
            DbType:="Image", UpdateCheck:=UpdateCheck.Never)> _
    Public Property Picture() As System.Data.Linq.Binary
        Get
            Return Me._Picture
        End Get
        Set(ByVal value As System.Data.Linq.Binary)
            If (Object.Equals(Me._Picture, Value) = False) Then
                Me.OnPictureChanging(Value)
                Me.SendPropertyChanging()
                Me._Picture = Value
                Me.SendPropertyChanged("Picture")
                Me.OnPictureChanged()
            End If
        End Set
    End Property

    <Global.System.Data.Linq.Mapping.AssociationAttribute(Name:="Category_Product",
            Storage:="_Products", ThisKey:="CategoryID", OtherKey:="CategoryID")> _
    Public Property Products() As EntitySet(Of Product)
        Get
            Return Me._Products
        End Get
        Set(ByVal value As EntitySet(Of Product))
            Me._Products.Assign(Value)
        End Set
    End Property

    Public Event PropertyChanging As PropertyChangingEventHandler Implements _
                 System.ComponentModel.INotifyPropertyChanging.PropertyChanging

    Public Event PropertyChanged As PropertyChangedEventHandler Implements _
                 System.ComponentModel.INotifyPropertyChanged.PropertyChanged

    Protected Overridable Sub SendPropertyChanging()
        If ((Me.PropertyChangingEvent Is Nothing)  _
                    = false) Then
            RaiseEvent PropertyChanging(Me, emptyChangingEventArgs)
        End If
    End Sub

    Protected Overridable Sub SendPropertyChanged(ByVal propertyName As [String])
        If ((Me.PropertyChangedEvent Is Nothing)  _
                    = false) Then
            RaiseEvent PropertyChanged(Me,
                                    New PropertyChangedEventArgs(propertyName))
        End If
    End Sub

    Private Sub attach_Products(ByVal entity As Product)
        Me.SendPropertyChanging
        entity.Category = Me
    End Sub

    Private Sub detach_Products(ByVal entity As Product)
        Me.SendPropertyChanging
        entity.Category = Nothing
    End Sub
End Class

---

The class is marked with the System.Data.Linq.TableAttribute attribute, meaning that it has to represent a database table. It implements both the INotifyPropertyChanging and INotifyPropertyChanged interfaces to provide the ability of notifying the user interface of changes about entities. It then defines partial methods that you can extend and customize when a particular event occurs. (This is covered when discussing data validation.) Each property is decorated with the System.Data.Linq.Mapping.ColumnAttribute that represents a column within a database table. This attribute takes some arguments that are self-explanatory. The most important of them are Storage, which points to a private field used as a data repository, and DbType, which contains the original SQL Server data type for the column. It is worth mentioning that Visual Basic provides an appropriate type mapping according to the related SQL data type. A primary key requires two other attributes, IsPrimaryKey = True and AutoSync. The second one establishes that it has to be auto-incremented and synchronized when a new item is added. In the end, notice how Set properties members perform a series of actions, such as raising events related to the beginning of property editing, storing the new value, and finally raising events related to the property set completion. This is auto-generated code from Visual Studio, and you should never change it manually. You are instead encouraged to use the LINQ to SQL designer that reflects changes in code. The last file for a LINQ to SQL class has a .dbml.layout extension and is just related to the diagram layout. Now that you are a little bit more familiar with LINQ to SQL classes, you can begin querying data with LINQ to SQL.

Behind the Scenes of LINQ to SQL Classes

The Visual Studio 2012 IDE generates LINQ to SQL classes invoking a command-line tool named SQLMetal.exe that is part of the Windows SDK for .NET. The following is an example of the command line for performing a manual generation of LINQ to SQL classes for the Northwind database and Visual Basic:

SQLMetal.exe /Server:.SQLExpress /DataBase:Northwind /dbml:Northwind.dbml
/language:VisualBasic

SQLMetal.exe offers other command-line options for generating LINQ to SQL classes, but in most cases you do not need such manual generation because the IDE will do all the appropriate work for you. There is only one scenario in which you need to manually create a LINQ to SQL class—when mapping SQL Server Compact Edition 3.5 databases. This is discussed at the end of this chapter (and that also applies to Windows Phone development). For further information on SQLMetal, visit the official page on MSDN: http://msdn.microsoft.com/en-us/library/bb386987(VS.110).aspx.

Querying Data with LINQ to SQL

Before you begin querying data with LINQ to SQL, you need to instantiate the DataContext class. Continuing with the console application example started in the previous section, you can declare such an instance at the module level as follows:

Private northwind As New NorthwindDataContext

Declaring a single instance at the module or class level allows one DataContext to manage entities for all the object model lifetimes. When you create such an instance, the DataContext connects to the database and provides required abstraction so that you can work against the object model instead of working against the database. The DataContext class’s constructor also accepts a connection string if you want it to be hard-coded instead of storing it within a configuration file. You have different alternatives for querying data. For example, you might want to retrieve the complete list of products that is accomplished as follows:

'Returns Table(Of Product)
Dim allProduct = northwind.Products

This code returns a System.Data.Linq.Table(Of Product) that is an object inheriting from IQueryable(Of T) and that represents a database table. IQueryable(Of T) is the general type returned by LINQ to SQL queries and inherits from IEnumerable(Of T) but also offers some more members specific for data manipulation. Although this type can be directly bound to user interface controls for presenting data as much as IEnumerable, it does not support data editing. A Table(Of T) instead supports adding, removing, and saving objects. To perform LINQ queries using filtering, ordering, and projection operators, you use the LINQ keywords and the same programming techniques provided by the unified programming model of this technology. A little difference from LINQ to Objects is that LINQ to SQL queries return an IQueryable(Of T) instead of IEnumerable(Of T). For example, the following LINQ query returns the list of products in which the unit price is greater than 10:

'Returns IQueryable(Of Product)
Dim queryByPrice = From prod In northwind.Products
                   Where prod.UnitPrice > 10
                   Select prod

You can also convert a query into an ordered collection such as the List(Of T) using extension methods:

'Returns List(Of Product)
Dim queryByPrice = (From prod In northwind.Products
                   Where prod.UnitPrice > 10
                   Select prod).ToList

Remember that LINQ queries are effectively executed only when used; therefore, the first example does not run the query until you invoke something on it. The second query is instead executed immediately because of the ToList invocation. For example, the following iteration would cause the first query to be executed when the enumerator is invoked:

'Returns IQueryable(Of Product)
Dim queryByPrice = From prod In northwind.Products
                   Where prod.UnitPrice > 10
                   Select prod

'Query is executed now
For Each prod In queryByPrice
    Console.WriteLine(prod.ProductName)
Next

This iteration shows the list of product names. You can also perform more complex queries that are probably what you will do in your real applications. The following method queries products for the specified category, given the category name taking only products that are not discontinued, finally ordering the result by the number of units in stock for product:

Function QueryByCategoryName(ByVal categoryName As String) _
         As List(Of Product)

    Dim query = From categories In northwind.Categories
                Where categories.CategoryName = categoryName
                Join prod In northwind.Products
                On prod.CategoryID Equals categories.CategoryID
                Where prod.Discontinued = False
                Order By prod.UnitsInStock
                Select prod

    Return query.ToList
End Function

You can invoke the method and iterate the result as follows:

Dim productsList = QueryByCategoryName("Seafood")

For Each prod In productsList
    Console.WriteLine("Product name: {0}, unit price: {1}",
                      prod.ProductName,
                      prod.UnitPrice)
Next

The preceding code produces the following result:

Product name: Rogede sild, unit price: 9.5000
Product name: Nord-Ost Matjeshering, unit price: 24.8900
Product name: Gravad lax, unit price: 26.0000
Product name: Konbu, unit price: 6.0000
Product name: Ikura, unit price: 31.0000
Product name: Carnarvon Tigers, unit price: 62.5000
Product name: Escargots de Bourgogne, unit price: 13.2500
Product name: Jack's New England Clam Chowder, unit price: 9.6500
Product name: Spegesild, unit price: 12.0000
Product name: Röd Kaviar, unit price: 15.0000
Product name: Inlagd Sill, unit price: 19.0000
Product name: Boston Crab Meat, unit price: 18.4000

In other cases, you need to data-bind your result to user interface controls. If you work with Windows Forms applications, a good idea is returning a System.ComponentModel.BindingList(Of T) that is a collection specific for data-binding. So the preceding method could be rewritten as follows:

Function QueryByCategoryName(ByVal categoryName As String) _
         As System.ComponentModel.BindingList(Of Product)

    Dim query = From categories In northwind.Categories
                Where categories.CategoryName = categoryName
                Join prod In northwind.Products
                On prod.CategoryID Equals categories.CategoryID
                Where prod.Discontinued = False
                Order By prod.UnitsInStock
                Select prod

    Return New System.ComponentModel.
               BindingList(Of Product)(query.ToList)
End Function

Similarly, for WPF applications you would return an ObservableCollection(Of T):

Function QueryByCategoryName(ByVal categoryName As String) _
         As System.ObjectModel.ObservableCollection(Of Product)

    Dim query = From categories In northwind.Categories
                Where categories.CategoryName = categoryName
                Join prod In northwind.Products
                On prod.CategoryID Equals categories.CategoryID
                Where prod.Discontinued = False
                Order By prod.UnitsInStock
                Select prod

    Return New System.ObjectModel.ObservableCollection(query)
End Function

You could also take advantage of anonymous types for collecting data from different tables into a unique collection. The following code obtains a list of products for the given category name, picking up some information:

Dim customQuery = From prod In northwind.Products
                  Join cat In northwind.Categories On
                  prod.CategoryID Equals cat.CategoryID
                  Order By cat.CategoryID
                  Select New With {.CategoryName = cat.CategoryName,
                                     .ProductName = _
                                      prod.ProductName,
                                     .UnitPrice = prod.UnitPrice,
                                     .Discontinued = _
                                      prod.Discontinued}

This query, when executed, returns an IQueryable(Of Anonymous type). As you already know, lists of anonymous types can be iterated or also bound to user interface controls for presenting data but cannot be edited. If you need to create custom objects from query results, such as collecting data from different tables, you first need to implement a class that groups all required data as properties. Consider the following class:

Class CustomObject
    Property CategoryName As String
    Property ProductName As String
    Property UnitPrice As Decimal?
    Property Discontinued As Boolean?
End Class––

Now you can rewrite the preceding query as follows, changing the Select clause allowing generating a new CustomObject instance:

Dim customQuery = From prod In northwind.Products
                  Join cat In northwind.Categories On
                  prod.CategoryID Equals cat.CategoryID
                  Order By cat.CategoryID
                  Select New CustomObject _
                         With {.CategoryName = cat.CategoryName,
                               .ProductName = prod.ProductName,
                               .UnitPrice = prod.UnitPrice,
                               .Discontinued = prod.Discontinued}

Now the query returns an IQueryable(Of CustomObject). You can convert it into a typed collection according to your needs or iterate it as in the following example:

For Each obj In customQuery
    Console.WriteLine("Category name: {0}, Product name: {1},
                      Unit price: {2}, Discontinued: {3}",
                      obj.CategoryName, obj.ProductName,
                      obj.UnitPrice, obj.Discontinued)
Next

Providing this approach instead of working against anonymous types can allow you to bind your collections to user interface controls and provide two-way data binding, or simpler, can provide the ability of programmatically coding Insert/Update/Delete operations as explained in the next section.

Insert/Update/Delete Operations with LINQ

LINQ to SQL is not just querying data but is also a complete infrastructure for data manipulation. This means that you can perform Insert/Update/Delete operations against your object model using LINQ. Let’s discuss first how a new entity can be added to an entity set.

Inserting Entities

You instantiate a new entity as any other .NET class and then set its properties. The following code shows how you can add a new Product to the Products entity set. Notice how the method receives the belonging category as an argument, which is required for setting the one-to-many relationship:

Sub AddProduct(ByVal categoryReference As Category)
    Dim aProduct As New Product

    aProduct.ProductName = "Italian spaghetti"
    aProduct.Discontinued = False
    aProduct.QuantityPerUnit = "10"
    aProduct.UnitPrice = 0.4D

    'Setting the relationship
    aProduct.Category = categoryReference

    'Adding the new product to the object model
    northwind.Products.InsertOnSubmit(aProduct)
End Sub

You set property values as you would in any other .NET class. Here you have to pay attention to add a non-null value to non-nullable members. In the previous example, QuantityPerUnit is a non-nullable and therefore must be assigned with a valid string. You can then omit assigning nullable members. LINQ to SQL can provide auto-increment functionalities on primary keys that in the original SQL Server database implement such a feature. In this example, ProductID is not assigned because it is an auto-incrementable primary key. You set a one-to-many relationship assigning the property referring to the other part of the relationship (Category in the preceding example) with the instance of the entity that completes the relationship. When this is performed, you invoke the InsertOnSubmit method on the instance of the entity set that receives the new entity (respectively Products and Product in our example). This method saves the new data into the object model, but it does not send data to the underlying database until you invoke the SubmitChanges method, as follows:

Sub SaveChanges()
    Try
        northwind.SubmitChanges()

    Catch ex As SqlClient.SqlException

    Catch ex As Exception

    End Try
End Sub

This saves data to the database. If something fails, you need to handle a SqlClient.SqlException exception. Now add an invocation to the custom SaveChanges method after the InsertOnSubmit one. At this point you can invoke the custom AddProduct method for programmatically adding a new product that must be bound to a specific category because of the one-to-many relationship. Working with a Console application, you can add such an invocation within the Main method. The following code demonstrates this:

Dim cerealsCategory As Category = _
    northwind.Categories.Single(Function(cat) _
    cat.CategoryName = "Grains/Cereals")

AddProduct(cerealsCategory)

You need the instance of the category you want to pass to the method. To accomplish this, you can invoke the Single extension method on the categories’ collection to get the unique category with the specified name, taking advantage of a lambda expression. As an alternative, you can directly pass a lambda as an argument as follows:

AddProduct(northwind.Categories.
           Single(Function(cat) cat.CategoryName = "Grains/Cereals"))

Both solutions accomplish the same result.

If you run this code and everything works fine, your new product is added to the Products table of the Northwind database when the DataContext.SubmitChanges method is invoked and a relationship with the Grains/Cereals category is also set. You can easily verify this by opening Server Explorer and then expanding the Northwind Tables folder; then right-click the Products table and select Show Table Data. (If you have a local copy of the Northwind database instead, you need to double-click the database copy available in the BinDebug or BinRelease folder to open it in Server Explorer.) As an alternative, you can open the new SQL Server Object Explorer tool window, expand the Northwind database, and then right-click the Products table and select View data. Figure 24.7 reproduces the scenario, showing also the new product, this time in SQL Server Object Explorer.

Figure 24.7. Checking that the new product has been correctly saved to the database.

One thing that you need to remember is to check whether an entity already exists to prevent malicious runtime exceptions. To accomplish this, you can take advantage of the Single extension method that throws an exception if the specified entity does not exist; therefore, it can be added. The AddProduct method can be rewritten as follows (see comments in code):

Sub AddProduct(ByVal categoryReference As Category)
    Try
        Dim productCheck = northwind.Products.
                           Single(Function(prod) _
                           prod.ProductName = "Italian spaghetti")
        productCheck = Nothing

        'the Product does not exist, so add it
    Catch ex As InvalidOperationException
        Dim aProduct As New Product

        aProduct.ProductName = "Italian spaghetti"
        aProduct.Discontinued = False
        aProduct.QuantityPerUnit = "10"

  aProduct.UnitPrice = 0.4D
        aProduct.CategoryID = categoryReference.CategoryID

        'Setting the relationship
        aProduct.Category = categoryReference

        'Adding the new product to the object model
        northwind.Products.InsertOnSubmit(aProduct)
        SaveChanges()

    End Try
End Sub

Now that you know how to create and save data, it’s also important to understand how updates can be performed.

Updating Entities

Updating existing entities is even easier than adding new ones. First, you need to obtain the instance of the entity you want to update. When you get the instance, you edit its properties and then invoke the DataContext.SubmitChanges method. The following code provides an example:

Sub UpdateProduct(ByVal productInstance As Product)

    'Throws an exception if a null value is passed
    If productInstance Is Nothing Then
        Throw New NullReferenceException
    Else
        With productInstance
            .ProductName = "Italian Linguine"
            .UnitsInStock = 100
        End With
    End If

    SaveChanges()
End Sub

This method requires an instance of the Product entity to be updated. To get an instance of the desired product, you can still take advantage of a lambda, but this time exception handling is reverted, as you can see from the following snippet:

Try
    UpdateProduct(northwind.Products.
                  Single(Function(prod) prod.
                  ProductName = "Italian spaghetti"))

    'The specified product does not exist
Catch ex As InvalidOperationException

End Try

When the NorthwindDataContext.SubmitChanges method is invoked, data is updated to the underlying database. Notice that you can update multiple entities and the SubmitChanges method sends changes all at once. You can easily check for correct updates following the steps shown in the previous paragraph and summarized in Figure 24.7.

Deleting Entities

Deleting an entity works similarly to update, at least for retrieving the entity instance. Deletion is performed by invoking the DeleteOnSubmit method, which works opposite to the InsertOnSubmit. The following is an example, which also checks whether the entity exists:

Sub DeleteProduct(ByVal productInstance As Product)

    If productInstance Is Nothing Then
        Throw New NullReferenceException
    Else
        northwind.Products.DeleteOnSubmit(productInstance)
        SaveChanges()
    End If
End Sub

Remember how the custom SaveChanges method invokes the NorthwindDataContext.SubmitChanges one. The following code shows invoking the previous method for performing a product deletion:

Try
    DeleteProduct(northwind.Products.
                  Single(Function(prod) prod.
                  ProductName = "Italian spaghetti"))

    'The specified product does not exist
Catch ex As InvalidOperationException

End Try

Similarly to InsertAllOnSubmit, you can also invoke DeleteAllOnSubmit to remove multiple entities from the object model.

Mapping Stored Procedures

LINQ to SQL allows mapping stored procedures from the SQL Server database into a .NET method that you can use within your object model and that is managed by the running instance of the DataContext. In this way, you do not lose the advantage of stored procedures when working with LINQ. To map a stored procedure, go back to the Visual Studio Designer for LINQ to SQL and ensure that the Methods pane is opened on the right side of the designer; then open Server Explorer, expand the database structure, and expand the Stored Procedures folder. After you’ve done this, drag the stored procedure you need onto the Methods pane. Figure 24.8 shows how to accomplish this against the Northwind database of the current example.

Figure 24.8. Mapping a stored procedure to a .NET method in LINQ to SQL.

Notice also how the Properties window shows method properties, such as access qualifier and signature. The Return type property is set as auto-generated because the result is determined according to the stored procedure type. Some procedures return a single result value, and therefore the returned type is ISingleResult(Of T). Other ones can return multiple result values, and therefore the returned type is IMultipleResult(Of T). Behind the scenes, a stored procedure is mapped into a method, but such a method also requires a support class mapping types used by the stored procedure. The following code is excerpted from the Northwind.designer.vb file and shows the class definition:

Partial Public Class Ten_Most_Expensive_ProductsResult

    Private _TenMostExpensiveProducts As String

    Private _UnitPrice As System.Nullable(Of Decimal)

    Public Sub New()
        MyBase.New
    End Sub

    <Global.System.Data.Linq.Mapping.
            ColumnAttribute(Storage:="_TenMostExpensiveProducts",
            DbType:="NVarChar(40) NOT NULL", CanBeNull:=False)> _
    Public Property TenMostExpensiveProducts() As String
        Get
            Return Me._TenMostExpensiveProducts
        End Get
        Set(ByVal value As String)
            If (String.Equals(Me._TenMostExpensiveProducts, value) = False)
            Then
                Me._TenMostExpensiveProducts = value
            End If
        End Set
    End Property

    <Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_UnitPrice",
            DbType:="Money")> _
    Public Property UnitPrice() As System.Nullable(Of Decimal)
        Get
            Return Me._UnitPrice
        End Get
        Set(ByVal value As System.Nullable(Of Decimal))
            If (Me._UnitPrice.Equals(Value) = False) Then
                Me._UnitPrice = Value
            End If
        End Set
    End Property
End Class

The class works like other auto-generated classes in that it sets or returns values taken from the data source. The method that actually performs the action is mapped as follows within the NorthwindDataContext class definition:

<Global.System.Data.Linq.Mapping.
        FunctionAttribute(Name:="dbo.[Ten Most Expensive Products]")> _
Public Function Ten_Most_Expensive_Products() As  _
        ISingleResult(Of Ten_Most_Expensive_ProductsResult)
    Dim result As IExecuteResult = Me.ExecuteMethodCall(Me,
                                   CType(MethodInfo.GetCurrentMethod,
                                   MethodInfo))
    Return CType(result.ReturnValue,
                 ISingleResult(Of Ten_Most_Expensive_ProductsResult))
End Function

The System.Data.Linq.Mapping.FunctionAttribute attribute decorates the method signature with the original stored procedure name. As you can see, this particular method returns an ISingleResult(Of T), and invocation to the stored procedure is performed via reflection. Invoking in code, a stored procedure is as simple as in other methods usage. The following code takes an ISingleResult(Of T):

'Gets the list of the ten most expensive
'products from the Products table
Dim result = northwind.Ten_Most_Expensive_Products

You can then iterate the result to get a list of the products as in the following example:

For Each r In result
    Console.WriteLine(r.UnitPrice)
Next

This simple iteration produces the following result:

263,5000
123,7900
97,0000
81,0000
62,5000
55,0000
53,0000
49,3000
46,0000
45,6000

Notice that an ISingleResult can be iterated only once; otherwise, you get an InvalidOperationException. If you plan to access this result multiple times, the only way is to convert the result into a generic collection such as the List(Of T). The following code converts the stored procedure result into a List, making possible iterations more than once:

'Gets the list of the ten most expensive
'products from the Products table into
'a List(Of T)
Dim result = northwind.Ten_Most_Expensive_Products.ToList

Also notice that converting to IQueryable(Of T) will not allow the result to be accessed more than once.

Using the Log

LINQ to SQL sends SQL instructions each time it has to perform an operation on our demand. This is accomplished via its complex infrastructure that relies on the .NET Framework. By the way, as a developer you may be interested in understanding what really happens behind the scenes and in getting information about the real SQL instructions sent to SQL Server. Luckily, you can use a SQL log that allows showing SQL instructions. You need to set the DataContext.Log property as follows, before taking actions you want to inspect:

northwind.Log = Console.Out

If you want to monitor everything happening, add the preceding code after the creation of the DataContext instance. If you apply this code before running the first example shown in the “Insert/Update/Delete operations with LINQ” section, you get the result shown in Figure 24.9.

Figure 24.9. Showing the LINQ to SQL log result.

As you can see, this is useful because you get an idea about the actual SQL instructions sent by LINQ to SQL to SQL Server. The DataContext.Log property is of type System.IO.TextWriter; therefore, you can assign it with a stream pointing to a file on disk if you want the SQL output to be redirected to a file instead of the Console window.

Advanced LINQ to SQL

While you become familiar with LINQ to SQL, you understand how it allows performing usual data operations in a strongly typed way. Because of this, you also see the need to perform other operations that you are used to making in classical data development, such as data validation and handling optimistic concurrency. The next section describes this but also something more.

Custom Validations

Validating data is one of the most important activities in every data access system, so LINQ to SQL provides its own methodologies, too. To accomplish data validation, you can take advantage of partial methods. You might remember that in Chapter 20, “Advanced Language Features,” you got a practical example of partial methods when discussing LINQ to SQL. Validation rules are useful in LINQ to SQL for two main reasons: The first one is that they enable you to understand whether supplied data is compliant to your requirements; the second one is that they allow you to check whether supplied data has a SQL Server type counterpart. The following code example demonstrates both examples. Imagine you want to add a new product to the object model and then save changes to the database, as you already did following the steps in the first part of the previous section. If you take a look at the QuantityPerUnit property—for example, recurring to the Visual Studio Designer—you notice that it is mapped to a String .NET type, but its SQL Server counterpart type is NVarChar(20), meaning that the content of the property is a string that must not be longer than 20 characters; otherwise, saving changes to SQL Server will be unsuccessful. To provide validation rules, the first step is to add a partial class. With that said, right-click the project name in Solution Explorer, then select Add New Class, and, when requested, supply the new class name, for example Product.vb. When the new class is added to the project, add the Partial keyword as follows:

Partial Public Class Product

End Class

At this point we can implement a partial method that performs validation. Because partial methods’ signatures are defined within the Northwind.designer.vb code file, here we can implement the full method body as follows:

Private Sub OnQuantityPerUnitChanging(ByVal value As String)
    If value.Length > 20 Then Throw New  _
        ArgumentException _
        ("Quantity per unit must be no longer than 20 characters")
End Sub

Notice that you have to handle methods whose names end with Changing, which maps an event that is raised before changes are sent to the object model. The code checks for the length of the supplied value, and if it does not match the NVarChar(20) type of SQL Server, it throws an ArgumentException. To understand how it works, consider the following code that creates a new product and then attempts to write changes:

Sub AddProduct(ByVal categoryReference As Category)
    Try
        Dim productCheck = northwind.Products.
                           Single(Function(prod) _
                           prod.ProductName = "Italian spaghetti")
        productCheck = Nothing

        'the Product does not exist, so add it
    Catch ex As InvalidOperationException

        Try
            Dim aProduct As New Product

            aProduct.ProductName = "Italian spaghetti"
            aProduct.Discontinued = False

            'The string is 22 characters long
            aProduct.QuantityPerUnit = "1000000000000000000000"
            aProduct.UnitPrice = 0.4D
            aProduct.CategoryID = categoryReference.CategoryID

            'Setting the relationship
            aProduct.Category = categoryReference

            'Adding the new product to the object model
            northwind.Products.InsertOnSubmit(aProduct)
            SaveChanges()

        Catch e As ArgumentException
            Console.WriteLine(e.Message.ToString)
            Exit Try
        Catch e As Exception

        End Try
    End Try
End Sub

Notice how a nested Try..End Try block has been provided to handle eventual ArgumentNullException errors coming from validation. You can still invoke the AddProduct method in the previous section as follows:

AddProduct(northwind.Categories.
           Single(Function(cat) cat.CategoryName = "Grains/Cereals"))

If you now try to run the code, you get an error message advising that the QuantityPerUnit content cannot be longer than 20 characters. In this way, you can control the content of your data but also ensure that data matches the related SQL Server type. By using this technique, you can perform validation on each data you want.

Handling Optimistic Concurrency

Optimistic concurrency is a scenario in which multiple clients send changes to the database simultaneously. LINQ to SQL allows resolving optimistic concurrency with the DataContext.ChangeConflicts.ResolveAll method. Such method receives an argument that is an enumeration of type System.Data.Linq.RefreshMode and allows resolving the exception with one of the enumeration members summarized in Table 24.1.

Table 24.1. RefreshMode Enumeration Members

The following is an example of handling optimistic concurrency, providing a revisited version of the previously utilized SaveChanges custom method:

Sub SaveChanges()
    Try
        northwind.SubmitChanges()

    Catch ex As System.Data.Linq.ChangeConflictException

        northwind.ChangeConflicts.ResolveAll(Data.Linq.RefreshMode.
                                             KeepCurrentValues)
        northwind.SubmitChanges()
    Catch ex As SqlClient.SqlException

    Catch ex As Exception

    End Try
End Sub

Notice first how a ChangeConflictException is handled. Here the ChangeConflicts.ResolveAll method is required to resolve concurrency. The KeepCurrentValues argument allows keeping original values in the database. Also notice how a subsequent invocation to SubmitChanges is made. This is necessary because the first invocation caused the exception; therefore, another execution must be attempted.

Using SQL Syntax Against Entities

LINQ to SQL also allows writing SQL code against entities so that you can still take advantage of the object model if you prefer the old-fashioned way of manipulating data. The DataContext class offers an instance method named ExecuteQuery(Of T) that allows sending SQL instructions in string form. For example, the following code retrieves a list of products for the Grain/Cereals category, ordered by product name:

Sub DirectSqlDemo()

    Dim products = northwind.
        ExecuteQuery(Of Product)("SELECT * FROM PRODUCTS WHERE " & _
                                 "CATEGORYID='5' ORDER BY PRODUCTNAME")

    For Each prod In products
        Console.WriteLine(prod.ProductName)
    Next
End Sub

ExecuteQuery(Of T) returns an IEnumerable(Of T) that you can then treat as you like, according to LINQ specifications. You can also send SQL instructions directly to the database, invoking the ExecuteCommand method. This method returns no value and allows performing Insert/Update/Delete operations against the data. For example, the following code updates the product name of a product:

northwind.ExecuteCommand("UPDATE PRODUCTS SET " & _
          "PRODUCTNAME='Italian mozzarella' WHERE PRODUCTID='72'")

If you then want to check that everything work correctly, get the instance of the product and get information:

Dim updatedProduct = _
    northwind.Products.First(Function(prod) prod.ProductID = 72)

'Returns "Italian mozzarella"
Console.WriteLine(updatedProduct.ProductName)

Remember: Sending SQL instructions can prevent you from taking advantage of compile-time checking offered by the LINQ syntax and exposes your code to possible runtime errors. Be aware of this.

LINQ to SQL with SQL Server Compact Edition 3.5

LINQ to SQL is not limited to querying and manipulating data from SQL Server databases, but you can also perform the same operations against SQL Server Compact Edition 3.5 databases (with .sdf extensions). There are some reasons to mention SQL Server Compact with LINQ to SQL. The first reason is that you might want to use a lightweight local database in your applications and SQL Compact is a good choice. The second reason is that SQL Server Compact 3.5 can be used also in Windows Phone 7.5 apps for local data access. In Chapter 38, “Building Apps for Windows Phone 7.5,” we introduce the development for this operating system; it is important to know how to create LINQ to SQL classes for SQL Compact. Visual Studio 2012 ships with SQL Server Compact Edition 4.0, but this new version does not support LINQ to SQL (more precisely, the SQLMetal.exe command line tool does not support SQL Compact 4.0). So you will be able to use LINQ with SQL Server Compact 3.5, which is also the one you still use with Windows Phone. If you have installed Visual Studio 2010 on your machine you already have version 3.5, otherwise you can download it from http://www.microsoft.com/en-us/download/details.aspx?id=5783. The big difference is that generating LINQ to SQL classes for this engine is not supported by the IDE, so you have to perform a couple of steps manually. First, run the Visual Studio command prompt; if you run Windows 7, it is under Start, All Programs, Microsoft Visual Studio 2012, Visual Studio Tools. If you run Windows 8, you can use the Search charm and type VS 2012; the one you will select is the VS 2012 Cross Tools one. When you get the command line, move to the folder where the SQL Compact database is available. For example, you can play with the Northwind database in the compact edition version. Type the following command line:

CD C:Program FilesMicrosoft SQL Server Compact Editionv3.5Samples

Next, type the following command line:

SQLMetal /dbml:Northwind.dbml /language:VisualBasic Northwind.sdf

This step generates a .dbml file, which is a complete LINQ to SQL class. To create a LINQ to SQL project supporting your .sdf database, you create a Visual Basic project, right-click the project name in Solution Explorer, and then select the Add Existing Item command. When the dialog box appears, select the newly created LINQ to SQL class and you’re done. Remember that you can use all LINQ to SQL possibilities with SQL Server Compact Edition databases as well, so everything you learned in this chapter is also applicable to SQL Compact files. Of course, there are limitations due to the database’s structure (for example SQL Compact databases do not support stored procedures), but this is something that is related more to SQL Server than Visual Basic. By the way, LINQ to SQL is the same in both SQL Server and SQL Compact.

Writing the Connection String

Different from classic LINQ to SQL, when you work with SQL Compact databases, you need to manually pass the connection string to the database; this is because the class generation could not take advantage of the IDE automation. This means that when you declare an instance of the DataContext class, you need to pass the connection string. For example, instantiating the DataContext for Northwind would be something like this:

Private NorthwindContext As New  _
        Northwind("Data Source=C:My FolderNorthwind.sdf")

Summary

LINQ to SQL is a built-in object relational mapping engine for Microsoft SQL Server databases. The engine maps database information such as tables and columns into .NET objects such as classes and properties, enabling you to work in a disconnected fashion against an object model rather than against the database. Mapped classes are known as entities. Adding LINQ to SQL classes to your projects can provide the ability of using LINQ for both querying entities and performing CRUD operations via specific methods offered by the DataContext class. This is responsible for managing the connection and entities during an application’s lifetime, including keeping track of changes that can be submitted to the database in one shot. LINQ to SQL also offers a trace log to understand which SQL instructions were sent to the database and provides the ability of handling optimistic concurrency as much as validating data by taking advantage of partial methods. Finally, you can still write your queries the old-fashioned way by sending SQL instructions directly to the data source. LINQ to SQL is useful if you need to work with a lightweight and if you are limited to SQL Server databases. If you instead need something more flexible and powerful, you should consider the ADO.NET Entity Framework discussed in Chapter 26.