Passing a string as a sortable parameter to use in a Linq query

Question

I am trying to achieve a dynamic way of ordering my database results by a string parameter passed into my controller sortByColumn but I cannot see how I would change OrderBy to allow for this. I guess I would need some kind of evaluation function? Is there a better way?

public ViewResult List(int page = 1, string? sortByColumn = null)
{
    JamesListViewModel model = new JamesListViewModel
    {
        James = repository.James
            //.OrderBy(s => s.Name)
            .OrderBy(s => s.sortByColumn) // This line is junk :(
            .Skip((page - 1) * PageSize)
            .Take(PageSize),
        PagingInfo = new PagingInfo
        {
            CurrentPage = page,
            ItemsPerPage = PageSize,
            TotalItems = repository.James.Count()
        }
    };
    return View(model);
}

Maybe [dynamic linq](http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx) will help you here. — sloth, Jan 03 '14 at 10:37
Might help you http://stackoverflow.com/questions/41244/dynamic-linq-orderby-on-ienumerablet — Satpal, Jan 03 '14 at 10:38
What is `repository`? There is no single technique that you can use with all LINQ providers. Even Dynamic LINQ is not really supported by Microsoft as it was more of a Sample than a library. — Panagiotis Kanavos, Jan 03 '14 at 11:40

score 1 · Accepted Answer · answered Jan 03 '14 at 10:50

Short of using Dynamic LINQ which would definitely help, you could build a simple switch statement covering all columns of James on which you plan to sort, like this:

var james = repository.James;
switch (sortByColumn)
{
    case "Name": james = james.OrderBy(s => s.Name); break;
    case "Age": james = james.OrderBy(s => s.Age); break;
    case "Strength": james = james.OrderBy(s => s.Strength); break;
    // No default: if sortByColumn is null or unknown, the result is unordered
}
JamesListViewModel model = new JamesListViewModel
{
    James = james
        .Skip((page - 1) * PageSize)
        .Take(PageSize),
    PagingInfo = new PagingInfo
    {
        CurrentPage = page,
        ItemsPerPage = PageSize,
        TotalItems = repository.James.Count()
    }
};

score 1 · Answer 2 · answered Jan 03 '14 at 10:58

1

You can do it with reflection,

James = repository.James
        .OrderBy(s =>s.GetType().GetProperty(sortByColumn).GetValue(s,null))
        .Skip((page - 1) * PageSize)
        .Take(PageSize)

answered Jan 03 '14 at 10:58

Victor Mukherjee

9,389
15
46
83

3

This won't work with LINQ to EF/NHibernate or another ORM that try to convert the LINQ query to a SQL statement. In LINQ to SQL it will load everything on the client and tryp to sorting and page there using LINQ to Objects. There is no generic solution to this problem – Panagiotis Kanavos Jan 03 '14 at 11:35

idipous · Answer 3 · 2014-01-03T11:01:29.733

0

You can do the following

public IEnumerable GetJames(int page = 1, Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,)
{ 
      var query = repository.James;
       //other stuff
      if (orderBy != null)
        {
            return orderBy(query).ToList();
        }

}

And use it like

public ActionView List()
{
   var model =   GetJames(orderBy: q => q.OrderBy(d => d.Name));
}

The code above is not functional it just gives you a context on what you could do.

edited Jan 03 '14 at 11:01

answered Jan 03 '14 at 10:55

idipous

2,794
2
26
40

The question is about string query parameter, and in your case you are using Expression. It's not the same. – Sergey Litvinov Jan 03 '14 at 10:56
Why pass the entire OrderBy expression instead of just the lambda? – Panagiotis Kanavos Jan 03 '14 at 10:56
@SergeyLitvinov I know it's not the same... I just find it a better solution to achieve the ordering requirement than a string which you have to make sure you pass correctly (especially if yo exposing it to third parties). – idipous Jan 03 '14 at 10:59
@PanagiotisKanavos you could pass the just the lamda expression but then what if you needed to have DESC and ASC ordering? – idipous Jan 03 '14 at 11:02
That wasn't in the question, and can be easily fixed by passing a boolean parameter to let you pick OrderBy or OrderByDescending. Your signature method makes stronger demands on the repository than Enumerable.OrderBy or Queryable.OrderBy – Panagiotis Kanavos Jan 03 '14 at 11:23
I don't think that passing additional parameters (like a boolean parameter) is the optimal solution. In general I would avoid passing more parameters than necessary. I am not sure what you mean by stronger demands than Queryable.OrderBy. If you mean what `TEntity` implies then this is easily fixed. If you mean something else please elaborate. – idipous Jan 03 '14 at 11:39
Eg, you are assuming the repository is a Queryable. What if it is an IEnumerable? Or what if it only implements IQueryable? OrderBy only wants a Func – Panagiotis Kanavos Jan 03 '14 at 11:42
I am assuming that the repository is IQueryable and returns IOrderedQueryable. IQueryable inherits from IEnumerable and IQueryable is a very logical assumption given that the question talks about a database. Of course if for some reason IEnumerable is what you need you can either downcast (ugly) or change it the signature to something suitable. The whole point of the answer is the way to pass ordering parameters. It's a good think you brought up those concerns however. – idipous Jan 03 '14 at 12:52

Passing a string as a sortable parameter to use in a Linq query

3 Answers3