How orderBy dynamically, based on navigation property

Question

var query = TableNoTracking;
query = query
            .Include(r => r.UserRoles).ThenInclude(o => o.Role)
            .Include(c => c.Photos.Where(x => x.IsMain))
            .Include(w => w.Wallets);

my sort part is like below and I want handle sorting dynamically

   if (!string.IsNullOrWhiteSpace(filterUser.SortBy))
            {
                switch (filterUser.Reverse)
                {
                    //reverse
                    case true:
                        switch (filterUser.SortBy)
                        {
                            case UserNavigationPropsForSort.InventorySum:
                                query = query.OrderByDescending(x => x.Wallets.Select(y => y.Inventory).Sum());
                                break;
                            case UserNavigationPropsForSort.InterMoneySum:
                                query = query.OrderByDescending(x => x.Wallets.Select(y => y.InterMoney).Sum());
                                break;
                            case UserNavigationPropsForSort.ExitMoneySum:
                                query = query.OrderByDescending(x => x.Wallets.Select(y => y.ExitMoney).Sum());
                                break;
                            case UserNavigationPropsForSort.OnExitMoneySum:
                                query = query.OrderByDescending(x => x.Wallets.Select(y => y.OnExitMoney).Sum());
                                break;
                        }

                        break;
                    case false:
                        switch (filterUser.SortBy)
                        {
                            case UserNavigationPropsForSort.InventorySum:
                                query = query.OrderBy(x => x.Wallets.Select(y => y.Inventory).Sum());
                                break;
                            case UserNavigationPropsForSort.InterMoneySum:
                                query = query.OrderBy(x => x.Wallets.Select(y => y.InterMoney).Sum());
                                break;
                            case UserNavigationPropsForSort.ExitMoneySum:
                                query = query.OrderBy(x => x.Wallets.Select(y => y.ExitMoney).Sum());
                                break;
                            case UserNavigationPropsForSort.OnExitMoneySum:
                                query = query.OrderBy(x => x.Wallets.Select(y => y.OnExitMoney).Sum());
                                break;
                        }
                        break;
                }
            }

I include several entities, now I want to order this query dynamically based on included properties, for example inventory in wallet. i tried dynamic linq library but it dont work for this situation;

How can I do this?

I din't get question. Could you please show how do you plan sort without dynamic realisation? Just LINQ query. — Svyatoslav Danyliv, Sep 09 '21 at 17:16
Does this answer your question? [Order by dynamic parameter](https://stackoverflow.com/questions/65849281/order-by-dynamic-parameter) — Svyatoslav Danyliv, Sep 09 '21 at 17:24
no not worked for me.I edit question for better understanding — monti123, Sep 09 '21 at 18:26
So, you have hidden a lot of details. Dynamic realisation a little bit tricky and better leave it as is. If you have a lot of such sorting techniques, add more examples to understand complexity of common solution. — Svyatoslav Danyliv, Sep 09 '21 at 18:38

score 1 · Answer 1 · answered Sep 09 '21 at 19:11

I'm not entirely sure it's possible to do it fully dynamic without setting up a mapping between UserNavigationPropsForSort and a Wallet property.

Unless you want to get into expression tree building, which I suspect is overkill for what you're actually trying to do. If not, refer to docs here: https://learn.microsoft.com/en-us/dotnet/csharp/expression-trees-building

What I've done is made your code a bit more concise and expandable:

            var query = TableNoTracking;
            query = query
                .Include(r => r.UserRoles).ThenInclude(o => o.Role)
                .Include(c => c.Photos.Where(x => x.IsMain))
                .Include(w => w.Wallets);
            
            if (!string.IsNullOrWhiteSpace(filterUser.SortBy.ToString()))
            {
                Func<Wallet, decimal> sortByToProperty = filterUser.SortBy switch
                {
                    UserNavigationPropsForSort.InventorySum => w => w.Inventory,
                    UserNavigationPropsForSort.InterMoneySum => w => w.InterMoney,
                    UserNavigationPropsForSort.ExitMoneySum => w => w.ExitMoney,
                    UserNavigationPropsForSort.OnExitMoneySum => w => w.OnExitMoney,
                    _ => throw new ArgumentOutOfRangeException()
                };

                query = filterUser.Reverse
                    ? query.OrderByDescending(x => x.Wallets.Select(sortByToProperty).Sum())
                    : query.OrderBy(x => x.Wallets.Select(sortByToProperty).Sum());
            }

If, at any point, you want to add more properties to sort by, you can expand the sortByToProperty switch.

Please let me know if this is what you meant.

Good start, but you have to deal with Expression instead of pure delegates. — Svyatoslav Danyliv, Sep 09 '21 at 19:15
@SvyatoslavDanyliv I'm not in a position to test it, but since the `Func sortByToProperty` is used *inside* the query's `Select` (which is an `IEnumerable`, not `IQueryable`), I think it might work — nbokmans, Sep 09 '21 at 19:44
How easy to write this via `IEnumerable` ;) With `IQueryable` such trick will not work. — Svyatoslav Danyliv, Sep 09 '21 at 19:49
"Expression of type 'System.Func`2[FarsiPay.Entities.MainDb.User.Wallet,System.Int32]' cannot be used for parameter of type 'System.Linq.Expressions.Expression`1[System.Func`2[FarsiPay.Entities.MainDb.User.Wallet,System.Int32]]' of method 'System.Linq.IQueryable`1[System.Int32] Select[Wallet,Int32](System.Linq.IQueryable`1[FarsiPay.Entities.MainDb.User.Wallet], System.Linq.Expressions.Expression`1[System.Func`2[FarsiPay.Entities.MainDb.User.Wallet,System.Int32]])' (Parameter 'arg1')" — monti123, Sep 09 '21 at 20:22

score 1 · Accepted Answer · edited Sep 10 '21 at 00:16

Credit to @nbokmans and @Svyatoslav Danyliv. I used Expression instead of Func and now code work.

        if (!string.IsNullOrWhiteSpace(filterUser.SortBy))
            {
                if (filterUser.SortableProperties.Any(x =>
                    string.Equals(x, filterUser.SortBy, StringComparison.CurrentCultureIgnoreCase)))
                {
                    Expression<Func<User, int>> exp = filterUser.SortBy switch
                    {
                        UserNavigationPropsForSort.InventorySum => w => w.Wallets.Select(x => x.Inventory).Sum(),
                        UserNavigationPropsForSort.InterMoneySum => w => w.Wallets.Select(x => x.InterMoney).Sum(),
                        UserNavigationPropsForSort.ExitMoneySum => w => w.Wallets.Select(x => x.ExitMoney).Sum(),
                        UserNavigationPropsForSort.OnExitMoneySum => w => w.Wallets.Select(x => x.OnExitMoney).Sum(),
                        _ => throw new ArgumentOutOfRangeException()
                    };

                    query = filterUser.Reverse ? query.OrderByDescending(exp) : query.OrderBy(exp);
                }
            }

Nicely done! This is very similar to what I was gonna propose changing my answer too. — nbokmans, Sep 10 '21 at 08:39

How orderBy dynamically, based on navigation property

2 Answers2