Linq to Entities subquery to fill array?

Question

New at linq to entities 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 so:

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; }
    }
}

Linq query is like this:

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 I have '??', can I use a subquery or something to get a list of Order_Id's from the CustomerOrders table? Right Now, I can only think to loop through the Customers table once it is filled and then query the DB again to get each array of Order Id's for each Customer.

Answer 1

If it's not a requirement, drop the "Row" collection from the "Customer" object. This should suffice:

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

Then you can query like this:

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's better to deal in objects when writing C# and using EF, than to deal in terms of tables and rows -less confusing.

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()
};

Where you have .Select(row => row.Key.ToString()) you can set the property you need (Key, Name, etc...). Select method is an extension method to IEnumerable and it return a collection of type of property you have seted, in this case, a collection of strings because I converted it with ToString() method.