GridView in GridView (2) - LINQ Eager Loading

In this blog, I provided a solution for nested GridViews. The problem with that, like other declarative data source bindings, is you have little control of the data source itself hence the database operations might not be optimal.
For example, as we have to use 2 separate data sources, one for each GridView, the ASP.NET engine will first query session data for the outer grid then query transaction logs for the inner grid.However, this actually can be done in one go. Following the pattern I mentioned in this blog to manually control data source object and using eager loading for LINQ, we can have far few SQL queries to the database hence improve the performance.
In .aspx, remove the data sources. As a result of not setting the DataSourceID property of the GridView, we have to add the OnPageIndexChanging event handler for paging.

<asp:GridView ID="gvSession" CssClass="datagrid" runat="server" 
  AllowPaging="True" PageSize="6" AutoGenerateColumns="False" 
  OnPageIndexChanging="gvSession_PageIndexChanging"
  OnRowDataBound="gvSession_RowDataBound" Width="680px">
  <Columns>
    <asp:BoundField DataField="StaffName" HeaderText="Staff Name" />
    <asp:BoundField DataField="SessionId" HeaderText="Session Id" />
    <asp:BoundField DataField="SessionStartTime" HeaderText="Session Start Time" />
    <asp:BoundField DataField="SessionDuration" HeaderText="Session Duration" />
    <asp:TemplateField HeaderText="Tx Logs">
      <ItemTemplate >
        <asp:GridView ID="gvTxLog" runat="server" AutoGenerateColumns="False" Width="100%">
          <Columns>
            <asp:BoundField DataField="Text" HeaderText="Tx Type" ItemStyle-Width="70%" />
            <asp:BoundField DataField="Value" HeaderText="Duration" />
          </Columns>
        </asp:GridView>
      </ItemTemplate>
    </asp:TemplateField>
  </Columns>
</asp:GridView>

In .aspx.cs, bind outer GridView in Page_load, set the inner GridView's data source in outer grid's OnRowDataBound event handler, and implement the OnPageIndexChanging event handler:

ReportDAO _reportDao;

protected void Page_Load(object sender, EventArgs e)
{
    ...
    if (reportTabs.ActiveTab == txViewTab)
    {
        _reportDao = _reportDao ?? new ReportDAO();
        gvSession.DataSource = _reportDao.GetSessions(txtStartDate.Text, txtEndDate.Text,
            int.Parse(ddlStaffs.SelectedValue));
        gvSession.DataBind();
    }
    ...
}

protected void gvSession_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        GridView subView = (GridView)e.Row.FindControl("gvTxLog");
        subView.DataSource = _reportDao._subTables[
            gvSession.PageIndex * gvSession.PageSize + e.Row.RowIndex];
        subView.DataBind();
    }
}

protected void gvSession_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gvSession.PageIndex = e.NewPageIndex;
    gvSession.DataBind();
}

The Data Access Object is rather simple. Using ListItem for inner GridView is because I only need 2 values: transaction type name and duration, corresponding to Text and Value DataField of inner GridView's BoundField in .aspx. You can change that to DataTable if more columns required.

public List<List<ListItem>> _subTables = new List<List<ListItem>>();
...
[DataObjectMethod(DataObjectMethodType.Select, false)]
public DataTable GetSessions(string start, string end, int staffId)
{
    if (staffId < 0) // None
        return null;

    ToDate(start, end, out _startDate, out _endDate);
    using (ImportTxDataContext dataContext = new ImportTxDataContext())
    {
        dataContext.EagerLoad();

        // staffId == 0 means all sessions, -1 means none
        var sessions = (from session in dataContext.Sessions
                        where session.StartTime > _startDate && session.EndTime < _endDate &&
                            (session.StaffId == staffId || staffId == 0)
                        select session).ToList();

        DataTable table = new DataTable();
        table.Columns.Add(new DataColumn("StaffName", typeof(string)));
        table.Columns.Add(new DataColumn("SessionId", typeof(int)));
        table.Columns.Add(new DataColumn("SessionStartTime", typeof(string)));
        table.Columns.Add(new DataColumn("SessionDuration", typeof(string)));
        
        foreach (Session session in sessions) // each row
        {
            DataRow row = table.NewRow();
            row["StaffName"] = session.Staff.Name;
            row["SessionId"] = session.Id;
            row["SessionStartTime"] = session.StartTime.ToString(Util.DATE_TIME_FORMAT);
            row["SessionDuration"] = Util.DurationAsString(session.EndTime - session.StartTime);
            table.Rows.Add(row);
            
            List<ListItem> subTable = new List<ListItem>();
            foreach (TxLog log in session.TxLogs)
            {
                subTable.Add(new ListItem(
                    log.TxType.Name, Util.DurationAsString(log.Duration, false)));
            }
            _subTables.Add(subTable);
        }

        return table;
    }
}

EagerLoad() is an extension method for the trick of LINQ eager loading:

public static void EagerLoad(this ImportTxDataContext dataContext)
{
    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<Session>(r => r.Staff);
    options.LoadWith<Session>(r => r.TxLogs);
    options.LoadWith<ImportTx.DAO.TxLog>(r => r.TxType);
    dataContext.LoadOptions = options;
}

The LINQ generated SQL is like this:

exec sp_executesql N'SELECT [t0].[Id], [t0].[StaffId], [t0].[LocationId], [t0].[StartTime], [t0].[EndTime], [t2].[SessionId], [t2].[TxTypeId], [t2].[Duration], [t3].[Id] AS [Id2], [t3].[Name], [t3].[Description], [t3].[Active], (
    SELECT COUNT(*)
    FROM [dbo].[TxLog] AS [t4]
    INNER JOIN [dbo].[TxType] AS [t5] ON [t5].[Id] = [t4].[TxTypeId]
    WHERE [t4].[SessionId] = [t0].[Id]
    ) AS [value], [t1].[Id] AS [Id3], [t1].[Name] AS [Name2], [t1].[NetworkName], [t1].[ModifiedBy], [t1].[LastModified], [t1].[Active] AS [Active2], [t1].[IsAdmin]
FROM [dbo].[Session] AS [t0]
INNER JOIN [dbo].[Staff] AS [t1] ON [t1].[Id] = [t0].[StaffId]
LEFT OUTER JOIN ([dbo].[TxLog] AS [t2]
    INNER JOIN [dbo].[TxType] AS [t3] ON [t3].[Id] = [t2].[TxTypeId]) ON [t2].[SessionId] = [t0].[Id]
WHERE ([t0].[StartTime] > @p0) AND ([t0].[EndTime] < @p1)
ORDER BY [t0].[Id], [t1].[Id], [t2].[TxTypeId]',N'@p0 datetime,@p1 datetime',@p0='2009-03-01 00:00:00',@p1='2010-03-09 00:00:00'

Without eager loading, it'll be 4 times more queires.
Ref:

• Binding Arrays to GridView in ASP.Net
• Walkthrough: Creating a Nested GridView Control
• Eager Loading in Linq to SQL
• LINQ To SQL: Support for Eager Loading, Really?
• HOW TO: Using sorting / paging on GridView w/o a DataSourceControl DataSource