How to JOIN a Sub-Query using LINQ

Question

I am new to Entity Framework and LINQ and am struggling to "convert" my SQL query to its syntax.

Given is a table that contains production orders with OrderNumber, Quantity, Material etc. as columns. Additionally there is a column Timestamp.

If the quantity of an order would be updated, this results in a completely new entry in the table where all values would be the same as a previous row except for Timestamp (indicating the time of the change) and Quantity (containing the new value). Thus allowing me to have a "history" of an order.

Selecting only the latest version of a specific order using SQL could look like this:

SELECT p1.*
FROM dbo.ProductionOrders as p1
  JOIN (SELECT OrderNumber, MAX(Timestamp) as Timestamp FROM dbo.ProductionOrders GROUP BY OrderNumber) as p2
    on p1.OrderNumber = p2.OrderNumber and p1.Timestamp = p2.Timestamp
WHERE p1.OrderNumber = 'order-182736'

The same functionality I got done in my C# code like this:

var productionOrder = _db.ProductionOrders.Where(po => po.OrderNumber == "order-182736")
                                          .OrderByDescending(po => po.Timestamp)
                                          .FirstOrDefault();

Where I am lost is when I want to get multiple rows. For example all orders for a specific material. Using SQL I only have to adjust the WHERE condition:

SELECT p1.*
FROM dbo.ProductionOrders as p1
  JOIN (SELECT OrderNumber, MAX(Timestamp) as Timestamp FROM dbo.ProductionOrders GROUP BY OrderNumber) as p2
    on p1.OrderNumber = p2.OrderNumber and p1.Timestamp = p2.Timestamp
WHERE p1.Material = 42

The examples I found for JOIN using LINQ don't operate on sub-queries (my main source is microsoft.com)

How can I get all latest "versions" of orders for a specific material using LINQ?

user_5202563 · Answer

You can do it the exact same way as in SQL. _db.ProductionOrders in LINQ to Entities represents the dbo.ProductionOrders in SQL, and you can use it in both top and subquery level:

var query = _db.ProductionOrders
    .Join(_db.ProductionOrders
        .GroupBy(p2 => new { p2.OrderNumber })
        .Select(g => new { g.Key.OrderNumber, Timestamp = g.Max(e => e.Timestamp) }), // subquery
        p1 => new { p1.OrderNumber, p1.Timestamp }, p2 => p2, // join condition
        (p1, p2) => p1) // result selector (p1.*)
    .Where(p1 => p1.Material == 42);

(Note: Not sure where Label is coming from in your SQL queries, so using OrderNumber instead).

or if you prefer, the same in LINQ query syntax:

var query =
    from p1 in _db.ProductionOrders
    join p2 in (from p2 in _db.ProductionOrders
                group p2 by new { p2.OrderNumber }) into g
                select new { g.Key.OrderNumber, Timestamp = g.Max(p2 => p2.Timestamp) })
    on new { p1.OrderNumber, p1.Timestamp } equals p2
    where p1.Material == 42
    select p1;

How to JOIN a Sub-Query using LINQ

1 Answers