LINQ To SQL SubSelect Like Query

Question

imagine there are two tables.

Order
+----------------+
| ID             |
| Name           |
+----------------+

OrderStatus
+----------------+
| ID             |
| OrderId        |
| StatusId       |
+----------------+

A Order can have more than one OrderStatus, which could be called OrderStatusHistory. I'll have an StronglyTypeObject Order, which is descripted as follows

namespace my.project
{
    public class Order
    {
        Int64 OrderId { get; set; }
        String Name { get; set; }
        Int64 StatusId { get; set; }
    }
}

This StatusId in the Order Object is meant to be the current (last) StatusId from the OrderStatus Table.

I have tried to build a IQueryable List of Objects with LINQ. Here is my, not working ;), Linq Code

var result = from r in dbContext.ORDER
             select new Order
             {
                 OrderId = r.ID,
                 Name = r.Name,
                 StatusId = dbContext.OrderStatus
                            .Where(p => p.OrderId == r.ID).Last().StatusId
             }

I have also tried working with Max(p=>p.XXX) but it hasn't worked out. Does anyone has a hint on this problem?

Any Help would be much appreciated...

Gordon

Answer 1

Based on your comment, I've updated the below to use First and, in that case, you will need to do an OrderByDescending on the key to get it in the right order.

var result = from r in dbContext.ORDER
         select new Order
         {
             OrderId = r.ID,
             Name = r.Name,
             StatusId = dbContext.OrderStatus
                        .Where(p => p.OrderId == r.ID)
                        .OrderByDescending( p => p.ID )
                        .First()
                        .StatusId
         }

Also, if you have a FK relationship defined, it should be much easier to get the last StatusId without creating an intermediate object. In this case, I think you can use Last (if the objects are preloaded) since you'll be doing LINQtoObjects, not LINQToSQL. YMMV.

var currentStatus = order.OrderStatuses.Last().StatusId;

The latter could be added as a method on a partial class for ORDER so that you can refer to it as.

var currentStatus = order.CurrentStatus;

public partial class ORDER
{
     public int64 CurrentStatus
     {
         get
         {
             return this.OrderStatuses.Last().StatusId;
         }
     }
}