4.2. Lesson 1: What Is LINQ to SQL?

LINQ to SQL provides a framework for managing relational data as objects, but you can still query the data. LINQ to SQL is an object-relational mapping (ORM) tool that enables you not only to query the data but also to insert, update, or delete the data. You can use an object-centric approach to manipulate the objects in your application while LINQ to SQL is in the background, tracking your changes.

In this lesson, you learn about modeling data.

After this lesson, you will be able to: Generate a LINQ to SQL model from an existing database. Use the LINQ to SQL model to map stored procedures to methods. Use a DataContext object to manage your database connection and context. Understand how LINQ to SQL connects to your database. Store information about objects and their state. Understand object lifetime and how objects are cached. Understand eager loading versus lazy loading. Estimated lesson time: 45 minutes

4.2.1. Modeling Your Data

Probably the best way to help you gain an understanding of LINQ to SQL is to start with some data modeling to help you see the big picture of the LINQ to SQL capabilities. This also helps by providing a visual model of your classes and how they relate to each other.

4.2.1.1. Generating a LINQ to SQL Model from an Existing Database

The easiest way to get started with LINQ to SQL is to generate a model from an existing database. This can be accomplished by right-clicking your project node in Solution Explorer, and choosing Add | New Item | LINQ to SQL Classes. Name the file Northwind.dbml, as shown in Figure 4-1.

Figure 4-1. Select LINQ to SQL classes and name the file Northwind.dbml.

The file extension is .dbml (database markup language), which is an XML file that contains the model settings. After naming the file, click Add. The file will be rendered to your screen as a two-paned window in which the left side displays table entities and the right side displays stored procedures.

---

Note:

BE CAREFUL WHEN NAMING THE .DBML FILE

The name you assign to the file will also be used to create a DataContext object called nameDataContext. To ensure Pascal casing on your data context object, be sure to use Pascal casing on this file name. For example, if you name this file nOrThWiNd.dbml, the data context class that is created will be called nOrThWiNdDataContext. You can go to the DataContext properties to change the name if you make a mistake when naming the file, but being careful when naming the file will save you time.

---

From Server Explorer, you can drag tables to the left pane and drop them. This requires you to have a configured connection to Microsoft SQL Server. If you don’t have a connection to the Northwind database, you can right-click the Data Connections node, click Add Connection, select Microsoft SQL Server, and click OK. In the Add Connection window, type your server name (for example, .\SQLExpress for your local SQL Server Express instance) and, in the Select Or Enter A Database Name drop-down list, select the Northwind database and click OK.

You can also drag stored procedures to the right pane and drop them. Figure 4-2 shows the model diagram after dragging and dropping the Customers, Orders, Order Details, and Employees tables and the CustOrderHist and CustOrdersDetail stored procedures.

Figure 4-2. The model diagram shows tables as classes and stored procedures as methods.

4.2.1.2. Examining the Model

In Figure 4-2, the Customers table was added, but a class, Customer (singular), is shown. An instance of the Customer class represents a row in the Customers table. The LINQ to SQL designer automatically attempts to singularize plural table names. Most of the time, this works as expected, but it’s not that smart. For example, a movies table will produce a Movy class instead of a Movie class. You can override the proposed name of any class by clicking the class in the design window and opening the Properties window to change the Name property to any valid class name.

In the Properties window, other properties can be configured. The Insert, Update, and Delete properties are defaulted to use the run time to generate the appropriate logic, but this can be changed to execute a stored procedure instead.

The primary key is also highlighted in the diagram by displaying a key beside all properties that make up the primary key. For example, notice that the Order_Detail class has two primary key properties to indicate that these properties are combined to produce a unique key.

The LINQ to SQL designer also imported the relationships into your diagram. For example, customers place orders, so you can see that an association line is drawn between the Customer class and the Order class. The association line shows a one-to-many relationship between the Customer class and the Order class. This also can be stated as “a customer has orders.” You can use the Properties window to change the configuration of the associations.

4.2.1.3. Mapping Stored Procedures

With LINQ to SQL, you can easily access stored procedures as regular methods in your code, as shown in Figure 4-2, in which two stored procedures were added to the model by dragging and dropping them to the designer surface. The icon displayed in the designer is the standard method icon. If you click the CustOrderHist stored procedure, you’ll see its properties, as shown in Figure 4-3.

