SelectMany in Linq to entity

Question

I was looking at some examples in microsoft site about linq and I see an example that I need to modify!

http://msdn.microsoft.com/en-us/vcsharp/aa336758.aspx#SelectManyCompoundfrom3

public void Linq16() { List customers = GetCustomerList();

var orders =
    from c in customers
    from o in c.Orders
    where o.OrderDate >= new DateTime(1998, 1, 1)
    select new { c.CustomerID, o.OrderID, o.OrderDate };

ObjectDumper.Write(orders);

}

Insted of having a select that retrives the CustomerID, OrderID and OrderDate I want to select the CustomerID and a System.Collection.Generic.List that contains all the orders for that user! Essentially I want to group my orders by CustomerID, but i noticed that linq to entity does not allow a .ToList(object) inside the select.

I want something like this... List customers = GetCustomerList();

var orders =
    from c in customers
    from o in c.Orders
    where o.OrderDate >= new DateTime(1998, 1, 1)
    select new xpto
    {
      TheCostumerID = c.CustomerID, 
      CostumerOrders = o.Select(i=>i.OrderID).ToList(),
    };

...but the .ToList() is a big problem, at least for me.

I'm trying to find out the solution for that but so far I have acomplished nothing!

Please help me.

Answer 1

Have you had a chance to group by clause?

 from orders in context.Orders
 group orders by orders.CustomerID into ordersGroup
 select new { CustomerID = ordersGroup.Key, 
              Orders = ordersGroup };

Let me know if it is not what you were looking for.

Answer 2

This is not exactly what I am looking for. I realy need to generate a list of objects inside the select because in the project that I am working I am trying to use the select to create the final object that i need. The code is executed faster and its cleaner that way. I use the select to fill my personal object to be used in the user interface and one of those is composed, between other things, by a List. Its something like this:

public class objectABC{

public objectABC() { }

....

public Guid FirstProperty { get; set; }

public String SecondProperty { get; set; }

public List OtherProperty { get; set; }

.... }

Inside the select I am trying to fill the "OtherProperty " (that is a List of ObjectXPTO), i am returning the information using entity framework from Microsoft, something like this...

var sel = from ent in entity.ObjectToRetriveInfo select new objectABC() { FirstProperty = ent.FirstProperty,

SecondProperty = ent.SecondProperty,

OtherProperty = (from entXPTO in ent.TheOtherInfo select new ObjectXPTO() {

 PropA = entXPTO.PropA,

 PropB = entXPTO.PropB,

 }
 ).ToList()

};

when I compile the project everything works fine but when I Debug the project I get an exception because the linq query cannot be translated to a sql query. And the problem is in the .ToList(). I have no more ideas to work around that problem, I realy appreciate some help.

Answer 3

Try:

var orders = (from c in customers from o in c.Orders where o.OrderDate >= new DateTime(1998, 1, 1) select new xpto { TheCostumerID = c.CustomerID,
CostumerOrders = o.Select(i=>i.OrderID) }).ToList();

Answer 4

My Problem (I believe) is with linq to entity, for example if i execute the following code...

var selection = (from ent in entity.EntityObject select new MyObject() { PropA = ent.FirstProp, PropB = ent.SecondProp, ProcC = new List( (from entXpto in ent.OtherObject select new ObjectXPTO() { } ) )
} );

...I will get the following exception --> "Only parameterless constructors and initializers are supported in LINQ to Entities." The problem is that PropC is a List and I want to construct that list Inside de first select, so far I have fail.

I have try to change the code for something like this....

var selection = (from ent in entity.EntityObject select new MyObject() { PropA = ent.FirstProp, PropB = ent.SecondProp, PropC = (from entXpto in ent.OtherObject select new ObjectXPTO() { }).ToList() });

... but stil got an exception --> "LINQ to Entities does not recognize the method 'System.Collections.Generic.List1[ObjectXPTO] ToList[ObjectXPTO](System.Collections.Generic.IEnumerable1[ObjectXPTO])' method, and this method cannot be translated into a store expression."

I have no idea how to work arownd that problem!