Month: July 2010

The Microsoft SQL Server 2008 R2 sample database AdventureWorks, contains a table Product which as a many to many relationship with the table Document, via the junction table ProductDocument. This post describes the steps you will have to take to show all products and per product all document titles.

 

 

As you can see, product “506” has a relationship with multiple documents, so the column “Documents” shows 2 titles.  Because there can be more then one document title to show, I use a BulletedList, but you also could use a  CheckBoxList or DropDownList.

 

The data model

 

ASP .NET (*.aspx) page

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" 
CodeBehind="ManyToManyGridView.aspx.cs" 
Inherits="Rvl.Demo.AspNet4.EF.WebApplication.Pages.ManyToManyGridView" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
    <link href="../Styles/ManyToManyGridView.css" rel="stylesheet" type="text/css" />
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    <p class="pageTitle">Products</p>
    <asp:GridView ID="GridView1" runat="server" AllowPaging="true" DataKeyNames="ProductID" 
         AutoGenerateColumns="false" PageSize="50" DataSourceID="GridViewEntityDataSource">
        <Columns>
            <asp:BoundField DataField="ProductID" HeaderText="Id" ItemStyle-CssClass="columnPadding" 
            HeaderStyle-CssClass="columnPadding" />
            <asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-CssClass="columnPadding" 
            HeaderStyle-CssClass="columnPadding" />
            <asp:TemplateField>
                <HeaderTemplate>
                    <asp:Label ID="documentsHeaderLabel" runat="server" Text="Documents"></asp:Label>
                </HeaderTemplate>
                <HeaderStyle CssClass="columnPadding" />
                <ItemStyle CssClass="columnPadding" />
                <ItemTemplate>
                    <asp:BulletedList DataSource='<%# FillBulletedList(Container) %>' 
                    ID="documentsBulletedList" DataValueField="DocumentID" DataTextField="Title" 
                    runat="server"></asp:BulletedList>
                 </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <!--
       - Set EnableFlattening to "false", so you can use real "Product" entities in the FillBulletedList 
         function and not wrapper (System.Web.UI.WebControls.EntityDataSourceWrapper) classes, generated 
         by the entity framework.
       - Don't use the "Include" property, this property should load related entities, like 
         "ProductDocument" and "Document", but in my case in did not work, use the "OnQueryCreated" event
         to provide a complicated inner join query, that loads related entities
    -->
    <asp:EntityDataSource ID="GridViewEntityDataSource" runat="server" EnableDelete="false" 
        EnableInsert="false" EnableUpdate="false" 
        ContextTypeName="Rvl.Demo.AspNet4.EF.WebApplication.Dal.AdventureWorksEntities" 
        ConnectionString="name=AdventureWorksEntities" DefaultContainerName="AdventureWorksEntities" 
        EntitySetName="Product" EnableFlattening="false" 
        OnQueryCreated="GridViewEntityDataSource_QueryCreated">
    </asp:EntityDataSource>
</asp:Content>

 

ASP .NET (*.cs) code behind page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Rvl.Demo.AspNet4.EF.WebApplication.Dal;
using System.Data.Objects;

namespace Rvl.Demo.AspNet4.EF.WebApplication.Pages
{
    public partial class ManyToManyGridView : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {   
        }
        public List<Document> FillBulletedList(object container)
        {
            // Get the product from the current row
            GridViewRow gvr = container as GridViewRow;
            Product product = gvr.DataItem as Product;

            // Create a list of documents related to the current product
            List<Document> documents = new List<Document>();
            foreach (ProductDocument pd in product.ProductDocument)
            {
                documents.Add(pd.Document);
            }

            return documents;
        }
        protected void GridViewEntityDataSource_QueryCreated(object sender, QueryCreatedEventArgs e)
        {
            // Load related entities ("ProductDocument" and "Document")
            ObjectQuery<Product> query = e.Query.Cast<Product>() as ObjectQuery<Product>;
            AdventureWorksEntities context = query.Context as AdventureWorksEntities;
            context.Product.Include("ProductDocument").Include("Document");

            // Show only product with related documents, by using a inner join (from .... from)
            // Show only unique rows by using "Distinct"
            // Show products ordered by name, by using "orderby"
            var items = from ps in
                            ((from p in query
                              from pd in p.ProductDocument
                              select p).Distinct())
                        orderby ps.Name
                        select ps;
            e.Query = items;
        }
    }
}

The EntityDataSource use the OnQueryCreated event to provide an inner join query, that loads related entities, like “ProductDocument” and “Document”. It does not use the “Include” property, because that did not work in my case, I don’t no why, but I don’t have time to investigate. The “Include” is done on the Context of the query.

The GridView uses a TemplateField column and the ‘<%# … %>’ syntax to call a function for every row in the GridView to fill a BulletedList that shows all documents per product.