Databind gridview with LINQ

I have two database tables, one for Users of a web site, containing the fields "UserID", "Name" and the foreign key "PageID". And the other with the fields "PageID" (here the primary key), and "Url".

I want to be able to show the data in a gridview with data from both tables, and I'd like to do it with databinding in the aspx page.

I'm not sure how to do this, though, and I can't find any good examples of this particular situation. Here's what I have so far:

  <%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="LinqBinding._Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <h2>
        Testing LINQ
    </h2>
    <asp:GridView ID="GridView1" runat="server" DataSourceID="LinqDataSourceUsers" AutoGenerateColumns="false">
        <Columns>
            <asp:CommandField ShowSelectButton="True" />
            <asp:BoundField DataField="UserID" HeaderText="UserID" />
            <asp:BoundField DataField="Name" HeaderText="Name" />
            <asp:BoundField DataField="PageID" HeaderText="PageID" />
            <asp:TemplateField HeaderText="Pages">
            <ItemTemplate
                <asp:DropDownList ID="DropDownList1"
                 DataSourceID="LinqDataSourcePages"
                 SelectedValue='<%#Bind("PageID") %>'
                 DataTextField="Url"
                 DataValueField="PageID"
                 runat="server">
                </asp:DropDownList>
            </ItemTemplate>
            </asp:TemplateField>           
        </Columns>
    </asp:GridView>
    <asp:LinqDataSource ID="LinqDataSourcePages" runat="server" 
    ContextTypeName="LinqBinding.UserDataContext" EntityTypeName="" 
        TableName="Pages">
    </asp:LinqDataSource>
    <asp:LinqDataSource ID="LinqDataSourceUsers" runat="server" 
        ContextTypeName="LinqBinding.UserDataContext" EntityTypeName="" 
        TableName="Users">
    </asp:LinqDataSource>
</asp:Content>

