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.

score 1 · Accepted Answer · answered May 27 '16 at 04:26

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.

answered May 27 '16 at 04:26

Jeff Mercado

129,526
32
251
272

Thanks so much for your answer .Actually, I forgot name property and I just created sample model for demonstration what my problem. In my code, I already implemented the dynamic Linq so I'm still looking for the solution on dynamic Linq – nvtthang May 27 '16 at 04:46
I found http://stackoverflow.com/questions/418609/how-to-do-subquery-in-linq reference my question – nvtthang May 27 '16 at 08:24

score 0 · Answer 2 · answered May 30 '16 at 03:18

Thanks so much for @Jeff Mercado answer. Finally, I customized your answer to solve my problem below:

var predicateStatuses = PredicateBuilder.False<Order>();
predicateStatuses = predicateStatuses.Or(p => (
                                        from j in db.Statuses 
                                        where j.OrderId == p.ID
                                        group j by j.OrderId into g
                                        select g.OrderByDescending(t=>t.Created)
                                        .FirstOrDefault()
                                    ).FirstOrDefault().Note == 'Ordered'
                                );

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

2 Answers2