Walkthrough: Querying Across Relationships (C#) (LINQ to SQL)

Article
11/16/2012

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 Visual Studio Settings.

This walkthrough was written by using Visual C# Development Settings.

Prerequisites

You must have completed Walkthrough: Simple Object Model and Query (C#) (LINQ to SQL). This walkthrough builds on that one, including the presence of the northwnd.mdf file in c:\linqtest5.

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 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 Order.Customer relates as a foreign key to Customer.CustomerID.

To add the Order entity class

Type or paste the following code after the Customer class:

[Table(Name = "Orders")]
public class Order
{
    private int _OrderID = 0;
    private string _CustomerID;
    private EntityRef<Customer> _Customer;
    public Order() { this._Customer = new EntityRef<Customer>(); }

    [Column(Storage = "_OrderID", DbType = "Int NOT NULL IDENTITY",
    IsPrimaryKey = true, IsDbGenerated = true)]
    public int OrderID
    {
        get { return this._OrderID; }
        // No need to specify a setter because IsDBGenerated is 
        // true.
    }

    [Column(Storage = "_CustomerID", DbType = "NChar(5)")]
    public string CustomerID
    {
        get { return this._CustomerID; }
        set { this._CustomerID = value; }
    }

    [Association(Storage = "_Customer", ThisKey = "CustomerID")]
    public Customer Customer
    {
        get { return this._Customer.Entity; }
        set { this._Customer.Entity = value; }
    }
}

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 EntitySet<Order> _Orders;
public Customer()
{
    this._Orders = new EntitySet<Order>();
}

[Association(Storage = "_Orders", OtherKey = "CustomerID")]
public EntitySet<Order> Orders
{
    get { return this._Orders; }
    set { this._Orders.Assign(value); }
}

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

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

// Query for customers who have placed orders. 
var custQuery = 
    from cust in Customers
    where cust.Orders.Any()
    select cust;

foreach (var custObj in custQuery)
{
    Console.WriteLine("ID={0}, Qty={1}", custObj.CustomerID,
        custObj.Orders.Count);
}

Press F5 to debug your application.

Note

You can eliminate the SQL code in the Console window by commenting out db.Log = Console.Out;.
Press Enter in the Console window 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

Add the following code above the Customer class declaration.

public class Northwind : DataContext
{
    // Table<T> abstracts database details per table/data type. 
    public Table<Customer> Customers;
    public Table<Order> Orders;

    public Northwind(string connection) : base(connection) { }
}

Modify the Main method to use the strongly typed DataContext as follows:

// Use a connection string.
Northwind db = new Northwind(@"C:\linqtest5\northwnd.mdf");

// Query for customers from Seattle.  
var custQuery =
    from cust in db.Customers
    where cust.City == "Seattle" 
    select cust;

foreach (var custObj in custQuery)
{
    Console.WriteLine("ID={0}", custObj.CustomerID);
}
// Freeze the console window.
Console.ReadLine();

Press F5 to debug your application.

The Console window output is:

ID=WHITC
Press Enter in the console window to stop debugging.

Next Steps

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

Share via