CODEWITHSUNDEEP
c#entity-frameworkgroup-bylinq-method-syntax
Masoud
Masoud

Reputation: 8171

GroupBy with Linq to entities query

I have following classes:

public class OrderItem
{
     public int Id { get; set; }
     public ICollection<NominalRouting> NominalRoutings{ get; set; }
}
public class NominalRouting
{
     public int Id { get; set; }
     public DateTime PlanedDate {get; set;} //yyyy/mm/dd
     public virtual Operation Operation{ get; set; }
}
public class Operation 
{
     public int Id { get; set; }
     public string Code { get; set; }
     public virtual AreaSpecification AreaSpecification{ get; set; }
}
public class AreaSpecification 
{
     public int Id { get; set; }
     public string Title { get; set; }
}

I have following data:

-----------------------------------------------------------
| OrderItemId |  AreaTitle | Operation Code | PlannedDate |
-----------------------------------------------------------
|      1      |    Area1   |       OP1       | 2016/01/01 |
|      1      |    Area1   |       OP2       | 2016/01/02 |
|      1      |    Area1   |       OP3       | 2016/01/03 |
|      1      |    Area2   |       OP4       | 2016/02/01 |
|      1      |    Area2   |       OP5       | 2016/02/02 |
|      1      |    Area3   |       OP6       | 2016/03/01 |
|      1      |    Area3   |       OP7       | 2016/03/04 |
|      1      |    Area3   |       OP7       | 2016/03/08 |
-----------------------------------------------------------

How can I write a linq to entities(method syntax) query using EF code first, to GroupBy above data by AreaTitle and get following result(earlear PlannedDate in each AreaTitle)?:

-----------------------------------------------------------
| OrderItemId |  AreaTitle | Operation Code | PlannedDate |
-----------------------------------------------------------
|      1      |    Area1   |       OP1       | 2016/01/01 |
|      1      |    Area2   |       OP4       | 2016/02/01 |
|      1      |    Area3   |       OP6       | 2016/03/01 |
-----------------------------------------------------------

Upvotes: 0

Views: 2171

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205549

I don't see anything special - group, order each group elements and take first.

Query syntax would be much cleaner, but here is the method syntax query as you wish:

var query = db.OrderItems
    .SelectMany(orderItem => orderItem.NominalRoutings)
    .GroupBy(routing => routing.Operation.AreaSpecification.Title)
    .Select(g => new
    {
        AreaTitle = g.Key,
        Routing = g.OrderBy(e => e.PlanedDate).FirstOrDefault()
    })
    .Select(e => new
    {
        e.Routing.OrderItem.Id,
        e.AreaTitle,
        e.Routing.Operation.Code,
        e.Routing.PlanedDate
    });

Upvotes: 2

MakePeaceGreatAgain
MakePeaceGreatAgain

Reputation: 37000

What about this:

var result = myList.GropuBy(x => x.AreaTitle).Select(x => 
    {
        var first = x.OrderBy(y => y.PlannedDate).First();
        return new 
        { 
            OrderItemId = first.OrderItemId,
            AreaTitle = x.Key,
            OperationCode = first.Operation Code,
            PlanedDate = first.PlanedDate 
        }
    });

Upvotes: 1

Related Questions