Walkthrough: Querying Across Relationships (Visual Basic)

This walkthrough demonstrates the use of LINQ to SQL associations to represent foreign-key relationships in the database.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalizing the IDE.

This walkthrough was written by using Visual Basic Development Settings.

Prerequisites

You must have completed Walkthrough: Simple Object Model and Query (Visual Basic). This walkthrough builds on that one, including the presence of the northwnd.mdf file in c:\linqtest.

Overview

This walkthrough consists of three main tasks:

  • Adding an entity class to represent the Orders table in the sample Northwind database.

  • Supplementing annotations to the Customer class to enhance the relationship between the Customer and Order classes.

  • Creating and running a query to test the process of obtaining Order information by using the Customer class.

Mapping Relationships across Tables

After the Customer class definition, create the Order entity class definition that includes the following code, which indicates that Orders.Customer relates as a foreign key to Customers.CustomerID.

To add the Order entity class

  • Type or paste the following code after the Customer class:

    <Table(Name:="Orders")> _
    Public Class Order
        Private _OrderID As Integer
        Private _CustomerID As String
        Private _Customers As EntityRef(Of Customer)
    
        Public Sub New()
            Me._Customers = New EntityRef(Of Customer)()
        End Sub
    
        <Column(Storage:="_OrderID", DbType:="Int NOT NULL IDENTITY", _
            IsPrimaryKey:=True, IsDBGenerated:=True)> _
        Public ReadOnly Property OrderID() As Integer
            Get
                Return Me._OrderID
            End Get
        End Property
        ' No need to specify a setter because IsDBGenerated is true.
    
        <Column(Storage:="_CustomerID", DbType:="NChar(5)")> _
        Public Property CustomerID() As String
            Get
                Return Me._CustomerID
            End Get
            Set(ByVal value As String)
                Me._CustomerID = value
            End Set
        End Property
    
        <Association(Storage:="_Customers", ThisKey:="CustomerID")> _
        Public Property Customers() As Customer
            Get
                Return Me._Customers.Entity
            End Get
            Set(ByVal value As Customer)
                Me._Customers.Entity = value
            End Set
        End Property
    End Class
    

Annotating the Customer Class

In this step, you annotate the Customer class to indicate its relationship to the Order class. (This addition is not strictly necessary, because defining the relationship in either direction is sufficient to create the link. But adding this annotation does enable you to easily navigate objects in either direction.)

To annotate the Customer class

  • Type or paste the following code into the Customer class:

    Private _Orders As EntitySet(Of Order)
    
    Public Sub New()
        Me._Orders = New EntitySet(Of Order)()
    End Sub
    
    <Association(Storage:="_Orders", OtherKey:="CustomerID")> _
    Public Property Orders() As EntitySet(Of Order)
        Get
            Return Me._Orders
        End Get
        Set(ByVal value As EntitySet(Of Order))
            Me._Orders.Assign(value)
        End Set
    End Property
    

Creating and Running a Query across the Customer-Order Relationship

You can now access Order objects directly from the Customer objects, or in the opposite order. You do not need an explicit join between customers and orders.

To access Order objects by using Customer objects

  1. Modify the Sub Main method by typing or pasting the following code into the method:

    ' Query for customers who have no orders.
    Dim custQuery = _
        From cust In Customers _
        Where Not cust.Orders.Any() _
        Select cust
    
    Dim msg As String = "", title As String = _
        "Customers With No Orders", response As MsgBoxResult, _
        style As MsgBoxStyle = MsgBoxStyle.Information
    
    For Each custObj In custQuery
        msg &= String.Format(custObj.CustomerID & vbCrLf)
    Next
    response = MsgBox(msg, style, title)
    
  2. Press F5 to debug your application.

    Two names appear in the message box, and the Console window shows the generated SQL code.

  3. Close the message box to stop debugging.

Creating a Strongly Typed View of Your Database

It is much easier to start with a strongly typed view of your database. By strongly typing the DataContext object, you do not need calls to GetTable. You can use strongly typed tables in all your queries when you use the strongly typed DataContext object.

In the following steps, you will create Customers as a strongly typed table that maps to the Customers table in the database.

To strongly type the DataContext object

  1. Add the following code above the Customer class declaration.

    Public Class Northwind
        Inherits DataContext
        ' Table(Of T) abstracts database details  per
        ' table/data type.
        Public Customers As Table(Of Customer)
        Public Orders As Table(Of Order)
    
        Public Sub New(ByVal connection As String)
            MyBase.New(connection)
        End Sub
    End Class
    
  2. Modify Sub Main to use the strongly typed DataContext as follows:

    ' Use a connection string.
    Dim db As New Northwind _
        ("C:\linqtest\northwnd.mdf")
    
    ' Query for customers from Seattle.
    Dim custs = _
        From cust In db.Customers _
        Where cust.City = "Seattle" _
        Select cust
    
    For Each custObj In custs
        Console.WriteLine("ID=" & custObj.CustomerID)
    Next
    
    ' Freeze the console window.
    Console.ReadLine()
    
  3. Press F5 to debug your application.

    The Console window output is:

    ID=WHITC

  4. Press Enter in the Console window to close the application.

  5. On the File menu, click Save All if you want to save this application.

Next Steps

The next walkthrough (Walkthrough: Manipulating Data (Visual Basic)) demonstrates how to manipulate data. That walkthrough does not require that you save the two walkthroughs in this series that you have already completed.

See also