0

I’m trying to sort a GridView using LINQ2SQL with multiple tables. Since the columns in the grid contain data from several tables how do I tell LINQ which column name to sort by.

For example the user clicks on the first column (POCNum) which is in table p so I would use p.POCNum to orderby. Problem is e.SortExpression only contains the word POCNum. No way to link this back to table p. So if somebody clicks on column Contact_ID which is in table c I need to orderby c.Contact_ID in the LINQ query.

Any suggestions would be appreciated.

protected void GridView1_Sort(Object sender, GridViewSortEventArgs e)
{
     DataClasses1DataContext db = new DataClasses1DataContext();
     var results = (from p in db.POCs<BR/>
                   join c in db.Contacts on p.POC_ID equals c.POC_ID
                   join a in db.Auxilaries on p.POC_ID equals a.POC_ID
                   join l in db.Legends on p.Basis equals l.Legend_code_value
                   orderby e.SortExpression // problem - 
                   //e.SortExpression does not contain table reference         
                   where p.DebtorObj_ID == Convert.ToInt32(ddlDebtor.SelectedItem.Value)
                   select new { p.POC_ID, p.POC_Date, p.POCNum, p.Total_Amt, p.Secure_Amt,
                                a.AMailing_Name1, l.Legend_Description }).ToList();
            GridView1.DataSource = results;
            GridView1.DataBind();
}
kaj
  • 5,133
  • 2
  • 21
  • 18
user1257206
  • 1
  • 1
    Why not bind the GridView to the data once and let the sorting be handled by the GridView eg by use of the Sort method with an expression? – kaj Mar 08 '12 at 15:21
  • I'm using ASP.NET. If you could provide an example of the sort method with an expression that would be great. I can't get it to work because of the different columns from multiple tables. – user1257206 Mar 08 '12 at 16:02

1 Answers1

1

What I'd be looking at is either expressions or dynamic linq.

If you see the Generic Sorter at http://www.singingeels.com/Articles/Self_Sorting_GridView_with_LINQ_Expression_Trees.aspx or approach in http://devtoolshed.com/content/gridview-objectdatasource-linq-paging-and-sorting that should help.

For dynamic linq see Is there a pattern using Linq to dynamically create a filter? which is about filters but the same applies for ordering. Note that your anonymous type doesn't have duplicate column names so the multiple table source shouldn't matter

Following the example within the generic sorter, within the Sorting handler for a GridView (explicit implementation, not your LINQ query but that shouldn't matter): 

IEnumerable<CustomerOrder> customerOrders = (LINQ statement)  
var param = Expression.Parameter = typeof(CustomerOrder));  
var sortExpression = Expression.Lambda<Func<CustomerOrder, object>>(Expression.Convert(Expression.Property(param,e.SortExpression), typeof(object), param);  

MyGridView.DataSource = customerOrders.AsQueryable().OrderBy(sortExpression);  
MyGridView.DataBind();
Community
  • 1
  • 1
kaj
  • 5,133
  • 2
  • 21
  • 18