How to order by a dynamic column name in EntityFramework?

Question

I am trying to get following code working , This was working fine for SQL Server, 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 depending on colName variable?

Heligoland

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. – Orlantha

@Heligoland Can you please check my answer as it seems better method for this question ;D – Zoometry

try this library Entity Framework Plus Library - Linq-Dynamic. you can also check my answer – Crookes

Duplicate: https://mcmap.net/q/11365/-dynamic-linq-orderby-on-ienumerable-lt-t-gt-iqueryable-lt-t-gt/861716 – Luxembourg

Answer 1

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();

Panelboard

Is it possible to stop duplication of this code ? db.areas .Where(x => x.Name.Contains(filter))) – Heligoland

You cant on this answer But You can use dynamic linq It is complex – Panelboard

@Heligoland 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(...) – Jann

Answer 2

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));

Zoometry

this should be the accepted answer – Daciadacie

if you are not using .Net Core look at @Taher Rahgooy answer here – Crookes

it reduced my whole page query to single line. thanks – Prostomium

Answer 3

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.

Evermore

THIS is the Correct answer. also Item => Item.GetType() can be use instead of item => typeof(area) – Glia

Answer 4

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.

Crookes

Are you sure it's still free and will remain so? Despite the name, that's not related to the EF team and the company behind it has a habit of picking up abandoned projects and dual-licensing them. Or creating "documentation" sites for other people's projects that promote their own unrelated libraries, like they did with Dapper – Dawes

For example, when did "BulkInsert" become a paid extension? – Dawes