Figure 4-3. This figure displays the properties of the CustOrderHist stored procedure.

In Figure 4-3, the method signature is defined as CustOrderHist (System.String customerID), which means that a method called CustOrderHist will be created that accepts a string argument representing the customer ID.

---

Note:

WHAT DOES THE STORED PROCEDURE METHOD RETURN?

The designer will make an attempt to auto-define a new type that represents the output, but this works in simple scenarios only. If you have a stored procedure with conditional code that will return different result types based on a condition, the designer won’t be smart enough to return the correct type. The design simply tries to execute the stored procedure with the SET FMTONLY ON option set, and it passes default values into the parameters to see what is returned. In the Properties window, you can specify the return type, but you can see that in some scenarios this will not be useful. Your solution will be either to rewrite the stored procedure or revert to traditional ADO.NET to get the returned result into a data table.

---

If you have a stored procedure that returns an entity type, for example, a stored procedure that returns a filtered list of customers, you can drag the stored procedure from Server Explorer and drop it on to the Customer entity. This will tell the designer that you want to return a list of Customer objects. If you’ve already added the stored procedure to the designer, you can set the Return Type in the Properties window, which informs the designer that you are returning an IEnumerable of the type you select.

Another example of using stored procedures with LINQ to SQL is when you want the insert, update, and delete statements to be executed as stored procedures instead of as dynamic SQL statements. This can be configured by clicking the appropriate entity class, for example, the Customer class, and setting Insert, Update, and Delete properties to the appropriate stored procedure, as shown in Figure 4-4.

Figure 4-4. Each entity class has properties for Insert, Update, and Delete.

Figure 4-4 shows the default settings for Insert, Update, and Delete, but you can assign a stored procedure to these commands. In Figure 4-4, there is no property for Select. If you want to use a stored procedure for selecting customers, you can create the stored procedure and drag it to the design surface to create a method for selecting and then change the return type of the stored procedure to the Customer type.

4.2.2. Examining the Designer Output

When you save and close the LINQ to SQL designer, it creates types in your application that can access the entities and stored procedures in your model diagram. These types can be viewed by clicking the plus sign beside the Northwind.dbml file. If you don’t have a plus sign beside the Northwind.dbml file, click the Show All Files button at the top of the Solution Explorer window. Under the Northwind.dbml file, you will see a Northwind.dbml.layout file, which is an XML file that contains layout information describing where the elements are on the design surface. The Northwind.dbml.vb (or Northwind.dbml.cs) file also contains the generated types. Open this file to see its contents.

The following classes are defined in this file: Customer, CustOrderHistResult, CustOrderDetailsResult, Employee, NorthwindDataContext, Order, and Order_Detail. The classes that have the “Result” suffix are auto-created to represent the results from the stored procedures.

4.2.2.1. Examining an Entity Class

The section focuses on one of the entity classes, the Customer class. All the other entity classes are implemented in a similar fashion. If you understand the Customer class, you should be able to understand the other entity classes.

When you locate the Customer class, you’ll notice that this class is adorned with attributes, as shown in the following code sample:

Sample of Visual Basic Code

<Global.System.Data.Linq.Mapping.TableAttribute(Name:="dbo.Customers"),  _
 Global.System.Runtime.Serialization.DataContractAttribute()>  _
Partial Public Class Customer
      Implements System.ComponentModel.INotifyPropertyChanging,
                 System.ComponentModel.INotifyPropertyChanged

   ' more code here
End Class

Sample of C# Code

[global::System.Data.Linq.Mapping.TableAttribute(Name = "dbo.Customers")]
[global::System.Runtime.Serialization.DataContractAttribute()]
public partial class Customer : INotifyPropertyChanging, INotifyPropertyChanged
{
   //mode code here
}

					  

The first attribute is TableAttribute, which LINQ to SQL uses to identify the table in SQL Server that this class represents. This means that TableAttribute links the Customer class to the dbo.Customers table in the database because the Name property specifies the exact name of the database table. If no Name property is supplied, LINQ to SQL assumes the database table has the same name as the class. Only instances of classes declared as tables are stored in the database. Instances of these types of classes are known as entities. The classes themselves are known as entity classes.

