Programmatically chain OrderBy/ThenBy using LINQ / Entity Framework

entity-framework linq

Question

I have a reporting interface where the end user gets to select multiple fields for the sort order of the returned report. The problem I am having is that I can't really chain the OrderBy / ThenBy methods, since I'm iterating through a list of sort fields. I'm thinking something like this:

foreach (string sort in data.SortParams)
{
    switch (sort)
    {
        case "state":
            query = query.ThenBy(l => l.RegionCode);
            break;
        case "type":
            query = query.ThenBy(l => l.Type);
            break;
        case "color":
            query = query.ThenBy(l => l.Color);
            break;
        case "category":
            query = query.OrderBy(l => l.Category);
            break;
    }
}

(Note: I've removed the switch determining if this is the first sort item for simplicity's sake.)

Any thoughts on how to iterate through a collection to determine the sort order?

1
14
8/3/2017 2:01:30 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

You could do what you want if you use an initial "seed" OrderBy:

EDIT you need to call OrderBy to create an IOrderedEnumerable (or IOrderedQueryable) first before attaching ThenBy clauses:

var orderedQuery = query.OrderBy(l => 0);
foreach (string sort in data.SortParams)
{
    switch (sort)
    {
        case "state":
            orderedQuery = orderedQuery.ThenBy(l => l.RegionCode);
            break;
        case "type":
            orderedQuery = orderedQuery.ThenBy(l => l.Type);
            break;
        case "color":
            orderedQuery = orderedQuery.ThenBy(l => l.Color);
            break;
        case "category":
            orderedQuery = orderedQuery.ThenBy(l => l.Category);
            break;
    }
}
query = orderedQuery;  // cast back to original type.

If you want something more flexible check out this answer

19
11/6/2017 3:35:47 PM

Popular Answer

I've created these extension methods to tackle an identical problem as stated in the question:

public static class QueryableExtensions
{
    public static IOrderedQueryable<T> AppendOrderBy<T, TKey>(this IQueryable<T> query, Expression<Func<T, TKey>> keySelector)
        => query.Expression.Type == typeof(IOrderedQueryable<T>)
        ? ((IOrderedQueryable<T>) query).ThenBy(keySelector)
        : query.OrderBy(keySelector);

    public static IOrderedQueryable<T> AppendOrderByDescending<T, TKey>(this IQueryable<T> query, Expression<Func<T, TKey>> keySelector)
        => query.Expression.Type == typeof(IOrderedQueryable<T>)
            ? ((IOrderedQueryable<T>)query).ThenByDescending(keySelector)
            : query.OrderByDescending(keySelector);
}

The code in the question could then be refactored to:

foreach (string sort in data.SortParams)
{
    switch (sort)
    {
        case "state":
            query = query.AppendOrderBy(l => l.RegionCode);
            break;
        case "type":
            query = query.AppendOrderBy(l => l.Type);
            break;
        case "color":
            query = query.AppendOrderBy(l => l.Color);
            break;
        case "category":
            query = query.AppendOrderBy(l => l.Category);
            break;
    }
}

REMARK These extension methods only check the previous expression in the expression tree to determine wether to use OrderBy or ThenBy, no other expressions are allowed in-between. If you also want to tackle that, you'll have to walk through the complete tree which might just add that overhead you don't want :)

huysentruitw

Related Questions

Retrieve single Entity Framework entities using a LINQ query or GetObjectKey?

It looks like GetObjectKey has the benefit of searching for existing, instantiated objects, and THEN the data store. However, it also seems like you lose some of the strong typing, and need to cast your resulting object:...GetObjectKey...int customerID = 1; EntityKey key = new EntityKey("MyEntities.Customers", "CustomerID", customerID); Customer c...
asp.net c# entity-framework linq
asked by John Bubriski

LINQ OrderBy Name ThenBy ChildrenCollection.Name

