Order a subcollection from linq
I am using linq/EF core to retrieve data via an API call.
How do I sort the collection of related data for each retrieved entity?
Assume the following structure: an Order is made on a certain date, and is comprised of (inter alia) a collection of OrderLines, where an OrderLine comprises a Product and a quantity.
How do I generate a list of Orders sorted by OrderDate, with the OrderLines for each Order displayed in Alphabetical order by Product?
I have tried:
context.TblOrder
.Include(o => o.TblOrderLine
.OrderBy(ol => ol.ProductName))
.OrderBy(o => o.OrderDate);
But when I try to send that to a list, I get an Expression of type 'System.Linq.IOrderedQueryable1<T> cannot be used for return type 'System.Linq.IOrderedEnumerable1<T>
I also tried to retrieve (correctly sorted) OrderDetails separately as a DbSet (ie same as the context), but then I couldn't link them through the navigation property.
var lines = context.TblOrderLine
.OrderBy(ol => ol.ProductName) as DbSet<TblOrderLine>;
context.TblOrders
.Include(o => o.lines)
.OrderBy(o => o.OrderDate);
TblOrder does not contain a definition for lines and no accessible extension method ... could be found.
How do I retrieve data with a correctly sorted sub-collection of data for each record?
1 answer
If you are using .NET Core 5.0 or more you should be able to write something like the following (not tested):
var data = context.TblOrder
.OrderBy(o => o.OrderDate)
.Select(o => new
{
Order = o,
OrderLines = o.TblOrderLine.OrderBy(ol => ol.ProductName)
});
The advantage of this approach is that you can specify the exact columns you need and the query will be more efficient (Include gets all the columns).
0 comment threads
0 comment threads