Linq: dynamic Where clause inside a nested subquery get latest records of each group

Question

I currently get problem to dynamic linq expression below

My Models

public class Orders
{
    public int OrderId ;
    public ICollection<OrderStatuses> Statuses;
}  

public class Statuses
{
    public int StatusId;
    public int OrderId;
    public string Note;
    public DateTime Created;
}

My Sample data :

Orders
|  ID   | Name       |
----------------------
|   1   | Order 01   |  
|   2   | Order 02   | 
|   3   | Order 03   | 

Statuses
|ID | OrderId | Note      | Created   |
---------------------------------------
| 1 |  1      | Ordered   | 2016-03-01|
| 2 |  1      | Pending   | 2016-04-02|
| 3 |  1      | Completed | 2016-05-19|
| 4 |  1      | Ordered   | 2015-05-19|
| 5 |  2      | Ordered   | 2016-05-20|
| 6 |  2      | Completed | 2016-05-19|
| 7 |  3      | Completed | 2016-05-19|

I'd like to get number of orders which have note value equal to 'Ordered' and max created time. Below is sample number of orders that I expect from query

| Name     |  Note    |  Last Created|
-------------------------------------|
| Order 01 |  Ordered | 2016-03-01   | 
| Order 02 |  Ordered | 2016-05-20   |

Here my idea but it's seem to wrong way

var outer = PredicateBuilder.True<Order>();

var orders   = _entities.Orders
                        .GroupBy(x => x.OrderId)
                        .Select(x => new { x.Key, Created = x.Max(g => g.Created) })
                        .ToArray();

var predicateStatuses = PredicateBuilder.False<Status>();

foreach (var item in orders)
{
    predicateStatuses = predicateStatuses.Or(x => x.OrderId == item.Key && x.Created == item.Created);
}
var predicateOrders = PredicateBuilder.False<JobOrder>();

predicateOrders = predicateOrders.Or(predicateStatuses); (I don't how to passed expression which different object type (Order and Status) here or I have to write an extension method or something

outer = outer.And(predicateOrders);

Please suggest me how to solve this dynamic linq expression in this case. Thanks in advance.

Answer 1

There's nothing dynamic about your query, at least, it doesn't need to be. You can express it as a regular query.

var query =
    from o in db.Orders
    join s in db.Statuses on o.Id equals s.OrderId
    where s.Note == "Ordered"
    orderby s.Created descending
    group new { o.Name, s.Note, LastCreated = s.Created } by o.Id into g
    select g.First();

p.s., your models doesn't seem to match the data at all so I'm ignoring that. Adjust as necessary.