translate first_value over partition by to linq
<p>I am new to LINQ and it's still challenge to learn for people like me who is very used to normal SQL query.</p>

<p>I have the below query in SQL using sub query for left join and SQL server standard function like FIRST_VALUE, can this be translated into LINQ?
Not sure how can this first_value function can be re-written in LINQ..</p>

<pre><code>SELECT *
FROM dbo.Stock

LEFT JOIN (

    SELECT distinct stockID, FIRST_VALUE(unitPrice) OVER (PARTITION BY stockid ORDER BY dbo.SalesOrder.orderDate desc ) last_unit_cost,
                             FIRST_VALUE(orderDate) OVER (PARTITION BY stockid ORDER BY dbo.SalesOrder.orderDate desc ) last_order_date

    FROM dbo.SalesOrderDetail 
    JOIN dbo.SalesOrder ON SalesOrder.salesOrderID = SalesOrderDetail.salesOrderID 
    WHERE customerID = 4
) x ON x.stockID = Stock.stockID
</code></pre>