The second attribute is DataContractAttribute, which enables serialization of the Customer class when used with Windows Communication Foundation (WCF) services. This attribute exists because the Serialization property on NorthwindDataContext was set to Unidirectional. If you didn’t set the Serialization Mode property, you won’t see this attribute. (Read more about this in the Section 4.2.2.2 section of this chapter).

The Customer class implements the INotifyPropertyChanging interface, which defines a PropertyChanging event. The Customer entity uses this interface to tell the LINQ to SQL change tracker when it has changed. If you don’t implement INotifyPropertyChanging, the LINQ to SQL change tracker assumes that all objects queried will change, and it automatically keeps a copy of all queried objects.

The Customer class also implements the INotifyPropertyChanged interface, which has a PropertyChanged event. This interface is implemented for use with data binding. If your object will not be data-bound, it will not need this interface implementation.

Next, the Customer class has private fields and public properties for each column in the database table. The following code sample shows the CustomerID.

Sample of Visual Basic Code

Private _CustomerID As String

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_CustomerID", _
       DbType:="NChar(5) NOT NULL", CanBeNull:=False, IsPrimaryKey:=True), _
    Global.System.Runtime.Serialization.DataMemberAttribute(Order:=1)> _
Public Property CustomerID() As String
   Get
      Return Me._CustomerID
   End Get
   Set(ByVal value As String)
      If (String.Equals(Me._CustomerID, value) = False) Then
         Me.OnCustomerIDChanging(value)
         Me.SendPropertyChanging()
         Me._CustomerID = value
         Me.SendPropertyChanged("CustomerID")
         Me.OnCustomerIDChanged()
      End If
   End Set
End Property

Sample of C# Code

private string _CustomerID;

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_CustomerID",
                DbType="NChar(5) NOT NULL", CanBeNull=false, IsPrimaryKey=true)]
[global::System.Runtime.Serialization.DataMemberAttribute(Order=1)]
public string CustomerID
{
   get
   {
      return this._CustomerID;
   }
   set
   {
      if ((this._CustomerID != value))
      {
         this.OnCustomerIDChanging(value);
         this.SendPropertyChanging();
         this._CustomerID = value;
         this.SendPropertyChanged("CustomerID");
         this.OnCustomerIDChanged();
      }
   }
}

					  

In the code example, the CustomerID public property is adorned with ColumnAttribute. This attribute identifies each persistable property. Without this attribute, CustomerID will not be saved to the database. ColumnAttribute has several properties that can be set to change the persistence behavior slightly. In the code example, the Storage property identifies the private field that has the data. The Name property on ColumnAttribute can be set if, for example, the field name in the table does not match the property name.

The CustomerID property is also decorated by DataMemberAttribute to indicate to WCF services that this property’s data can be serialized.

The property getter isn’t doing anything other than returning the value of the private field. The setter has code that first attempts to call the partial OnCustomerIDChanging and OnCustomerChanged methods. If you decide to implement these methods, they will be called automatically to notify you before and after the change. The setter also has code to trigger the PropertyChanging and PropertyChanged events to notify anyone who has subscribed to these events.

An additional private field and public property for each child table is also referenced. In the Customer class, there is a private field and public property for the related orders because a customer has orders. The following code sample shows the private field and public property that represent the orders related to a customer.

Sample of Visual Basic Code

Private _Orders As EntitySet(Of [Order])

<Global.System.Data.Linq.Mapping.AssociationAttribute(Name:="Customer_Order", _
         Storage:="_Orders", ThisKey:="CustomerID", OtherKey:="CustomerID"), _
      Global.System.Runtime.Serialization.DataMemberAttribute(Order:=12, _
         EmitDefaultValue:=False)> _
Public Property Orders() As EntitySet(Of [Order])
   Get
      If (Me.serializing _
            AndAlso (Me._Orders.HasLoadedOrAssignedValues = False)) Then
         Return Nothing
      End If
      Return Me._Orders
   End Get
   Set(ByVal value As EntitySet(Of [Order]))
      Me._Orders.Assign(value)
   End Set
End Property

Sample of C# Code

private EntitySet<Order> _Orders;

