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 via the Server Explorer tool window (or the Database Explorer if you work with Visual Basic Express).

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

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

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

Figure 25.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 25.3 and then expand Server Explorer to show the Northwind database structure; then expand the Tables folder.

Figure 25.3 Preparing to pick tables from the Northwind database.

Now keep the Ctrl key pressed and click on 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 25.4.

Figure 25.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 25.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 basically 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 lifetime

• Translating Visual Basic code into the appropriate SQL instructions

• Managing entities’ lifetime, no matter how long

Visual Studio generates a new DataContext class forming its name 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 25.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 25.5 within the Properties window.

Figure 25.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 25.6 shows how the Properties window describes such an object.

Figure 25.6 Examining associations.

Notice how a one-to-many relationship is represented. The Child Property property shows the “many” part of the one-to-many relationship, whereas 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.diagram extension and is the class diagram that we just saw in the Visual Studio Designer. All edits, including Visual Studio-generated items that 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 25.1 shows the definition of the NorthwindDataContext class:

Listing 25.1 The NorthwindDataContext Class Definition

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 25.2 shows the definition of the Category class.

Listing 25.2 The Category Class Definition

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 that points to a private field used as a data repository and DbType that 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 autoincremented 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.