Ef Sorting by property of property of object with string

Question

Ef Sorting by property of property of object with string

asp.net-core asp.net-web-api c# entity-framework entity-framework-core

Question

I'm currently developing an api with asp.core. For the data access I use entity framework with lazy loading.

Example:

+-------------------+
| Car               |
+-------------------+
| Id       | int    |
+----------+--------+
| Name     | string |
+----------+--------+
| DriverId | int    |
+----------+--------+

+----------------------+
| Driver               |
+----------------------+
| Id          | int    |
+-------------+--------+
| PhoneNumber | string |
+-------------+--------+

The DriverId in the Car table is a foreign key.

Now I want to get a list of cars from my api. Therefore I want pagination, searching and sorting. The only problem for me is sorting.

By default they should be ordered like this:

By Car.Name
Then by Car.Driver.PhoneNumber
Then by Car.DriverId
Then by Car.Id

But that is not the problem. I can do that with data.OrderBy(x => x.Name).ThenBy(x => x.Driver.PhoneNumber...

But now I want that the client can give me the name of the property by that the cars should be ordered (over the query string).

For the Car.Name property that is no problem because after I ordered the cars with the default code. I can get the property like this: var sortingProp = typeof(Car).GetProperty(sortingPropName);. And then I can order by that property like this: data.OrderBy(x => sortingProp).

Now my question: How can I let the client give me sth like this: sorting property name = 'Driver.PhoneNumber' and then the api should order by that property of the cars? Is it possible to do that without strongly type a new query for every property of the Car object?

Thanks

juliushuck

Simpler scenario with pure Linq:

If your scenario is simpler and you don't need dynamic at all, you can just do like this:

query = query.OrderBy(i => i.item.item_id);
// OR
query = query.OrderByDescending(i => i.item.item_id);

..which would use the property itself.

Dynamic scenario with "Dynamic Linq"

If you really needed dynamic (which means you would have to order by any fields and didn't want to put a lot of ifs in your code), then you would need to install this package System.Linq.Dynamic by running this in the Package Manager Console:

Install-Package System.Linq.Dynamic

Then you add this using clause:

using System.Linq.Dynamic;

Then you can achieve dynamic ordering with the following:

query = query.OrderBy("item.item_id DESC"); // keep the IQueryable
// OR
var items = query.OrderBy("item.item_id DESC").ToList(); // order in database...

When using this dynamic library, there is no OrderByDescending, since you can pass ASC or DESC with the string passed to the OrderBy() method (as in my example).

Take note item is the same name of the property inside ItemInfo. Also, ToList() is just an example, actually you don't need it at all, depending on your scenario.

Although I'd suggest you to change your ItemInfo to be like this:

public class ItemInfo {
        public int item_id { get; set; }
        public int category_id { get; set; }
    }

...then fix your query to populate accordingly:

IQueryable<ItemInfo> query = (from i in Item
                              join c in ItemCategory on i.category_id equals c.category_id
                              select new ItemInfo() {
                                 item_id = i.item_id,
                                 category_id = c.category_id
                              };

query.OrderBy("item_id DESC");

This looks simpler and cleaner in my point of view. I'd avoid to put the whole object inside ItemInfo, keeping there exactly the properties you need, so you wouldn't load all the fields unnecessarily from database every time.

Ef Sorting by property of property of object with string

Question

Fastest Entity Framework Extensions

Accepted Answer

Simpler scenario with pure Linq:

Dynamic scenario with "Dynamic Linq"

Related Questions

Fastest Entity Framework Extensions

Performance

Expression Evaluator

Related