EntityDataSource accessing a collection from another entity in GridView

Question

I'm not sure if my title accurately describes what I'm trying to do.

I have two tables in my Entity Framework, person and user. I am trying to display a grid of users, so I set up this EntityDatasource:

<asp:EntityDataSource ID="peopleQuery" runat="server" 
    ConnectionString="name=myEntities" DefaultContainerName="myEntities"
    EnableFlattening="False" EntitySetName="people" Include="location" 
    Where="it.active = 1" OrderBy="it.lastname, it.firstname">    
</asp:EntityDataSource>

Now in my GridView, I can bind to fields like lastname, firstname, emailaddress, and fields from the location table like address and city.

Now, in my system, a user record can be attached to one or more person records. So it's a 1-to-many relationship. In the gridview, I would like to display the users associated with each person. I can do it with a RowDataBound event, but that requires an extra DB lookup for every row in the database.

Is there some efficient way to navigate from the "many" end of a "1-to-many" foreign key relationship?

Actually, it doesn't even have to be 1-to-many. It could be 1-to-1, but you're on the other end of the foreign key relationship. So, in this example, the EntityDataSource uses the "person" table, but in the user table, I have a personid field. So the foreign key is user.personid => person.personid. Since the GridView is driven from the user person table, how can I navigate to the user table to display user.username in my GridView?

score 1 · Accepted Answer · answered Jun 05 '13 at 21:00

You can nest either a gridview or repeater inside your Gridview and use the relationship to populate the data by setting the datasource to the relationship:

<asp:EntityDataSource ID="eds" runat="server" 
     ContextTypeName="people" 
     EnableFlattening="False" 
     EntitySetName="people" 
     Include="users" >      
</asp:EntityDataSource>     

<asp:GridView ID="gv" runat="server" AutoGenerateColumns="False" DataKeyNames="PeopleId" DataSourceID="eds"> 
    <Columns>             
        <asp:BoundField DataField="FirstName" HeaderText="First name"  /> 
        <asp:BoundField DataField="LastName" HeaderText="Last name" /> 
        <asp:TemplateField HeaderText="People">
            <ItemTemplate>
            <asp:Repeater ID="rptUsers" runat="server" DataSource='<%# Eval("users") %>'>
                    <ItemTemplate>
                        <%# Eval("username") %>
                    </ItemTemplate>
            </asp:Repeater>
             </ItemTemplate>
         </asp:TemplateField>
    </Columns>
</asp:GridView>

This assumes you have a relationship setup in your data model, eg:

public class person(){
    public int PersonId{get;set;}
    public string FirstName{ get;set;}
    public string LastName{ get;set;}
    pubic virtual ICollection<user> users { get; set; }
}

public class user(){
    public int UserId{ get;set;}
    public string username{ get;set; }
}

I see what you're doing there and that would work if person had a collection called users. It doesn't. The user records have field personid such that one or more users may be associated with a person. (Example: John Smith may have an administrative login and a transactonal login.) — RMS2, Jun 06 '13 at 03:22
So basically, using your code above, I would need to set the DataSource for the TemplateField to something like [pseudo-code] 'select * from user where personid = Eval("personid")' but I think that requires the database to be queried N + 1 times. — RMS2, Jun 06 '13 at 03:25
On second look, your answer is right on the money and I do have that relationship set up in my data model. I added your code to my GridView and I don't get any errors, but it also doesn't display the usernames. Not sure why - there is definitely a collection called "users" and each item in that collection has a property called "username". Any ideas? What are we missing? BTW, if I change "users" to "users1" I get an error. But if I change "username" to "username1", I do not get an error, and it still displays nothing. — RMS2, Jun 07 '13 at 17:11
Got it! I didn't have "users" in the "include" of the EntityDataSource. Accepting your answer now, thanks! — RMS2, Jun 07 '13 at 17:52

EntityDataSource accessing a collection from another entity in GridView

1 Answers1

Linked