Order by dynamic parameter
If you want to do sorting on the Server Side, you have to correct expression.
var query = from c in db.CUSTOMERS select c;
if (!string.IsNullOrEmpty(sortBy) && !string.IsNullOrEmpty(direction))
{
query = query.ApplyOrderBy(new [] {Tuple.Create(sortBy, direction != "asc")});
}
return query.ToList();
And implementation:
public static class QueryableExtensions
{
public static IQueryable<T> ApplyOrderBy<T>(this IQueryable<T> query, IEnumerable<Tuple<string, bool>> order)
{
var expr = ApplyOrderBy(typeof(T), query.Expression, order);
return query.Provider.CreateQuery<T>(expr);
}
static Expression MakePropPath(Expression objExpression, string path)
{
return path.Split('.').Aggregate(objExpression, Expression.PropertyOrField);
}
static Expression ApplyOrderBy(Type entityType, Expression queryExpr, IEnumerable<Tuple<string, bool>> order)
{
var param = Expression.Parameter(entityType, "e");
var isFirst = true;
foreach (var tuple in order)
{
var lambda = Expression.Lambda(MakePropPath(param, tuple.Item1), param);
var methodName =
isFirst ? tuple.Item2 ? nameof(Queryable.OrderByDescending) : nameof(Queryable.OrderBy)
: tuple.Item2 ? nameof(Queryable.ThenByDescending) : nameof(Queryable.ThenBy);
queryExpr = Expression.Call(typeof(Queryable), methodName, new[] { entityType, lambda.Body.Type }, queryExpr, lambda);
isFirst = false;
}
return queryExpr;
}
}
How do I specify the Linq OrderBy argument dynamically?
Here's a possiblity using reflection...
var param = "Address";
var propertyInfo = typeof(Student).GetProperty(param);
var orderByAddress = items.OrderBy(x => propertyInfo.GetValue(x, null));
How to accept @OrderBy of JPA sort value as a dynamic parameter?
It cannot be done because when the entityManager is initialized the annotations are evaluated.
The best options are, create a query that comes ordered or once the list is obtained if you use Java 8 from now on, order with the sort method of stream.
How can I do an OrderBy with a dynamic string parameter?
Absolutely. You can use the LINQ Dynamic Query Library, found on Scott Guthrie's blog. There's also an updated version available on CodePlex.
It lets you create OrderBy
clauses, Where
clauses, and just about everything else by passing in string parameters. It works great for creating generic code for sorting/filtering grids, etc.
var result = data
.Where(/* ... */)
.Select(/* ... */)
.OrderBy("Foo asc");
var query = DbContext.Data
.Where(/* ... */)
.Select(/* ... */)
.OrderBy("Foo ascending");
How to specify dynamic order by clause in Oracle
Order by two outer CASE
expressions, one ASC
one DESC
. In the first one check if you want to sort ASC
and if not let the expression return a constant or NULL
, i.e. it doesn't change the order. For the case you want to sort ASC
add an inner CASE
expression, that returns the column to sort by. Analogously handle the DESC
case.
...
ORDER BY CASE
WHEN in_order_dir = 'ASC' THEN
CASE
WHEN in_order_by = 1 THEN
city_code
WHEN in_order_by = 2 THEN
city_name
...
END
END ASC,
CASE
WHEN in_order_dir = 'DESC' THEN
CASE
WHEN in_order_by = 1 THEN
city_code
WHEN in_order_by = 2 THEN
city_name
...
END
END DESC;
Maybe you have to adapt it because of type incompatibilities between the columns, I can't tell that from what you posted. But it should convey the general concept.
Edit:
On the type problem:
One possibility is to convert the columns to compatible data types in a way the order is kept. For instance, if you have a char_column
(of the type char
), a date_column
and an integer_column
(of date
and integer
) you could do
to_char(date_column, 'YYYYMMDDHH24MISS')
to convert the date and
lpad(11, 38, '0')
to convert the integer to char
.
The cleaner thing to do, but the one with more work to do (for the programmer, in terms of performance there should be no significant difference whatsoever) is to split the expressions once more. I.e. have an outer CASE
for each type your columns are for each direction.
Like
ORDER BY CASE
WHEN in_order_dir = 'ASC' THEN
CASE
WHEN in_order_by = 1 THEN
char_column1
WHEN in_order_by = 2 THEN
char_column2
...
END
END ASC,
CASE
WHEN in_order_dir = 'ASC' THEN
CASE
WHEN in_order_by = 3 THEN
date_column1
WHEN in_order_by = 4 THEN
date_column2
...
END
END ASC,
and so on for all types and analogously for DESC
. Remember, if the column isn't meant to be sorted after such CASE
will yield NULL
and sorting by that won't influence the order.
Passing dynamic order by in stored procedure
You can use a complicated order by
clause. That requires one case
for each sort direction and each data type. With this example dataset:
create table t1 (id int, name varchar(50), created date);
insert t1 values
(1, 'Chihiro Ogino', '2012-01-01'),
(2, 'Spirit of the Kohaku River', '2012-01-03'),
(3, 'Yubaba', '2012-01-02');
You could use an order by
clause like:
declare @sortColumn varchar(50) = 'created'
declare @sortOrder varchar(50) = 'DESC'
select *
from t1
order by
case
when @sortOrder <> 'ASC' then 0
when @sortColumn = 'id' then id
end ASC
, case
when @sortOrder <> 'ASC' then ''
when @sortColumn = 'name' then name
end ASC
, case
when @sortOrder <> 'ASC' then cast(null as date)
when @sortColumn = 'created' then created
end ASC
, case
when @sortOrder <> 'DESC' then 0
when @sortColumn = 'id' then id
end DESC
, case
when @sortOrder <> 'DESC' then ''
when @sortColumn = 'name' then name
end DESC
, case
when @sortOrder <> 'DESC' then cast(null as date)
when @sortColumn = 'created' then created
end DESC
Working example at SQL Fiddle.
Another option is to create the query dynamically, and run it with exec
. For example:
declare @sql nvarchar(max)
set @sql = 'select * from YourTable order by ' + @sortColumn + ' ' + @sortDir
exec (@sql)
Dynamic order direction
You could have two near-identical ORDER BY
items, one ASC
and one DESC
, and extend your CASE
statement to make one or other of them always equal a single value:
ORDER BY
CASE WHEN @OrderDirection = 0 THEN 1
ELSE
CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
WHEN @OrderByColumn = 'Title' THEN Title
END
END ASC,
CASE WHEN @OrderDirection = 1 THEN 1
ELSE
CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
WHEN @OrderByColumn = 'Title' THEN Title
END
END DESC
How to sort a Comparator with dynamic parameters?
Make a method:
private final Comparator<Person> personComparator(String dynaValue) {
return Comparator.comparing(person -> {
if (person.getFirstname() != null) return person.getFirstname();
return evaluateWithDynamicProperty(person, dynaval);
}
Invoke like:
Collections.sort(persons, personComparator(dynaValue));
Or, use a lambda:
String personComparatorFn(Person person, String dynaValue) {
if (person.getFirstname() != null) return person.getFirstname();
return evaluateWithDynamicProperty(person, dynaval);
}
and invoke like:
Collections.sort(persons, Comparator.comparing(p -> personComparatorFn(p, dynaValue));
Dynamic Order By in Linq
Well, you definitely want to use OrderByDescending
instead of reversing. It's not going to be quite as brief as the SQL, but you could at least use:
IQueryable<Entity> GetSortedData(IQueryable<Entity> result, String orderby, bool desc)
{
switch (orderby.ToLowerInvariant())
{
case "id":
return desc ? result.OrderByDescending(c => c.Id) : result.OrderBy(c => c.Id);
case "code":
return desc ? result.OrderByDescending(c => c.Code) : result.OrderBy(c => c.Code);
case "active":
return desc ? result.OrderByDescending(c => c.Active) : result.OrderBy(c => c.Active);
default:
return desc ? result.OrderByDescending(c => c.Name) : result.OrderBy(c => c.Name);
}
}
You could remove that repetition with your own extension method:
public static IOrderedQueryable<TSource> OrderBy<TSource, TKey>(
this IQueryable<TSource> source,
Expression<Func<TSource, TKey>> keySelector,
bool descending) =>
descending ? source.OrderByDescending(keySelector) : source.OrderBy(keySelector);
Then write:
IQueryable<Entity> GetSortedData(IQueryable<Entity> result, String orderby, bool desc)
{
switch (orderby.ToLowerInvariant())
{
case "id": return result.OrderBy(c => c.Id, desc);
case "code": return result.OrderBy(c => c.Code, desc);
case "active": return result.OrderBy(c => c.Active, desc);
default: return result.OrderBy(c => c.Name, desc);
}
}
Related Topics
Recommendation for C# Matrix Library
Log4Net, How to Add a Custom Field to My Logging
How to Send an Email in .Net According to New Security Policies
Remove the Title Bar in Windows Forms
When to Use a Sortedlist<Tkey, Tvalue> Over a Sorteddictionary<Tkey, Tvalue>
Adding and Removing Anonymous Event Handler
Show Controls Added Programmatically in Winforms App in Design View
How to Convert a Character in to Equivalent System.Windows.Input.Key Enum Value
From Excel to Datatable in C# with Open Xml
Do I Need to Lock or Mark as Volatile When Accessing a Simple Boolean Flag in C#
What Is the Command to Exit a Console Application in C#
Create Web Service Proxy in Visual Studio from a Wsdl File