Working with Related Data

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

This topic demonstrates how to work with related data.

The Getting Started sample uses a single domain class: Category. What if you want to display the related Cars objects? You can retrieve values from a table and values from any related tables with one OpenAccessLinqDataSource control. When a table (e.g. Cars) has a foreign key relationship with another table (e.g. Categories), the corresponding domain class contains a navigation property for the related class. This property returns an object that represents the related table. The related object in turn contains properties for the columns in the related table. You can select values from related tables with the OpenAccessLinqDataSource control.

This section demonstrates how to retrieve data from related tables. The key moment for this scenario is that you need to create a Custom Grouping.

Consider the Cars and Categories tables from the SofiaCarRental database. There is an one-to-many relation between those tables, i.e. Cars.CategoryId. It is supposed that you have created a new domain model based on the SofiaCarRental database.

To retrieve data from related tables:

Start the OpenAccessLinqDataSource Configuration Wizard.
In the Select Context Type Dialog, select the Cars entity. Click Next to continue.
In the Configure Data Selection Dialog, switch to the GroupBy tab. From the Key Member drop-down, select the < Custom > entry.
Write down the select statement manually. For example: new(CarID, Make, Model, Category.CategoryID, Category.CategoryName). The result will be a new projection including the CarID, Make and Model columns from the Cars table, as well as the CategoryID and CategoryName columns from the Categories table.
Click Finish to close the wizard.

To display the data:

Drag a new data-bound control and set its DataSourceID property to the OpenAccessLinqDataSource.
In this example, a RadGrid control is used. Ensure that the AutoGenerateColumns property is set to True.
Test your page in a Web browser.

Consider the following example. By following the steps described in the Getting Started with OpenAccessLinqDataSource topic, you are creating a new data-bound (RadGrid) control that performs CRUD operations with the Car entity (from the SofiaCarRental database). When you create new Car objects, you need to link the Car entity with an existing Category. If you use a single OpenAccessLinqDataSource control, you should specify the CategoryId property manually:

You could use a second OpenAccessLinqDataSource control to enable editing for navigation properties. So, instead of using a simple text control, you could use a drop-down, allowing you to select an existing category:

To use a new OpenAccessLinqDataSource control to enable editing of navigation properties:

By following the steps described in the Getting Started with OpenAccessLinqDataSource topic, create a new ASP.NET Web Application and a new Telerik Data Access Domain Model based on the SofiaCarRental database.
Open Default.aspx page in Design view.
Drag two OpenAccessLinqDataSource controls from the toolbox to the page designer.
Connect the first OpenAccessLinqDataSource to the Cars table.
1. In the Configure Data Selection Dialog, select the default SelectAll (*) option.
2. In the Configure Data Selection Dialog, switch to the Advanced tab page and check the Enable Automatic Inserts, Enable Automatic Updates and Enable Automatic Deletes options.
3. Click Finish to the close the configuration wizard.
4. Set the ID property to OpenAccessLinqDataSourceCars.
  
  The following example shows the markup for part of an ASP.NET Web page that contains the OpenAccessLinqDataSourceCars.
```
   <telerik:OpenAccessLinqDataSource
      ID="OpenAccessLinqDataSourceCars"
      runat="server"
      ContextTypeName="OpenAccessLinqDataSourceDemo.EntitiesModel"
      ResourceSetName="Cars"
      EntityTypeName=""
      EnableInsert="True"
      EnableUpdate="True"
      EnableDelete="True" />
```
Connect the second OpenAccessLinqDataSource to the Categories table.
1. In the Configure Data Selection Dialog, select the default SelectAll (*) option.
2. Set the ID property to OpenAccessLinqDataSourceCategories.
  
  The following example shows the markup for part of an ASP.NET Web page that contains the OpenAccessLinqDataSourceCategories.
```
   <telerik:OpenAccessLinqDataSource
      ID="OpenAccessLinqDataSourceCategories"
      runat="server"
      ContextTypeName="OpenAccessLinqDataSourceDemo.EntitiesModel"
      ResourceSetName="Categories"
      EntityTypeName="" />
```
Drag a new RadGrid control. By using the SmartTag panel, set the data source to OpenAccessLinqDataSourceCars. By using the Properties pane, set the following properties:
1. Set AllowAutomaticUpdates to True.
2. Set AllowAutomaticInserts to True.
3. Set AllowAutomaticDeletes to True.
4. Set AutoGenerateDeleteColumn to True.
5. Set AutoGenerateEditColumn to True.
6. Expand the MasterTableView section and set the CommandItemDisplay property to TopAndBottom.
By using the SmartTag panel of the RadGrid control, click the Refresh Schema link.

The following example shows the markup for the RadGrid control.

