how to order by a dynamic column name in EntityFramework?

Question

I am trying to get following code working , This was working fine for MSSQL , but since i changed to use mySql it is not working

  records.Content = db.areas
                         .Where(x =>   x.Name.Contains(filter)))
                         .OrderBy("dated desc") 
                         .ToList();

I get the error " Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information."

string colName = "datedD" ;

how to order by depneding on colName variable ?
`

I guess by "string version" you mean the so called "Dynamic LINQ" (or System.Linq.Dynamic). Please add `dynamic-linq` tag and update the question accordingly. — Ivan Stoev, Jun 27 '16 at 13:29
@Kas Can you please check my answer as it seems better method for this question ;D — S.Serpooshan, Dec 15 '20 at 18:42
try this library [Entity Framework Plus Library - Linq-Dynamic](https://entityframework-plus.net/linq-dynamic). you can also check my answer — Ben.S, Dec 18 '20 at 09:50

S.Serpooshan · Answer 1 · 2019-12-28T07:46:18.097

63

In .Net Core, we can use the EF.Property method to specify the name of the property as a string:

string sortColumn = "Price";

//IQueryable<Product> q = from p in myDbContext.Products select p;
q = q.OrderBy(p => EF.Property<object>(p, sortColumn));

edited Dec 28 '19 at 07:46

answered Jul 23 '18 at 14:07

S.Serpooshan

7,608
4
33
61

4

this should be the accepted answer – Desolator Dec 15 '20 at 18:07
if you are not using .Net Core look at @Taher Rahgooy answer [here](https://stackoverflow.com/questions/31955025/generate-ef-orderby-expression-by-string) – Ben.S Dec 18 '20 at 07:52

neer · Accepted Answer · 2016-06-27T13:41:34.860

4

Try this

string filterString = "dated";
bool isAscSorting = false;

Func<dynamic, dynamic> orderingFunction = i =>
                                filterString == "dated" ? i.dated :
                                filterString == "something" ? i.columnx : "";

records.Content = (isAscSorting) ?
                      db.areas
                         .Where(x =>   x.Name.Contains(filter)))
                         .OrderBy(orderingFunction) 
                         .ToList()
                   :
                        db.areas
                         .Where(x =>   x.Name.Contains(filter)))
                         .OrderByDescending(orderingFunction) 
                         .ToList();

edited Jun 27 '16 at 13:41

answered Jun 27 '16 at 13:34

neer

4,031
6
20
34

Is it possible to stop duplication of this code ? db.areas .Where(x => x.Name.Contains(filter))) – Kas Jun 27 '16 at 14:07
You cant on this answer But You can use dynamic linq It is complex – neer Jun 27 '16 at 14:09
@Kas you can put result of `db.areas.Where(x => x.Name.Contains(filter)))` into local variable `var query = ...` and then use `query = (isAscSorting) ? query.OrderBy(...) : query.OrderByDescending(...)` – Evgeny Nozdrev Oct 26 '20 at 10:48

score 3 · Answer 3 · answered Jan 23 '20 at 22:59

I used the mix of 2 answers by @NEER and @S.Serpooshan to aviod LINQ queries

IQueryable<area> filteredItems = db.areas.Where(x =>   x.Name.Contains(filter)));
IQueryable<area> orderedItems;
if (IsAscending)
{
    orderedItems = filteredItems.OrderBy(item => typeof(area).GetProperty(colName).GetValue(item).ToString());
}
else
{
    orderedItems = filteredItems.OrderByDescending(item => typeof(area).GetProperty(colName).GetValue(item).ToString());
}

The bad thing is - my code is comparing items as strings. I had no time to make it more "type-friendly", but I'm pretty sure it is possible.

THIS is the Correct answer. also Item => Item.GetType() can be use instead of item => typeof(area) — Rouzbeh Zarandi, Nov 01 '22 at 12:41

Ben.S · Answer 4 · 2021-03-17T06:12:17.083

2

For dynamic ordering most convenient way I found:

string propertyNameForOrdering = "UserName";

var list = context.Customers.OrderByDescendingDynamic(x => "x." + propertyNameForOrdering).ToList(); // DESC

var list = context.Customers.OrderByDynamic(x => "x." + propertyNameForOrdering).ToList(); // ASC

Entity Framework Plus Library - Linq-Dynamic

That feature is free even for commercial use.

edited Mar 17 '21 at 06:12

answered Dec 18 '20 at 09:50

Ben.S

708
1
5
24

how to order by a dynamic column name in EntityFramework?

4 Answers4

Linked