GridView Sort using LINQ2SQL with multiple tables

Question

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();
}

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
– kaj, Commented Mar 8, 2012 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
– user1257206, Commented Mar 8, 2012 at 16:02

Community · Accepted Answer · 2017-05-23 11:47:35Z

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();

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered Mar 8, 2012 at 17:24

kaj

5,2712 gold badges23 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1257206 Over a year ago

KAJ, thanks for your help. I'm going to try the generic sorter from devtoolsshed. It looks like it should work.

kaj Over a year ago

I've added an example having implemented it myself based on the links quoted

Collectives™ on Stack Overflow

GridView Sort using LINQ2SQL with multiple tables

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related