Linq select a record based on child table data

Question

Trying to select orders based on their current status which is stored in a another table. Keep getting all orders instead of orders filtered by current status. When status is not empty, the query should filter based on the latest status of the order which I think should be status in descending order of date of record taking the first status.

private IQueryable<order_tbl> CreateQuery(string status, int? orderId, DateTime? orderDate) {
    var query = from o in context.order_tbl select o;

    if (orderId.HasValue) query = query.Where(w => w.id.Equals(orderId.Value));

    if (orderDate.HasValue) query = query.Where(w => w.OrderDate.Equals(orderDate.Value));

    if (!string.IsNullOrEmpty(status)) {
        query = (from q in query
                from s in q.order_status
                .OrderByDescending(o => o.DateStatusUpdated)
                .Take(1)
                .Where(w => w.Status.Equals(status))
                select q);
    }

    return query;
}

There are more fields in the tables which I omitted for brevity.

order_tbl

id    date    customerId
1    2/1/2018      6
2    2/3/2018      5
3    2/6/2018      3

order_status

id    orderId    DateStatusUpdated   status
1        1         2/1/2018           open
2        1         2/2/2018           filled
3        2         2/3/2018           open
4        2         2/4/2018           filled
5        3         2/6/2018           open

When searching only on 'open', the query will return orders 1,2,3 instead of just order 3. What is wrong with the query on the status?

Answer 1

This answer pointed me in the right direction, LINQ Query - Only get Order and MAX Date from Child Collection

Modified my query to the below.

if (!string.IsNullOrEmpty(status)) {
{
    query = query
            .SelectMany(s => s.order_status
                              .OrderByDescending(o => o.DateStatusUpdated)
                              .Take(1)
                       )
            .Where(w => w.Status.Equals(status))
            .Select(s => s.order_tbl);
}