Is there any way in LINQ to do an OrderBy and then do a ThenBy with the ThenBy using the children of the parent object to do the secondary ordering?..._repository.GetActiveDepartmentGroupsWithDepartments().OrderBy(c => c.DepartmentGroupName).ThenBy(c => c.Departments.OrderBy(d => d.DepartmentName)) ...In the above case, c.Departments is an EntityCo...
c# entity-framework linq linq-to-entities
asked by TehOne

Entity Framework vs Linq to Entities vs Linq to SQL

There are two ways of writing queries with Entity Framework:...Linq to Entities ( ...http://msdn.microsoft.com/en-us/library/bb386964.aspx... )...Entity SQL ( ...http://msdn.microsoft.com/en-us/library/bb387145.aspx... )...L2E is not a separate technology, but rather a part of EF....Have a look at this article for a comparison: ...http://thedatafar...
ado.net entity-framework linq-to-entities linq-to-sql
asked by Yakimych

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities in entity framework

This problem can be solved using the AsExpandable() method present in LINQKIT by Joe Albahari. He's the same creator of ...PredicateBuilder... that I see you're using....If querying with Entity Framework, change the last line to this:...return objectContext.Products.AsExpandable().Where(predicate);...You can grab LINQKIT DLL ...here... or install i...
c# entity-framework expression linq predicate
asked by Leniel Maccaferri

Compare Dates using LINQ to Entities (Entity Framework)

I need to return a list of items from my database that expire at a pre-specified time on the date supplied by the item. My ...erroneous... code is as follows:...return All().Where(o => new DateTime(o.expiry_date.Year, o.expiry_date.Month, o.expiry_date.Day, 17, 30, 0) >= DateTime.Now) ...The error I get is:...Only parameterless constructors and ini...
c# datetime entity-framework
asked by Jimbo

LINQ to Entities -- OrderBy().ToLisT() vs. ToList().OrderBy()

I'm looking for confirmation/clarification with these LINQ expressions:...var context = new SomeCustomDbContext() // LINQ to Entities? var items = context.CustomItems.OrderBy(i => i.Property).ToList(); // LINQ to Objects? var items2 = context.CustomItems.ToList().OrderBy(i => i.Property); ...(Q1)... Am I correct in thinking the first method is ....
entity-framework linq linq-to-entities performance
asked by one.beat.consumer

How to chain alternative conditions in where clause in Entity Framework

I'm trying to rewrite the following query as a separate statements:...var sql = Repository.Products.AsQueryable(); sql = sql.Where(x => x.Name == "aaaaa" || x.Name == "bbbbb"); ...If I do it like this:...sql = sql.Where(x => x.Name == "aaaaa"); sql = sql.Where(x => x.Name == "bbbbb"); ...then the resulting query is equal to:...sql = sql.Where(x =>...
.net c# entity-framework linq-to-entities
asked by dodbrian

Dynamic LINQ OrderBy Date only on DateTime Object Entity Framework

You can use the approach from my answer to ...Dynamic Linq + Entity Framework: datetime modifications for dynamic select..., i.e. post process the query expression with custom ...ExpressionVisitor... and replace the unsupported methods with their ...DbFunctions... equivalents. In this particular case, replace the ...DateTime.Date... property with ....
c# entity-framework entity-framework-6 linq linq-to-entities
asked by Ivan Stoev

Entity Framework linq orderby function

I am new to Entity Framework and linq. I am working with asp.net mvc 5 and C#. I write a query with dynamic sorting as follows:...public static IEnumerable<T> OrderByDynamic<T>(this IEnumerable<T> source, string propertyName, bool Ascending) { if (Ascending) return source.OrderBy(x => x.GetType().GetProperty(propertyName).GetValue(x, nu...
asp.net-mvc-5 c# entity-framework-6 function linq
asked by saeedeh

How to pass an Expression into Entity Framework LINQ query OrderBy clause

This isn't as simple as it seems. You need to combine ...Expression...s or build ...Expression...s to generate what you want, and unfortunately C# doesn't include a lot of help in that area....The easiest approach is to use an extension method for ...LambdaExpression... composition. It depends on some ...Expression... extension methods for replacin...
c# entity-framework-6 expression lambda linq
asked by NetMage

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow