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:
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
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.
If you really needed dynamic (which means you would have to order by any fields and didn't want to put a lot of if
s 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.