Linq to Entities subquery to populate an array?

Question

New in linq for objects trying to figure this out. I have the following tables:

Customer: Cust_Id, Name

Orders: Order_Id

CustomerOrders: Cust_Id, Order_Id

I have a class like:

 public class Customers { public List<Row> Rows { get; set; } public Customers() { Rows = new List<Row>(); } public class Row { public int Key { get; set; } public string Name { get; set; } public List<string> Order_Ids { get; set; } } } 

The Linq query is as follows:

 var query = from c in context.Customer select new Customers.Row { Key = c.Cust_Id, Name = c.Name, Order_IDs = List<string>( ?? ) }; foreach (var row in query) { Customers.Rows.Add(row); } var serializer = new JavaScriptSerializer(); return serializer.Serialize(Customers); 

Where am I ??? can I use a subquery or something to get the Order_Id list from the CustomerOrders table? Right now, I can only think to go through the Customers table after filling it out, and then query the DB again to get each Order Id array for each Client.

Answer 1

If this is not required, drop the Row collection from the Client object. That should be enough:

 public class Customer { public int Key { get; set; } public string Name { get; set; } public List<string> Order_Ids { get; set; } } 

Then you can request the following:

 var customers = from c in context.Customers select new Customer { Key = c.Cust_Id, Name = c.Name, Order_IDs = c.Orders.Select(o => o.Order_Id).ToList() }; 

It is better to deal with objects when writing C # and using EF than in terms of tables and rows without error.

Answer 2

Try something like this:

 var query = from c in context.Customer select new Customers.Row { Key = c.Cust_Id, Name = c.Name, Order_Ids = c.Rows.Select(row => row.Key.ToString()).ToList() }; 

If you have .Select(row => row.Key.ToString()) , you can set the required property (key, name, etc.). The select method is an extension method for IEnumerable , and it returns a collection of the type of the property you set, in this case a rowset because I converted it using the ToString() method.