CODEWITHSUNDEEP
c#linqone-to-many
Masoud
Masoud

Reputation: 8171

Order by with Linq

I have a One-Many relation with two Entities :

Order:
int OrderId 
string OrderNumber
...

OrderItem:
int ItemId
int sequence
decimal Quantity
...

One Order Has multiple OrderItems, I want to write a Linq that return all orders with their items and items be sorted by sequence field. How can i write a query such as following query?

Order myOrder = db.Orders.Include("OrderItems").Include("sequence").FirstOrDefault(x => x.OrderId == 3);

Upvotes: 2

Views: 525

Answers (4)

Geoff Appleford
Geoff Appleford

Reputation: 18832

You can't do that without projecting the results into a new type. 

var myOrder= db.Orders.Include("OrderItems")
             .Where(x => x.OrderId == 3)  
             .Select(o => new {
                 order = o,
                 orderItems = o.OrderItems.OrderBy(i => i.sequence)
             }).FirstOrDefault();

// Usage
myOrder.order; // Order

myOrder.order.OrderItems; // OrderItems
myorder.orderItems;  // OrderItems alternative

Whilst it's not ideal projecting it into a new object, according to this blog post, there is a nice side-effect that now order.OrderItems on your anonymous object will be sorted correctly too.

Upvotes: 1

daryal
daryal

Reputation: 14919

var temp = db.Orders.Include("OrderItems").Select(q => q.OrderItems.OrderBy(p => 
                     p.sequence)).ToList();

Upvotes: 2

DooDoo
DooDoo

Reputation: 13437

var result = from a in Order 
             join b in OrderItems
                on a.Id equals b.OrderId
             order by b.sequence
             select new
             {     
                 a.Id,
                 b.sequence,
                 ...
             };

Upvotes: 2

rexcfnghk
rexcfnghk

Reputation: 15432

var Orders = db.Orders.Include(a => a.OrderItems)
.OrderBy(a => a.OrderItems.sequence).ToList()

I recommend using lambdas to avoid the magic strings in your query.

Upvotes: -1

Related Questions