CODEWITHSUNDEEP
c#entity-frameworklinq
Tom el Safadi
Tom el Safadi

Reputation: 6746

Select First of Each Navigation Property

I have three classes that look like the following. Basically, one order will be assigned multiple status.

public class Order {
    public int Id { get; set; }
    public DateTime Created { get; set; }
    ...
    public ICollection<OrderStatus> OrderStatus { get; set; }
}

public class Status {
    public int Id { get; set; }
    public DateTime Date { get; set; }
    ...
    public ICollection<OrderStatus> OrderStatus { get; set; }
}

public class OrderStatus {
    public int Id { get; set; }
    public Order Order{ get; set; }
    public Status Status { get; set; }
}

My goal is to get the last status of every single order that has been assigned to a status between 0001-1000. This is what I have so far:

orders.SelectMany(x => x.OrderStatus.Where(x => x.Status.Id >= 0001 && x.Status.Id <= 1000).OrderByDescending(x => x.Date).Select(x => x.Date));

This part is where I only want to select the first of each and not all of them.

.Select(x => x.Date)

Edit:

I got it working, with the help of @StriplingWarrior!

I forgot to mention, that I only want the date of the last status per order. So basically, I will have a list of dates at the end for each order. This is the final product:

List<DateTime> dates = buchungen.SelectMany(x => x.OrderStatus.Where(x => x.Status.Id >= 0001 && x.Status.Id <= 1000).GroupBy(x => x.OrderId).Select(g => g.OrderByDescending(x => x.Date).FirstOrDefault()).Select(x => x.Date));

Upvotes: 1

Views: 241

Answers (1)

StriplingWarrior
StriplingWarrior

Reputation: 156459

If you want the order information along with its last status, this is the general structure to use:

orders
  .Select(x => new
    {
      order = x,
      lastStatus = x.OrderStatus
        .Where(x => x.Status.Id >= 0001 && x.Status.Id <= 1000)
        .OrderByDescending(x => x.Date)
        .FirstOrDefault()
    })

If you literally only want the last status per order, you can probably go straight to the order status table, something like this:

context.OrderStatuses
  .Where(s => s.Id >= 0001 && s.Id <= 1000)
  .GroupBy(s => s.OrderId)
  .Select(g => g.OrderByDescending(x => x.Date).FirstOrDefault())

Upvotes: 1

Related Questions