LINQ To SQL SubSelect Like Query

Question

Imagine there are two tables.

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

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

An order can have more than one OrderStatus, which can be called OrderStatusHistory. I will have a StronglyTypeObject Order , which will look like this

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

This StatusId in the Order object must be the current (last) StatusId from the OrderStatus table.

I tried to create an IQueryable List of objects with LINQ. Here's mine, doesn't work;), 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 also tried working with Max (p => p.XXX), but it did not work. Does anyone have a hint of this problem?

Any help would be greatly appreciated ...

Gordon

Answer 1

, First, OrderByDescending , .

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
         }

, FK, StatusId . , , Last ( ), LINQtoObjects, LINQToSQL. YMMV.

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

ORDER, .

var currentStatus = order.CurrentStatus;

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

Answer 2

, : ( , .. 1)

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.OrderID)
                                 .Select(p => p.StatusId)
                                 .FirstOrDefault()
         }