Select rows with minumum date in join query

I am using entity Framework and LINQ two perform the join between these two tables:

var query =
        orders.Join(
        orderdetails,
        order => order.Code,
        orderdt => orderdt.Order.Code,            
        (order, orderdt)            
        => new
        {
            Code = order.Code,
            WarehouseDivision = orderdt.WarehouseDivision,
            Type = order.Type,
            SupplierCode = order.SupplierCode,
            SupplierDescription = order.SupplierDescription,
            ExpectedDeliveryDate = orderdt.ExpectedDeliveryDate
        });

the join works fine. Now for each join row I need to select the row with the minimum ExpectedDelivery Date. Any hint on how to achieve this?

Solution

I see something strange on your Join.

Every Order has a Code. Every OrderDetail has an Order, which also has a Code.

If there is a one-to-many relationship between Orders and OrderDetails, then every Order has zero or more OrderDetails. Every OrderDetail belongs to exactly one Order, namely the Order that the foreign key points to. Is that the same Order as OrderDetail.Order?

In that case Order.Code equals OrderDetail.Order.Code for every OrderDetail of this code. Not very useful to perform a join on these values.

I think that you mean to join on primary key and foreign key.

Either way, whether you join on keys or on property Code, the solution to your problem is to perform a GroupJoin instead of a Join.

If you've followed the entity framework code first conventions, you'll have something similar to this:

class Order
{
    public int Id {get; set;}    // Primary key
    ...                          // other Order properties

    // every Order has zero or more OrderDetails (one-to-many)
    public virtual ICollection<OrderDetail> OrderDetails {get; set;}
}

class OrderDetail
{
    public int Id {get; set;}
    public DateTime ExpectedDeliveryDate {get; set;}
    ...                          // other OrderDetail properties

    // every OrderDetail belongs to exactly one Order, using foreign key
    public int OrderId {get; set;}
    public virtual Order Order {get; set;}
}

It might be that you used different identifiers for your properties, but the main thing are the virtual properties.

In entity framework the non-virtual properties represent the columns in the table, the virtual properties represent the relations between the tables (one-to-many, many-to-many).

Requirement

From every Order, give me some properties, together with some properties of the OrderDetail with the minimum ExpectedDeliveryDate.

Solution with GroupJoin

GroupJoin on primary / foreign key:

var OrdersWithOldestOrderDetail = dbContext.Orders  // GroupJoin Orders 
    .GroupJoin(dbContext.OrderDetails,              // with OrderDetails
    order => order.Id,                              // from every Order take the Id
    orderDetail => orderDetail.OrderId,             // from every OrderDetail take the OrderId
    (order, orderDetailsOfThisOrder) => new         // take the Order with all its matchin OrderDetails
    {                                               // to make one new
        // Select the Order properties that you plan to use:
        ...

        // Select the OrderDetail with the minimum ExpectedDeliveryDate
        // To do this, order by ascending deliveryDate
        // then Select the properties you want to used
        // and take the FirstOrDefault
        EarliestDeliveredOrderDetail = orderDetailsOfThisOrder
           .OrderBy(orderDetail => orderDetail.ExpectedDeliveryDate)
           .Select(orderDetail => new
           {
               ...
           })
           .ToList(),
    });

If you really want to join on the properties you said:

var result = dbContext.Orders                  // GroupJoin Orders 
    .GroupJoin(dbContext.OrderDetails,         // with OrderDetails
    order => order.Code,                       // from every Order take the Code
    orderDetail => orderDetail.Order.Code,     // from every OrderDetail take Order.Code
    (order, orderDetailsWithThisCode) => new   // take the Order with all its matchin OrderDetails
    {   
        ... etc, same as above

Easier Solution using the ICollection

If you are using entity framework and you need to select "All orders with its OrderDetails", "All Schools with its Students", "All Products with its Components", in fact: all Items together with all related sub-items, it is usually way simpler to use the ICollections instead of executing a join yourself:

var OrdersWithOrderDetails = dbContext.Orders.Select(order => new
{
    // Select the Order properties that you plan to use:
    ...

    EarliestDeliveredOrderDetail = order.OrderDetails
        .OrderBy(orderDetail => orderDetail.ExpectedDeliveryDate)
        .Select(orderDetail => new
        {
            // Select the OrderDetail properties that you plan to use:
            ...
        })
        .FirstOrDefault(),
   })

See how naturally it feels if you use the ICollections instead of the (Group-)join? Entity framework knows your relations and performs the correct join for you with the correct (foreign) keys.

In fact, I seldom create a Join myself. Most of the time I use the virtual properties.