<telerik:RadGrid ID="RadGrid1" runat="server" CellSpacing="0" 
         DataSourceID="OpenAccessLinqDataSourceCars"
   GridLines="None">
   <MasterTableView AutoGenerateColumns="False" DataKeyNames="CarID" 
                    DataSourceID="OpenAccessLinqDataSourceCars">
       <CommandItemSettings ExportToPdfText="Export to PDF"></CommandItemSettings>
       <RowIndicatorColumn FilterControlAltText="Filter RowIndicator column">
           <HeaderStyle Width="20px"></HeaderStyle>
       </RowIndicatorColumn>
       <ExpandCollapseColumn FilterControlAltText="Filter ExpandColumn column">
           <HeaderStyle Width="20px"></HeaderStyle>
       </ExpandCollapseColumn>
       <Columns>
           <telerik:GridCheckBoxColumn DataField="ABS" DataType="System.Boolean" 
               FilterControlAltText="Filter ABS column" HeaderText="ABS" 
               SortExpression="ABS" UniqueName="ABS">
           </telerik:GridCheckBoxColumn>
           <telerik:GridCheckBoxColumn DataField="ASR" DataType="System.Boolean" 
               FilterControlAltText="Filter ASR column" HeaderText="ASR" 
               SortExpression="ASR" UniqueName="ASR">
           </telerik:GridCheckBoxColumn>
           <telerik:GridCheckBoxColumn DataField="AirConditioner" DataType="System.Boolean"
               FilterControlAltText="Filter AirConditioner column" HeaderText="AirConditioner"
               SortExpression="AirConditioner" UniqueName="AirConditioner">
           </telerik:GridCheckBoxColumn>
           <telerik:GridCheckBoxColumn DataField="Available" DataType="System.Boolean" 
               FilterControlAltText="Filter Available column" HeaderText="Available" 
               SortExpression="Available" UniqueName="Available">
           </telerik:GridCheckBoxColumn>
           <telerik:GridBoundColumn DataField="CarID" DataType="System.Int32" 
               FilterControlAltText="Filter CarID column" HeaderText="CarID" 
               ReadOnly="True" SortExpression="CarID" UniqueName="CarID">
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="CarYear" DataType="System.Int16" 
               FilterControlAltText="Filter CarYear column" HeaderText="CarYear" 
               SortExpression="CarYear" UniqueName="CarYear">
           </telerik:GridBoundColumn>

           <telerik:GridBoundColumn DataField="CategoryID" DataType="System.Int32" 
               FilterControlAltText="Filter CategoryID column" HeaderText="CategoryID" 
               SortExpression="CategoryID" UniqueName="CategoryID">
           </telerik:GridBoundColumn>

           <telerik:GridCheckBoxColumn DataField="DVDPlayer" DataType="System.Boolean" 
               FilterControlAltText="Filter DVDPlayer column" HeaderText="DVDPlayer" 
               SortExpression="DVDPlayer" UniqueName="DVDPlayer">
           </telerik:GridCheckBoxColumn>
           <telerik:GridBoundColumn DataField="ImageFileName" 
               FilterControlAltText="Filter ImageFileName column"
               HeaderText="ImageFileName" SortExpression="ImageFileName" UniqueName="ImageFileName">
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Latitude" DataType="System.Double" 
               FilterControlAltText="Filter Latitude column" HeaderText="Latitude" 
               SortExpression="Latitude" UniqueName="Latitude">
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Longitude" DataType="System.Double" 
               FilterControlAltText="Filter Longitude column" HeaderText="Longitude" 
               SortExpression="Longitude" UniqueName="Longitude">
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Make" FilterControlAltText="Filter Make column"
               HeaderText="Make" SortExpression="Make" UniqueName="Make">
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Model" FilterControlAltText="Filter Model column"
               HeaderText="Model" SortExpression="Model" UniqueName="Model">
           </telerik:GridBoundColumn>
           <telerik:GridCheckBoxColumn DataField="Mp3Player" DataType="System.Boolean" 
               FilterControlAltText="Filter Mp3Player column" HeaderText="Mp3Player" 
               SortExpression="Mp3Player" UniqueName="Mp3Player">
           </telerik:GridCheckBoxColumn>
           <telerik:GridCheckBoxColumn DataField="Navigation" DataType="System.Boolean" 
               FilterControlAltText="Filter Navigation column" HeaderText="Navigation" 
               SortExpression="Navigation" UniqueName="Navigation">
           </telerik:GridCheckBoxColumn>
           <telerik:GridBoundColumn DataField="NumberOfRatings" DataType="System.Int32" 
               FilterControlAltText="Filter NumberOfRatings column" HeaderText="NumberOfRatings" 
               SortExpression="NumberOfRatings" UniqueName="NumberOfRatings">
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="Rating" DataType="System.Decimal" 
               FilterControlAltText="Filter Rating column" HeaderText="Rating" 
               SortExpression="Rating" UniqueName="Rating">
           </telerik:GridBoundColumn>
           <telerik:GridBoundColumn DataField="TagNumber" FilterControlAltText="Filter TagNumber column"
               HeaderText="TagNumber" SortExpression="TagNumber" UniqueName="TagNumber">
           </telerik:GridBoundColumn>
       </Columns>
       <EditFormSettings>
           <EditColumn FilterControlAltText="Filter EditCommandColumn column">
           </EditColumn>
       </EditFormSettings>
   </MasterTableView>
   <FilterMenu EnableImageSprites="False">
   </FilterMenu>
   <HeaderContextMenu CssClass="GridContextMenu GridContextMenu_Default">
   </HeaderContextMenu>
</telerik:RadGrid>

In the MasterTableView, locate the CategoryID column.

<telerik:GridBoundColumn DataField="CategoryID" DataType="System.Int32" 
     FilterControlAltText="Filter CategoryID column" HeaderText="CategoryID" 
     SortExpression="CategoryID" UniqueName="CategoryID">
</telerik:GridBoundColumn>

Replace with the following:

<telerik:GridDropDownColumn UniqueName="DropDownCategoryListColumn" 
       ListTextField="CategoryName" ListValueField="CategoryID" 
       DataSourceID="OpenAccessCategoryDataSource" HeaderText="Category"
       DataField="CategoryID" DropDownControlType="RadComboBox" AllowSorting="true"
       ItemStyle-Width="120px" />

Start your application and try to edit an existing car.

Working with Related Data

Selecting and Displaying Data from Related Tables

How to Use a New OpenAccessLinqDataSource Control to Enable Editing of Navigation Properties