Linq Group by, Concatenate and load child entities on a list

Question

I'm going to simplify a little bit my model in order to show what I want to do. I know perfectly how do that using SQLbut I'm having problems with EF and LINQ.

Sorry for the long post.

I have the next POCO's:

public class Order
{
   public int Id {get; set;}
   public double Quantity {get; set;}   
   public List<Status> StatusChanges{get; set;}
}
public class Status
{
   public int Id {get; set;}
   public DateTime StatusDate {get; set;}
   public StatusType StatusType  {get; set;}
}
public class StatusType 
{
   public int Id {get; set;}
   public string Code {get; set;}
   public string Description {get; get;}
}

An Order have multiple Status and one Status have only one StatusType

My questions:

1) How to select in a LINQ query a list containg all the orders with the loaded StatusType

I try with

var results= (from o in db.Orders
            select new {o.Id,o.Quantity,o.StatusChanges})
            .Include(o=>o.StatusChanges.Select(s=>s.StatusType));

But that's not that I want, I want a list which contains, Order, Status and StatusType (even if the Order info is repetead) I know I can make a double foreach but I would like to know if is a single query way (or a double query one to the db and another using linq to entities)

2) How make a list which contains the Order Id and the Codes of all the status concatenated. Example

Orders:

Id    Quantity
1       2

Status

Id  StatusDate   StatusTypeId
1    01-10-2014       1
2    02-10-2014       2

StatusType

Id   Code  Description
1      E    Entered
2      C    Confirmed
3      D    Deleted

Result:

OrderId  Quantity    StatusResume
1         2           E,C

I know I can use this question but I don't know how have access to the loaded elements in the list.

score 4 · Answer 1 · answered Jan 10 '14 at 20:30

The first one from Ilija Dimov's answer should work. The second one is a little bit more tricky, because Aggregate extension method is not supported by LINQ to Entities. That's why you have to download necessary data to memory and perform string concatenation in-memory as LINQ to Objects query.

var results = (from o in db.Orders
               select new
               {
                   o.Id,
                   o.Quantity,
                   o.StatusCodes.Select(c => c.StatusType.Code)
               }).AsEnumerable()
                 .Select(x => new {
                            x.Id,
                            x.Quantity, 
                            StatusResume = string.Join(',', x.StatusCodes)
                         }).ToList();
                   }).ToList();

Can you please help me with this linq query:http://stackoverflow.com/questions/38120664/how-to-group-by-on-2-child-entities-and-get-total-of-both-this-child-entities — I Love Stackoverflow, Jun 30 '16 at 12:53

Ilija Dimov · Accepted Answer · 2014-01-10T20:42:25.067

Try the following queries:

1) How to select in a LINQ query a list containg all the orders with the loaded StatusType

var results = (from o in db.Orders
               from s in o.StatusChanges
               select new
                   {
                       OrderId = o.Id,
                       Quantity = o.Quantity,
                       StatusId = s.Id,
                       StatusDate = s.StatusDate,
                       StatusCode = s.StatusType != null ? s.StatusType.Code : null
                   }).ToList();

2) How make a list which contains the Order Id and the Codes of all the status concatenated

var results = (from o in db.Orders
                       select new
                           {
                               o.Id,
                               o.Quantity,
                               StatusCodes =
                           o.StatusChanges.Where(c => c.StatusType != null).Select(c => c.StatusType.Code)
                           }).ToList().Select(x => new
                               {
                                   x.Id,
                                   x.Quantity,
                                   StatusResume = string.Join(",", x.StatusCodes)
                               }).ToList();

Sorry to say, but the second one won't work. `Aggregate` is not supported by LINQ to Entities. — MarcinJuraszek, Jan 10 '14 at 20:24
@MarcinJuraszek: You are right. Totally forgot that Aggregate is not supported by LINQ To Entities. Will update the answer. — Ilija Dimov, Jan 10 '14 at 20:35
Thanks both! I really don't know the trick of making subconsults and the possibility of making a select of a LINQ to Entities to make a new consult. — rlartiga, Jan 11 '14 at 21:16

Linq Group by, Concatenate and load child entities on a list

2 Answers2