[global::System.Data.Linq.Mapping.AssociationAttribute(Name="Customer_Order", Storage="_
Orders", ThisKey="CustomerID", OtherKey="CustomerID")]
[global::System.Runtime.Serialization.DataMemberAttribute(Order=12,
EmitDefaultValue=false)]
public EntitySet<Order> Orders
{
   get
   {
      if ((this.serializing && (this._Orders.HasLoadedOrAssignedValues == false)))
      {
         return null;
      }
      return this._Orders;
   }
   set
   {
      this._Orders.Assign(value);
   }
}

					  

At first glance, this code looks similar to the code example for CustomerID, but ColumnAttribute has been replaced by AssociationAttribute. This attribute identifies Customers_Order as the relationship that navigates from the Customers table to the Orders table. The attribute also identifies the key(s) used on the Customers and Orders tables.

The data type for Orders is a generic entity set of Order. The generic EntitySet is a specialized collection that provides deferred loading and relationship maintenance for the collection side of one-to-many and one-to-one relationships.

The getter has code to return nothing (C# null) if Customer is currently being serialized to keep from also serializing Orders. The getter also returns nothing (C# null) if no value has been assigned to this property or if this property has not been loaded.

The setter has simple code to pass the incoming value to the Assign method of the private field. EntitySet has a ListChanged event to which you can subscribe if you want to be notified when an assignment is made to this collection.

4.2.2.2. Examining the DataContext Class

The NorthwindDataContext class was created by the LINQ to SQL designer. This class inherits from the DataContext class that is part of the .NET Framework. The DataContext class is the main object for moving data to and from the database. You must instantiate the NorthwindDataContext class and then use its properties and methods to provide access to the database. To see the DataContext properties, click an empty area of the LINQ to SQL designer surface. Figure 4-5 shows the DataContext properties.

Figure 4-5. The NorthwindDataContext properties provide access to the connection string and other settings.

Of all the classes created by the LINQ to SQL designer, this is the only class that doesn’t inherit from an object. The Base Class property provides the opportunity to create an intermediate class that inherits from DataContext, by which you add more functionality. You can then assign the intermediate class to the Base Class property.

---

Tip:

EXAM TIP

You can expect to be tested on the DataContext class because it’s explicitly called out in the exam objectives.

---

You also can set the namespace for the data context and entity classes so you can avoid naming collisions that could result if any of the created class names match the name of a class that already exists in your application.

If you are writing a WCF service, you might want to return instances of your entity classes from the service. This requires you to assign DataContract and DataMember attributes to the class and its properties by changing the Serialization Mode property from None to Unidirectional.

Looking at the NorthwindDataContext class that was produced by the LINQ to SQL designer, the following code example shows the class definition:

Sample of Visual Basic Code

<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()
   'more members here
End Class

Sample of C# Code

[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="Northwind")]
public partial class NorthwindDataContext : System.Data.Linq.DataContext
{
   private static System.Data.Linq.Mapping.MappingSource mappingSource = _
      new AttributeMappingSource();
   //more members here
}

					  

This class is adorned with DatabaseAttribute, by which you specify the name of the database to which you will connect. This class inherits from DataContext.

This class also has a static field called mappingSource, which defaults to an instance of the AttributeMappingSource class. This field holds the mapping between the classes in the domain and the database as specified by attributes on the entity classes. You could opt to replace this object with an instance of XmlMappingSource, which would enable you to externalize the mappings to an XML file.

The NorthwindDataContext class contains a public property per type of entity class. The following code sample shows the Customers property:

Sample of Visual Basic Code

Public ReadOnly Property Customers() As System.Data.Linq.Table(Of Customer)
   Get
      Return Me.GetTable(Of Customer)()
   End Get
End Property

Sample of C# Code

public System.Data.Linq.Table<Customer> Customers
{
   get
   {
      return this.GetTable<Customer>();
   }
}

Notice that the property type is the generic Table class of Customer. The Table class provides functionality for querying, inserting, updating, and deleting.

The NorthwindDataContext class also contains partial methods that you could implement for hooking into insert, update, and delete objects in any of the tables’ properties on this class.

4.2.3. Managing Your Database Connection and Context Using DataContext

This section examines the connection string and how the DataContext object uses the connection string to connect to the database.

4.2.3.1. How LINQ to SQL Connects to Your Database

When you added items from Server Explorer, you automatically added the database connection string to your project as well. If you look in your config file, you will find the following connection string setting:

Config File

<connectionStrings>
    <add name="LinqToSqlSampleCode.Properties.Settings.NorthwindConnectionString"
        connectionString="Data Source=.;Initial Catalog=Northwind;Integrated
Security=True"
        providerName="System.Data.SqlClient" />
</connectionStrings>

					  

LINQ to SQL uses the traditional ADO.NET SqlConnection class to open a connection to the SQL Server database. In this example, the data source property is set to a period, which means to connect to the local SQL Server instance. If you have only SqlExpress installed, your data source will be set to .\SQLEXPRESS, which means you want to connect to the SqlExpress instance of SQL Server on your local machine.

---

Tip:

EXAM TIP

For the exam, know that you must be on SQL Server 2000 or later to use LINQ to SQL because you will be tested on the requirements to use LINQ to SQL. You must be using .NET Framework 3.5 or later as well, and SQL Server 2000 has many limitations.

---

The DataContext object has a Connection property, and some of the constructors of the NorthwindDataContext accept a connection string. The following code sample shows the parameterless constructor for the NorthwindDataContext class:

Sample of Visual Basic Code

Public Sub New()
  MyBase.New(Global.LinqToSqlSampleCode.MySettings.Default.NorthwindConnectionString, _
      mappingSource)
  OnCreated()
End Sub

Sample of C# Code

public NorthwindDataContext() :
  base(global::LinqToSqlSampleCode.Properties.Settings.Default.
NorthwindConnectionString,
      mappingSource)
{
  OnCreated();
}

					  

In this code example, the parameterless constructor makes a call to the base class (DataContext) constructor but is passing NorthwindConnectionString, which is in the configuration file. This means that you can instantiate the NorthwindDataContext without passing any parameter, and you automatically use the connection string that’s in your config file. Also, you can easily change the connection string in the config file without requiring a rebuild of the application.

4.2.3.2. What’s Sent to SQL Server, and When Is It Sent?

You might be wondering what kind of query is sent to SQL Server. Is the query efficient? When is the query sent to SQL Server? This section explores a simple LINQ to SQL query to answer these questions.

In the following code sample, a simple LINQ query is presented that retrieves a list of employees whose last names start with “D” and binds the result to a Windows Presentation Foundation (WPF) data grid.

Sample of Visual Basic Code

Private Sub mnuSimpleLinq_Click(ByVal sender As System.Object, _
                                ByVal e As System.Windows.RoutedEventArgs)
   Dim ctx = New NorthwindDataContext()
   Dim employees =  From emp In ctx.Employees
                    Where emp.LastName.StartsWith("D")
                    Select emp
   dg.ItemsSource = employees
End Sub

Sample of C# Code

private void mnuSimpleLinq_Click(object sender, RoutedEventArgs e)
{
   var ctx = new NorthwindDataContext();
   var employees = from emp in ctx.Employees
                   where emp.LastName.StartsWith("D")
                   select emp;
   dg.ItemsSource = employees;
}

This example shows the use of the parameterless constructor to create the NorthwindDataContext object. There is no reference to a connection in this code. NorthwindDataContext has an Employees property that can be used in your LINQ query. The LINQ query that follows creates the IQueryable<Employee> query object; however, remember that LINQ query execution is deferred until the result of the query is enumerated. The last statement assigns the employees query object to the ItemsSource property on the WPF data grid. The data grid will enumerate the employees query object, which will cause the query to execute and retrieve the two employees whose last names start with “D.”

When the LINQ to SQL query is created by initializing the employees variable, connection pooling is initialized, but nothing has executed yet. When the employees variable is assigned to the ItemsSource property of the data grid, the LINQ to SQL query is executed, and two employees’ names are returned, as shown in Figure 4-6.

Figure 4-6. Two employees are returned from the LINQ to SQL query. In this example, AutoGenerateColumns is set to true.

How did this query work? Did LINQ to SQL send a query to SQL Server to retrieve all the employees and then filter the employees within your application? How can you find the answers to these questions?

One way to find the answers is to set a breakpoint in your program on the statement that assigns the employees query to the data grid. Run the application and, when you reach the break point, hover over the employees variable, and you’ll see a tool tip with the SQL query that will be sent to SQL Server; however, it’s difficult to see the whole query within the small tool tip.

---

Note:

LINQ TO SQL DEBUG VISUALIZER

You can find various LINQ to SQL debug visualizers on the Internet. After installing one of these visualizers, you will see a magnifying glass when hovering over the variable. Clicking the magnifying glass typically displays a pop-up window with the query in a much more readable format.

---

Another way to find the answers is to use the Log property on NorthwindDataContext. This property accepts a TextWriter object and will write out all queries so you can create a StreamWriter object that references a file so you can write everything to a file. You can also assign a StringWriter to the Log property, which will send the SQL queries to a memory stream, and then you can display the contents. The following code sample shows the creation of a StringWriter that is assigned to the Log property, and its contents are displayed after the query is executed.

Sample of Visual Basic Code

Private Sub mnuSimpleLinq_Click(ByVal sender As System.Object, _
                                ByVal e As System.Windows.RoutedEventArgs)
   Dim ctx = New NorthwindDataContext()
   Dim sw = New StringWriter()
   ctx.Log = sw
   Dim employees = From emp In ctx.Employees
                   Where emp.LastName.StartsWith("D")
                   Select emp
   dg.ItemsSource = employees
   MessageBox.Show(sw.GetStringBuilder().ToString())
End Sub

Sample of C# Code

private void mnuSimpleLinq_Click(object sender, RoutedEventArgs e)
{
   var ctx = new NorthwindDataContext();
   var sw =  new StringWriter();
   ctx.Log = sw;
   var employees = from emp in ctx.Employees
                   where emp.LastName.StartsWith("D")
                   select emp;
   dg.ItemsSource = employees;
   MessageBox.Show(sw.GetStringBuilder().ToString());
}

					  

After running this code sample, a message box is displayed, as shown in Figure 4-7. This query is retrieving all columns from the Employees table, but the query includes a where clause to provide the filtering at the database. SQL Server then performs the filtering and returns two rows to your application, thus providing efficient SQL for your LINQ to SQL query.

Figure 4-7. Here is the SQL query that is sent to SQL Server.

Finally, another way to see the queries sent to SQL Server is to use the SQL Server Profiler tool that comes with the Developer edition of SQL Server. The SQL Server Profiler can capture all SQL statements sent to SQL Server. Although this tool isn’t included with SQL Server Express, it does work with that edition.

To use the SQL Server Profiler, you must have administrator privileges on SQL Server, or the SQL Server administrator can grant permissions for you to run the profiler tool. This tool can store the captured statements to a file or a database table. Figure 4-8 shows the output when running the sample LINQ to SQL code.

Figure 4-8. The SQL Server Profiler can capture all the traffic between the application and SQL Server.

The SQL Server Profiler can capture and display much more information than is shown in the Log property of NorthwindDataContext. In fact, in Figure 4-7, you can see that three select statements were sent to SQL Server. The first select statement is highlighted, and it matches the statement that was shown when using the Log property. The second SQL statement has a where clause to return only EmployeeID=2, and the third SQL statement has a where clause to return EmployeeID=5. These two queries were caused by the data grid in an effort to retrieve the most recent value for the employees.

4.2.3.3. Eager Loading vs. Lazy Loading

When specifying properties or associations for which to query on your entity, you can perform eager loading or lazy loading. Lazy loading is also known as delay loading. Eager loading is also known as pre-fetch loading. The default behavior is to perform eager loading of the properties, which means that a property is loaded when a query is executed that references the property.

Lazy loading is configured in the LINQ to SQL designer by selecting an entity and then, in the Properties window, setting the Delay Loaded property to true. Figure 4-9 shows the Delay Loaded property.

Figure 4-9. The Delay Loaded property can be set to true to perform lazy loading.

When using lazy loading, the property is not loaded until the property is accessed. When examining lazy loading, you need to think about performance and when you will you take the performance hit. In one extreme, if every property were lazy loaded, there would be a cost associated with establishing the connection each time and transferring the data. To the user, this might make the application feel choppy or erratic. If you’re fairly certain that you will use the data, why not pull the properties in one call? You take a big hit, maybe when a page is displayed to the user, but the page feels crisp afterward. The choice you make depends on the how much data will be transferred and how certain you are that you will use the data. With lazy loading, you’re making the decision to incur the performance cost to retrieve the data when you need it because you’re fairly certain you won’t need the data anyway. In Figure 4-9, the Photo entry on the Employee entity is set to Delay Loaded. The following code example shows the effect of setting Delay Loaded to true:

Sample of Visual Basic Code

Private Sub mnuLazyLoading_Click(ByVal sender As System.Object, _
                                ByVal e As System.Windows.RoutedEventArgs)
   Dim ctx = New NorthwindDataContext()
   Dim sw = New StringWriter()
   ctx.Log = sw
   Dim employee = (From emp In ctx.Employees
                   Where emp.LastName.StartsWith("D")
                   Select emp).First()
   MessageBox.Show(sw.GetStringBuilder().ToString())

   sw = New StringWriter()
   ctx.Log = sw
   Dim photo = New MemoryStream(Employee.Photo.ToArray())
   MessageBox.Show(sw.GetStringBuilder().ToString())
End Sub

Sample of C# Code

private void mnuLazyLoading_Click(object sender, RoutedEventArgs e)
{
   var ctx = new NorthwindDataContext();
   var sw = new StringWriter();
   ctx.Log = sw;
   var employee = (from emp in ctx.Employees
                   where emp.LastName.StartsWith("Davolio")
                   select emp).First();
   MessageBox.Show(sw.GetStringBuilder().ToString());

   sw = new StringWriter();
   ctx.Log = sw;
   var photo = new MemoryStream(employee.Photo.ToArray());
   MessageBox.Show(sw.GetStringBuilder().ToString());
}

					  

This code sample retrieves a single employee and displays the SQL statement that was generated. The code is then able to access the Photo property successfully, and the query that was run is displayed. Figure 4-10 shows the queries that were executed when this example code has been run.

Figure 4-10. The SQL statements are shown that retrieve the employee name and then retrieve the employee’s photo.

The first query that ran didn’t include Photo, even though the LINQ expression requested the whole employee object. The Photo property was not included because the Delay Loaded property was set to true. When the Photo property was accessed, LINQ to SQL made a call to retrieve Photo. This is the second query displayed. When you use lazy loading, you’re essentially betting that you’re not going to need all columns’ content, but if you do need the data, it will be automatically fetched for you.

4.2.3.3.1. Practice Working with the LINQ to SQL Designer

In this practice, you create a new WPF application that accepts orders from customers, using the Northwind database. After creating the application, you use the LINQ to SQL designer to create an entity model for this application. In later exercises, you will add functionality to make the application operational.

This practice is intended to focus on the classes that have been defined in this lesson, so the graphical user interface (GUI) will be minimal.

If you encounter a problem completing an exercise, the completed projects can be installed from the Code folder on the companion CD.

EXERCISE Create the Project and LINQ to SQL Entity Model

In this exercise, you create a WPF Application project and the entity model, using the North-wind database.

4.2.4. Lesson Summary

This lesson provided detailed information about the LINQ to SQL designer.

• You can create an entity model easily by dragging and dropping database tables from Server Explorer.

• A table that is dropped on to the LINQ to SQL designer surface creates an entity class that represents each row in the table.

• You can drag and drop stored procedures to the LINQ to SQL designer surface, which creates methods that you can call from your application.

• Entity classes implement INotifyPropertyChanging and INotifyPropertyChanged to be tracked efficiently by the object tracking service.

• The DataContext object provides a property for each table and a method for each stored procedure.

• LINQ to SQL supports eager loading by default, but you can enable deferred loading, also known as lazy loading, to defer loading of the entity properties until you actually reference the property in your code.

4.2.5. Lesson Review

You can use the following questions to test your knowledge of the information in Section 4.2 The questions are also available on the companion CD if you prefer to review them in electronic form.

---

Note:

ANSWERS

Answers to these questions and explanations of why each answer choice is correct or incorrect are located in the Appendix A section at the end of the book.

---