Walkthrough: Simple Object Model and Query (Visual Basic)

This walkthrough provides a fundamental end-to-end LINQ to SQL scenario with minimal complexities. You will create an entity class that models the Customers table in the sample Northwind database. You will then create a simple query to list customers who are located in London.

This walkthrough is code-oriented by design to help show LINQ to SQL concepts. Normally speaking, you would use the Object Relational Designer to create your object model.

This walkthrough was written by using Visual Basic Development Settings.

• This walkthrough uses a dedicated folder ("c:\linqtest") to hold files. Create this folder before you begin the walkthrough.

• This walkthrough requires the Northwind sample database. If you do not have this database on your development computer, you can download it from the Microsoft download site. For instructions, see Downloading Sample Databases. After you have downloaded the database, copy the file to the c:\linqtest folder.

This walkthrough consists of six main tasks:

• Creating a LINQ to SQL solution in Visual Studio.

• Mapping a class to a database table.

• Designating properties on the class to represent database columns.

• Specifying the connection to the Northwind database.

• Creating a simple query to run against the database.

• Executing the query and observing the results.

In this first task, you create a Visual Studio solution that contains the necessary references to build and run a LINQ to SQL project.

1. On the File menu, click New Project.

2. In the Project types pane of the New Project dialog box, click Visual Basic.

3. In the Templates pane, click Console Application.

4. In the Name box, type LinqConsoleApp.

5. Click OK.

This walkthrough uses assemblies that might not be installed by default in your project. If System.Data.Linq is not listed as a reference in your project (click Show All Files in Solution Explorer and expand the References node), add it, as explained in the following steps.

1. In Solution Explorer, right-click References, and then click Add Reference.

2. In the Add Reference dialog box, click .NET, click the System.Data.Linq assembly, and then click OK.

   The assembly is added to the project.

3. Also in the Add Reference dialog box, click .NET, scroll to and click System.Windows.Forms, and then click OK.

   This assembly, which supports the message box in the walkthrough, is added to the project.

4. Add the following directives above Module1:

   Imports System.Data.Linq
   Imports System.Data.Linq.Mapping
   Imports System.Windows.Forms
   
   

In this step, you create a class and map it to a database table. Such a class is termed an entity class. Note that the mapping is accomplished by just adding the TableAttribute attribute. The Name property specifies the name of the table in the database.

• Type or paste the following code into Module1.vb immediately above Sub Main:

  <Table(Name:="Customers")> _
  Public Class Customer
  End Class
  

In this step, you accomplish several tasks.

• You use the ColumnAttribute attribute to designate CustomerID and City properties on the entity class as representing columns in the database table.

• You designate the CustomerID property as representing a primary key column in the database.

• You designate _CustomerID and _City fields for private storage. LINQ to SQL can then store and retrieve values directly, instead of using public accessors that might include business logic.

• Type or paste the following code into Module1.vb just before End Class:

  Private _CustomerID As String
  <Column(IsPrimaryKey:=True, Storage:="_CustomerID")> _
  Public Property CustomerID() As String
      Get
          Return Me._CustomerID
      End Get
      Set(ByVal value As String)
          Me._CustomerID = value
      End Set
  End Property
  
  Private _City As String
  <Column(Storage:="_City")> _
  Public Property City() As String
      Get
          Return Me._City
      End Get
      Set(ByVal value As String)
          Me._City = value
      End Set
  End Property
  

In this step you use a DataContext object to establish a connection between your code-based data structures and the database itself. The DataContext is the main channel through which you retrieve objects from the database and submit changes.

You also declare a Table(Of Customer) to act as the logical, typed table for your queries against the Customers table in the database. You will create and execute these queries in later steps.

• Type or paste the following code into the Sub Main method.

  Note that the northwnd.mdf file is assumed to be in the linqtest folder. For more information, see the Prerequisites section earlier in this walkthrough.

  ' Use a connection string.
  Dim db As New DataContext _
      ("c:\linqtest\northwnd.mdf")
  
  ' Get a typed table to run queries.
  Dim Customers As Table(Of Customer) = _
      db.GetTable(Of Customer)()
  

In this step, you create a query to find which customers in the database Customers table are located in London. The query code in this step just describes the query. It does not execute it. This approach is known as deferred execution. For more information, see Introduction to LINQ Queries (C#).

You will also produce a log output to show the SQL commands that LINQ to SQL generates. This logging feature (which uses Log) is helpful in debugging, and in determining that the commands being sent to the database accurately represent your query.

• Type or paste the following code into the Sub Main method after the Table(Of Customer) declaration:

  ' Attach the log to show generated SQL in a console window.
  db.Log = Console.Out
  
  ' Query for customers in London.
  Dim custQuery = _
      From cust In Customers _
      Where cust.City = "London" _
      Select cust
  

In this step, you actually execute the query. The query expressions you created in the previous steps are not evaluated until the results are needed. When you begin the For Each iteration, a SQL command is executed against the database and objects are materialized.

1. Type or paste the following code at the end of the Sub Main method (after the query description):

   ' Format the message box.
   Dim msg As String = "", title As String = "London customers:", _
       response As MsgBoxResult, style As MsgBoxStyle = _
       MsgBoxStyle.Information
   
   ' Execute the query.
   For Each custObj In custQuery
       msg &= String.Format(custObj.CustomerID & vbCrLf)
   Next
   
   ' Display the results.
   response = MsgBox(msg, style, title)
   

2. Press F5 to debug the application.

   Note

   If your application generates a run-time error, see the Troubleshooting section of Learning by Walkthroughs.

   The message box displays a list of six customers. The Console window displays the generated SQL code.

3. Click OK to dismiss the message box.

   The application closes.

4. On the File menu, click Save All.

   You will need this application if you continue with the next walkthrough.

The Walkthrough: Querying Across Relationships (Visual Basic) topic continues where this walkthrough ends. The Querying Across Relationships walkthrough demonstrates how LINQ to SQL can query across tables, similar to joins in a relational database.

If you want to do the Querying Across Relationships walkthrough, make sure to save the solution for the walkthrough you have just completed, which is a prerequisite.

• Learning by Walkthroughs