Columns of two related database tables in one ASP.NET GridView with EntityDataSource

Question

I have two SQL Server tables with Primary Keys (PK) and a Foreign Key (FK) linking the two tables:

1) Table "Order"

OrderID, int, PK  
AddressID, int, FK
...

2) Table "Address"

AddressID, int, PK
City, nvarchar(50)
...

Then I've created an (ADO.NET) Entity Data Model out of those two tables. Now on my (ASP.NET) webpage I put a GridView with an EntityDataSource. In my GridView I want to display two columns:

• OrderID
• City (belonging to that order and linked by the AddressID-key)

How can I do that? My problem is: When I configure the Entity Data Source I can select an "EntitySetName" which can be either "Order" or "Address" but not both, nor can I select any kind of relationship. If I select "Order" as EntitySetName then in the GridView I can add the columns

• OrderID
• Address
• Address.AddressID

Adding the column "Address" displays empty cells. Adding "OrderID" and "Address.AddressID" displays the expected IDs. But how can I add the "City" of the related address to my GridView?

Thank you for help in advance!

Edit: Clarification:

The Entity Framework has created a class "Order" and a class "Address" corresponding to the database tables. The class "Order" has a reference to an "Address" object as a navigation property, corresponding to the 1-n relationship between Address and Order table.

Basically I want to have a column in my GridView which displays Order.Address.City. I have tried to add a bound field with "Address.City" as data field to the GridView but it results in a runtime error ("no such property...").

Answer 1

OK, much too many hours later I found the solution myself:

Option 1:

It is possible to use the select property in the EntityDataSource which allows to create arbitrary projections of data from several related entities/database tables (in my case: OrderID from Order entity and City from the Address entity)

Drawback: Using select in the EntityDataSource makes using Insert, Update and Delete in the GridView impossible!

Option 2:

The EntityDataSource must have the include property to include the related address property along with the queried orders. The markup looks likes this:

<asp:EntityDataSource ID="EntityDataSourceOrders" runat="server" 
    ConnectionString="name=MyEntitiesContext" 
    DefaultContainerName="MyEntitiesContext" 
    EntitySetName="Order" Include="Address"
    EnableDelete="True" EnableInsert="True" 
    EnableUpdate="True">
</asp:EntityDataSource>

Then the columns collection of the GridView can have a template field like this:

<asp:TemplateField HeaderText="City" >
  <ItemTemplate>
    <asp:Label ID="LabelCity" runat="server" Text='<%# Eval("Address.City") %>'>
    </asp:Label>
  </ItemTemplate>
</asp:TemplateField>

Here Eval is important. Bind does not work. Also using a BoundField as a column...

<asp:BoundField DataField="Address.City" HeaderText="City" />

... is NOT possible. So it is not possible in the GridView to edit the City (which makes sense because its a related field belonging to another table and perhaps to many other orders). But it is possible to edit flat fields of the order entity and also the "AddressID" of the order to assign another address.

Answer 2

Hi, your answer helped me a lot but I have one more problem. Lets say you have list of cities and you want to give an option to the user to select the city from dropdownlist in edit template of griview.

                <asp:DropDownList ID="EditDropDownList1" runat="server"
                    DataValueField  ="AddressID" DataTextField="City"
                    SelectedValue='<%# Bind("AddressID") %>'>   <- ??
                </asp:DropDownList>

How to bind it to AddressID of Order? AddressID is a Navigation Property how to bind it and change it?

Answer 3

You have explained this thing very clearly. Thank you very much. I was so stuck, but now it works like charm! Excellent!