jomendez

How to specify the OrderBy method using LINQ, with a string value as a parameter.

How do I specify the argument passed to OrderBy method with LINQ, using a string value as a parameter?
This is the current implementation:

var list =   _dbContext.Advertisement
.Where(y => (DateTime.Now > y.StartDate) && (DateTime.Now < y.EndDate) && 
!y.Archived && adId.Contains(y.StoreId)) .OrderBy(x => x.Name).ToList();

Instead of x.Name, I want to take x.Name as a string parameter and make it more dynamic. For example:

string orderByParam = "Name";
var list =   _dbContext.Advertisement
.Where(y => (DateTime.Now > y.StartDate) && (DateTime.Now < y.EndDate) && 
!y.Archived && adId.Contains(y.StoreId))
.OrderBy(orderByParam).ToList();

Obviously LINQ’s OrderBy() method doesn’t accept string parameters, only lambda expressions.

Possible solution:

Using reflection…

var orderBy = String.IsNullOrEmpty(orderBy) ? "Name" : orderBy;
var dynamicPropFromStr = typeof(Advertisement).GetProperty(orderBy); 

var list =   _dbContext.Advertisement
.Where(y => (DateTime.Now > y.StartDate) && (DateTime.Now < y.EndDate) && 
!y.Archived && adId.Contains(y.StoreId)) 
.OrderBy(x => dynamicPropFromStr.GetValue(x, null)).ToList();

This will certainly solve the problem, but this code will throw an exception:

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code.

Additional information: LINQ to Entities does not recognize the method 'System.Object GetValue(System.Object, System.Object[])' method, and this method cannot be translated into a store expression.

This happen because LINQ to Entities uses IQueryable type, and within Where, OrderBy etc only support Lambda Expressions cannot contain any C# specific code, they can only contain expression tree, which is translated to SQL. You cannot call any arbitrary methods there, except the ones that are mentioned by EF documentation such as SqlFunctions etc.

To avoid this exception you can convert the expression to Enumerable with AsEnumerable() method, before the OrderBy:

var list = _dbContext.Advertisement
.Where(y => (DateTime.Now > y.StartDate) && (DateTime.Now < y.EndDate) && 
!y.Archived && adId.Contains(y.StoreId)).AsEnumerable() 
.OrderBy(x => dynamicPropFromStr.GetValue(x, null)).ToList();

References:

https://msdn.microsoft.com/en-us/library/vstudio/bb534966(v=vs.100).aspx
If you have a solution for this, don't be shy and share it with us. 🙂