But this only works in so far as it gets the user table into the gridview (that's not a problem), and I get the page data into the dropdown, but here's the problem: I of cou开发者_Go百科rse get ALL the page data in there, not just the pages for each user on each row. So how do I put some sort of "where" constraint on dropdown for each row to only show the pages for the user in that row? (Also, to be honest I'm not sure I'm getting the foreign key relationship right, because I'm not too used to working with relationships).

EDIT:

I think I have set up the relationship incorrectly. I keep getting the message that "Pages" doesn't exist as a property on the User object. And I guess it can't since the relationship right now is one way. So I tried to create a many-to-many relationship. Again, my database knowledge is a bit limited, but I added a so called "junction table" with the fields UserID and PageID, same as the other tables' primary keys. I wasn't able to make both of these primary keys in the junction table though (which it looked like some people had in examples I've seen...but since it wasn't possible I guessed they shouldn't be). Anyway, I created a relationship from each table and created new LINQ classes from that.

But then what do I do? I set the junction table as the Linq data source, since I guessed I had to do this to access both tables, but that doesn't work. Then it complains there is no Name property on that object. So how do I access the related tables?

(BTW: Here's one page I looked at for finding a solution: http://www.iaingalloway.com/2015/06/many-to-many-relationships-in-linq-to-sql.html , but first of all I don't understand how he modifies the "Order" class codebehind. I can only get into the context class (I can't right-click the class in design view as he suggests and view code...), and in there there doesn't seem to be any way to refer to the junction class - Order_Details in his case)... Am I missing something?)

Here's what I have now with the many-to-many relationship:

 <%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="ManyToMany._Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <h2>
        Many to many LINQ
    </h2>
    <asp:GridView ID="GridView1" runat="server" DataSourceID="LinqDataSource1" AutoGenerateColumns="false">
    <Columns>
        <asp:CommandField ShowSelectButton="True" />
        <asp:BoundField DataField="UserID" HeaderText="UserID" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="PageID" HeaderText="PageID" />
        <asp:TemplateField HeaderText="Pages">
        <ItemTemplate>
            <asp:DropDownList ID="DropDownList1"
             DataSource='<%#Eval("Pages") %>'
             SelectedValue='<%#Bind("PageID") %>'
             DataTextField="Url"
             DataValueField="PageID"
             runat="server">
            </asp:DropDownList>
        </ItemTemplate>
        </asp:TemplateField>           
    </Columns>
</asp:GridView>
    <asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="ManyToMany.UserPageDataContext"
        EntityTypeName="" TableName="UserPages">
    </asp:LinqDataSource>
</asp:Content>

Well, it seems I found the answer myself. This page explains the problems with many-to-many relationships and LINQ: Link. Using that information I could modify the User class so that it has a property to return an IEnumerable collection. I added this to the class User (in the codebehind for the Linq context):`

    public IEnumerable<Page> Pages
    {
        get { return UserPages.Select(u => u.Page); }
    }

That way I could have the many-to-many relationship, and I could simply refer to the new Pages property in the aspx directly:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ManyToMany._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView2" runat="server" DataSourceID="LinqDataSourceUsers" AutoGenerateColumns="false">
            <Columns>
                <asp:CommandField ShowSelectButton="True" />
                <asp:BoundField DataField="UserID" HeaderText="UserID" />
                <asp:BoundField DataField="Name" HeaderText="Name" />
                <asp:TemplateField HeaderText="Pages">
                    <ItemTemplate>
                        <asp:DropDownList ID="DropDownList1" DataSource='<%#Eval("Pages") %>' DataTextField="Url"
                            runat="server">
                        </asp:DropDownList>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:LinqDataSource ID="LinqDataSourceUsers" runat="server" ContextTypeName="ManyToMany.UserPageDBDataContext"
            TableName="Users">
        </asp:LinqDataSource>
    </div>
    </form>
</body>
</html>

I hope this helps someone else who has been struggling with this like I did!

Try binding the DropDownList DataSource to Pages property of your user object

<asp:GridView ID="GridView1" runat="server" DataSourceID="LinqDataSourceUsers" AutoGenerateColumns="false">
    <Columns>
        <asp:CommandField ShowSelectButton="True" />
        <asp:BoundField DataField="UserID" HeaderText="UserID" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="PageID" HeaderText="PageID" />
        <asp:TemplateField HeaderText="Pages">
        <ItemTemplate
            <asp:DropDownList ID="DropDownList1"
             DataSource='<%#Eval("Pages") %>'
             SelectedValue='<%#Bind("PageID") %>'
             DataTextField="Url"
             DataValueField="PageID"
             runat="server">
            </asp:DropDownList>
        </ItemTemplate>
        </asp:TemplateField>           
    </Columns>
</asp:GridView>

I've run into similar situations before with a ListView and LinqDataSources. The only solution i've found is to set the datasource for the DropDownlist in the OnRowCreated or OnRowDatabound events.

You can still use the LinqDataSource for your BoundFields but I suspect you might need to do the TemplateField in the code behind in this case.

Code:

<asp:GridView ID="GridView1" runat="server" OnRowCreated="GridView1_OnRowCreated" DataSourceID="LinqDataSource1" AutoGenerateColumns="false">
<Columns>
    <asp:CommandField ShowSelectButton="True" />
    <asp:BoundField DataField="UserID" HeaderText="UserID" />
    <asp:BoundField DataField="Name" HeaderText="Name" />
    <asp:BoundField DataField="PageID" HeaderText="PageID" />
    <asp:TemplateField HeaderText="Pages">
    <ItemTemplate>
        <asp:DropDownList ID="DropDownList1"
         DataSource='<%#Eval("Pages") %>'
         SelectedValue='<%#Bind("PageID") %>'
         DataTextField="Url"
         DataValueField="PageID"
         runat="server">
        </asp:DropDownList>
    </ItemTemplate>
    </asp:TemplateField>           
</Columns>

C#:

protected void GridView1_OnRowCreated(object sender, EventArgs e)
{
   //Create int variable with the UserId value
   //Create new Linq query to be used as datasource.  must get pages and filter by userid.
   //FindControl DropDownList1
   //use the linq query mentioned in the second comment  as the datasource for DropDownList1
   //DataBind DropDownList1

   //Example: 
int userId = /*get userid logic*/;
var userPages = from t in dc.Pages
                where t.UserId = userId
                select t;
((DropDownList)/*Find drop down list*/).DataSource = userPages;
((DropDownList)/*Find drop down list*/).DataBind()
}