2.7.4.Querying Entities and Associations

This section creates strongly-typed queries against the CLR objects that represent entities and associations in the School model, and bind display controls to the object collections returned from these queries.

Query the departments in the School database

  1. At the beginning of the code file for the

    CourseViewer

    form, add the following

    using

    (C#) or

    Imports

    (Visual Basic) statements to reference the model created from the School database and the entity namespace.

    Visual Basic
    
    Imports System.Data.Objects
    Imports System.Data.Objects.DataClasses
    
    C#
    
    using System.Data.Objects;
    using System.Data.Objects.DataClasses;
    
  2. At the top of the partial class definition for the

    CourseViewer

    form, add the following code that creates an

    ObjectContext

    instance.

    Visual Basic
    
    ' Create an ObjectContext instance based on SchoolEntity.
    Private schoolContext As SchoolEntities
    
    C#
    
    // Create an ObjectContext instance based on SchoolEntity.
    private SchoolEntities schoolContext;
    
  3. In the

    CourseViewer

    form designer, double-click the

    CourseViewer

    form. This opens the code page for the form and creates the

    courseViewer _Load

    event handler method.

  4. In the

    courseViewer _Load

    event handler method, copy and paste the following code that defines the

    DataGridView

    , executes a query that returns a collection of departments (ordered by

    Name

    ), and binds the collection of

    Department

    objects to the departmentList control.

    Visual Basic
    
    ' Initialize the ObjectContext.
    schoolContext = New SchoolEntities()
    
    ' Define a query that returns all Department objects and related
    ' Course objects, ordered by name.
    Dim departmentQuery As ObjectQuery(Of Department) = _
        schoolContext.Department.Include("Course").OrderBy("it.Name")
    
    Try
        ' Bind the ComboBox control to the query, which is
        ' executed during data binding.
        Me.departmentList.DisplayMember = "Name"
        Me.departmentList.DataSource = departmentQuery
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    
    C#
    
    // Initialize the ObjectContext.
    schoolContext = new SchoolEntities();
    
    // Define a query that returns all Department objects and related
    // Course objects, ordered by name.
    ObjectQuery<Department> departmentQuery =
        schoolContext.Department.Include("Course").OrderBy("it.Name");
    
    try
    {
        // Bind the ComboBox control to the query, which is
        // executed during data binding.
        this.departmentList.DisplayMember = "Name";
        this.departmentList.DataSource = departmentQuery;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    

Display courses for the selected department

  1. In the

    CourseViewer

    form designer, double-click the

    departmentList

    control. This creates the

    departmentList_SelectedIndexChanged

    event handler method.

  2. Paste the following code that loads the courses that are related to the selected department.

    Visual Basic
    
    Try
        ' Get the object for the selected department.
        Dim department As Department = _
            CType(Me.departmentList.SelectedItem, Department)
    
        ' Bind the grid view to the collection of Course objects
        ' that are related to the selected Department object.
        courseGridView.DataSource = department.Course
    
        ' Hide the columns that are bound to the navigation properties on Course.
        courseGridView.Columns("Department").Visible = False
        courseGridView.Columns("CourseGrade").Visible = False
        courseGridView.Columns("OnlineCourse").Visible = False
        courseGridView.Columns("OnsiteCourse").Visible = False
        courseGridView.Columns("Person").Visible = False
    
        courseGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    
    C#
    
    try
    {
        // Get the object for the selected department.
        Department department =
            (Department)this.departmentList.SelectedItem;
    
        // Bind the grid view to the collection of Course objects
        // that are related to the selected Department object.
        courseGridView.DataSource = department.Course;
    
        // Hide the columns that are bound to the navigation properties on Course.
        courseGridView.Columns["Department"].Visible = false;
        courseGridView.Columns["CourseGrade"].Visible = false;
        courseGridView.Columns["OnlineCourse"].Visible = false;
        courseGridView.Columns["OnsiteCourse"].Visible = false;
        courseGridView.Columns["Person"].Visible = false;
    
        courseGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }