Querying Entities and Associations

In this task, you will create 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.

To 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.

   Imports System.Data.Objects
   Imports System.Data.Objects.DataClasses
   

   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.

   ' Create an ObjectContext instance based on SchoolEntity.
   Private schoolContext As SchoolEntities
   

   //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.

   ' 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) = _
       From d In schoolContext.Departments.Include("Courses") _
       Order By d.Name _
       Select d
   Try
       ' Bind the ComboBox control to the query.
       ' To prevent the query from being executed multiple times during binding, 
       ' it is recommended to bind controls to the result of the Execute method. 
       Me.departmentList.DisplayMember = "Name"
       Me.departmentList.DataSource = CType(departmentQuery, ObjectQuery).Execute(MergeOption.AppendOnly)
   Catch ex As Exception
       MessageBox.Show(ex.Message)
   End Try
   

   //Initialize the ObjectContext
   schoolContext = new SchoolEntities();
   
   // Define a query that returns all Department  
   // objects and course objects, ordered by name.
   var departmentQuery = from d in schoolContext.Departments.Include("Courses")
                         orderby d.Name
                         select d;
   try
   {
       // Bind the ComboBox control to the query, 
       // which is executed during data binding.
       // To prevent the query from being executed multiple times during binding, 
       // it is recommended to bind controls to the result of the Execute method. 
       this.departmentList.DisplayMember = "Name";
       this.departmentList.DataSource = ((ObjectQuery)departmentQuery).Execute(MergeOption.AppendOnly);
   }
   catch (Exception ex)
   {
       MessageBox.Show(ex.Message);
   }
   

To 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.

   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.Courses
   
       ' Hide the columns that are bound to the navigation properties on Course.
       courseGridView.Columns("Department").Visible = False
       courseGridView.Columns("StudentGrades").Visible = False
       courseGridView.Columns("OnlineCourse").Visible = False
       courseGridView.Columns("OnsiteCourse").Visible = False
       courseGridView.Columns("People").Visible = False
       courseGridView.Columns("DepartmentId").Visible = False
   
       courseGridView.AllowUserToDeleteRows = False
   courseGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
   Catch ex As Exception
       MessageBox.Show(ex.Message)
   End Try
   

       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.Courses;
   
           // Hide the columns that are bound to the navigation properties on Course.
           courseGridView.Columns["Department"].Visible = false;
           courseGridView.Columns["StudentGrades"].Visible = false;
           courseGridView.Columns["OnlineCourse"].Visible = false;
           courseGridView.Columns["OnsiteCourse"].Visible = false;
           courseGridView.Columns["People"].Visible = false;
           courseGridView.Columns["DepartmentId"].Visible = false;
   
           courseGridView.AllowUserToDeleteRows = false;
   courseGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
       }
       catch (Exception ex)
       {
           MessageBox.Show(ex.Message);
       }
   

Next Steps

You have successfully created queries that return Department and Course objects and bound those objects to controls. Next, you will save changes made to Course objects in the data grid back to the database: Inserting and Updating Data.

See Also

Concepts

Querying a Conceptual Model
Working with Entity Data

Other Resources

Samples