C# How to use .OrderBy with multiple columns and decode column number to column name?

Question

So I'm using Datatable.net, Asp.net MVC Controller serving up Json data.
I'm trying to do something simliar to these posts:

• LINQ Sort by Multiple Fields
• Multi Column Sort for Datatables.net

I have the following parameters from the Javascript Ajax call to the sever:

> int iSortingCols    Number of columns to sort on
> int iSortCol_(int)  Column being sorted on 
>          (you will need to decode this number for your database)
> string  sSortDir_(int)  Direction to be sorted - "desc" or "asc".

Here's my Dataset:

var db = new GER_MAPV_Context();
var allTags = db.TrimTables;
filteredTags = db.TrimTables.AsEnumerable();
IEnumerable<TrimTable> filteredTags;

//Dependent function for Sorting

Func<TrimTable, string> getColumnName = (
    c => getCurrentSortColumn(1) == 1 ? c.TAG :
    sortColumnIndex == 2 ? c.DESCRIPTION :
    sortColumnIndex == 3 ? c.SET_POINT :
    sortColumnIndex == 4 ? c.PRIORITY :
    sortColumnIndex == 5 ? c.LIMIT_TYPE :
    sortColumnIndex == 6 ? c.ALARM_TYPE :
    sortColumnIndex == 7 ? c.AUTOMATED_SYSTEM :
    sortColumnIndex == 8 ? c.COL_POL :
    sortColumnIndex == 9 ? c.PROPERTY :
    sortColumnIndex == 10 ? c.EQUIP_TYPE:
    sortColumnIndex == 11 ? c.P_ID :
    sortColumnIndex == 12 ? c.AREA :
    sortColumnIndex == 13 ? c.COMPLEX :
    sortColumnIndex == 14 ? c.UNIT :  //PI Unit Format Long-text
    sortColumnIndex == 15 ? c.UNIT_ : //Loop Number Format
    sortColumnIndex == 16 ? c.LOOP_TYPE : //Loop Type i.e. PI, FIT, PSV
    sortColumnIndex == 17 ? c.LOOP_ : //Loop Number
    sortColumnIndex == 18 ? c.LOOP_EXT  :
    c.UNIT
    );

Then I need to Sort each of these columns accordingly up to 4 sorts.

filteredTags.OrderBy(getColumnName).ThenByDescending(getColumnName);
//could have 1 - 4 orderby.thenby arrangements

the trick is I need to input "sortColumnIndex"
(which is a number, and JSON parameter of iSortCol_1 or iSortCol_2 respectively)
into the getColumnName Function so it return the proper column name.

I'm racking my brain here.

Q: So what are the basic steps to do this?
And what is the basic jist of the LINQ expression I'm trying to build here?

Answer 1

I believe this will work for you. You just need to add a lambda function.

var result = filteredTags
.OrderBy(t => getColumnName(t, iSortCol_1))
.ThenBy(t => getColumnName(t, iSortCol_2));

But your getColumnName needs to have two input parameters (the table and the columnNum).

Update:

To include the sort column count and the descending:

var r = firstDesc ? 
filteredTags
.OrderByDescending(t => getColumnName(iSortCol_1))
: filteredTags
.OrderBy(t => getColumnName(iSortCol_1))



for ( var i =1;  i < colCount; i++)
{
    r = nDesc ? 
    r.ThenByDescending(t => getColumnName(iSortCol_1))
    : r.OrderBy(t => getColumnName(iSortCol_1))
}

Answer 2

Okay so here's my final code if anyone is curious: I also found these two sites helpful:

• Multiple Field Sorting

• Farm Fresh Code MVC, DataTables

      //Dependent function for Sorting
      //This Functions Retrieves the Database Field Name to be used by the Orderby("Tag") function 
      // i.e. OrderBy(t => t.Area);
      // t = TrimTable
      // iSortCol = Column Number to Sort By
      Func<TrimTable, Int32, string> getColName =(
      (t, iSortCol) => iSortCol == 1 ? t.TAG :
                      iSortCol == 2 ? t.DESCRIPTION :
                      iSortCol == 3 ? t.SET_POINT :
                      iSortCol == 4 ? t.PRIORITY :
                      iSortCol == 5 ? t.LIMIT_TYPE :
                      iSortCol == 6 ? t.ALARM_TYPE :
                      iSortCol == 7 ? t.AUTOMATED_SYSTEM :
                      iSortCol == 8 ? t.COL_POL :
                      iSortCol == 9 ? t.PROPERTY :
                      iSortCol == 10 ? t.EQUIP_TYPE:
                      iSortCol == 11 ? t.P_ID :
                      iSortCol == 12 ? t.AREA :
                      iSortCol == 13 ? t.COMPLEX :
                      iSortCol == 14 ? t.UNIT :  //PI Unit Format Long-text
                      iSortCol == 15 ? t.UNIT_ : //Loop Number Format
                      iSortCol == 16 ? t.LOOP_TYPE : //Loop Type i.e. PI, FIT, PSV
                      iSortCol == 17 ? t.LOOP_ : //Loop Number
                      iSortCol == 18 ? t.LOOP_EXT  :
                      t.UNIT_
          );
  
  
     //Help Info: 
      //http://stackoverflow.com/questions/958220/how-can-i-use-linq-to-sort-by-multiple-fields
      //http://stackoverflow.com/questions/21582725/c-sharp-how-to-use-orderby-with-multiple-columns-and-decode-column-number-to-co#21583371
      //http://activeengine.net/2011/02/09/datatablepager-now-has-multi-column-sort-capability-for-datatables-net/
      //http://msdn.microsoft.com/en-us/library/bb534852(v=vs.110).aspx
  
  
      //Number of Columns to Sort
      int iSortCols = Convert.ToInt32(param.iSortingCols);
      Debug.WriteLine("Count of Sortable Columns" + iSortCols);
      //int   iSortCol_(int)  Column being sorted on (you will need to decode this number for your database)
      //string    sSortDir_(int)  Direction to be sorted - "desc" or "asc".
  
      //If Sort Expression Exists
      if (iSortCols > 0)
      {
          //Sorting
          string[] sSortDirection = new string[iSortCols]; // asc or desc
          Int32[] iSortColNum = new Int32[iSortCols]; //number
  
          //Get Sorting Parameters from MVC Controller
          for (int h = 0; h < iSortCols; h++)
          {
              //Get Sort Direction
              var s1 = "sSortDir_" + h;
              sSortDirection[h] = Convert.ToString(Request[s1]);
  
              //Get Sort Column Number
              var s2 = "iSortCol_" + h;
              iSortColNum[h] = Convert.ToInt32(Request[s2]);
  
          }
  
          //Build Orderby Statement
          for (int i = 0; i < iSortCols; i++)
          {
              // We need to keep the loop index, not sure why it is altered by the Linq.
              var index = i;
  
              //If Current Column is Ascending/Descending
              if (sSortDirection[index] == "asc")
              {
                  //Orderby / Thenby
                  orderedTags = (index == 0) ? filteredTags.OrderBy(t => getColName(t, iSortColNum[index]))
                                          : orderedTags.ThenBy(t => getColName(t, iSortColNum[index]));
  
              }
              else
              {
                  orderedTags = (index == 0) ? filteredTags.OrderByDescending(t => getColName(t, iSortColNum[index]))
                                         : orderedTags.ThenByDescending(t => getColName(t, iSortColNum[index]));
              }
  
          }
  
          //Return Orderby LINQ to Original Result Variable
          filteredTags = orderedTags;
      }
      else
      {
          //Default Sort if None is Selected
          filteredTags = filteredTags.OrderBy(t => t.TAG);
      }