Linq To SQL Order By String Instead of a Static Field

If you need to dynamically sort on the SQL server using LINQ where the value is coming as a string data type from your user UI, this is what you need:

#1 First create a class file to put the function that we are going to create as an extension method. Extension methods allow you to extend the functionality of a class. This is good when you don't have the original source code. In this case, we are going to extend the IQueryable interface.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Linq.Expressions; public static class Extensions { public static IQueryable OrderByField(this IQueryable q, string Field, bool Asc) { var param = Expression.Parameter(typeof(T), "p"); var prop = Expression.Property(param, Field); var exp = Expression.Lambda(prop, param); string method = Asc ? "OrderBy" : "OrderByDescending"; Type[] types = new Type[] { q.ElementType, exp.Body.Type }; var mce = Expression.Call(typeof(Queryable), method, types, q.Expression, exp); return q.Provider.CreateQuery(mce); } }

VB.NET

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Imports Microsoft.VisualBasic Imports System.Runtime.CompilerServices Imports System.Linq.Expressions Public Module Extensions <Extension()> _ Public Function OrderByField(Of T)(ByRef q As IQueryable(Of T), ByVal Field As String, ByVal Asc As Boolean) As IOrderedQueryable(Of T) Dim param = Expression.Parameter(GetType(T), "p") Dim prop = Expression.Property(param, Field) Dim exp = Expression.Lambda(prop, param) Dim arr() As String = Field.Split(" ") Dim method As String = IIf(Asc, "OrderBy", "OrderByDescending") Dim types() As Type = New Type() {q.ElementType, exp.Body.Type} Dim mce = Expression.Call(GetType(Queryable), method, types, q.Expression, exp) Return q.Provider.CreateQuery(Of T)(mce) End Function End Module

Notice that in the VB.NET code it is a "Module" instead of a "Class". This is because in VB.NET only Modules may contain extension methods.

#2 This is how you use it anywhere in your project:

1 2 3 4 5 6

private void BindGrid() { var db = new dbDataContext(); MyRepeater.DataSource = db.t_users.OrderByField("ColumnName", true).ToArray(); MyRepeater.DataBind(); }

VB.NET

1 2 3 4 5

Private Sub BindGrid() Dim db As New dbDataContext MyRepeater.DataSource = db.t_users.OrderByField("ColumnName", True).ToArray() MyRepeater.DataBind() End Sub

"t_users" is my table in the database. And as you can see, I extended the functionality of it by adding another function, "OrderByField", which takes two arguments. The first one is the column name and the second one is whether you want to sort ascending or not.

Now, a real simple scenario would be like this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (IsPostBack == false) { BindGrid(); } } //The column name of the table to sort by. public string SortField { get { var s = ViewState["SortField"]; if (s != null) { return (string)(s); } else { return "f_id"; //This is my default sort field. } } set { ViewState["SortField"] = value; } } //The sort direction of the column, true = asc, false = desc public bool SortAsc { get { var s = ViewState["SortAsc"]; if (s != null) { return (bool)(s); } else { return false; //This is my default sort direction. } } set { ViewState["SortAsc"] = value; } } private void BindGrid() { var db = new dbDataContext(); MyRepeater.DataSource = db.t_users.OrderByField(this.SortField, this.SortAsc).ToArray(); MyRepeater.DataBind(); } protected void MyRepeater_ItemCommand(object source, System.Web.UI.WebControls.RepeaterCommandEventArgs e) { if (e.CommandName == "Sort") { this.SortField = e.CommandArgument.ToString(); this.SortAsc = this.SortAsc ^ true; //Changes the value from true to false and from false to true. BindGrid(); } } }

On my html page, I have the following:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

<asp:Repeater ID="MyRepeater" runat="server" OnItemCommand="MyRepeater_ItemCommand"> <HeaderTemplate> <table border="1"> <tr> <td> <asp:LinkButton ID="b0" runat="server" ToolTip="Click here to sort." CommandArgument="f_id" CommandName="Sort" Text="ID" /> </td> <td> <asp:LinkButton ID="b1" runat="server" ToolTip="Click here to sort." CommandArgument="f_name" CommandName="Sort" Text="Name" /> </td> <td> <asp:LinkButton ID="b2" runat="server" ToolTip="Click here to sort." CommandArgument="f_created" CommandName="Sort" Text="Created" /> </td> </tr> </HeaderTemplate> <ItemTemplate> <tr> <td> <%# Eval("f_id")%> </td> <td> <%# Eval("f_name")%> </td> <td> <%# Eval("f_created")%> </td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater>